如何限制mysql的cpu使用率??_MySQL CPU性能定位

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

CPU的5种状态

在linux平台下cpu存在5种状态使用组合。

• 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语句
那些常见的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 tableThe server is copying to a temporary table in memory.Copying to tmp table on diskThe 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语句
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， 先对应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案例中，优化的方向：

• 对于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性能上去得案例也存在