Mysql5.7性能利器—性能分析数据库Sys
最近翻看2016PHPCon大会的ppt,发现一个关于Mysql5.7新特性新增的Sys数据库,发现这个东东很有用,对开发者来说简直是把数据库相关的性能都生成好了展示给你看就行了,其实在5.7以前mysql本身也提供了performance_schema性能字典,但是这个东东太专业了很多数据很难搞懂,5.6(需要开启),5.7(默认开启)sys库提供了一些列的表,视图,存储过程,函数来帮助开发者分析数据库的性能。通过各个角度(用户,请求,内存,io,锁,会话等等)来分析当前数据库的运行状况,
提醒下,这个数据库其实只有一张表,sys_config,其他的都是view,数据都是来自:performance_schema,其中statement_analysis跟x$statement_analysis内容是一样的只是数据格式不一样,x$系列的表是为了跟程序分析使用。
看下statement_analysis的创建语句,(不用看这个,只是举例说明都是视图不是真实的表)
举个例子:以前慢查询需要去开启慢查询log,然后用脚本处理log,现在只要查询对应的表就可以
表结构(语句分析表)
mysql> desc statement_analysis; ------------------- --------------------- ------ ----- --------------------- ------- | Field | Type | Null | Key | Default | Extra | ------------------- --------------------- ------ ----- --------------------- ------- | query | longtext | YES | | NULL | | | db | varchar(64) | YES | | NULL | | | full_scan | varchar(1) | NO | | | | | exec_count | bigint(20) unsigned | NO | | NULL | | | err_count | bigint(20) unsigned | NO | | NULL | | | warn_count | bigint(20) unsigned | NO | | NULL | | | total_latency | text | YES | | NULL | | | max_latency | text | YES | | NULL | | | avg_latency | text | YES | | NULL | | | lock_latency | text | YES | | NULL | | | rows_sent | bigint(20) unsigned | NO | | NULL | | | rows_sent_avg | decimal(21,0) | NO | | 0 | | | rows_examined | bigint(20) unsigned | NO | | NULL | | | rows_examined_avg | decimal(21,0) | NO | | 0 | | | rows_affected | bigint(20) unsigned | NO | | NULL | | | rows_affected_avg | decimal(21,0) | NO | | 0 | | | tmp_tables | bigint(20) unsigned | NO | | NULL | | | tmp_disk_tables | bigint(20) unsigned | NO | | NULL | | | rows_sorted | bigint(20) unsigned | NO | | NULL | | | sort_merge_passes | bigint(20) unsigned | NO | | NULL | | | digest | varchar(32) | YES | | NULL | | | first_seen | timestamp | NO | | 0000-00-00 00:00:00 | | | last_seen | timestamp | NO | | 0000-00-00 00:00:00 | | ------------------- --------------------- ------ ----- --------------------- ------- 23 rows in set (0.00 sec)
查看执行时间最长的前17条语句
select * from statement_analysis limit 17;
各个相关的参数都给出来了
下面简单sys库里面的概况,(excle粘进来不能看,就贴图了)观看一遍这个就大概知道这个sys库可以给我们提供什么东西了
表:
视图:
存储过程:这个就不备注了,因为我也没太懂
函数:
官方文档详见:
http://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-index.html
http://dev.mysql.com/doc/refman/5.7/en/sys-schema.html
官方文档有对应的例子和解释,系统的学习资料还是官方文档,
通过上面的各种视图我们简单的举例说明:(本机没有跑什么数据,主要是看对应的字段),根据对应的表名基本就可以知道是做什么分析用的
查看当前的会话
mysql> select * from session; -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ - --------------- ----------- ------------------- -------- ------------- ----------- ---------------- ------ -------------- | thd_id | conn_id | user | db | command | state | time | current_statement | statement_latency | progress | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | current_memory | last_wait | last_wait_latency | source | trx_latency | trx_state | trx_autocommit | pid | program_name | -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ - --------------- ----------- ------------------- -------- ------------- ----------- ---------------- ------ -------------- | 65 | 40 | root@localhost | NULL | Sleep | NULL | 7651 | NULL | NULL | NULL | 0 ps | 7 | 7 | 0 | 1 | 0 | YES | SHOW DATABASES | 1.07 ms | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 66 | 41 | root@localhost | admin | Sleep | NULL | 7024 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | SHOW CREATE TABLE `text` | 62.72 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 77 | 52 | root@localhost | sys | Sleep | NULL | 606 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | SHOW CREATE VIEW `statement_analysis` | 685.38 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 78 | 53 | root@localhost | sys | Sleep | NULL | 596 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | NULL | 67.00 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 64 | 39 | root@localhost | sys | Query | Sending data | 0 | select * from session | 2.59 ms | NULL | 2.00 ms | 0 | 0 | 0 | 4 | 1 | YES | NULL | NULL | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | 1688 | mysql | -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ -
谁使用了最多的资源
select * from user_summary; ------------ ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------ | user | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts | current_memory | total_memory_allocated | ------------ ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------ | root | 379996 | 3.34 m | 527.06 us | 962 | 22235 | 409.29 ms | 5 | 50 | 1 | 0 bytes | 0 bytes | | background | 0 | 0 ps | 0 ps | 0 | 1499 | 22.81 ms | 25 | 30 | 0 | 0 bytes | 0 bytes | ------------ ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------
大部分连接来自那里
select * from host_summary; mysql> select * from host_summary; ----------- ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------ | host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated | ----------- ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------ | localhost | 380076 | 3.34 m | 527.00 us | 963 | 22235 | 409.29 ms | 5 | 50 | 1 | 0 bytes | 0 bytes | ----------- ------------ ------------------- ----------------------- ------------- ---------- ----------------- --------------------- ------------------- -------------- ---------------- ------------------------ 1 row in set (0.02 sec)
在哪个文件产生了最多的io
mysql> select * from io_global_by_file_by_bytes limit 17; ----------------------------------------------------------------------- ------------ ------------ ----------- ------------- --------------- ----------- ------------ ----------- | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | ----------------------------------------------------------------------- ------------ ------------ ----------- ------------- --------------- ----------- ------------ ----------- | @@datadir/admin/nba_live_tv.MYD | 1058 | 65.58 MiB | 63.47 KiB | 3691 | 1.55 MiB | 440 bytes | 67.13 MiB | 2.31 | | @@datadir/mysql/proc.MYD | 1862 | 5.01 MiB | 2.75 KiB | 6 | 10.02 KiB | 1.67 KiB | 5.02 MiB | 0.19 | | @@datadir/admin/nba_live_tv.MYI | 2 | 981 bytes | 490 bytes | 3749 | 649.75 KiB | 177 bytes | 650.70 KiB | 99.85 | | C:\Program Files\MySQL\MySQL Server 5.7\share\english\errmsg.sys | 3 | 73.69 KiB | 24.56 KiB | 0 | 0 bytes | 0 bytes | 73.69 KiB | 0.00 | | @@datadir/admin/nba_live_tv.frm | 25 | 22.54 KiB | 923 bytes | 121 | 23.22 KiB | 196 bytes | 45.76 KiB | 50.74 | | @@datadir/admin/roster_player.frm | 25 | 10.54 KiB | 432 bytes | 133 | 9.21 KiB | 71 bytes | 19.75 KiB | 46.65 | | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\Index.xml | 1 | 18.27 KiB | 18.27 KiB | 0 | 0 bytes | 0 bytes | 18.27 KiB | 0.00 | | @@datadir/sakila/film.frm | 50 | 11.58 KiB | 237 bytes | 46 | 6.00 KiB | 134 bytes | 17.58 KiB | 34.12 | | @@datadir/admin/roster_team.frm | 25 | 8.85 KiB | 363 bytes | 110 | 8.37 KiB | 78 bytes | 17.23 KiB | 48.61 | | @@datadir/performance_schema/replication_connection_configuration.frm | 7 | 11.86 KiB | 1.69 KiB | 0 | 0 bytes | 0 bytes | 11.86 KiB | 0.00 | | @@datadir/sys/metrics.frm | 17 | 10.18 KiB | 613 bytes | 0 | 0 bytes | 0 bytes | 10.18 KiB | 0.00 | | @@datadir/sakila/film_text.frm | 26 | 4.60 KiB | 181 bytes | 25 | 5.38 KiB | 220 bytes | 9.98 KiB | 53.89 | | @@datadir/admin/roster_comment_log.frm | 25 | 3.99 KiB | 163 bytes | 57 | 5.94 KiB | 107 bytes | 9.93 KiB | 59.82 | | @@datadir/admin/roster_complaint_log.frm | 25 | 3.78 KiB | 155 bytes | 54 | 5.84 KiB | 111 bytes | 9.62 KiB | 60.68 | | @@datadir/sakila/staff.frm | 19 | 3.58 KiB | 193 bytes | 41 | 5.55 KiB | 139 bytes | 9.13 KiB | 60.75 | | @@datadir/sakila/customer.frm | 19 | 3.21 KiB | 173 bytes | 37 | 5.35 KiB | 148 bytes | 8.55 KiB | 62.50 | | @@datadir/admin/roster_abilities.frm | 25 | 3.03 KiB | 124 bytes | 71 | 5.46 KiB | 79 bytes | 8.49 KiB | 64.31 | ----------------------------------------------------------------------- ------------ ------------ ----------- ------------- --------------- ----------- ------------ ----------- 17 rows in set (0.03 sec)
哪张表被访问最多
mysql> select * from session; -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ - --------------- ----------- ------------------- -------- ------------- ----------- ---------------- ------ -------------- | thd_id | conn_id | user | db | command | state | time | current_statement | statement_latency | progress | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | current_memory | last_wait | last_wait_latency | source | trx_latency | trx_state | trx_autocommit | pid | program_name | -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ - --------------- ----------- ------------------- -------- ------------- ----------- ---------------- ------ -------------- | 65 | 40 | root@localhost | NULL | Sleep | NULL | 7651 | NULL | NULL | NULL | 0 ps | 7 | 7 | 0 | 1 | 0 | YES | SHOW DATABASES | 1.07 ms | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 66 | 41 | root@localhost | admin | Sleep | NULL | 7024 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | SHOW CREATE TABLE `text` | 62.72 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 77 | 52 | root@localhost | sys | Sleep | NULL | 606 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | SHOW CREATE VIEW `statement_analysis` | 685.38 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 78 | 53 | root@localhost | sys | Sleep | NULL | 596 | NULL | NULL | NULL | 0 ps | 0 | 0 | 0 | 0 | 0 | NO | NULL | 67.00 us | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 64 | 39 | root@localhost | sys | Query | Sending data | 0 | select * from session | 2.59 ms | NULL | 2.00 ms | 0 | 0 | 0 | 4 | 1 | YES | NULL | NULL | 0 bytes | NULL | NULL | NULL | NULL | NULL | NULL | 1688 | mysql | -------- --------- ---------------- ------- --------- -------------- ------ ----------------------- ------------------- ---------- -------------- --------------- ----------- --------------- ------------ ----------------- ----------- --------------------------------------- ------------------------ -
那些sql用了磁盘临时表
mysql> select * from statements_with_temp_tables limit 10; ------------------------------------------------------------------- ------- ------------ --------------- ------------------- ----------------- -------------------------- ------------------------ --------------------- --------------------- ---------------------------------- | query | db | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen | last_seen | digest | ------------------------------------------------------------------- ------- ------------ --------------- ------------------- ----------------- -------------------------- ------------------------ --------------------- --------------------- ---------------------------------- | SHOW FIELDS FROM `admin` . `nba_live_tv` | admin | 44 | 26.27 ms | 44 | 44 | 1 | 100 | 2016-07-06 18:53:04 | 2016-07-06 18:54:12 | 9acef572876ef88f8015f687f31951e2 | | SELECT `trigger_name` , `event ... AND `event_object_table` = ? | admin | 38 | 55.15 ms | 38 | 38 | 1 | 100 | 2016-07-06 18:52:28 | 2016-07-06 19:02:53 | b44452a287a238423836977884363fab | | SELECT IF ( ( `locate` ( ? , ` ... . `COMPRESSED_SIZE` ) ) DESC | sys | 18 | 178.76 ms | 66 | 32 | 4 | 48 | 2016-07-05 20:56:29 | 2016-07-06 19:04:17 | a7a8900602e4ad6155c15c5d15d49950 | | INSERT INTO TEXT SELECT * FROM TEXT | admin | 19 | 2.85 m | 19 | 19 | 1 | 100 | 2016-07-06 19:03:28 | 2016-07-06 19:06:32 | 910cae3942493b44e4eddcf5df8e8f14 | | SELECT `performance_schema` . ... name` . `SUM_TIMER_WAIT` DESC | sys | 3 | 72.68 ms | 332 | 15 | 111 | 5 | 2016-07-05 20:56:30 | 2016-07-05 21:45:29 | 6f58edd9cee71845f592cf5347f8ecd7 | | SELECT IF ( `isnull` ( `perfor ... me` . `SUM_TIMER_WAIT` ) DESC | sys | 18 | 109.17 ms | 26 | 13 | 1 | 50 | 2016-07-05 20:56:29 | 2016-07-06 19:00:54 | b2e2b5f6d63a6c9aa1728cd652edd1a4 | | SELECT IF ( `isnull` ( `perfor ... _host_by_event_name` GROUP BY | sys | 18 | 239.08 ms | 105 | 12 | 6 | 11 | 2016-07-05 20:56:29 | 2016-07-06 20:38:37 | 9a23c4ec6de58d79422ff5a7fea99b18 | | SELECT `information_schema` . ... SELECT `information_schema` . | sys | 3 | 5.84 ms | 18 | 9 | 6 | 50 | 2016-07-05 20:56:29 | 2016-07-05 21:05:09 | 778282a821be7a22a384b0043b1bccdc | | SELECT `t` . `THREAD_ID` AS `t ... _NUMBER_OF_BYTES_USED` ) DESC | sys | 8 | 196.12 ms | 16 | 4 | 2 | 25 | 2016-07-05 20:56:29 | 2016-07-06 20:02:15 | 2c31e954cd7a78f6a7e10182f873547f | | ( SELECT `lower` ( `performanc ... AND ( `performance_schema` . | sys | 3 | 26.06 ms | 7 | 4 | 2 | 57 | 2016-07-05 20:56:29 | 2016-07-06 20:01:45 | beb8cf71b3299d999e053d6f8f5abc8e | ------------------------------------------------------------------- ------- ------------ --------------- ------------------- ----------------- -------------------------- ------------------------ --------------------- --------------------- ---------------------------------- 10 rows in set (0.00 sec)
那个表占用了最多的buffer pool空间
mysql> select * from innodb_buffer_stats_by_table; --------------- ------------- ------------ ------------ ------- -------------- ----------- ------------- | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | --------------- ------------- ------------ ------------ ------- -------------- ----------- ------------- | admin | text | 1.81 MiB | 313.41 KiB | 116 | 116 | 116 | 989 | | InnoDB System | SYS_TABLES | 192.00 KiB | 99.38 KiB | 12 | 12 | 12 | 2145 | | sys | sys_config | 16.00 KiB | 338 bytes | 1 | 1 | 1 | 6 | --------------- ------------- ------------ ------------ ------- -------------- ----------- ------------- 3 rows in set (0.01 sec)
还有很多的例子详见官网:
http://dev.mysql.com/doc/refman/5.7/en/sys-schema.html
PHPCon2016的资料