0

I have used top command to check memory usage on both mysql master and slave server. Master is using only 12% of allotted memory but slave is using 82%. configuration wise both are same. can anyone tell me how to resolve this and why its taking more than slave?

9
  • please add more information about slave and master and there cnf, also server status. Oct 29, 2015 at 13:05
  • Hi Ahmad, Here is the details which you have asked. Master status: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 36694 mysql 20 0 4571028 1.851g 5204 S 17.6 11.8 102090:30 mysqld
    – Jeevitha
    Oct 30, 2015 at 5:05
  • Slave Status: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 28034 mysql 20 0 14.825g 0.013t 11272 S 2.3 83.0 2475:49 mysqld
    – Jeevitha
    Oct 30, 2015 at 5:06
  • Total Memory : 16G Buffers alloted for both M and S: key_buffer_size = 64M innodb_buffer_pool_size = 1G join_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M query_cache_type = 1 query_cache_limit = 20M query_cache_min_res_unit = 4096 query_cache_size = 200M
    – Jeevitha
    Oct 30, 2015 at 5:07
  • sorry for the delay. did not get chance to check the website. since the slave and master has same amount of resources and shared same cnf into ( memory info). there is one attribute you can add to slave ( I believe its 5.6.x) table_cache (> 4096) this should lower the memory usage Nov 1, 2015 at 8:37

2 Answers 2

1

query_cache_size = 200M

Too big. Don't go past 50M, else the maintenance will hurt performance.

innodb_buffer_pool_size = 1G

If you are using InnoDB, set this to 70% of available RAM.

table_open_cache (formerly called table_cache) relates to the number of tables you might need to open, but not the sizes of the tables. A few hundred is typically enough. 4096 is needed by only a few systems; don't go past that without a clear indication that you need it.

All the activities are happening at master

If so, what is the Slave for?

Please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (on each server) so I can help you dig deeper into your puzzle.

1

Had a similar problem,were we had same mysql configuration on master and slave.

Here it is, i encountered an issue once when i was checking TOP output for mysqld process and found that the mysqld memory continuously increasing. Also there was one more thing that it was using swap memory even if there was enough RAM memory available. we have monitored for a week and this was causing an issue like slow query processing, performance and slowness on DB server.

To solve this problem we have to set correct swappiness

To check vm.swappiness:

  cat /proc/sys/vm/swappiness    (Default value : 60 (Range 0 to 100))

To set a new non-persistent value :

   sysctl -w vm.swappiness=0

To set a new persistent value :

   add vm.swappiness=0 in the /etc/sysctl.conf file

All Set !!

1
  • Set that to 1. This will avoid being killed by OOM. Meanwhile, swapping will be minimized.
    – Rick James
    Oct 1, 2017 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.