Near identical MySQL deployments behaving very different - High CPU Usage problem

Question

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

• mustaccio: You said that you had "noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT". What led you to this conclusion? What links server CPU utilization to MySQL in general and inserts/updates in particular?
• Adergaard: The conclusion came from seeing those queries showing up in "Slow queries" log when load on the machine passed > 2.0. These are very simple updates i.e. using the primary index as update key. Removing the updates (they are primarily there for statistics), I manage to keep the load of the machine < 1.0 but the CPU is still a lot higher from time to time, than it ever is on the other four machines with the exact same code and table definitions. My feeling is that there's something caused by the language settings, as strange as it may sound, since that's the only thing that differs.
• mustaccio: That unfortunately doesn't say if those statements are the cause or the victim of high CPU usage. Since you're running full application stacks on your machines, I would start with figuring out what processes exactly use the CPU and analyze where they use it (user/system/iowait?)
• Adergaard: You are indeed correct. Thanks for the thought. I'm planning to isolate MySQL on its own machine just to see how it behaves there. I'm beginning to think that it's a general problem with the collation chosen. The thing that is so annoying though is that it only happens to this instance, and not the other four. Even after having set it up twice from scratch and once switching machine with one of the good instances i.e. it points to being a MySQL problem and not other processes on the machine.
• Rick James: DIff the SHOW GLOBAL VARIABLES between the slow guy and one of the others. Provide the diff for us to advise on. Also check that all of your tables are using InnoDB, not MyISAM.

Answer 1

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_%)?

• Adergaard: First, thanks for answering. 1: yes, 2: no idea, these are all VPS' and are provisioned the same way, 3+4: I have made zero changes to the my.cnf (or the .d-directories it's split up into these days, I'm just running it with all defaults) = yes, they are the same, they are set to whatever is default these days.
• Gordan Bobić: 2: So it could be one of the VMs being on a host with noisy neighbours. Have you explicitly compared the boot parameters (/proc/cmdline) and fstabs between the machines? 3,4: Have you actually downloaded them and diff-ed the configs? A lot of the time discrepancies get missed because the admin "knows" they must be the same.
• Adergaard: See item 3 in my list above. I've moved hosts twice. The host is not the problem.
• Gordan Bobić: Other things to check: disk I/O scheduler, kernel tuning (tuned-adm), kernel version, network configuration.(offload, buffers).

Answer 2

Things to consider:

• Some underlying VM systems can provide resource preferences to one vserver vs another. (unlikely... but)

• Also depending on what type of disk using, where and how the volume is carved you might have a volume on either a slower drive or a drive that is more active.

• If you're using NAS - it could also be because of misconfigured vNIC or if you have a dedicated hard NIC it could be misconfigured or failing, maybe event failing switch port too in this case.

I hate these situations so just trying to think outside the box a bit for you.