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.