MySQL CPU性能定位

MySQL CPU性能定位

目录

• MySQL CPU性能定位
  • CPU的5种状态
  • MySQL常见CPU 案例
    • 1.SQL语句导致CPU高
      • Sending data官网解释：
      • Copying to tmp table，Copying to tmp table on disk：官网解释：
    • 2.SQL语句IO问题导致CPU高
  • SQL语句定位cpu核
  • 补充：CPU使用率过高
  • 3.总结
    • 以往的CPU案例中，优化的方向：
    • MySQL性能测试CPU优化方向：

经常会看到看到cpu 使用率非常高的情况。在这种情况下，资源的使用监控分析才是性能故障分析的根本首要任务，通过这些分析，理解服务器如何运行，资源损耗在哪些方面对问题进行故障诊断是非常有价值有意义的。MySQL那些情况，会导致cpu上升。

CPU的5种状态

[root@prod-gp-seg01 ~]# top
top - 10:24:03 up 36 days, 28 min,  1 user,  load average: 0.98, 2.18, 4.09
Tasks: 659 total,   1 running, 498 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.2 us,  0.3 sy,  3.8 ni, 95.6 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 65967416 total, 12253260 free,  2329408 used, 51384748 buff/cache
KiB Swap: 16777212 total, 16650492 free,   126720 used. 41636348 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                
22087 gpadmin   39  19 3452152   1.7g   1.7g S  50.7  2.8   0:02.04 postgres 


us:用户空间占用CPU百分比
sy:内核空间占用CPU百分比
ni:用户进程空间内改变过优先级的进程占用CPU百分比
id:空闲CPU百分比
wa: 等待输入输出的CPU时间百分比
hi: 硬件中断
si: 软件中断
st: 实时

备注：从上述情况介绍来看，sy系统和ni&si软硬中断，基本系统自动控制，干涉部分不是太多.
us，id，wa有一定的优化空间，有效的使用资源。

通过上述介绍，已经了解了cpu的基础，下面看看MySQL方面cpu的表现

MySQL常见CPU 案例

以往的MySQL案例中，因为使用上的一些问题，经常会导致高CPU使用率上升情况： **这里包括连接数增加、执行差效率的查询SQL、哈希连接或多表合并连接、写和读IO慢、参数设置不合理等。
**

1.SQL语句导致CPU高

那些常见的SQL语句会导致cpu上升 先从最直观的SHOW PROCESSLIST，查询时间长、运行状态（State列）

• “Sending data”、
• “Copying to tmp table”、
• “Copying to tmp table on disk”、
• “Sorting result”、
• “Using filesort”等都可能是有性能问题的查询（SQL）。

Sending data官网解释：

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。
体现在：
1.没有使用索引
2.mysql索引表结构，要是没有使用主键查询的话，需要进行回表操作，在返回客户端。
3.返回的行数太多，需要频繁io交互

Copying to tmp table，Copying to tmp table on disk：官网解释：

Copying to tmp table
The server is copying to a temporary table in memory.
Copying to tmp table on disk
The server is copying to a temporary table on disk. The temporary result set has become too large

整体来说生成临时表内存空间，落磁盘临时表，临时表使用太
体现在 多表join，buffer_size设置不合理，alter algrithem copy等方式

Sorting result：
For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables. 

结果集使用大的排序，基本上SQL语句上order by 字段上没有索引
上述的情况大量堆积，就会发现CPU飙升的情况，当然也有并发量太高的情况。

优化方向:

• 1.添加索引，组合索引，坚持2张表以内的join方式 这样查询执行成本就会大幅减少。
• 2.隐私转换避免，系统时间函数的调用避免
• 3.相关缓存大小设置：join_buffer_size，sort_buffer_size，read_buffer_size ，read_rnd_buffer_size ，tmp_table_size。

在紧急情况下，无法改动下，通过参数控制并发度，执行时间 innodb_thread_concurrency ，max_execution_time都是有效的临时控制手段。

2.SQL语句IO问题导致CPU高

CPU对于IO方面的处理方式如下：等待的IO队列信息，会放置CPU里进行spin操作。

Mysql事务关联操作方面有redo， undo， binlog日志。但实际InnoDB实现方式是同步IO和异步IO两种文件读写方式

