MySQL 5.7 使用diagnostics() Procedure生成”AWR” Report
Oracle Database有强大的AWR报告分析整体的服务器性能问题， 但是MySQL之前是没有的，需要自定义大量的脚本生成监控数据， 从MySQL 5.7 (5.7.9)开始，可以使用sys.diagnostics()存储过程依赖于PERFORMANCE_SCHEMA，生成类似于Oracle AWR一样的MySQL性能报告。 官方文档更多看这里
This procedure disables binary logging during its execution by manipulating the session value of the
sql_log_binsystem 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
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。