Oracle Database有强大的AWR报告分析整体的服务器性能问题， 但是MySQL之前是没有的，需要自定义大量的脚本生成监控数据， 从MySQL 5.7 (5.7.9)开始，可以使用sys.diagnostics()存储过程依赖于PERFORMANCE_SCHEMA，生成类似于Oracle AWR一样的MySQL性能报告。 官方文档更多看这里
此存储过程是也是利用snapshot快照前的性能视图增量值，生成全局性能报告。
该存储过程有三个重要参数，in_max_runtime、in_interval、in_auto_config
in_max_runtime： 总共最大收集时间，单位秒，null 为默认值60秒；
in_interval：快照间的间隔时间，单位秒，null为默认30秒；
in_auto_config： Performance Schema的选项分析current\\medium\\full, 启的选项指标越全，对MySQL服务的性能影响越大，FULL的影响最大。
下面我们收集2分钟的一个性能报告，每次间隔30秒，生成本本报告 ，下面是只附上了部分内容 。
view diag.out
这数据再配合上OS crontab就可以实现类似AWR的功能了
references https://dev.mysql.com/doc/refman/5.7/en/sys-diagnostics.html & Mahmoud Hatem's Archive
This procedure disables binary logging during its execution by manipulating the session value of the sql_log_bin system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables.此存储过程是也是利用snapshot快照前的性能视图增量值，生成全局性能报告。
该存储过程有三个重要参数，in_max_runtime、in_interval、in_auto_config
in_max_runtime： 总共最大收集时间，单位秒，null 为默认值60秒；
in_interval：快照间的间隔时间，单位秒，null为默认30秒；
in_auto_config： Performance Schema的选项分析current\\medium\\full, 启的选项指标越全，对MySQL服务的性能影响越大，FULL的影响最大。
下面我们收集2分钟的一个性能报告，每次间隔30秒，生成本本报告 ，下面是只附上了部分内容 。
mysql> tee diag.out;
mysql> CALL sys.diagnostics(120, 30, 'current');
mysql> notee;
view diag.out
+-------------------------+---------------------------------------------------------+
| Name | Value |
+-------------------------+---------------------------------------------------------+
| Hostname | localhost.localdomain |
| Port | 3306 |
| Socket | /tmp/mysql.sock |
| Datadir | /usr/local/mysql/data/ |
| Server UUID | 44094390-4fa3-11e9-b3ae-080027963204 |
| ----------------------- | ------------------------------------------------------- |
| MySQL Version | 5.7.25-enterprise-commercial-advanced |
| Sys Schema Version | 1.5.1 |
| Version Comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| Version Compile OS | el7 |
| Version Compile Machine | x86_64 |
| ----------------------- | ------------------------------------------------------- |
| UTC Time | 2019-03-26 14:15:37 |
| Local Time | 2019-03-26 10:15:37 |
| Time Zone | SYSTEM |
| System Time Zone | EDT |
| Time Zone Offset | -04:00:00 |
+-------------------------+---------------------------------------------------------+
17 rows in set (0.02 sec)
| InnoDB | |
=====================================
2019-03-26 10:17:08 0x7f9e104f0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 13 srv_active, 0 srv_shutdown, 2670 srv_idle
srv_master_thread log flush and writes: 2683
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8
OS WAIT ARRAY INFO: signal count 8
RW-shared spins 0, rounds 14, OS waits 7
RW-excl spins 0, rounds 30, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 14.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3131
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421792143439696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
262 OS file reads, 650 OS file writes, 47 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.65 writes/s, 0.06 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
3.35 hash searches/s, 10.19 non-hash searches/s
---
LOG
---
Log sequence number 2525074
Log flushed up to 2525074
Pages flushed up to 2525074
Last checkpoint at 2525065
0 pending log flushes, 0 pending chkp writes
30 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 266936
Buffer pool size 8191
Free buffers 7779
Database pages 404
Old database pages 0
Modified db pages 19
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 231, created 173, written 603
0.00 reads/s, 0.00 creates/s, 0.61 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 404, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3001, Main thread ID=140316866721536, state: sleeping
Number of rows inserted 15898, updated 0, deleted 0, read 18467
42.61 inserts/s, 0.00 updates/s, 0.00 deletes/s, 43.48 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
+--------------------------+
| The following output is: |
+--------------------------+
| InnoDB - Transactions |
+--------------------------+
1 row in set (1 min 31.40 sec)
Empty set (1 min 31.40 sec)
+-------------------------------+
| The following output is: |
+-------------------------------+
| SELECT * FROM sys.processlist |
+-------------------------------+
1 row in set (1 min 31.40 sec)
+---------------------------------------------------+
| The following output is: |
+---------------------------------------------------+
| SELECT * FROM sys.memory_by_host_by_current_bytes |
+---------------------------------------------------+
1 row in set (1 min 31.50 sec)
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| host | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| background | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
2 rows in set (1 min 31.50 sec)
+-----------------------------------------------------+
| The following output is: |
+-----------------------------------------------------+
| SELECT * FROM sys.memory_by_thread_by_current_bytes |
+-----------------------------------------------------+
1 row in set (1 min 31.50 sec)
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 16 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 17 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 18 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 19 | innodb/srv_master_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 20 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 21 | innodb/srv_purge_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 22 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 23 | innodb/dict_stats_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 24 | innodb/buf_dump_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 25 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 26 | sql/compress_gtid_table | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 30 | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 1 | sql/main | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 2 | sql/thread_timer_notifier | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 3 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 4 | innodb/io_log_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 5 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 6 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 7 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 8 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 10 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 11 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 12 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 13 | innodb/page_cleaner_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| 15 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
26 rows in set (1 min 31.55 sec)
+---------------------------------------------------+
| The following output is: |
+---------------------------------------------------+
| SELECT * FROM sys.memory_by_user_by_current_bytes |
+---------------------------------------------------+
1 row in set (1 min 31.55 sec)
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| root | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
| background | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
2 rows in set (1 min 31.56 sec)
+---------------------------------------+
| The following output is: |
+---------------------------------------+
| SHOW ENGINE PERFORMANCE_SCHEMA STATUS |
+---------------------------------------+
1 row in set (1 min 31.58 sec)
+--------------------+-------------------------------------------------------------+-----------+
| Type | Name | Status |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | events_waits_current.size | 176 |
| performance_schema | events_waits_current.count | 1536 |
| performance_schema | events_waits_history.size | 176 |
| performance_schema | events_waits_history.count | 2560 |
| performance_schema | events_waits_history.memory | 450560 |
| performance_schema | events_waits_history_long.size | 176 |
| performance_schema | events_waits_history_long.count | 10000 |
| performance_schema | events_waits_history_long.memory | 1760000 |
| performance_schema | (pfs_mutex_class).size | 256 |
...
+-----------------------------------------------+
| The following output is: |
+-----------------------------------------------+
| CALL sys.ps_statement_avg_latency_histogram() |
+-----------------------------------------------+
1 row in set (1 min 31.58 sec)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Performance Schema Statement Digest Average Latency Histogram |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
. = 1 unit
* = 2 units
# = 3 units
(0 - 3831ms) 2 | .
(3831 - 7662ms) 0 |
(7662 - 11494ms) 0 |
(11494 - 15325ms) 0 |
(15325 - 19156ms) 0 |
(19156 - 22987ms) 0 |
(22987 - 26819ms) 0 |
(26819 - 30650ms) 0 |
(30650 - 34481ms) 0 |
(34481 - 38312ms) 0 |
(38312 - 42144ms) 0 |
(42144 - 45975ms) 0 |
(45975 - 49806ms) 0 |
(49806 - 53637ms) 0 |
(53637 - 57469ms) 0 |
(57469 - 61300ms) 0 |
+-------------------------------+
| The following output is: |
+-------------------------------+
| Delta io_by_thread_by_latency |
+-------------------------------+
1 row in set (1 min 31.72 sec)
+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| page_cleaner_thread | 625 | 230.51 ms | 5.02 us | 368.82 us | 86.48 ms | 13 | NULL |
| main | 1715 | 107.05 ms | 364.34 ns | 62.42 us | 78.26 ms | 1 | NULL |
| io_write_thread | 11 | 75.88 ms | 3.72 ms | 6.90 ms | 34.03 ms | 9 | NULL |
| srv_master_thread | 20 | 47.61 ms | 44.57 us | 2.38 ms | 8.08 ms | 19 | NULL |
| io_log_thread | 7 | 30.85 ms | 3.79 ms | 4.41 ms | 5.74 ms | 4 | NULL |
| buf_dump_thread | 108 | 2.00 ms | 1.89 us | 18.56 us | 115.23 us | 24 | NULL |
+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
6 rows in set (1 min 31.72 sec)
+-------------------------------+
| The following output is: |
+-------------------------------+
| Delta waits_global_by_latency |
+-------------------------------+
1 row in set (1 min 31.81 sec)
+--------------------------------------+-------+---------------+-------------+-------------+
| events | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/innodb/innodb_data_file | 131 | 114.58 ms | 874.65 us | 86.48 ms |
| wait/io/file/innodb/innodb_log_file | 4 | 9.15 ms | 2.29 ms | 8.08 ms |
+--------------------------------------+-------+---------------+-------------+-------------+
2 rows in set (1 min 31.81 sec)
+------------------------------------------+
| The following output is: |
+------------------------------------------+
| Delta wait_classes_global_by_avg_latency |
+------------------------------------------+
1 row in set (1 min 31.81 sec)
+--------------+-------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file | 135 | 123.73 ms | 0 ps | 916.52 us | 86.48 ms |
+--------------+-------+---------------+-------------+-------------+-------------+
1 row in set (1 min 31.81 sec)
+--------------------------------------+
| The following output is: |
+--------------------------------------+
| Delta wait_classes_global_by_latency |
+--------------------------------------+
1 row in set (1 min 31.81 sec)
+--------------+-------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file | 135 | 123.73 ms | 0 ps | 916.52 us | 86.48 ms |
+--------------+-------+---------------+-------------+-------------+-------------+
+---------------------------+
| The following output is: |
+---------------------------+
| SELECT * FROM sys.metrics |
+---------------------------+
1 row in set (1 min 31.81 sec)
...
使用-H先选项可以生成html页面，不过没有样式，非常丑，确实是ORACLE RDBMS 还差了好几条街。
mysql -u root -p -H -e"CALL sys.diagnostics(120, 30, 'current');" > ./current_instance_report.html这数据再配合上OS crontab就可以实现类似AWR的功能了
0 * * * * mysql -u root -H -e"CALL sys.diagnostics(3600, 1800, 'current');" > /home/mysql/awr/instance_report_$(date +"%Y-%m-%d_%H-%M")
references https://dev.mysql.com/doc/refman/5.7/en/sys-diagnostics.html & Mahmoud Hatem's Archive
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
