I was setting up a new server to split off some of the load from my primary database servers by moving my stats tables to a separate server/db. The primary servers are running (mostly) stock MySQL 5.5 on Debian 8, and since the queries were remaining the same, I expected the Percona CPU usage to be less than what was currently seen on either of the primary db servers.
So I installed Percona MySQL 5.7 on the new server, imported the stats tables, setup the PHP script that would do the updating, and it all worked. Then I checked the CPU usage to see how much less it was… I was shocked to find that the CPU usage was way higher than either of my other MySQL 5.5 servers. All three servers (the 2 MySQL and the 1 Percona server) have the same hardware specs (Digital Ocean 512MB VPS), yet the Percona server was averaging 30-50% CPU with spikes even higher. I thought perhaps that moving the server had changed something in the connection setup, so I made sure the stats updater was connecting via localhost, got rid of my SSL config (who needs that on localhost…), and even copied some of my settings over from the other servers to see if that helped. Nothing I did would reduce the CPU usage, at least not noticeably.
So what’s going on? Why is Percona hogging so much CPU?
There are two tables that get updated by the script:
daily_image_stats has 1 row per day, with 8 columns: 1 auto-incrementing ID, 1 DATE, and 6 int columns
stats table has 3 columns: 1 auto-incrementing ID, 1 text field, and 1 int field
There are 5 queries performed by the stats updating script:
1 select query, which looks to see if the record for “today” exists: SELECT images FROM daily_image_stats WHERE date=‘2017-04-24’
If that is false, 1 INSERT query to create the record for today, so that hardly counts as it runs 1 time each day
1 update query for daily_image_stats, which increments the appropriate values for the record corresponding to “today”, like so: UPDATE daily_image_stats SET images=images+1, lossy=lossy+1,etc. WHERE date=‘2017-04-24’
2 update queries for the stats table, which increment a couple “global” counters: UPDATE stats SET value=value+1 WHERE name LIKE ‘images’
and: UPDATE stats SET value=value+123 WHERE name LIKE ‘savings’
So nothing heavy-duty, no joins or anything weird, just these queries. The script runs approximately 2-3 times per second, depending on how busy our API is at the moment.
Lastly, I wasn’t content just to leave Percona sitting there eating up CPU for no good reason, so I ripped it out, apt-get purged it, put MySQL 5.5 in, reimported the database, and the CPU usage plummeted to 1-2%. Color me shocked…