CPU usage unusually high on Debian 8 (jessie)

MySQL & MariaDB Percona Server for MySQL 5.7
1

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…

2

In an effort to take some of the load off my existing database servers, I was moving a few tables that gather server statistics to a new VPS. In doing that, I thought I’d give Percona MySQL a try, to see how the performance was. The tables and queries are pretty simple, so I wasn’t expecting anything huge, but I ended up with something quite unexpected, so I’m hoping someone can solve this mystery.
This was setup on a brand new Digital Ocean 512MB VPS, running Debian 8.

There are two tables that I was importing:
daily_image_stats has eight columns and about 1300 existing rows. The columns are like so:
id - int(11)
date - date
and the other 6 are all int columns that simply get incremented when a particular action happens on our servers.
stats is smaller with only 3 columns and a handful of rows, only two of them are currently used.
id - int(11)
name - text
value - bigint(20)

Since these were existing tables, I simply modified the existing update code to point to the new server. The actual queries are performed from the localhost though, not from the remote “slaves”. The existing setup was that two master servers (in dual-replication mode) were doing all the updates, on MySQL 5.5. CPU usage was generally 12%, although it would spike to 20-30% when our systems were processing a lot of images. Thus I figured anything that didn’t NEED to be on the two masters, could just as well be done by a separate node.
There are 5 queries in the PHP script, but only 4 of them fire on a regular basis, and they look like this:
SELECT images FROM daily_image_stats WHERE date=‘2017-04-26’ (so as to make sure today’s record exists, if not, one is inserted)
UPDATE daily_image_stats SET images=images+1,lossy=lossy+1,lossy_fast=lossy_fast+1,webp=webp+1,pdf=pdf+1,pdf_lossy=pdf_lossy+1 WHERE date=‘2017-04-26’
–on this one the actual increments are 1 or 0, depending on the image parameters used
UPDATE stats SET value=value+1 WHERE name LIKE ‘images’
UPDATE stats SET value=value+235 WHERE name LIKE ‘savings’
–and here the increment could be any integer representing the number of bytes that an image was reduced in size

This PHP script fires approximately 3 times per second on an average day, once per image processed by our servers.

So here’s the unexpected part. I had Percona 5.5 all setup, stock settings, because it claims to be well tuned, and this is a minimal load. I flipped the switch on my app servers to start sending their updates to the new stats db server, and CPU skyrocketed. Most of the time it was around 30%, but it hit 60-80% quite frequently. So I went through the PHP script, disabled SSL even though I don’t think it would use it on localhost, and then started looking for things I could tweak in the Percona config. I copied some of my normal settings from the MySQL 5.5 configs, and nothing changed.

Not content to let Percona eat up CPU for no good reason, I apt-get purged it, and installed the stock MySQL 5.5, reimported the data, and watched. The CPU usage plummeted and has averaged 4% since Monday.

So, why is this? Does Percona stink at simple stuff? Is it meant only to excel with huge datasets? I use it on another server that does 1 update per image, and it sits at around 20% CPU usage doing 1 insert query per image. It has millions of records though, far more than the simple stats server (30,509,354 to be exact). Any ideas?