火焰图就是看顶层的哪个函数占据的宽度最大。只要有”平顶”，就表示该函数可能存在性能问题。颜色没有特殊含义，因为火焰图表示的是 CPU 的繁忙程度，所以一般选择暖色调。常见的火焰图类型有On-CPU、Off-CPU、Memory等等。

采集原理分析

1）硬件厂商加入PMU单元（performance monitor unit），允许软件针对某种硬件事件设置 counter，此后处理器便开始统计该事件的发生次数，当发生的次数超过 counter 内设置的值后，便产生中断。比如 cache miss 达到某个值后，PMU 便能产生相应的中断。捕获这些中断，便可以考察程序对这些硬件特性的利用效率了。

2）Tracepoint 是散落在内核源代码中的一些 hook，在特定的代码被运行到时被触发，这一特性可以被各种 trace/debug 工具所使用。

工具选择

Perf 利用的就是Tracepoint的hook。假如您想知道在应用程序运行期间，内核内存管理模块的行为，便可以利用潜伏在 slab 分配器中的 tracepoint。当内核运行到这些 tracepoint 时，便会通知 perf。Perf 将 tracepoint 产生的事件记录下来，生成报告，通过分析这些报告，调优人员便可以了解程序运行时期内核的种种细节，对性能症状作出更准确的诊断。

分析使用

perf的使用有很详细的文档，这里就不做介绍，有兴趣的可以去搜索看一下。

开源的火焰图工具

  • 1）cpu火焰图

    cpu占用过高正常做法通过日志等方式去确定问题。有了火焰图，可以很清晰的发现哪个函数占用cpu过高并解决问题。

    2.   cpu占用过高，执行中的时间通常又分为用户态时间user和系统态时间sys。
    4.   b）off-CPU
    5.   cpu过低，利用率不高。等待下一轮CPU，或者等待I/O、锁、换页等等，其状态可以细分为可执行、匿名换页、睡眠、锁、空闲等状态。

  • 2）内存火焰图

    程序如果出现内存泄漏，同样也可以使用内存级别火焰图快速分析问题。

总结

利用上面的工具可以很详细的分析出cpu的消耗分布和内存的一个具体占用情况。

首先摸清资源的消耗分布，主要从cpu，内存，io来分析可能存在的瓶颈。

系统层面主要通过top能看到系统每个进程的cpu消耗情况，从而确定当前mysql进程的cpu消耗情况，如果发现该mysql的cpu确实消耗大，从而进一步分析cpu消耗在什么地方。

可能原因：cpu消耗过大通常情况下都是有慢sql造成的，这里的慢sql包括全表扫描，扫描数据量过大，内存排序，磁盘排序，锁争用等待，等待磁盘io等。

sql执行状态：show processlist后出现大量的语句，sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，locked，Using filesort，都是有潜在有性能问题的sql。

状态解析：

  • sending data: 表示sql正在从表中查询数据，如果查询条件没有走适当的索引，可能会导致sql执行时间过长。
  • Copying to tmp table on disk：表示由于临时结果集太大，超过了数据库规定的临时内存大小，需要拷贝临时结果集到磁盘上，这个时候需要用户对sql进行优化，比如从业务角度来增加过滤条件和结果集限制等减少结果集。
  • Sorting result, Using filesort：表示sql正在执行排序操作，排序操作会引起较多的cpu消耗，通常的优化方法会添加适当的索引来消除排序，或者缩小排序的结果集。

