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
1)cpu火焰图
cpu占用过高正常做法通过日志等方式去确定问题。有了火焰图,可以很清晰的发现哪个函数占用cpu过高并解决问题。
a)on-CPU
cpu占用过高,执行中的时间通常又分为用户态时间user和系统态时间sys。
b)off-CPU
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。
状态解析:
sql定位:
如何更好的定位这些特别是大并发排序导致cpu飚高,导致cpu忙碌,这里可以从top sql耗时详细阶段分布分析和top sql排序消耗和并发数来定位。
topsql耗时分析
1)定位top耗时长的前5条sql
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long order by Duration desc limit 5;
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1436 | 0.033460 | SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long order by Duration desc limit 5 |
| 4323480 | 0.003146 | update node set role = 'main', heartbeat = current_timestamp where id = 1960 |
| 4445223 | 0.002929 | update node set role = 'main', heartbeat = current_timestamp where id = 1960 |
| 4689042 | 0.002895 | update node set role = 'main', heartbeat = current_timestamp where id = 1960 |
| 4380520 | 0.002753 | update node set role = 'main', heartbeat = current_timestamp where id = 1960 |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2)根据对应的top5耗时长的sql的event_id查看该sql具体耗时在哪个地方
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=1436;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000097 |
| stage/sql/checking permissions | 0.000014 |
| stage/sql/Opening tables | 0.000019 |
| stage/sql/init | 0.000026 |
| stage/sql/System lock | 0.000009 |
| stage/sql/optimizing | 0.000006 |
| stage/sql/statistics | 0.000014 |
| stage/sql/preparing | 0.000012 |
| stage/sql/Sorting result | 0.000007 |
| stage/sql/executing | 0.000005 |
| stage/sql/Sending data | 0.000009 |
| stage/sql/Creating sort index | 0.028948 |
| stage/sql/end | 0.000005 |
| stage/sql/query end | 0.000006 |
| stage/sql/closing tables | 0.000008 |
| stage/sql/freeing items | 0.000019 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+----------+
topsql排序分析
排序占用cpu高,可以从平均单条sql指纹的资源占用和并发条数来统计总的一个消耗,这里可以用cout_star(可以每次采集后置0)和sum_sort_rows来计算总的排序行数。
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;
*************************** 1. row ***************************
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`
COUNT_STAR: 309
SUM_SORT_ROWS: 20040
AVG_TIMER_WAIT: 24672197000
SUM_ROWS_EXAMINED: 205704
SUM_CREATED_TMP_TABLES: 927
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_ROWS_SENT: 20040
FIRST_SEEN: 2018-10-17 17:51:48
LAST_SEEN: 2018-10-23 11:39:16
1 row in set (0.00 sec)
mysql经常性的OOM,通常是内存不够被OS kill掉,内存主要从两个方面分析,系统进程问题的驻留内存和实际的使用内存进行分析。当你选择了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连接线程的内存消耗统计,这里只是统计分配值(具体驻留内存占用值统计不到)
select b.thd_id, b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id limit 1;
*************************** 1. row ***************************
thd_id: 30
user: root@localhost
current_count_used: 11259
current_allocated: 8.34 MiB
current_avg_alloc: 777 bytes
current_max_alloc: 3.48 MiB
total_allocated: 215.64 MiB
current_statement: select b.thd_id, b.user, curre ... b where a.thread_id = b.thd_id
注意:
这里要注意的是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占用内存的表
mysql> select * from innodb_buffer_stats_by_table order by pages desc limit 10;
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| dbaas | request_stat | 1.69 MiB | 1.17 MiB | 108 | 0 | 41 | 8103 |
| mysql | innodb_index_stats | 608.00 KiB | 503.25 KiB | 38 | 21 | 0 | 4711 |
| InnoDB System | SYS_COLUMNS | 400.00 KiB | 332.66 KiB | 25 | 11 | 3 | 5312 |
| InnoDB System | SYS_TABLES | 160.00 KiB | 50.36 KiB | 10 | 2 | 0 | 357 |
| InnoDB System | SYS_INDEXES | 128.00 KiB | 84.78 KiB | 8 | 5 | 2 | 1162 |
| InnoDB System | SYS_FIELDS | 112.00 KiB | 71.89 KiB | 7 | 3 | 3 | 1686 |
| dbaas | kpi_key_ins | 80.00 KiB | 32.95 KiB | 5 | 0 | 3 | 214 |
| mysql | innodb_table_stats | 80.00 KiB | 30.85 KiB | 5 | 2 | 0 | 428 |
| InnoDB System | SYS_DATAFILES | 64.00 KiB | 25.91 KiB | 4 | 3 | 0 | 445 |
| 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直接关系到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的性能诊断除了上面我提的几点,当然还有很多其他的办法我就不一一列出了(比如网络等等),更微粒度的监控数据采集,更多维度多功能的问题诊断分析,数据库性能问题的诊断将不是问题。以后的数据库将迈着自治的道路上去走,有感兴趣的同学可以与我交流,共同进步。