Percona MySQL 8.0 (and 5.7) has strange bursts of CPU activity after isolated UPDATE commands.
1-2 seconds after single UPDATE command you’ll get 2-3 seconds burst of CPU activity.
On a slightly loaded server, with few updates every few seconds it gives you constant
average CPU activity of 10-20%.
Am I hitting some bug? Or is it a feature of Percona MySQL?
Oracle MySQL 8.0 and MariaDB 10.3 do not show this strange behaviour.
Steps to reproduce
[LIST]
[]install Ubuntu 18.04 Server
[]install Percona MySQL 8.0
[/LIST]
## setup repository
wget "https://repo.percona.com/apt/percona-release_latest.bionic_all.deb"
dpkg -i "percona-release_latest.bionic_all.deb"
percona-release setup ps80
## configure root password
cat <<EOF | debconf-set-selections
percona-server-server percona-server-server/root-pass password mypwd
percona-server-server percona-server-server/re-root-pass password mypwd
percona-server-server percona-server-server/default-auth-override select Use Strong Password Encryption (RECOMMENDED)
EOF
## install
apt install -y percona-server-server
[LIST]
[*]create DB, table and insert single line
[/LIST]
cat <<EOF | mysql -u root -pmypwd -t
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
CREATE TABLE mydb.t1(k INTEGER PRIMARY KEY, v INTEGER);
INSERT INTO mydb.t1 VALUES (1, 2);
SELECT * FROM mydb.t1;
EOF
[LIST]
[*]in first terminal run top
[/LIST]
top -b -H -d1 -p $(pidof mysqld) | grep -e mysql -e ^top | grep -v ' 0\.0 '
[LIST]
[*]in second terminal run single update command
[/LIST]
mysql -u root -pmypwd -e "UPDATE mydb.t1 SET v=v+1 WHERE k=1; commit;"
in first terminal, 1-2 seconds after the update command you’ll see burst of CPU activity
top - 07:37:32 up 32 min, 2 users, load average: 0.00, 0.00, 0.00
top - 07:37:33 up 32 min, 2 users, load average: 0.00, 0.00, 0.00
6181 mysql 20 0 1829144 413396 32348 S 1.0 10.2 0:11.75 mysqld
top - 07:37:34 up 32 min, 2 users, load average: 0.00, 0.00, 0.00
6181 mysql 20 0 1829144 413396 32348 R 15.8 10.2 0:11.91 mysqld
top - 07:37:35 up 32 min, 2 users, load average: 0.08, 0.02, 0.01
6181 mysql 20 0 1829144 413396 32348 S 82.0 10.2 0:12.73 mysqld
top - 07:37:36 up 32 min, 2 users, load average: 0.08, 0.02, 0.01
top - 07:37:37 up 32 min, 2 users, load average: 0.08, 0.02, 0.01
[LIST]
[*]identify the thread eating the cpu
[/LIST]
mysql -u root -pmypwd -e "SELECT THREAD_ID,name,TYPE,PROCESSLIST_ID,ROLE,THREAD_OS_ID FROM performance_schema.threads WHERE THREAD_OS_ID=6181"
+-----------+---------------------------------------------+------------+----------------+------+--------------+
| THREAD_ID | name | TYPE | PROCESSLIST_ID | ROLE | THREAD_OS_ID |
+-----------+---------------------------------------------+------------+----------------+------+--------------+
| 8 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL | NULL | 6181 |
+-----------+---------------------------------------------+------------+----------------+------+--------------+
[LIST]
[*]when you repeat the above steps with Percona 5.7, you’ll get CPU activity burst on different thread
[/LIST]
mysql -u root -pmypwd -e "SELECT THREAD_ID,name,TYPE,PROCESSLIST_ID,ROLE,THREAD_OS_ID FROM performance_schema.threads WHERE THREAD_OS_ID=4169"
+-----------+-----------------------------------+------------+----------------+------+--------------+
| THREAD_ID | name | TYPE | PROCESSLIST_ID | ROLE | THREAD_OS_ID |
+-----------+-----------------------------------+------------+----------------+------+--------------+
| 9 | thread/innodb/page_cleaner_thread | BACKGROUND | NULL | NULL | 4169 |
+-----------+-----------------------------------+------------+----------------+------+--------------+