Not the answer you need?
Register and ask your own question!

Strange bursts of CPU activity after isolated UPDATE commands

it-fgit-fg EntrantInactive User Role Novice
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
  • install Ubuntu 18.04 Server
  • install Percona MySQL 8.0
## 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 
  • create DB, table and insert single line
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
  • in first terminal run top
top -b -H -d1 -p $(pidof mysqld) | grep -e mysql -e ^top | grep -v ' 0\.0 '
  • in second terminal run single update command
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
  • identify the thread eating the cpu
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 |
+-----------+---------------------------------------------+------------+----------------+------+--------------+
  • when you repeat the above steps with Percona 5.7, you'll get CPU activity burst on different thread
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 |
+-----------+-----------------------------------+------------+----------------+------+--------------+
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.