MySQL CPU性能定位

CPU的5种状态 在linux平台下cpu存在5种状态使用组合。 us:用户空间占用CPU百分比 sy:内核空间占用CPU百分比 ni:用户进程空间内改变过优先级的进程占用CPU百分比 id:空闲CPU百分比 wa: 等待输入输出的CPU时间百分比 hi: 硬件中断 si: 软件中断 st: 实时 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 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语句 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; 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性能上去得案例也存在