0

I migrated my server to a new Rocky Linux 9 server and since then, my MySQL server of a web server takes almost 100% of CPU after few hours of use.

For now the only way I have found to reduce the CPU load is by restarting the MySQL server which takes 6 seconds and so my web server is out of line while this reboot. After the reboot, the MySQL process takes about 3-10% of CPU.

Before the migration the version of MySQL was mysql Ver 8.0.30-0ubuntu0.20.04.2 and now I am using the version mysql Ver 8.0.28 for Linux on x86_64 (Source distribution).

I am using exactly the same configuration, I did only a copy / past:

[mysqld]
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
max_binlog_size   = 100M

The only option that I have found on my previous version which is not available on my new one is:

optimizer_max_subgraph_pairs 100000

But this option is not available on new server and so I can't set this global var.

It's like on my new server, MySQL was not clearing automatically some buffer or cache and after a few hours the CPU load explodes.

The server configuration is the same with 1G of RAM.

I have checked the mem usage and it increased by 100M in less than 20 minutes because of MySQL:

free -h
               total        used        free      shared  buff/cache   available
Mem:           954Mi       690Mi        65Mi        23Mi       199Mi        92Mi
Swap:          2.5Gi       2.1Gi       442Mi

So I restarted MySQL and the use of RAM has immediately decreased

systemctl restart mysqld.service
free -h
               total        used        free      shared  buff/cache   available
Mem:           954Mi       735Mi        70Mi       4.0Mi       148Mi        72Mi
Swap:          2.5Gi       1.4Gi       1.1Gi

Now MySQL is only under 10% of CPU usage.

What should I do to limit MySQL in the usage of RAM?

EDIT

I could limit the RAM usage with these 2 settings:

table_definition_cache = 400 #Default 2000
table_open_cache = 400 #Default 4000

But still have the issue and I really feel like there is a background process on this installation of Rocky Linux which overload MySQL.

Maybe a background process from MySQL itself.

I also do think this overload is due to a background process because if I restart my MySQL server when it's taking over of 90% of my CPU, it goes down between 3 to 15%. So it's not due to the accesses on my web application.

So, by restarting the server I stopped the background process!

Which background process can it be?

1 Answer 1

0

What is the value of innodb_buffer_pool_size?

It looks to me like the RAM usage went up and the Swap usage went down.

Both setups have only 1GB of RAM, correct? Swapping is deadly for performance; you have a lot of swapping. Are there other apps on the server>

The above setting is the main tunable to cut back on RAM. I suggest starting at no more than 100M for that setting.

Also check SHOW STATUS LIKE 'Max_used_connections';, then set max_connections to, say, 2 more than the 'used' value.

For further analysis (including looking for other things that can probably safely lowered, follow the instructions here: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

1
  • Max_used_connections is at 15 Sep 5, 2022 at 13:10

Your Answer

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

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