Mysql – Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes

cpuinnodbMySQLperconaxtradb

I'm trying to understand why Percona and xtradb InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.

I've a InnoDB database with only one table with about 10 rows.

`ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`randomnumber` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)

When I insert a single row, about 1-2 seconds after commit the mysql process causes 30-40% CPU usage for about one second.

INSERT INTO status.statustest (randomnumber) VALUES ('1111111');

But when inserting one row every 500ms for 10 seconds (so in total 20 rows), the CPU spikes only after the last commit.

I found a similar question, but there was no answer to it:

I see this behaviour only with Percona and XtraDB, not with MariaDB.

My system:

2GB RAM

2CPU Cores

20GB SSD

Debian 9.9 x64

mysqld Ver 5.7.26-29 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '29', Revision '11ad961')

Has anyone found a solution as how to get rid of those CPU spikes and what causes them?

Best Answer

In all versions (Percona, XtraDB, MariaDB) of InnoDB, writes and index updates are delayed. A separate thread(s) takes care of doing the writes 'eventually' and in the 'background'.

Data writes are put into the desired blocks immediately, but not written until they get bumped out of the buffer_pool.

UNIQUE indexes are updated immediately but left cached in the buffer_pool (like data).

Non-unique indexes are stored in the "Change buffer". Later they are collected together for putting into the actual index blocks and eventually written to disk.

Redo log and double-write writes are done more immediately.

There are many settings relating to the frequency of background actions, sizes of caches, etc. These have an impact on both CPU and I/O. Normally it is best to leave them alone.

To further address your question, please provide the RAM size of the servers being tested and a diff of the SHOW VARIABLES. The diff may help zoom in on the cause of the difference you are seeing.

Related Question