MySQL server increased load after upgrade to 4.2

[Gabriel Torres]

Hi guys,

We run a very busy community, with around 600,000 registered users.

We use two dedicated servers.

The webserver: PowerEdge R520, 2x Xeon E5-2430, 32 GiB RAM

The database server: PowerEdge R520, 2x Xeon E5-2430, 64 GiB RAM, 2x 120 GiB SSD RAID 0, Percona with XtraDB (InnoDB)

They are connected through a private Gigabit Ethenet connection to reduce latency and improved security.

Since the upgrade to 4.2 this week we are seeing a higher than usual load in our database server. Before, load rarely reached 1.00, usually stayed in the 0.50~0.70 range. Now we are seeing load constantly above 1.00, sometimes reaching 3.00.

SHOW PROCESSLIST doesn't show anything unusual, several connections sleeping. Slow log queries are being logged and the only queries that are taking a long time are search-related queries.

What is strange is that the load is concentrated in the first two cores of the CPU, all the other cores are pretty much unused.

From my experience, when the load on a MySQL server is high, it usually means the server needs more RAM, but that isn't the case, as we have plenty of unused RAM as you can see from the screenshots.

I hope you guys can give me some input on what might be going on and/or how to debug or find the source of this problem.

Thank you in advance!

 

 

[Mastric]

How's the slow query log look?

[Dll]

Are the background tasks still running - there's some re-indexing etc that runs after updating?

[Gabriel Torres]

@Dll At this point I am thinking it is a couple of background processes from some custom apps we have. I will need a few more days to investigate this possibility.

[Daniel F]

Im currios about this too. Let me know if you need help and I'll check it in my free time. 

[Gabriel Torres]

Thanks, @Daniel F. I will have a more definitive answer within the next couple of days, I am performing some tests at the moment.

[Gabriel Torres]

There is one final thing I need to do before throwing the towel, which is upgrading PHP to 7.x. We are still running 5.6.x. I will keep you guys posted.

[ProSkill]

5 hours ago, Gabriel Torres said:

There is one final thing I need to do before throwing the towel, which is upgrading PHP to 7.x. We are still running 5.6.x. I will keep you guys posted.

That made all the difference for me.

[Gabriel Torres]

Hi guys!

@ProSkill @Daniel F @Dll @Mastric

Thanks for your interest in helping me. The load is now back to "normal", below 1.0. Here is what has been done that lowered the MySQL server load and, more dramatically, the load on the webserver:

1. Upgraded from PHP 5.6 to 7.1

2. Enabled Opcache instead of Xcache

3. Enabled Memcached (we weren't using any caching mechanism for the datastorage data)

4. Several bugfixes performed and upgrade to IPS 4.2.2

5. Our backup routine for the MySQL database, which runs nightly as a cronjob, was been performed incorrectly, locking the database and therefore increasing the server load during the process. (For those curious about this one, I highly suggest reading this: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html)

Still monitoring and making some fine tunings, as I guess we can improve performance a little bit more. For that we will need the servers up and running without a restart for a few days.

Cheers!

[Daniel F]

Thanks for the update.

[Serega Zavodin]

I can confirm there is a new CPU mysql problem

When I choise use information in admin panel mysql stuck at 100% cpu for 10 sec

I can't find what php file/mysql query create this problem