sql定位:

  • topsql排序分析

    排序占用cpu高，可以从平均单条sql指纹的资源占用和并发条数来统计总的一个消耗，这里可以用cout_star（可以每次采集后置0）和sum_sort_rows来计算总的排序行数。

    1.   SELECT DIGEST_TEXT, COUNT_STAR,SUM_SORT_ROWS,TRUNCATE(AVG_TIMER_WAIT/1000000000000,6) as AVG_TIMER_WAIT,SUM_ROWS_EXAMINED,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES,SUM_ROWS_SENT,FIRST_SEEN, LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC limit 1\G;
    2. *************************** 1. row ***************************
    3.               DIGEST_TEXT: SELECT `table_schema` , TABLE_NAME , ENGINE , `data_size` + `index_size` + `data_free` AS `total_size` , `data_size` , `index_size` , `data_free` , CASE WHEN `data_size` = ? THEN ? ELSE `ROUND` ( ? * `data_free` / ( `data_size` + `index_size` + `data_free` ) , ? ) END AS `pct_free` , `table_rows` FROM ( SELECT `table_schema` , TABLE_NAME , ENGINE , `IFNULL` ( `ROUND` ( SUM ( `data_length` ) / ? / ?, ... ) , ? ) `data_size` , `IFNULL` ( `ROUND` ( SUM ( `index_length` ) / ? / ?, ... ) , ? ) `index_size` , `IFNULL` ( `ROUND` ( SUM ( `data_free` ) / ? / ?, ... ) , ? ) `data_free` , `table_rows` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `table_schema` NOT IN (...) GROUP BY `table_schema` , TABLE_NAME ORDER BY `data_length` DESC LIMIT ? ) `t` 
    4.                COUNT_STAR: 309
    5.             SUM_SORT_ROWS: 20040
    6.            AVG_TIMER_WAIT: 24672197000
    7.         SUM_ROWS_EXAMINED: 205704
    8.    SUM_CREATED_TMP_TABLES: 927
    9. SUM_CREATED_TMP_DISK_TABLES: 0
    10.             SUM_ROWS_SENT: 20040
    11.                FIRST_SEEN: 2018-10-17 17:51:48
    13. 1 row in set (0.00 sec)

mysql经常性的OOM，通常是内存不够被OS kill掉，内存主要从两个方面分析，系统进程问题的驻留内存和实际的使用内存进行分析。当你选择了mysql首先要清楚mysql会在哪些地方可能大量消耗内存。目前主要分布在两个方面，用户连接独享线程，全局共享内存。

mysql进程驻留内存

通过 top 进行驻留内存查看，这里注意一个问题，mysql 的 bufferpool 比较特别，正常 bufferpool 的驻留内存会越来越大（即使truncate了表对应的这部分的驻留内存也不会释放的，但free list会增加，可以复用），最大为初始设置的大小。

Mysql Server Memory Usage= Sum of Global Buffers + (number of Connection * Per thread memory variables)

  • 1）用户连接线程独享内存 这里可以具体看下独享内存参数的配置：比如线程栈thread_stack，排序sort_buffer_size，关联join_buffer_size，顺序读read_buffer_size，随机读read_rnd_buffer_size，客户端结果集暂存net_buffer_length，插入缓存bulk_insert_buffer_size，临时表tmp_table_size等。

    a）单个mysql连接线程的内存消耗统计，这里只是统计分配值（具体驻留内存占用值统计不到）

    注意：

    这里要注意的是net_buffer_length，可以动态的申请，根据需求最大能到 max_allowed_packet，所以sql的结果集要尽量小，max_allowed_packet不要设置的过大，如果设置1G，相当于这个连接如果由于大sql可能内存占用会达到1G，如果又是大并发，很可能OOM。

  • 2）全局共享内存 这里可以看下全局共享内存参数的配置：比如连接线程数目thread_cache_size、表缓存数目table_open_cache，表定义缓存数目table_definition_cache，二进制日志binlog_cache_size，innodb日志缓存innodb_log_buffer_size，数据索引缓存innodb_buffer_pool_size，数据字典缓存innodb_additional_mem_pool_size，back_log队列等等。

    a）统计top 10的buffer pool占用内存的表

    1.   mysql> select * from innodb_buffer_stats_by_table order by pages desc limit 10;
    2. +---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
    3. | object_schema | object_name        | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
    4. +---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
    5. | dbaas         | request_stat       | 1.69 MiB   | 1.17 MiB   |   108 |            0 |        41 |        8103 |
    6. | mysql         | innodb_index_stats | 608.00 KiB | 503.25 KiB |    38 |           21 |         0 |        4711 |
    7. | InnoDB System | SYS_COLUMNS        | 400.00 KiB | 332.66 KiB |    25 |           11 |         3 |        5312 |
    8. | InnoDB System | SYS_TABLES         | 160.00 KiB | 50.36 KiB  |    10 |            2 |         0 |         357 |
    9. | InnoDB System | SYS_INDEXES        | 128.00 KiB | 84.78 KiB  |     8 |            5 |         2 |        1162 |
    10. | dbaas         | kpi_key_ins        | 80.00 KiB  | 32.95 KiB  |     5 |            0 |         3 |         214 |
    11. | mysql         | innodb_table_stats | 80.00 KiB  | 30.85 KiB  |     5 |            2 |         0 |         428 |
    12. | InnoDB System | SYS_DATAFILES      | 64.00 KiB  | 25.91 KiB  |     4 |            3 |         0 |         445 |
    13. | InnoDB System | SYS_TABLESPACES    | 64.00 KiB  | 25.48 KiB  |     4 |            3 |         0 |         445 |

    注意：

    这里的内存消耗主要在bufferpool这块，可以查看bufferpool中的free list的状况和命中率来决定配置多大的bufferpool合适，不是越大越好。

