Mysql 8.0 Parallel scanning 导致cpu使用率飙升

1.版本

1)操作系统版本

cat /proc/version
Linux version 3.10.0-1062.1.1.el7.x86_64

2) 数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.01 sec)
 

2.问题描述

  监控同事发现有一台数据库服务器cpu使用率异常

top：

 

perf top -p 29328

##主要是Parallel_reader 函数占用了cpu， Parallel scanning 是MySQL 8.0.14 开始支持的新特性，详情请见

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

 

mysql 活跃会话只有几个类似如下查询(表有将近30G，100万行左右数据)

SELECT  COUNT(*) FROM  table_xxxx LIMIT 0, 1000;

为了先把cpu使用降下来，我kill thread_id; 杀了几个会话，杀过会话后发现 cpu 使用率并未恢复正常，查看发现

##被杀掉的几个query，一直处于killed状态，并未结束

##上面的sql 一直处于killed状态，这时如果想通过mysqladmin shutdown 清理掉killed会话，会发现shutdown操作也长时间无法结束，查看error log 会看到如下信息：

2021-03-05T10:13:10.365056+08:00 3192266 [System] [MY-013172] [Server] Received SHUTDOWN from user dba_pit. Shutting down mysqld (Version: 8.0.19).
2021-03-05T10:13:10.382309+08:00 0 [Note] [MY-010067] [Server] Giving 14 client threads a chance to die gracefully
2021-03-05T10:13:10.382376+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads
2021-03-05T10:13:10.392506+08:00 0 [Note] [MY-010054] [Server] Event Scheduler: Killing the scheduler thread, thread id 4
2021-03-05T10:13:10.392535+08:00 0 [Note] [MY-010050] [Server] Event Scheduler: Waiting for the scheduler thread to reply
2021-03-05T10:13:10.392638+08:00 0 [Note] [MY-010048] [Server] Event Scheduler: Stopped
2021-03-05T10:13:12.392752+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 13 remaining clients
2021-03-05T10:13:12.392817+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3151976  user: 'dba_pit'.
2021-03-05T10:13:12.392833+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3190089  user: 'dba_pit'.
2021-03-05T10:13:12.392963+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3189754  user: 'dba_pit'.
2021-03-05T10:13:12.393036+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3189746  user: 'dba_pit'.
2021-03-05T10:13:12.393085+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3190914  user: 'dba_pit'.
2021-03-05T10:13:12.393234+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3190084  user: 'dba_pit'.
2021-03-05T10:13:12.393280+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 2962052  user: 'common_msgservice'.
2021-03-05T10:13:12.393656+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3191972  user: 'dba_pit'.
2021-03-05T10:13:12.393807+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3191981  user: 'dba_pit'.
2021-03-05T10:13:12.394091+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3189277  user: 'dba_pit'.
2021-03-05T10:13:12.394134+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3189749  user: 'dba_pit'.
2021-03-05T10:13:12.394223+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3191462  user: 'dba_pit'.
2021-03-05T10:13:12.394358+08:00 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3191039  user: 'dba_pit'.
2021-03-05T10:13:12.394423+08:00 0 [Note] [MY-010043] [Server] Event Scheduler: Purging the queue. 0 events

此时数据库cpus使用率很高，通过 perf top -p $mysqld  查看数据库进程cpu使用情况，发现主要是如下函数在占用cpu

Parallel_reader::Ctx::move_to_next_node

##最后 kill -9 杀掉数据库进程后重启

3.问题处理：

    查询发现这是一个bug

https://bugs.mysql.com/bug.php?id=100326

https://jira.percona.com/browse/PS-7009 ## 这里有人指出innodb_parallel_read_threads设置为1，并不能解决该问题

https://jira.percona.com/browse/PS-7038 ##percona 8.0.20 修改该问题