1179239 - Check binary log settings for treeherder stage and production

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Ed Morley [:emorley]]

In bug 1179223 we've noticed that the bin logs for treeherder stage are a whopping 15-20x larger than those for production, even though:
1) Stage and prod are supposed to both have the same time window of logs (24hrs)
2) Stage and prod have similar DB throughputs:
Stage: https://rpm.newrelic.com/accounts/677903/applications/5585473/datastores#/overview/MySQL?value=calls_per_minute
Prod: https://rpm.newrelic.com/accounts/677903/applications/4180461/datastores#/overview/MySQL?value=calls_per_minute

ie:

Stage (treeherder1.stage.db.scl3):

bash-4.1$ du -ch treeherder1-bin.* | tail -n 1
222G    total


Prod (treeherder1.db.scl3):

bash-4.1$ du -ch treeherder1-bin.* | tail -n 1
12G     total


Now 222GB for stage is kinda crazy since the DBs themselves are only 57GB!

Is there something up with the binary log settings that could be causing this? Do you have any suggestions as to how we can work out why they are so large?

Thanks! :-)

Comment 1

[Ed Morley [:emorley]]

Bah default_security_groups--

Comment 2

[Sheeri Cabral [:sheeri]]

I checked and both treeherder1 and treeherder1.stage have the exact same settings running in the db:

mysql> show variables like '%bin%';
+-----------------------------------------+--------------------------------------+
| Variable_name                           | Value                                |
+-----------------------------------------+--------------------------------------+
| bind_address                            | *                                    |
| binlog_cache_size                       | 32768                                |
| binlog_checksum                         | CRC32                                |
| binlog_direct_non_transactional_updates | OFF                                  |
| binlog_format                           | MIXED                                |
| binlog_max_flush_queue_time             | 0                                    |
| binlog_order_commits                    | ON                                   |
| binlog_row_image                        | FULL                                 |
| binlog_rows_query_log_events            | OFF                                  |
| binlog_stmt_cache_size                  | 32768                                |
| innodb_api_enable_binlog                | OFF                                  |
| innodb_locks_unsafe_for_binlog          | OFF                                  |
| log_bin                                 | ON                                   |
| log_bin_basename                        | /var/lib/mysql/treeherder1-bin       |
| log_bin_index                           | /var/lib/mysql/treeherder1-bin.index |
| log_bin_trust_function_creators         | OFF                                  |
| log_bin_use_v1_row_events               | OFF                                  |
| max_binlog_cache_size                   | 18446744073709547520                 |
| max_binlog_size                         | 1073741824                           |
| max_binlog_stmt_cache_size              | 18446744073709547520                 |
| sql_log_bin                             | ON                                   |
| sync_binlog                             | 0                                    |
+-----------------------------------------+--------------------------------------+
22 rows in set (0.00 sec)

Comment 3

[Ed Morley [:emorley]]

Thank you for checking :-)