We have a typical master-slave set up. When we find ourselves under considerable load, the master grinds to a halt. The slave continues to march along all happy like.
So during a recent slow-down, I took a VERY simple query (no joins, nicely indexed, not many results) and ran it on the slave and master, with profiling turned on.
Notice how different the results of the profiling are.
Master
Slave
I fear someone might tell me: "Of course your database will perform worse when it's under heavy load." And to them, I say: "You're right!"
But I am really curious if the results from the profile would suggest anything like:
- Your DB server needs more memory, or
- It's writing to disk too much, or
- Change this setting and everything will be fixed, or
- ...
EDIT 1 The following graph shows how queries slow down when we're having a problem. The gold line is average write time and the green line is average read time. As you can see writes get really slow. The average write peaks at around 500 ms. When things are running smoothly, average writes peaks are down around 2ms.
SELECT VERSION();
2) Is all your data InnoDB ? 3) Please runSHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%'
and post the results above. 3) Is the DB Server bare metal or VM ? 4) How much RAM on the DB Server ? 5) How many cores (or virtual cores) ? – RolandoMySQLDBA Sep 30 '14 at 14:415.1.73-log
. All data is INNODB.innodb_buffer_pool_size = 4294967296
. The DB has 48 gigs of RAM and 16 cores. DB server is bare metal. – Alan C. Sep 30 '14 at 15:04SELECT SUM(data_length+index_length) Total_InnoDB_Data_Indexes FROM information_schema.tables WHERE engine='InnoDB';
– RolandoMySQLDBA Sep 30 '14 at 17:11