Mysql – Near identical MySQL deployments behaving very different – High CPU Usage problem

cpuloadMySQL

So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text stored in MySQL tables.

Four of them have no problems what so ever. One is struggling a LOT under the same or somewhat less load than the other four.

I cannot understand why this is.

Things I've done so far:

  1. Checked slow queries. All queries uses indexes and are in the realm of 0.0008 Sec execution time i.e. very fast
  2. I've noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT, so much so, I've turned off the UPDATE's that were there, for this instance. Bear in mind that these UPDATE's doesn't cause a blip on the other servers.
  3. Tried to eliminate external factors i.e. noisy neighbours (moved host) etc.

Worth noticing is that the machines are deployed the same i.e. a vanilla Debian 10 installation with a LEMP stack, nothing out of the ordinary at all.

Still, the problem persists. I can see the load of the machine struggling to keep under 1.00.
The other machines are in the 0.10 – 0.20 range all the time.

Looking at CPU for the MySQL process on this machine (with 2 CPU cores as the other machines have as well) it is quite often above 100%. The other machines are never – EVER – over 60% for the MySQL process.

So, any help is much appreciated.

Please do let me know if you need me to run a command that you need to see the output from in order to help.

Thanks.

EDIT Spelling and clarifications

Best Answer

This sounds like a configuration issue. INSERT/UPDATE statements result in writes, so you should look at the write path. Check the following:

  1. Is write caching on the disks enabled?
  2. Are the disks configured the same way (RAID geometry, file system, mount options, controller)?
  3. Are your InnoDB transaction logs the same size and configuration (innodb_log_% options)?
  4. Are your disk flushing options configured the same (sync_%, innodb_flush_%)?
Related Question