0

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?

1 Answer 1

0

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.

8
  • Thank you, I've updated the start post. The server has 2GB of RAM. Here are the variables: pastebin.com/qcFec5HR
    – mat
    Jul 10, 2019 at 13:58
  • @mat - Please also provide SHOW GLOBAL STATUS;.
    – Rick James
    Jul 11, 2019 at 14:17
  • Here is the output from SHOW GLOBAL STATUS; pastebin.com/WqKKX9VL
    – mat
    Jul 12, 2019 at 12:24
  • @mat - I think you did SHOW STATUS instead of SHOW GLOBAL STATUS.
    – Rick James
    Jul 12, 2019 at 21:25
  • unfortunately no, this really is the output from 'SHOW GLOBAL STATUS', I just checked the command history and the command was: mysql -u root -p -e "show global status;" > status.txt
    – mat
    Jul 13, 2019 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.