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?
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?
SHOW VARIABLES; and
SHOW GLOBAL STATUS; (on each server) so I can help you dig deeper into your puzzle.
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 !!