Issue High memory usage caused by the "mysqld" process

[MicheleB]

I've a strange behaviour since about one month, an high CPU usage caused by the "mysqld" process.
However, I don't see any abnormal activity on the server.

Yesterday I also applied this workaround on "my.cnf" (innodb_buffer_pool_size=1024M, query_cache_size=64M) but it didn't work, after a few hours everything is back as before as you can see in the graph below (and the consumption has increased even more).

How to troubleshoot slow performance of MySQL on a Plesk server?

Applicable to: Plesk Question How to troubleshoot slow performance of MySQL on a Plesk server? Answer Note: Database performance tuning is out of the scope of Plesk Free Technical Support. This ...

support.plesk.com

Is there a way to understand the origin of the problem?
I'm using Cloudlinux 7.8 with mod_Isapi activated (LSPHP7.4 handler).

Thanks.

 

[AYamshanov]

Hi MicheleB,

You mentioned that "high CPU usage" but the graphs show memory usage. It is better to find what exact cause load of CPU if is possible (e.g.: slow queries and try to optimize them). Is it repeated every day/week/month? Could be it a result of some periodic task (like database backup)?

When you tuned MySQL service, it could start consuming, as an example, more (or less) memory; it depends on what exactly was changed and how the settings were configured previously; there is no one the best answer because it depends on a server, a project/site/CMS, visitors, and so on. All of these changes should be made and validated individually.

---

The Advanced Monitoring extension configured to show alarms if a database uses more than 20% of the system's memory. If you are sure that on your server could consume more memory and it is ok, the threshold could be configured in Settings of the Advanced Monitoring extension.

 

[MicheleB]

Ok, thanks for the input, I'll try to investigate in more depth.
The only relevant change that I had in the last month, it was switched from PHP7.4-FPM to LSPHP7.4 handler, applying this workaround for WordPress Toolkit:

Wordpress Toolkit does not operate properly on Cloudlinux server with alt-php

Applicable to: Plesk for Linux Symptoms Plesk installed on a server with Cloud Linux and alt-php is used in Domains > example.com > PHP settings; On attempt to install WordPress via Wo...

support.plesk.com

I know that Cloudlinux is bad supported from Plesk and maybe there's some incompatibility problem with this OS about MySQL.

 

[MicheleB]

The Advanced Monitoring extension configured to show alarms if a database uses more than 20% of the system's memory. If you are sure that on your server could consume more memory and it is ok, the threshold could be configured in Settings of the Advanced Monitoring extension.

I added RAM memory (2GB) to the cloud server but I keep receiving the alert.



What means "20% of the system's memory"?
Now I've 9GB of RAM on the server, so 20% should be 1,8GB but the "MySQL memory usage" send me alert when a value exceeds about 1,3GB.


Is it a normal behaviour? Do I need to reset the monitoring? I've clicked on "detect hardware changes" but the limit is always the same.

 

[Bitpalast]

Regarding what's going on in the MySQL server, have you looked into its processlist with the SQL

show processlist;

command? Sometimes this will reveal some users who continuously run queries through the server that are unnecessary queries caused by buggy scripts. Many users also have LEFT JOIN statements where an INNER JOIN could dramatically reduce the amount of data involved in a query. Many use a wealth of repeated SELECTs in while...next or for...next loops in their software while single query would be better and the result split afterwards. So normally, a high cpu usage on MySQL is caused by bad or buggy scripts.

 

[MicheleB]

Regarding what's going on in the MySQL server, have you looked into its processlist with the SQL

show processlist;

Hello Peter,
I've run three times consecutively:

plesk db "SHOW FULL PROCESSLIST"

and this is the result:


I've two values in the "time" column that grow each time I execute the command.
Is it normal?

 

[Bitpalast]

Sleeping processes are no issues. It is o.k. if the values increase.
The running processes can be an issue, but obviously your MySQL server is not doing anything big but executing the "show processlist" command at this time. What you should still do is to run the show processlist when the load is high (e.g. when the "top" system command shows that MySQL is using a lot of cpu time). Then you will most likely see other users' queries that are causing that load.

 

[MicheleB]

Ok, thanks.
Do you know why, now that I've 9GB of RAM, Plesk send me alert when the value exceeds about 1,3GB?
The alert is settings to send only when the "MySQL memory usage" is over the 20% and now should be 1,8GB and not 1,3GB.

 

[Bitpalast]

I am not sure about that due to lack of experience. I think there could be two reasons: The alert either takes not only the MySQL RAM usage but the overall RAM usage into account, or there might be tiny spikes that trigger that alert that do not show up in the graph as the graph resolution is discrete.

 

[MicheleB]

Do you know if "Plesk support plan" cover this request of assistance?
I'd like to have more information about this strange behaviour and now "Advanced monitor" not give me so much to investigate.

 

[Bitpalast]

