mysql cpu飙高的处理案例1

mysql是一个多线程的架构，所以从linux主机上观察到的mysqld进程的cpu飙高，通常都是由其下的某个或者某几个线程消耗大量cpu资源。这种情况下，第一步要先确定具体是哪个线程消耗cpu。linux上，最简单的就是用top命令的-H选项：

-H  :Threads-mode operation
            Instructs top to display individual threads.  Without this command-line option a summation of all threads in each process is shown.  Later this can be changed  with    the `H' interactive command.

使用top -d1 -H -p pid命令之后，基本就能显示mysqld进程中按cpu使用率从高到低排序的线程信息，其中最重要的PID列就对应了mysqld中线程的os id，对应在mysql性能视图中是performance_schema.THREAD_OS_ID列。接下来，就可以进入到mysql内部，通过各种性能视图来发现问题。

1，首先，根据如下sql，获取当前cpu高的线程在干啥：

select ts.`THREAD_OS_ID`,esc.* from performance_schema.threads ts,performance_schema.events_statements_current esc 
where ts.`THREAD_OS_ID` in ('')
and   esc.`THREAD_ID` = ts.`THREAD_ID`;

这个在大部分情况下就可以了，但是不排除在我们从top中copy出来pid到sql去执行的时候，这个时候线程已经把当前的活干完了，这种情况下，我们看到的就刚好不是cpu高的元凶了。这时候就需要换一种方法，从events_statements_history中获取：

select ts.`THREAD_OS_ID`,esc.* from performance_schema.threads ts,performance_schema.events_statements_history esc 
where ts.`THREAD_OS_ID` in ('')
and   esc.`THREAD_ID` = ts.`THREAD_ID`;

events_statements_history保存了线程最近执行的sql信息，可以用来查询thread执行的sql历史，单个thread能保存的历史记录数受参数performance_schema_events_statements_history_size控制。performance_schema_events_statements_history_size,The number of rows per thread in the events_statements_history table.因此，如果还是没找到的话，可以适当调大此参数。

2，到这，基本就拿到了cpu高的元凶，大部分时候都是一些烂sql，当然也有可能是系统自身的任务线程，例如reader/writer线程等，这些通常是由于配置不当，可通过修改系统参数解决，不在本次讨论范围内。因为本次处理的问题是sql相关，所以其他的就先略过。performance_schema.events_statements_history视图中有大量的sql执行过程中的性能信息，通常需要关注这里的如下几个指标：
    TIMER_WAIT，sql执行过程中所花费的时间，并不是这个值较大就一定有问题，如果大部分时间都花费在LOCK_TIME，那其实这个sql并没有消耗多少cpu，只是在等待而已；
    ROWS_EXAMINED与ROWS_SENT的比值，如果观察到ROWS_EXAMINED非常大，而ROWS_SENT又非常小，那通常就是有问题的，试想一下，一个sql为了获取2、3行记录，但是此过程中扫描了成百上千万的记录，大大的不合理；要么就是表设计有问题，要么就是执行计划不合理；
    CREATED_TMP_DISK_TABLES，sql生成的中间结果集视图所占空间，如果不大于min(max_heap_table_size,tmp_table_size)那么就会存放在内存中，作为内存临时表，如果大于这个值，那结果集会溢出到磁盘存储，称为磁盘临时表。磁盘io总是整个计算机系统中最慢的部分，尤其是如果这部分磁盘数据进行multi pass操作，更是慢出天际；
    SORT_MERGE_PASSES，关于one pass 、multi pass，oracle性能调优文档中是这么写的，mysql原理一致：

The size of a work area can be controlled and tuned. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Ideally, the size of a work area is big enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is known as the optimal size of a work area. When the size of the work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the work area. Under the one-pass threshold, when the size of a work area is far too small compared to the input data size, multiple passes over the input data are needed. This could dramatically increase the response time of the operator. This is known as the multi-pass size of the work area. For example, a serial sort operation that must sort 10 GB of data needs a little more than 10 GB to run optimal and at least 40 MB to run one-pass. If this sort gets less that 40 MB, then it must perform several passes over the input data.
The goal is to have most work areas running with an optimal size (for example, more than 90% or even 100% for pure OLTP systems), while a smaller fraction of them run with a one-pass size (for example, less than 10%). Multi-pass execution should be avoided. Even for DSS systems running large sorts and hash-joins, the memory requirement for the one-pass executions is relatively small. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.

什么意思呢？，假设需要排序10GB大小的数据，如果你的sort area这块内存有10GB大小，那么ok，所有数据都读到内存中一次排序完毕，这被称为是optimize size of workarea；如果sort area只有40MB怎么办？那么所有10GB数据，只需要磁盘-内存交换一次就能排序完毕，这被称为one pass size；如果sort area 连40MB都没有，那排序的中间结果可能还要溢出到磁盘，10GB的数据需要反复的在内存-磁盘之间进行交换，这被称为multi-pass，这是最差的情况，需要极力避免的，不仅磁盘io承受不住，磁盘io也是需要cpu进行管理的，额外的cpu消耗。
    SORT_ROWS，排序操作是cpu，内存密集操作，过多的排序总不是好事。
通过以上几个指标，透过这些信息基本就能确定sql是否有问题，这里有一点需要指出的是，mysql性能视图中的时间单位都是以皮秒为单位，如果要转换为秒，需要去掉12个0：The timer baseline (“time zero”) occurs at Performance Schema initialization during server startup. TIMER_START and TIMER_END values in events represent picoseconds since the baseline. TIMER_WAIT values are durations in picoseconds.

并且timer_start、timer_end等值都是以server启动的时间为基点的逝去值。

在这一步中，mysql提供了大量的性能视图可用来从各种维度进行观察：

--等同于oracle中v$sqlstat的视图，基于performance_schema.events_statements_summary_by_digest表做的。方便展示使用。
sys.x$statement_analysis
query  : sql_text
digest : sql_id

--与上面同源
statement_analysis

--这个要重点关注，单次平均耗时处于数据库前5%的语句
*****
sys.statements_with_runtimes_in_95th_percentile

--全表扫描，记录总执行次数，未使用索引的执行次数，query和digest
sys.x$statements_with_full_table_scans

--整个数据库95%以上的sql的平均执行时间都可以在这个时间内执行完
sys.x$ps_digest_95th_percentile_by_avg_us

--类似于dba_hist_sqlstats的表
performance_schema.events_statements_summary_by_digest

--当前执行的sql，受到开关表setup_consumers的控制的
performance_schema.events_statements_current

--对于等待时间，基于各种维度的统计
performance_schema.events_stages_summary*

--对于sql基于各种维度的统计，与oracle中的sqlstats展示的信息类似
performance_schema.events_statements_summary_by*

--表上io操作的统计
sys.x$ps_schema_table_statistics_io

--一个以文件为统计单位，统计读次数，读字节数，平均单次读字节数；写次数，写字节数，平均单次写字节数，写操作占百分比的。基于`performance_schema`.`file_summary_by_instance`统计的视图
sys.io_global_by_file_by_bytes

--与上面类似
io_global_by_file_by_latency,io_global_by_wait_by_bytes,io_global_by_wait_by_latency

--展示当前每个线程对不同文件的操作统计。
sys.latest_file_io

--全表扫描信息
sys.schema_tables_with_full_table_scans

--与域索引（主键索引）冗余的索引
schema_redundant_indexes

--未使用的索引
sys.schema_unused_indexes

--有排序的sql
sys.statements_with_sorting

--有使用临时表的sql
sys.statements_with_temp_tables

这里需要有一个清晰的理解，mysql提供了  information_schema、performance_schema、sys、mysql四个元数据库，其中，mysql数据库是整个系统的元数据字典居于核心位置，其中的大部分字典表是不能直接使用的，甚至是查询也不可以。为了给用户提供一种手段来查询mysql中的字典信息，诞生了 information_schema这个视图数据库，其中的大部分都是基于mysql库中元数据的视图。performance_schema集中了各种性能视图，展示了系统运行过程中的各种比较基础的性能指标，在其之上，推出了更结合实际的sys视图库。也就是说sys库中的内容，大部分是基于performance_schema中内容经过加工后的视图。

3，拿到具体的sql之后，就需要进一步的优化。如何优化sql？如果要展开的话，又是一个长篇。这里简单提一下：首先当然是获取执行计划了，建议使用获取扩展的执行统计信息：
        explain format=json select ...from ....;

可以获取更为详细的执行计划信息。接着，对照sql优化法则：

1，减少表连接；2，合适的索引；3，合适的复合索引；4，合适的连接顺序，最好的顺序是驱动视图结果集最小。