数据库内核月报

数据库内核月报 - 2018 / 11

MySQL · 最佳实践 · 性能问题多维度诊断

Author: jixiang

背景

mysql性能问题是所有业务开发和运维人员的噩梦,有时候会遇到一些疑难杂症,监控系统也无法立即能分析出问题产生的原因,如何更好的定位和分析问题所产生的原因变得尤为重要,以下主要从两个大维度来定位分析,系统层面进程层面来分析,从正向思维,首先摸清资源的消耗分布,然后才能真正的知道消耗在什么地方,从而推断出问题的产生的原因。

通过主机层面进程调用栈分析

火焰图

火焰图就是看顶层的哪个函数占据的宽度最大。只要有”平顶”,就表示该函数可能存在性能问题。颜色没有特殊含义,因为火焰图表示的是 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的使用有很详细的文档,这里就不做介绍,有兴趣的可以去搜索看一下。

开源的火焰图工具

github的地址:git clone https://github.com/lidaohang/quick_location.git

总结

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

通过mysql资源维度统计分析

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

A)cpu维度

系统层面主要通过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。

状态解析

sql定位:

如何更好的定位这些特别是大并发排序导致cpu飚高,导致cpu忙碌,这里可以从top sql耗时详细阶段分布分析和top sql排序消耗和并发数来定位。

B)内存维度

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)

C)空间IO维度

物理空间

空间问题是线上经常出现的用户痛点,怎么规划好自己的实例空间很重要,能更好的利用好资源。

IO消耗

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

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

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

mysql> SELECT object_schema AS tb_schema,            object_name AS table_name,            count_star AS rows_io_total,            count_read AS rows_read,            count_write AS rows_write,            count_fetch AS rows_fetchs,            count_insert AS rows_inserts,            count_update AS rows_updates,            count_delete AS rows_deletes,             CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency,             CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,             CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency,             CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency     FROM table_io_waits_summary_by_table        ORDER BY sum_timer_wait DESC limit 10 ;
+-----------+-----------------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+
| tb_schema | table_name            | rows_io_total | rows_read | rows_write | rows_fetchs | rows_inserts | rows_updates | rows_deletes | fetch_latency | insert_latency | update_latency | delete_latency |
+-----------+-----------------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+
| dba11     | sys_test              |     318474368 | 317338900 |    1135468 |   317338900 |            6 |      1135462 |            0 | 0.25h         | 0.00h          | 0.05h          | 0.00h          |
| dba11     | dba_montest           |         17688 |     17688 |          0 |       17688 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | sys_test11            |           220 |       220 |          0 |         220 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | dba_operation         |           208 |       208 |          0 |         208 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | dba_diagnosis_test11  |           208 |       208 |          0 |         208 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| tst       | aa                    |            43 |        42 |          1 |          42 |            1 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | sys_actest_log        |            12 |         0 |         12 |           0 |           12 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| as        | instance_test         |             8 |         8 |          0 |           8 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | dba_diagnosis_tratest |           104 |       104 |          0 |         104 |            0 |            0 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
| dba11     | sys_actest            |            36 |        30 |          6 |          30 |            0 |            6 |            0 | 0.00h         | 0.00h          | 0.00h          | 0.00h          |
+-----------+-----------------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+

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

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

总结

mysql的性能诊断除了上面我提的几点,当然还有很多其他的办法我就不一一列出了(比如网络等等),更微粒度的监控数据采集,更多维度多功能的问题诊断分析,数据库性能问题的诊断将不是问题。以后的数据库将迈着自治的道路上去走,有感兴趣的同学可以与我交流,共同进步。