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.– Ahmad AbuhasnaOct 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– JeevithaOct 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– JeevithaOct 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– JeevithaOct 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– Ahmad AbuhasnaNov 1, 2015 at 8:37
|
Show 4 more comments
2 Answers
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.
answered Dec 6, 2015 at 21:04
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 !!
-
Set that to
1. This will avoid being killed by OOM. Meanwhile, swapping will be minimized. Oct 1, 2017 at 15:11