Migration from Ubuntu 20.04 to Rocky 9 strange MySQL high CPU

Hello All,

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):

    166 16:55
    204 16:56
    278 16:57
    220 16:58
    202 16:59
    173 17:00
    197 17:01
    137 17:02
    143 17:03
    319 17:04
    217 17:05
    186 17:06
    174 17:07

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:
Screenshot from 2022-09-05 19-16-23

Which background process can it be? What should I check to find it?

Was Rocky9 too new to migrate to 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.

Ok, thank you for the advises.

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:

The binary update log is enabled
The expire_logs_days is not set.

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

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:

The binary update log is NOT enabled.
You will not be able to do point in time recovery

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

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.

Unfortunately MySQL is so stressed that I receive automatic Linode Alert - CPU Usage emails several times a day!

On the previous server, on DigitalOcean with a Ubuntu server it was working fine:

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!

RHEL, or rather Rocky in this instance since it’s based on RHEL requires at least 1.5GB memory see: Red Hat Enterprise Linux Technology Capabilities and Limits - Red Hat Customer Portal

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.

In terms of server specs, in year 2022 with Rocky 9, web server, dumbo php and mysql (not mariadb?), it really needs to start at 2Gb.

BUT, that’s only the spec side of things, there could be something else wrong.

  1. Does mysql (on it’s own) use high cpu when memory is low?
  2. Does the cpu magically drop to zero if you stop the web server, but allow the database to keep running?
  3. Have you checked slow query log (set threashold if needed).
  4. When cpu is high, is there any swap file activiry?

Best to test locally, if you don’t control the hardware (due to shared hosting) there could be severe overloading (contention) on the host’s hardware.

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.
Screenshot from 2022-09-10 10-40-36

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…

But we were saying that in the context of Rocky 9, not Ubuntu.

Anyway, the cpu still looks wrong to me, and you didn’t do the four tests.

What junk website are you sending. Every five seconds, all mouse clicks will be forced to pop up spam ads… :rage: :rage: :rage: :rage: :rage:

@yuleba thanks for that, link removed. I’ll be watching this user closely from now on if they spam such stuff again.