The plan covers all on Plesk, and their support is really excellent. Plus, if you are not sure, you have a 30 days trial period (the first month of the plan is free).

 

[AYamshanov]

I have checked how it works on my server. Every time when I change the threshold limit, I see updated graphs right after the saving of form is finished. I also have asked developers about this strange behavior of "Advanced Monitoring". I will keep you posted.

 

[AYamshanov]

Could you please execute the next two commands in the server's console and show the output:

• plesk sbin sysinfo memory
• cat /proc/meminfo

It will show information about memory; it is used in Advanced Monitoring for calculating percent-based thresholds.

 

[MicheleB]

Total: 7144235008
Used: 6294720512
Free: 849514496
Shared: 518504448
Buffer: 203943936
Cached: 2483101696

MemTotal:        6976792 kB
MemFree:          731876 kB
MemAvailable:    2266520 kB
Buffers:          199224 kB
Cached:          1825576 kB
SwapCached:            0 kB
Active:          3699172 kB
Inactive:        1701076 kB
Active(anon):    2871908 kB
Inactive(anon):  1075584 kB
Active(file):     827264 kB
Inactive(file):   625492 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       2047996 kB
SwapFree:        2047996 kB
Dirty:               796 kB
Writeback:             0 kB
AnonPages:       3375596 kB
Mapped:           219000 kB
Shmem:            572044 kB
Slab:             720268 kB
SReclaimable:     671080 kB
SUnreclaim:        49188 kB
KernelStack:        7680 kB
PageTables:        41768 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     5536392 kB
Committed_AS:    8632232 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       37432 kB
VmallocChunk:   34359685116 kB
HardwareCorrupted:     0 kB
AnonHugePages:      8192 kB
CmaTotal:              0 kB
CmaFree:               0 kB
Tcache:                0 kB
Tswap:                 0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      116592 kB
DirectMap2M:     5126144 kB
DirectMap1G:     4194304 kB

If you prefer you can do it yourself using the credentials in the ticket 258036 (assigned to Vladimir).
Thanks.

 

[AYamshanov]

Now I've 9GB of RAM on the server, so 20% should be 1,8GB but the "MySQL memory usage" send me alert when a value exceeds about 1,3GB.

Total: 7144235008

7144235008 bytes = 6976792 Kbytes = 6813 Mbytes = 6.6 Gbytes

I suppose something else should be done with the cloud server for applying such hardware changes (e.g. reboot) but not sure. Currently, the Linux system is still thinking they have only 6 Gb of memory.

 

[MicheleB]

Yes you're right, the system see 6GB instead of 9GB.


I think that my hosting provider has rebooted the server when I've upgraded the RAM (and the previous value for the RAM wasn't 6GB but 7GB).


Anyway, during the weekend the RAM memory used by MySQL continued to grow.

 

[MicheleB]

Could have something to do with some incompatibilities with Cloudlinux and their LVE Manager?

 

[AYamshanov]

Anyway, during the weekend the RAM memory used by MySQL continued to grow.

In this case, I usually use "mysqltuner.pl" (http://mysqltuner.pl,
major/MySQLTuner-perl) script that helps to better understand what maximum memory usage is possible (and what else could be tuned).

Here is an example from my testing server, it also could consume more memory than the server has:

 >>  MySQLTuner 1.7.19 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.31-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(374B)
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 1 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

[...]

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 32d 0h 5m 10s (18M q [6.579 qps], 1M conn, TX: 14G, RX: 1G)
[--] Reads / Writes: 84% / 16%
[--] Binary logging is disabled
[--] Physical Memory     : 978.6M
[--] Max MySQL memory    : 2.7G
[--] Other process memory: 0B
[--] Total buffers: 192.0M global + 17.1M per thread (151 max threads)
[...]
[OK] Maximum reached memory usage: 516.2M (52.75% of installed RAM)
[!!] Maximum possible memory usage: 2.7G (282.89% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/18M)
[OK] Highest usage of available connections: 12% (19/151)
[OK] Aborted connections: 0.00%  (2/1095201)

[...]
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
[...]

Could have something to do with some incompatibilities with Cloudlinux and their LVE Manager?

In this case, Advanced Monitoring shows graphs based on data that provides Linux kernel. I don't think that there are could be some incompatibilities.

You also could check the system's uptime with the command "uptime" (and when the system was rebooted).

 

[MicheleB]

Ok but only to understand, your assistance here in this forum is linked to the ticket 258036 or are they two separate things?
I would prefer you to do these checks, I would not want to do any damage to the server.

 

[AYamshanov]

As it is a Plesk forum with community, there are my personal suggestions that could help to investigate the problem by yourself. I am interested in improving Advanced Monitoring and monitor any interesting cases and questions about it

I sure Vladimir will contact you to resolve the ticket because it is a separate workflow from the forum.