I migrated my server from a Ubuntu 20.04 to a new Rocky Linux 9 server and since then, my MySQL server takes sometimes almost 100% of CPU for a variable period of time.
My Rocky9 is an web app with a normal average of 3 to 10% of CPU usage by MySQL.
If I take a look at the access.log file with this command: cat /var/log/nginx/mywebapp.com-access.log | cut -d[ -f2 | cut -d] -f1 | awk -F: '{print $2":"$3}' | sort -nk1 -nk2 | uniq -c
I get for example (requests by minute on the web server):
And when MySQL is overloaded and takes more than 90% of the CPU I don’t have more entries than the average on my logs.
So I do think this overload is due to a background process because if I restart MySQL 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.
And by restarting the server I stop the background process!
I hadn’t this strange behavior when the server was on Ubuntu. I don’t have more users than before and the PHP script is the same.
Here is a screenshot of the server CPU usage, whenever there are blue peaks, it’s MySQL:
Which background process can it be? What should I check to find it?
If MySQL is taking 90% of CPU, it could require tuning, depending on how much cpu/ram your server has got. There is a great tool called tuning-primer.sh that can help with this. Maybe your mysql is configured to use too much ram that it doesn’t have access to. If restarting mysql sorts out the CPU usage, that means it’s mysql causing it.
What are the specs of your server? How many CPU? How much ram? What else is running on the server?
As a rule of thumb, you normally configure 2/3 of RAM for the database on a server, therefore you also need say 1-2GB for the operating system, and then for any other services on top of that. Eg: Apache will need 1-2GB of ram maybe more depending on how it’s configured. Using PHP? Also going to need to account for it. You can run Apache+PHP+MySQL on a server with 2GB of ram, but you then need to look at configuring Mysql appropriately, as well as Apache and PHP to control it’s resources. And in some cases once it’s used more, can require increasing the amount of cpu/ram available.
The server is a 1G of RAM web server, the same configuration of the server before the migration to Rocky 9.
The Web app is very light and fast hand coded PHP app, I don’t need a very powerful server.
I downloaded and launched tuning-primer.sh and the results were not that good:
BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
MEMORY USAGE
Max Memory Ever Allocated : 164 M
Configured Max Per-thread Buffers : 953 M
Configured Max Global Buffers : 136 M
Configured Max Memory Limit : 1.06 G
Physical Memory : 954 M
Max memory limit exceeds 90% of physical memory
TABLE CACHE
Current table_open_cache = 512 tables
Current table_definition_cache = 4096 tables
You have a total of 417 tables
You have 512 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
I did some modifications and now everything is green:
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
MEMORY USAGE
Max Memory Ever Allocated : 131 M
Configured Max Per-thread Buffers : 75 M
Configured Max Global Buffers : 128 M
Configured Max Memory Limit : 203 M
Physical Memory : 954 M
Max memory limit seem to be within acceptable norms
TABLE CACHE
Current table_open_cache = 4975 tables
Current table_definition_cache = 4096 tables
You have a total of 417 tables
You have 448 open tables.
The table_cache value seems to be fine
Hope this will be enough to calm down the excesses of MySQL.
Yeah that will most likely help since you’ve now limited it to about 200MB. If you only have under 500 tables though, you don’t need table_open_cache and table_definition_cache to be so high. You could set this to about 600 or even 1000 if you want. You can count all the tables for all the databases on the system and set accordingly. You can then see if it reduces any further.
I would suggest running the script again once mysql has been running at least 2 days, since usage will help you tune those values even further in case your memory of 200mb is too low.
You can see the migration which occurred the last 08/28. And before the migration I was on an average of 30% of CPU (MySQL + PHP-fpm) with an average of 300 requests on the web server per minute.
Now I get a high CPU usage almost all the time closed 100% only because of MySQL.
So I changed the hosting service and the distribution.
1/ It’s due to Linode
2/ It’s due to MySQL Rocky Linux 9 default config
Same server configuration for Linode / Digitalocean ($5 shared server) with same amount of RAM (1G).
Maybe it’s not the MySQL configuration but the Linode architecture which is the cause of the problem!
Whilst it’s doable on Rocky for example removing sssd packages, and gssproxy for example which then removes some other nfs dependencies, you can reduce the amount of memory.
I run either Apache or Nginx, with PHP and MySQL and whilst it’s doable with 1GB ram, it’s not very great in terms of performance. Taking also into account the minimum specs for RHEL/Rocky, you should really be looking at a 2GB ram machine.
I’ve had 1GB VM’s with Linode, DigitalOcean running a similar setup but with Debian and whilst like you, you don’t see high CPU usage it works, but not very fast. I now have a 2GB VM, and the difference was unbelieveable - so it’s not really the hosting provider (I’ve used Linode since 2012 with varying VM specs). Far better performance, and I could unleash more resources for Apache/Nginx as well as MySQL. You cannot expect to do much with a 1GB machine - maybe if you have static HTML pages it would suffice.
Is there any particular reason you don’t want to use a 2GB machine and have better performance? I don’t see a reason to restrict it so much to a 1GB machine. If you want to restrict it, perhaps use a Linux distro that is meant to use very little memory, for example Alpine Linux (which incidently is used in a lot of containers). Very lightweight in terms of resources. I had minimal of Alpine using less than 50MB ram before I added Apache/PHP/MySQL. Each Linux distro has varying hardware requirements, so what worked for you with Ubuntu, isn’t necessarily going to work with RHEL/Rocky as you found out.
First of all I would like to thank you for your help, it allowed me to deepen my knowledge in the Rocky system and the configuration and optimization of MySQL.
I must admit that I am not a specialist in the configuration and optimization of a web server (Nginx, PHP, MySQL), it is something I do every 4 to 5 years…
But what is sure, it was not necessary to have a bigger server when I was under Ubuntu 20.04.
And I didn’t understand what was going on with Rocky 9. I didn’t know that there was a minimum of 2G of RAM to run the system.
So I decided to migrate the server to Ubuntu 22.04 and voila, my CPU is back under 20% on average.
So no, I don’t agree that you need a minimum of 2G of RAM to run a dynamic site.
It’s quick, looking at the page source, I show the PHP runtime at the end (< ! – Page generated in 0.04281 seconds.–>).
Of course, I didn’t use frameworks like Laravel, Symphony or Drupal. It’s 100% hand coding and always in the spirit of optimization.
I wanted to switchmy server to Rocky because I switched to Fedora on my work laptop. But well, maybe later.
Thanks again for your help, it also made me realize that on my server I have 11G MySQL binlogs! I didn’t understand why I had less and less space available…
