0

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

Improve this question
8
  • 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?
    – mustaccio
    Commented Sep 12, 2020 at 12:09
  • 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.
    – Adergaard
    Commented Sep 12, 2020 at 12:18
  • 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?)
    – mustaccio
    Commented Sep 12, 2020 at 12:36
  • 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.
    – Adergaard
    Commented Sep 12, 2020 at 12:50
  • 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.
    – Rick James
    Commented Sep 12, 2020 at 21:36

2 Answers 2

Reset to default
0

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_%)?
Improve this answer
4
  • 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.
    – Adergaard
    Commented Sep 12, 2020 at 10:53
  • 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.
    – Gordan Bobić
    Commented Sep 12, 2020 at 11:04
  • See item 3 in my list above. I've moved hosts twice. The host is not the problem.
    – Adergaard
    Commented Sep 12, 2020 at 11:34
  • Other things to check: disk I/O scheduler, kernel tuning (tuned-adm), kernel version, network configuration.(offload, buffers).
    – Gordan Bobić
    Commented Sep 12, 2020 at 13:10
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.