• 1.对于读操作，通常用户线程触发的数据请求都是同步读，其他后台线程触发的是异步读。
  同步读写操作通常由用户线程来完成，当用户线程执行一句SQL时，如果请求的数据页不在buffer pool中，就需要将文件中的数据页加载到buffer pool中，如果IO有瓶颈，响应延迟，那么该线程就会被阻塞。

• 2.对于写操作，InnoDB是WAL（Write-Ahead Logging）模式，先写日志，延迟写数据页然后在写入磁盘，这样保证数据的安全性 数据不丢失；
  异步写，主要在下面场景下触发

• binlog，undo，redo log 空间不足时 ；

• 当参数innodb_flush_log_at_trx_commit，sync_binlog设置为1时，每次事务提交都会做一次fsync，相当于是同步写；

• master线程每秒做一次redo fsync；

• Checkpoint

• undo，binlog切换时

• Page cleaner线程负责脏页的刷新操作，其中double write buffer的写磁盘是同步写， 数据文件的写入是异步写。

大量的io堆积，等待的状态下，都会导致CPU使用率上升。
log方面多注意以下方面配置：

• 1.相关mysql参数 innodb_flush_log_at_trx_commit ，sync_binlog，innodb_io_capacity ，sync_relay_log的参数合理设置。
• 2.独立表空间 （innodb_file_per_table），日志文件伸缩大小，临时表使用，
• 3.尽量使用IOPS高的硬件设备

SQL语句定位cpu核

通过sys库定位当前执行pid：

##通过sys库定位当前执行pid， 先对应3247
mysql> select thd_id,conn_id,user,pid,program_name,command,current_statement  from sys.processlist where conn_id>0 and pid>0;
+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+
| thd_id | conn_id | user           | pid  | program_name | command | current_statement                                                 |
+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+
|     33 |       3 | root@localhost | 3247 | mysql        | Query   | select thd_id,conn_id,user,pid ... list where conn_id>0 and pid>0 |
+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+

ps方式：

##通过ps工具查看对应的cpu是在哪个核上执行
[root@ss30 ~]# ps -o pid,psr,comm -p 3247
   PID PSR COMMAND
  3247   3 mysql

输出表示进程的 PID 为 3247（名为”mysql”）目前在编号为 3的CPU 上运行着。如果该过程没有被固定，PSR 列会根据内核可能调度该进程到不同CPU而改变显示。

top方式：
通过top方式查看对应的cpu是在哪个核上执行，
按下 “F”键->使用上下键选择P = Last Used Cpu，并按下空格键，出现 “*”即可->ESC 退出，这时候top界面上的Ｐ列就是对应的CPU信息.

root@ss30 ~]# top -p 3247

补充：CPU使用率过高

查看CPU飙高的mysql线程，top -H -p <mysqld进程id>

#top -H -p 1821
top - 21:04:24 up 10 min,  1 user,  load average: 0.00, 0.02, 0.04
Threads:  31 total,   0 running,  31 sleeping,   0 stopped,   0 zombie
%Cpu(s):  90.0 us,  0.2 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1867048 total,   390212 free,  1227176 used,   249660 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.   454824 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                               
  1942 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.26 mysqld                                                                                                
  1821 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:13.02 mysqld                                                                                                
  1924 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.00 mysqld   

根据具体PID，定位问题SQL

SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;

3.总结

以往的CPU案例中，优化的方向：

• 对于MySQL硬件环境资源，建议CPU起步8核开始，SSD硬盘；
• 索引 ，合理设计表结构，优化SQL。
• 读写分离，将对数据一致性不敏感的查询转移到只读实例上，分担主库压力。
• 对于由应用负载高导致的 CPU 使用率高的状况，从应用架构、实例规格等方面来解决。
• 使用 Memcache 或者 Redis缓存技术，尽量从缓存中获取常用的查询结果，减轻数据库的压力。

MySQL性能测试CPU优化方向：

• 系统参数：磁盘调度算，SHELL资源限制,numa架构，文件系统ext4，exfs
• 刷新mysql log相关刷新参数：
• 临近页（innodb_flush_neighbors）
• 死锁检查机制（innodb_deadlock_detect），
• 双1刷新：sync_binlog，innodb_flush_log_at_trx_commit
• 并发参数: innodb_buffer_pool_instances, innodb_thread_concurrency 等
• 因为一些服务器的特性，导致cpu通道 和 内存协调存在一些问题，导致cpu性能上去得案例也存在