物理空间

  • 1）临时空间ibtmp

    ibtmp相当于一个共享表空间，用来存放sql查询的临时表，大实例上有大的sql和大并发的临时表sql会导致这个文件空间越来越大，从而可能把主机空间磁盘打满。

    注意:

    如果想清理ibtmp空间需要重启mysql实例，然后会进行初始化这个ibtmp。

  • 2）日志空间

    日志空间的快速增长主要体现在主库的binlog和从库的binlog、relaylog。根据需求合理的删除对应的binlog，可以对应上传日志到oss来保留增量。

    注意:

    清除binlog purge就好了，但如果对于从库有大量的relaylog导致空间打满，可以考虑暂停io线程来拉取主库的日志。可以利用sql线程把本地已有的relaylog给回放掉。

  • 3）数据空间

    数据空间主要体现在数据ibd文件和索引文件上， 经常性的看下top表的数据大小或者索引大小，杜绝这种大表或者大索引导致空间满。

    注意:

    表数据量不要太大，要做好拆分方案，对于分区表，不要过多分区，做好定期的清除掉不要的分区。如果有表没去考虑做拆分或者分区的，有大量的delete，要考虑做定期的表重组，减少碎片。

IO消耗

磁盘的io直接关系到mysql的吞吐率，对于io的消耗能精准的定位到资源的分布显得尤为重要。

  • 1）逻辑io请求数

逻辑io请求数能更好的反映对应表的访问频率，也能让业务人员能更好的把握系统数据库的一个调用情况。

a) 统计top表的逻辑io次数（根据增删改查的请求频率来了解当前数据库的一个压力情况）

b）统计mysql物理文件的物理io写入字节数(可以定位出一个表的读写io占比和实际的平均写入量来判断占用io资源)

  1. mysql> select * from io_global_by_file_by_bytes limit 10;
  2. +---------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
  3. | file                                              | count_read | total_read | avg_read  | count_write | total_written | avg_write | total      | write_pct |
  4. +---------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
  5. | @@basedir/data/ibtmp1                             |          0 | 0 bytes    | 0 bytes   |         326 | 16.91 MiB     | 53.10 KiB | 16.91 MiB  |    100.00 |
  6. | @@basedir/data/ibdata1                            |        419 | 8.56 MiB   | 20.93 KiB |           5 | 96.00 KiB     | 19.20 KiB | 8.66 MiB   |      1.08 |
  7. | @@basedir/data/dbaas/request_test.ibd             |        110 | 1.72 MiB   | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 1.72 MiB   |      0.00 |
  8. | @@basedir/data/mysql/innodb_index_stats.ibd       |         42 | 672.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 672.00 KiB |      0.00 |
  9. | @@basedir/data/clouddba/sys_test.ibd              |         14 | 224.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 224.00 KiB |      0.00 |
  10. | @@basedir/data/clouddba/sys_actest_log.ibd        |         11 | 176.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 176.00 KiB |      0.00 |
  11. | @@basedir/data/clouddba/dba_diagnosis_adtest.ibd  |          8 | 128.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 128.00 KiB |      0.00 |
  12. | @@basedir/data/dbaas/testkey_ins.ibd              |          7 | 112.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 112.00 KiB |      0.00 |
  13. | @@basedir/data/mysql/innodb_table_stats.ibd       |          7 | 112.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 112.00 KiB |      0.00 |
  14. | @@basedir/data/clouddba/dba_diagnosis_tratest.ibd |          6 | 96.00 KiB  | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 96.00 KiB  |      0.00 |
  15. +---------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+