Bug #73498 Mysql Slave memory Usage is High and increasing
Submitted: 7 Aug 2014 11:12 Modified: 13 Nov 2014 8:28
Reporter: Naheem Munir Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.18-enterprise-commercial-advanced-lo OS:Linux (Red Hat Enterprise Linux Server release 6.3 (Santiago))
Assigned to: CPU Architecture:Any
Tags: 5.6.18, mysql memory issue

[7 Aug 2014 11:12] Naheem Munir
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.
[13 Aug 2014 9:07] Naheem Munir
Yesterday performed MySQL restart on slaves as they were hitting around 96%.

This morning they are on around 80%.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27237 mysql     20   0 60.5g  56g 7020 S  1.0 80.2 426:26.13 mysqld

 free -m
             total       used       free     shared    buffers     cached
Mem:         72507      71406       1101          0        376      11617
-/+ buffers/cache:      59412      13095
Swap:         5119         27       5092
[13 Nov 2014 8:28] MySQL Verification Team
Thank you for the report.

Could not repeat this issue at my end with latest builds(5.6.21 and 5.6.23)
I strongly suspect this could be similar to the one reported in Bug #72610
Also, could you please take a look at the suggestions posted in Bug #68287 by Shane/James and see if that helps in your case too.

Thanks,
Umesh