Description:
We have one master 2 slave setup using 5.6.18 Enterprise edition. All are dedicated servers running on VMware.
On both slaves memory utilization by MySQL process is above 90% consistently and gradually increasing.
When ran Top on Server results were as following
top - 12:49:08 up 21 days, 23:42, 3 users, load average: 0.49, 0.54, 0.61
Tasks: 226 total, 1 running, 225 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.2%us, 0.7%sy, 0.0%ni, 96.0%id, 2.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 74248312k total, 73714112k used, 534200k free, 277980k buffers
Swap: 5242872k total, 1389504k used, 3853368k free, 2988820k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12661 mysql 20 0 70.8g 65g 6708 S 8.3 93.1 5056:14 mysqld
mysqld process %MEM keeps going up.
To check database activity at the time did show processlist
| 84 | service_agent | localhost | NULL | Sleep | 46 | | NULL |
| 119212 | system user | | NULL | Connect | 73917 | Waiting for master to send event | NULL |
| 119213 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 126691 | defgh | 0.0.0.0 | XXXXXXx | Sleep | 10409 | | NULL |
| 127946 | root | localhost | NULL | Query | 0 | init | show processlist |
| 127949 | abcd | 0.0.0.0 | xxxx | Query | 2 | Sending data | SELECT
there is no significant activity on database.
slave setting are :
mysql> show variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name | Value |
+------------------------------+-----------------------+
| init_slave | |
| log_slave_updates | ON |
| log_slow_slave_statements | OFF |
| pseudo_slave_mode | OFF |
| rpl_stop_slave_timeout | 31536000 |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp_mysql |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+------------------------------+-----------------------+
Rely and Bin Log info setting are :
show variables like '%info%';
+---------------------------+----------------+
| Variable_name | Value |
+---------------------------+----------------+
| master_info_repository | TABLE |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| sync_master_info | 1 |
| sync_relay_log_info | 10000 |
+---------------------------+----------------+
free memory on server:
free -m
total used free shared buffers cached
Mem: 72508 72013 494 0 272 2946
-/+ buffers/cache: 68795 3712
Swap: 5119 1356 3763
buffer setting from my.cnf
cat my.cnf |grep buff
innodb_buffer_pool_instances = 29
innodb_buffer_pool_size = 52G
innodb_log_buffer_size = 8M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
sort_buffer_size = 64M
join_buffer_size = 80M
key_buffer_size = 200M
read_buffer_size = 8M
other setting from my.cnf related to replication
binlog-format = MIXED
gtid-mode = on
enforce-gtid-consistency
How to repeat:
setup master and slave replication on 5.6.18 enterprise edition using above values.
Suggested fix:
None.
Description: We have one master 2 slave setup using 5.6.18 Enterprise edition. All are dedicated servers running on VMware. On both slaves memory utilization by MySQL process is above 90% consistently and gradually increasing. When ran Top on Server results were as following top - 12:49:08 up 21 days, 23:42, 3 users, load average: 0.49, 0.54, 0.61 Tasks: 226 total, 1 running, 225 sleeping, 0 stopped, 0 zombie Cpu(s): 1.2%us, 0.7%sy, 0.0%ni, 96.0%id, 2.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 74248312k total, 73714112k used, 534200k free, 277980k buffers Swap: 5242872k total, 1389504k used, 3853368k free, 2988820k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 12661 mysql 20 0 70.8g 65g 6708 S 8.3 93.1 5056:14 mysqld mysqld process %MEM keeps going up. To check database activity at the time did show processlist | 84 | service_agent | localhost | NULL | Sleep | 46 | | NULL | | 119212 | system user | | NULL | Connect | 73917 | Waiting for master to send event | NULL | | 119213 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 126691 | defgh | 0.0.0.0 | XXXXXXx | Sleep | 10409 | | NULL | | 127946 | root | localhost | NULL | Query | 0 | init | show processlist | | 127949 | abcd | 0.0.0.0 | xxxx | Query | 2 | Sending data | SELECT there is no significant activity on database. slave setting are : mysql> show variables like '%slave%'; +------------------------------+-----------------------+ | Variable_name | Value | +------------------------------+-----------------------+ | init_slave | | | log_slave_updates | ON | | log_slow_slave_statements | OFF | | pseudo_slave_mode | OFF | | rpl_stop_slave_timeout | 31536000 | | slave_allow_batching | OFF | | slave_checkpoint_group | 512 | | slave_checkpoint_period | 300 | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp_mysql | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_parallel_workers | 0 | | slave_pending_jobs_size_max | 16777216 | | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN | | slave_skip_errors | OFF | | slave_sql_verify_checksum | ON | | slave_transaction_retries | 10 | | slave_type_conversions | | | sql_slave_skip_counter | 0 | +------------------------------+-----------------------+ Rely and Bin Log info setting are : show variables like '%info%'; +---------------------------+----------------+ | Variable_name | Value | +---------------------------+----------------+ | master_info_repository | TABLE | | relay_log_info_file | relay-log.info | | relay_log_info_repository | TABLE | | sync_master_info | 1 | | sync_relay_log_info | 10000 | +---------------------------+----------------+ free memory on server: free -m total used free shared buffers cached Mem: 72508 72013 494 0 272 2946 -/+ buffers/cache: 68795 3712 Swap: 5119 1356 3763 buffer setting from my.cnf cat my.cnf |grep buff innodb_buffer_pool_instances = 29 innodb_buffer_pool_size = 52G innodb_log_buffer_size = 8M innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 sort_buffer_size = 64M join_buffer_size = 80M key_buffer_size = 200M read_buffer_size = 8M other setting from my.cnf related to replication binlog-format = MIXED gtid-mode = on enforce-gtid-consistency How to repeat: setup master and slave replication on 5.6.18 enterprise edition using above values. Suggested fix: None.