|
MySQL性能问题，除了慢语句，binlog分析的常规手段，其实sys库下也提供了比较分析全面的功能。目前版本的中提供了许多有意思系统存储过程：比如创建整个库的视图（create_synonym_db）等。
##通过create_synonym_db一键创建视图库，创建db1对应的db1_info的视图库
mysql > show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| db3 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
16 rows in set (0.00 sec)
mysql > CALL sys.create_synonym_db('db1', 'db1_info');
+---------------------------------------------+
| summary |
+---------------------------------------------+
| Created 41 views in the `db1_info` database |
+---------------------------------------------+
1 row in set (0.18 sec)
Query OK, 0 rows affected (0.18 sec)
mysql > SHOW FULL TABLES FROM `db1_info`;
+-------------------------+------------+
| Tables_in_db1_info | Table_type |
+-------------------------+------------+
| a1 | VIEW |
| a2 | VIEW |
| a3 | VIEW |
| articles | VIEW |
| articles1 | VIEW |
。。。
对于MySQL来说目前最欠缺的性能排查更有效的手段，下面来了解下，性能排查的3个sys存储过程：
性能排查存储过程
1. diagnostics诊断当前服务器状态报告
创建诊断报告，该报告每30秒开始一次迭代，使用当前的Performance Schema设置最多运行30秒。
因为内容比较多可以采用tee 方式输出。需要开启secure_file_priv参数才可以。
mysql> tee diag.out;
mysql> CALL sys.diagnostics(30, 30, 'current');
mysql> notee;
报告内容非常多，一下指标都有相关的数据：
- GLOBAL VARIABLES
- SHOW MASTER STATUS
- SHOW ENGINE INNODB STATUS
- SELECT * FROM sys.processlist
- Performance Schema Setup - Instruments
- SHOW ENGINE PERFORMANCE_SCHEMA STATUS
- sys.memory_by_host_by_current_bytes
- Overall host_summary
- host_summary_by_file_io_type
- host_summary_by_statement_latency
- Overall io_by_thread_by_latency
- Overall io_global_by_file_by_bytes
- Overall schema_index_statistics
- Overall schema_table_statistics
mysql > CALL sys.diagnostics(30, 30, 'current');
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)
+-------------------------+--------------------------------------+
| Name | Value |
+-------------------------+--------------------------------------+
| Hostname | schouse |
| Port | 3380 |
| Socket | /opt/data8.0/data/mysql.sock |
| Datadir | /opt/data8.0/data/ |
| Server UUID | 4af6a158-aa6d-11eb-82f2-00163e23e2cc |
| ----------------------- | ------------------------------------ |
| MySQL Version | 8.0.28 |
| Sys Schema Version | 2.1.1 |
| Version Comment | MySQL Community Server - GPL |
| Version Compile OS | Linux |
| Version Compile Machine | x86_64 |
| ----------------------- | ------------------------------------ |
| UTC Time | 2022-03-21 03:24:58 |
| Local Time | 2022-03-21 11:24:58 |
| Time Zone | +08:00 |
| System Time Zone | CST |
| Time Zone Offset | 08:00:00 |
+-------------------------+--------------------------------------+
=======================
Configuration
=======================
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
+--------------------------+
| The following output is: |
+--------------------------+
| GLOBAL VARIABLES |
+--------------------------+
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Variable_value |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| activate_all_roles_on_login | OFF |
| admin_address | |
| admin_port | 33090 |
| admin_ssl_crl |
| admin_tls_version | TLSv1.2,TLSv1.3 |
| back_log | 1000 |
| basedir | /opt/idc/mysql8.0.28/ |
| big_tables | OFF |
| bind_address | 172.17.27.48 |
| binlog_cache_size | 32768
。。。
+--------------------------+
| The following output is: |
+--------------------------+
| SHOW MASTER STATUS |
+--------------------------+
1 row in set (15.07 sec)
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000025 | 56904 | | | 4af6a158-aa6d-11eb-82f2-00163e23e2cc:1-511629 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (15.07 sec)
+---------------------------+
| The following output is: |
+---------------------------+
| SHOW ENGINE INNODB STATUS |
+---------------------------+
=====================================
2022-03-22 10:16:48 139752869254912 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 386 srv_active, 0 srv_shutdown, 773146 srv_idle
srv_master_thread log flush and writes: 0
。。。
+-------------------------------+
| The following output is: |
+-------------------------------+
| SELECT * FROM sys.processlist |
+-------------------------------+
+----------------------------------------+
| The following output is: |
+----------------------------------------+
| Performance Schema Setup - Instruments |
+----------------------------------------+
+---------------------------+------------+----------+
| InstrumentClass | EnabledPct | TimedPct |
+---------------------------+------------+----------+
| error | 100.00 | 0.00 |
| idle | 100.00 | 100.00 |
| memory/archive | 100.00 | 0.00 |
| memory/blackhole | 100.00 | 0.00 |
| memory/client | 100.00 | 0.00 |
| memory/component_sys_vars | 100.00 | 0.00
。。。
+-------------------------------+
| The following output is: |
+-------------------------------+
| Delta io_by_thread_by_latency |
+-------------------------------+
1 row in set (0.50 sec)
+-------------------------------+--------+---------------+-------------+-------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-------------------------------+--------+---------------+-------------+-------------+-------------+-----------+----------------+
| log_flusher_thread | 54600 | 1.15 min | 108.69 us | 1.26 ms | 88.44 ms | 16 | NULL |
| log_writer_thread | 108516 | 6.99 s | 994.15 ns | 64.45 us | 87.38 ms | 18 | NULL |
| page_flush_coordinator_thread | 10293 | 1.99 s | 2.94 us | 193.37 us | 8.96 ms | 13 | NULL |
| io_write_thread | 1107 | 852.79 ms | 52.75 us | 770.36 us | 8.72 ms | 12 | NULL |
。。。
这个应该可以形成完整的性能检测报告。
2. ps_trace_statement_digest追踪语句报告
通过events_statements_summary_by_digest表DIGEST列MD5值，在轮询时间内，分析性能。
Explain + Time + Event latency。
##('891ec6860f98ba46d89dd20b0c03652c', 10, 0.1, TRUE, TRUE);
##语句md5，分析需要多长时间，分析间隔，是否截断truncate表 events_statements_history_long and events_stages_history_long，是否自动启用所需的消费者
mysql> CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 10, 0.1, TRUE, TRUE);
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
1 row in set (9.11 sec)
+------------+-----------+-----------+-----------+---------------+------------+------------+
| executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans |
+------------+-----------+-----------+-----------+---------------+------------+------------+
| 21 | 4.11 ms | 2.00 ms | 0 | 21 | 0 | 0 |
+------------+-----------+-----------+-----------+---------------+------------+------------+
1 row in set (9.11 sec)
+------------------------------------------+-------+-----------+
| event_name | count | latency |
+------------------------------------------+-------+-----------+
| stage/sql/statistics | 16 | 546.92 us |
| stage/sql/freeing items | 18 | 520.11 us |
| stage/sql/init | 51 | 466.80 us |
...
| stage/sql/cleaning up | 18 | 11.92 us |
| stage/sql/executing | 16 | 6.95 us |
+------------------------------------------+-------+-----------+
17 rows in set (9.12 sec)
+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
1 row in set (9.16 sec)
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| 166646 | 618.43 us | 1.00 ms | 0 | 1 | 0 | 0 |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
1 row in set (9.16 sec)
# Truncated for clarity...
+-----------------------------------------------------------------+
| sql_text |
+-----------------------------------------------------------------+
| select hibeventhe0_.id as id1382_, hibeventhe0_.createdTime ... |
+-----------------------------------------------------------------+
1 row in set (9.17 sec)
+------------------------------------------+-----------+
| event_name | latency |
+------------------------------------------+-----------+
| stage/sql/init | 8.61 us |
| stage/sql/init | 331.07 ns |
...
| stage/sql/freeing items | 30.46 us |
| stage/sql/cleaning up | 662.13 ns |
+------------------------------------------+-----------+
18 rows in set (9.23 sec)
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
| 1 | SIMPLE | hibeventhe0_ | const | fixedTime | fixedTime | 775 | const,const | 1 | NULL |
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
1 row in set (9.27 sec)
Query OK, 0 rows affected (9.28 sec)
3. statement_performance_analyzer语句性能分析
创建服务器上运行的语句的报告。视图是基于整体和/或增量活动计算的。
| 执行的操作 | 说明 |
|---|
| snapshot | 存储快照。默认情况下是对性能模式events_statements_summary_by_digest表的当前内容做一个快照，通过设置in_table，可以覆盖它以复制指定表的内容。快照存储在sys架构tmp_digests临时表中。 | | overall | 根据in_table指定的表的内容生成一个分析。对于整体分析，in_table可以使用NOW()来使用一个新的快照。这将覆盖现有的快照。使用in_table的NULL值来使用现有的快照。如果in_table为NULL且不存在快照，则创建一个新的快照。in_views参数和statement_performance_analyzer。限制配置选项会影响此过程的操作。 | | delta | 生成delta分析。这个增量是在in_table指定的引用表和快照之间计算的，快照必须存在。此操作使用sys架构tmp_digests_delta临时表。in_views参数和statement_performance_analyzer。限制配置选项会影响此过程的操作。 | | create_tmp | 创建一个适合存储快照以供以后使用的临时表(例如，用于计算增量)。 | | create_table | 创建一个适合存储快照以供以后使用的普通表(例如，用于计算增量)。 | | save | 将快照保存到in_table指定的表中。表必须存在并且具有正确的结构。如果不存在快照，则创建新的快照。 | | cleanup | 删除用于快照和增量的临时表。 |
参考性能数据in_views SET：
| views | 参考视图 |
|---|
| with_runtimes_in_95th_percentile | 使用statements_with_runtimes_in_95th_percentile视图。 | | analysis | 使用statement_analysis视图。 | | with_errors_or_warnings | 使用statements_with_errors_or_warnings视图。 | | with_full_table_scans | 使用statements_with_full_table_scans视图。 | | with_sorting | 使用statements_with_sorting视图。 | | with_temp_tables | 使用statements_with_temp_tables视图。 | | custom | 使用自定义视图。 |
例子1：
events_statements_summary_by_digest最后一次截断后的95% 语句报告
mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> DO SLEEP(60);
Query OK, 0 rows affected (1 min 0.00 sec)
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.05 sec)
。。。
mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.03 sec)
备注：对应的更改in_views SET参数就可以获取不同类型的SQL语句性能报表。
例子2：
创建一个包含全表扫描的第95百分位查询和前10个查询的整体报告:
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> SET @sys.statement_performance_analyzer.limit = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
+-----------------------------------------+
| Next Output |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)
。。。
+-------------------------------------+
| Next Output |
+-------------------------------------+
| Top 10 Queries with Full Table Scan |
+-------------------------------------+
1 row in set (0.09 sec)
。。。
例子3：
使用Linux中的watch命令刷新,总执行时间排序的前10个查询:
mysql> CREATE OR REPLACE VIEW mydb.my_statements AS
SELECT sys.format_statement(DIGEST_TEXT) AS query,
SCHEMA_NAME AS db,
COUNT_STAR AS exec_count,
sys.format_time(SUM_TIMER_WAIT) AS total_latency,
sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC;
Query OK, 0 rows affected (0.10 sec)
mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
Query OK, 0 rows affected (0.10 sec)
shell > watch -n 10 "mysql -uroot -p123456 -S /opt/data8.0/data/mysql.sock sys --table -e \"
SET @sys.statement_performance_analyzer.view = 'mydb.my_statements';
SET @sys.statement_performance_analyzer.limit = 10;
CALL statement_performance_analyzer('snapshot', NULL, NULL);
CALL statement_performance_analyzer('delta', 'mydb.digests_prev', 'custom');
CALL statement_performance_analyzer('save', 'mydb.digests_prev', NULL);
\""
Every 10.0s: mysql sys --table -e " schouse: Tue Mar 22 00:00:47 2022
+----------------------------------+
| Next Output |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
。。。
+----------------------------------+
| Next Output |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
。。。
总结
3个存储过程都有不同的特色，运行报告+性能分析的结合数据库，提供了一系类系统指标，更准确判断问题根源。
- 目前系统库存储过程5.7 和 8.0 版本都支持。
- 上诉过程中，需要足够的权限来设置的会话变量sql_log_bin禁用二进制日志记录。
- 还需开启performance_schema
- 除此之外，因使用存储过程，临时表，ps库，对于高负载下可能存在一定的影响性能。
这些手段需要使用经验，所以善用！
|