I have to restart MySQL to lower the CPU usage

Question

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?

Answer 1

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