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?