Buy Percona ServicesBuy Now!

Strange bursts of CPU activity after isolated UPDATE commands

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strange bursts of CPU activity after isolated UPDATE commands

    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
    Code:
    ## 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
    Code:
    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
    Code:
    top -b -H -d1 -p $(pidof mysqld) | grep -e mysql -e ^top | grep -v ' 0\.0 '
    • in second terminal run single update command
    Code:
    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
    Code:
    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
    Code:
    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
    Code:
    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 |
    +-----------+-----------------------------------+------------+----------------+------+--------------+
Working...
X