首页 » MySQL » MySQL 5.7 使用diagnostics() Procedure生成”AWR” Report

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_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

打赏

目前这篇文章还没有评论(Rss)

我要评论