MySQL 5.7 SYS scheme解析

sys 库是MySQL 5.7其中的一个系统库,里面有很多很好用的跟性能相关的视图、函数和存储过程, 增强MySQL的易用性

例如:哪些语句使用了临时表,哪个用户请求了最多的io,哪个线程占用了最多的内存,哪些索引是没有使用过的索引。

有了sys 库以后,5.7分析问题和定位问题,减少依赖percona-toolkit工具,更多的使用里面的视图。下面我们做个实验,来看sys库那几个性能视图的作用

环境介绍:

192.168.23.10 安装sysbench

192.168.23.12 安装MySQL5.7.13

两台虚拟机,都是4线程,3G内存,宿主机是4核,16G内存,普通SATA盘

sysbench压测脚本,为了做实验,把压测时间设置大一点,方便查看那几个性能视图

 /opt/app/sysbench/bin/sysbench --test=/opt/app/sysbench/lua/oltp.lua \

--db-driver=mysql --mysql-host=192.168.23.12 --oltp-table-size=100000 \

--oltp-tables-count=10 --mysql-db=sysbench --mysql-user=sysbench \

--mysql-password=1qaz2wsx --max-time=30000 --max-requests=0 \

--num-threads=8 --report-interval=1 run

 

 

第一:查看用户级别的相关情况,

 

 

##看一下host发来过的SQL请求情况:total_latency 总延时, lock_latency 总的锁延时,row_sent 发送了多少行, full_ssans 全表扫描总的次数
mysql> select * from x$host_summary_by_statement_latency;
+---------------+--------+-----------------+---------------+----------------+-----------+---------------+---------------+------------+
| host          | total  | total_latency   | max_latency   | lock_latency   | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+--------+-----------------+---------------+----------------+-----------+---------------+---------------+------------+
| 192.168.23.10 | 200669 | 560038986996000 | 8997005086000 | 23331049000000 |   3120190 |       7153244 |         40115 |          0 |
| localhost     |    109 |   2130722779000 |  712619558000 |    90944000000 |       119 |          1269 |             0 |          5 |
| background    |      0 |               0 |             0 |              0 |         0 |             0 |             0 |          0 |
+---------------+--------+-----------------+---------------+----------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)

##查看按客户端相关IO线程相关的情况 total_latency 总延时,avg_latency 平均延时, max_latency 最大延时
mysql> select * from x$waits_by_host_by_latency order by total_latency desc ;
+---------------+--------------------------------------+---------+-----------------+-------------+---------------+
| host          | event                                | total   | total_latency   | avg_latency | max_latency   |
+---------------+--------------------------------------+---------+-----------------+-------------+---------------+
| 192.168.23.10 | wait/io/file/sql/binlog              |   24841 | 163211274405180 |  6570237410 |  913839905965 |
| 192.168.23.10 | wait/io/table/sql/handler            | 6244558 |  88270585548280 |    14135530 | 3369646613620 |
| background    | wait/io/file/sql/binlog              |  128486 |  44014622163915 |   342563495 | 3842357737710 |
| background    | wait/io/file/innodb/innodb_data_file |    1846 |  14855266117140 |  8047272805 |  882840258320 |
| background    | wait/io/file/innodb/innodb_log_file  |     547 |  13470228043550 | 24625645405 | 1777250192660 |
| 192.168.23.10 | wait/io/file/innodb/innodb_data_file |     584 |  11086987247300 | 18984567010 |  380929741155 |
| 192.168.23.10 | wait/io/file/innodb/innodb_log_file  |   13877 |  10311024630260 |   743029715 |  331577308990 |
| background    | wait/io/file/sql/FRM                 |    1404 |   1405484392445 |  1001056970 |  584735335675 |
| localhost     | wait/io/file/sql/FRM                 |     526 |   1067680283670 |  2029810380 |  238569415220 |
| 192.168.23.10 | wait/lock/table/sql/handler          |  266974 |    551579769605 |     2065765 |   25834904220 |
| background    | wait/io/file/myisam/kfile            |      33 |    473172383825 | 14338556910 |  331729231625 |
| 192.168.23.10 | wait/io/file/sql/FRM                 |     130 |     84844547115 |   652650285 |   31024603660 |
| localhost     | wait/io/file/myisam/dfile            |      25 |     56702742150 |  2268109625 |   23357858920 |
| localhost     | wait/io/file/sql/file_parser         |     204 |     28396423845 |   139198035 |   23793573990 |
| background    | wait/io/file/mysys/cnf               |       5 |     19273647840 |  3854729385 |   19169800220 |
| background    | wait/io/file/myisam/dfile            |      24 |     13431475015 |   559644500 |   11908891835 |
| background    | wait/io/file/mysys/charset           |       3 |      9642786060 |  3214262020 |    9606707000 |
| background    | wait/io/file/sql/ERRMSG              |       5 |      8360195485 |  1672038975 |    3770732080 |
| localhost     | wait/io/file/myisam/kfile            |       4 |      1356593640 |   339148410 |    1331465300 |
| background    | wait/io/file/sql/binlog_index        |      15 |      1232293025 |    82152665 |    1120797225 |
| background    | wait/io/file/sql/pid                 |       3 |       296007380 |    98669025 |     207822120 |
| localhost     | wait/io/file/sql/dbopt               |      12 |       214129520 |    17844025 |      83346740 |
| background    | wait/io/file/sql/casetest            |      10 |       148326380 |    14832455 |      52451460 |
| 192.168.23.10 | wait/io/file/sql/dbopt               |       4 |        79659900 |    19914975 |      70984480 |
| background    | wait/io/file/sql/global_ddl_log      |       2 |        18142620 |     9071310 |      15014540 |
+---------------+--------------------------------------+---------+-----------------+-------------+---------------+
25 rows in set (0.01 sec)

mysql> 
##查看按用户相关的延时情况 total_latency 总延时,avg_latency 平均延时, max_latency 最大延时
mysql> select * from x$waits_by_user_by_latency order by total_latency desc ;
+----------+--------------------------------------+---------+-----------------+-------------+---------------+
| user     | event                                | total   | total_latency   | avg_latency | max_latency   |
+----------+--------------------------------------+---------+-----------------+-------------+---------------+
| sysbench | wait/io/file/sql/binlog              |   27875 | 224727885984870 |  8061986920 | 2402537542350 |
| sysbench | wait/io/table/sql/handler            | 7068872 |  96860278959465 |    13702125 | 3369646613620 |
| sysbench | wait/io/file/innodb/innodb_log_file  |   15628 |  20073781544475 |  1284475170 | 1609081505740 |
| sysbench | wait/io/file/innodb/innodb_data_file |     598 |  11246278276650 | 18806485135 |  380929741155 |
| root     | wait/io/file/sql/FRM                 |     526 |   1067680283670 |  2029810380 |  238569415220 |
| sysbench | wait/lock/table/sql/handler          |  302216 |    613611714840 |     2030080 |   25834904220 |
| sysbench | wait/io/file/sql/FRM                 |     130 |     84844547115 |   652650285 |   31024603660 |
| root     | wait/io/file/myisam/dfile            |      25 |     56702742150 |  2268109625 |   23357858920 |
| root     | wait/io/file/sql/file_parser         |     204 |     28396423845 |   139198035 |   23793573990 |
| root     | wait/io/file/myisam/kfile            |       4 |      1356593640 |   339148410 |    1331465300 |
| root     | wait/io/file/sql/dbopt               |      12 |       214129520 |    17844025 |      83346740 |
| sysbench | wait/io/file/sql/dbopt               |       4 |        79659900 |    19914975 |      70984480 |
+----------+--------------------------------------+---------+-----------------+-------------+---------------+
12 rows in set (0.01 sec)

mysql> 
#查看按用户发来的sql情况,比如:总的SQL条目,语句执行延时,平均延时,是否存在表扫描情况,文件IO,IO延时
mysql> select * from x$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 |
+------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| sysbench   |     367069 |  1332340924078000 |       3629674323.0237 |           0 |    48220 | 297374827396840 |                   8 |                 8 |            1 |              0 |                      0 |
| root       |        115 |     2249030116000 |      19556783617.3913 |          11 |      771 |   1154350172825 |                   1 |                 1 |            1 |              0 |                      0 |
| background |          0 |                 0 |                0.0000 |           0 |   132723 |  89773752093085 |                  25 |                27 |            0 |              0 |                      0 |
+------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.48 sec)

mysql> 

 

 

第二:IO相关情况

 

##可以通过这几个视图来看一下数具体数据文件IO相关的情况
#查看相关数据文件:total_read 总的读取字节,avg_read 平均读取字节,total_written总的写的字节,avg_write 平均写的字节,write_pct 以及写比例
 mysql> select * from x$io_global_by_file_by_bytes ORDER BY total desc limit 10;
+-----------------------------------------+------------+------------+------------+-------------+---------------+-------------+------------+-----------+
| file                                    | count_read | total_read | avg_read   | count_write | total_written | avg_write   | total      | write_pct |
+-----------------------------------------+------------+------------+------------+-------------+---------------+-------------+------------+-----------+
| /data/3306/logs/mysql-bin.000006        |     128456 | 1052302029 |  8191.9259 |           0 |             0 |      0.0000 | 1052302029 |      0.00 |
| /data/3306/data/ib_logfile0             |          7 |      70144 | 10020.5714 |       52836 |     177236480 |   3354.4644 |  177306624 |     99.96 |
| /data/3306/logs/mysql-bin.000007        |          0 |          0 |     0.0000 |       52464 |      94883118 |   1808.5376 |   94883118 |    100.00 |
| /data/3306/data/ibdata1                 |        284 |    6766592 | 23826.0282 |         633 |      48414720 |  76484.5498 |   55181312 |     87.74 |
| /data/3306/data/sysbench/sbtest3.ibd    |        734 |   12025856 | 16384.0000 |         140 |       2293760 |  16384.0000 |   14319616 |     16.02 |
| /data/3306/data/ibtmp1                  |          0 |          0 |     0.0000 |          46 |      13139968 | 285651.4783 |   13139968 |    100.00 |
| /data/3306/data/mysql/proc.MYD          |        204 |     605753 |  2969.3775 |           0 |             0 |      0.0000 |     605753 |      0.00 |
| /data/3306/logs/mysql-bin.000001        |         14 |      99474 |  7105.2857 |           0 |             0 |      0.0000 |      99474 |      0.00 |
| /opt/app/mysql/share/english/errmsg.sys |          3 |      75456 | 25152.0000 |           0 |             0 |      0.0000 |      75456 |      0.00 |
| /data/3306/data/mysql/engine_cost.ibd   |          4 |      65536 | 16384.0000 |           0 |             0 |      0.0000 |      65536 |      0.00 |
+-----------------------------------------+------------+------------+------------+-------------+---------------+-------------+------------+-----------+
10 rows in set (0.00 sec)

mysql> 
#查看相关数据文件延时:如total_latency 总的延时 ,read_latency 总的读延时 write_latency 总的写延时
mysql
> select * from x$io_global_by_file_by_latency ORDER BY total_latency desc limit 10; +---------------------------------------------------+--------+-----------------+------------+----------------+-------------+-----------------+------------+-----------------+ | file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency | +---------------------------------------------------+--------+-----------------+------------+----------------+-------------+-----------------+------------+-----------------+ | /data/3306/logs/mysql-bin.000007 | 104272 | 857635813928830 | 0 | 0 | 59585 | 128645758363315 | 44687 | 728990055565515 | | /data/3306/data/ib_logfile0 | 61003 | 130330488448400 | 7 | 23089028260 | 59975 | 70847663102440 | 1021 | 59459736317700 | | /data/3306/logs/mysql-bin.000006 | 128462 | 43435687994120 | 128456 | 43435586393740 | 0 | 0 | 6 | 101600380 | | /data/3306/data/ibdata1 | 1567 | 21265602188520 | 284 | 763552576960 | 633 | 7409130660570 | 650 | 13092918950990 | | /data/3306/data/sysbench/sbtest3.ibd | 1048 | 14948103372255 | 740 | 12899909284700 | 140 | 20390455360 | 168 | 2027803632195 | | /data/3306/data/mysql/db.frm | 13 | 584825990215 | 7 | 584777107255 | 0 | 0 | 6 | 48882960 | | /data/3306/logs/mysql-bin.000001 | 17 | 573208795500 | 14 | 573147683260 | 0 | 0 | 3 | 61112240 | | /data/3306/data/mysql/db.MYI | 4 | 331753618205 | 2 | 331732946525 | 0 | 0 | 2 | 20671680 | | /data/3306/data/sys/x@0024memory_global_total.frm | 6 | 239301100460 | 2 | 238573608360 | 0 | 0 | 4 | 727492100 | | /data/3306/data/mysql/server_cost.frm | 13 | 181650638825 | 7 | 181592420425 | 0 | 0 | 6 | 58218400 | +---------------------------------------------------+--------+-----------------+------------+----------------+-------------+-----------------+------------+-----------------+ 10 rows in set (0.00 sec) mysql> ##可以通过下面这几个视图,看一下binlog redolog innodb数据文件等相关数据文件的IO情况:比如total_latency  总延时,min_latency 最小延时,avg_latency平均延时 max_latency 最大延时 mysql> select * from x$io_global_by_wait_by_bytes ORDER BY total_latency desc; +-------------------------+--------+-----------------+-------------+-------------+----------------+------------+------------+------------+-------------+---------------+-------------+-----------------+ | event_name | total | total_latency | min_latency | avg_latency | max_latency | count_read | total_read | avg_read | count_write | total_written | avg_written | total_requested | +-------------------------+--------+-----------------+-------------+-------------+----------------+------------+------------+------------+-------------+---------------+-------------+-----------------+ | sql/binlog | 244630 | 952619534005725 | 198860 | 3894123795 | 14005923994180 | 128470 | 1052401503 | 8191.8074 | 66365 | 119844266 | 1805.8354 | 1172245769 | | innodb/innodb_log_file | 67875 | 133766003954530 | 871080 | 1970769700 | 6905414126165 | 7 | 70144 | 10020.5714 | 66784 | 223841280 | 3351.7202 | 223911424 | | innodb/innodb_data_file | 2843 | 36773287483315 | 0 | 12934676985 | 926390076160 | 1097 | 20086784 | 18310.6509 | 819 | 63848448 | 77959.0330 | 83935232 | | sql/FRM | 2251 | 2561727311090 | 0 | 1138039485 | 584735335675 | 1070 | 645174 | 602.9664 | 0 | 0 | 0.0000 | 645174 | | myisam/kfile | 37 | 474528977465 | 984540 | 12825107260 | 331729231625 | 18 | 4926 | 273.6667 | 0 | 0 | 0.0000 | 4926 | | myisam/dfile | 332 | 77683544835 | 183000 | 233986545 | 23357858920 | 208 | 608353 | 2924.7740 | 0 | 0 | 0.0000 | 608353 | | sql/file_parser | 104 | 25346760565 | 0 | 243718790 | 23793573990 | 1 | 720 | 720.0000 | 0 | 0 | 0.0000 | 720 | | mysys/cnf | 5 | 19273647840 | 561200 | 3854729385 | 19169800220 | 3 | 56 | 18.6667 | 0 | 0 | 0.0000 | 56 | | mysys/charset | 3 | 9642786060 | 10367560 | 3214262020 | 9606707000 | 1 | 18710 | 18710.0000 | 0 | 0 | 0.0000 | 18710 | | sql/ERRMSG | 5 | 8360195485 | 18419560 | 1672038975 | 3770732080 | 3 | 75456 | 25152.0000 | 0 | 0 | 0.0000 | 75456 | | sql/binlog_index | 15 | 1232293025 | 0 | 82152665 | 1120797225 | 2 | 198 | 99.0000 | 0 | 0 | 0.0000 | 198 | | sql/pid | 3 | 296007380 | 7516420 | 98669025 | 207822120 | 0 | 0 | 0.0000 | 1 | 5 | 5.0000 | 5 | | sql/dbopt | 16 | 293789420 | 207400 | 18361610 | 83346740 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | 0 | | sql/casetest | 10 | 148326380 | 0 | 14832455 | 52451460 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | 0 | | sql/global_ddl_log | 2 | 18142620 | 0 | 9071310 | 15014540 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | 0 | | archive/data | 1 | 8641260 | 0 | 8641260 | 8641260 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | 0 | +-------------------------+--------+-----------------+-------------+-------------+----------------+------------+------------+------------+-------------+---------------+-------------+-----------------+ 16 rows in set (0.01 sec) mysql> ##还可以看一下io_wait,比如总的延时,平均延时,最大延时,读延时,写的延时 mysql> select * from x$io_global_by_wait_by_latency ORDER BY total desc; +-------------------------+--------+------------------+-------------+----------------+----------------+-----------------+-----------------+------------+------------+------------+-------------+---------------+-------------+ | event_name | total | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read | count_write | total_written | avg_written | +-------------------------+--------+------------------+-------------+----------------+----------------+-----------------+-----------------+------------+------------+------------+-------------+---------------+-------------+ | sql/binlog | 254738 | 1041025347208805 | 4086651080 | 14005923994180 | 44008734077000 | 150831466222860 | 846185146908945 | 128470 | 1052401503 | 8191.8074 | 72233 | 130580710 | 1807.7708 | | innodb/innodb_log_file | 73858 | 146987100334955 | 1990130795 | 6905414126165 | 23089028260 | 79908484855465 | 67055526451230 | 7 | 70144 | 10020.5714 | 72674 | 243804160 | 3354.7646 | | innodb/innodb_data_file | 2866 | 37182251112810 | 12973569670 | 926390076160 | 14299955007730 | 7562926369195 | 15319369735885 | 1104 | 20201472 | 18298.4348 | 827 | 67190784 | 81246.4135 | | sql/FRM | 2251 | 2561727311090 | 1138039485 | 584735335675 | 2539017648540 | 0 | 22709662550 | 1070 | 645174 | 602.9664 | 0 | 0 | 0.0000 | | myisam/dfile | 332 | 77683544835 | 233986545 | 23357858920 | 77180177715 | 0 | 503367120 | 208 | 608353 | 2924.7740 | 0 | 0 | 0.0000 | | sql/file_parser | 104 | 25346760565 | 243718790 | 23793573990 | 23793573990 | 0 | 1553186575 | 1 | 720 | 720.0000 | 0 | 0 | 0.0000 | | myisam/kfile | 37 | 474528977465 | 12825107260 | 331729231625 | 474326969865 | 0 | 202007600 | 18 | 4926 | 273.6667 | 0 | 0 | 0.0000 | | sql/dbopt | 16 | 293789420 | 18361610 | 83346740 | 0 | 0 | 293789420 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | | sql/binlog_index | 15 | 1232293025 | 82152665 | 1120797225 | 5070320 | 0 | 1227222705 | 2 | 198 | 99.0000 | 0 | 0 | 0.0000 | | sql/casetest | 10 | 148326380 | 14832455 | 52451460 | 0 | 0 | 148326380 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | | sql/ERRMSG | 5 | 8360195485 | 1672038975 | 3770732080 | 4789648020 | 0 | 3570547465 | 3 | 75456 | 25152.0000 | 0 | 0 | 0.0000 | | mysys/cnf | 5 | 19273647840 | 3854729385 | 19169800220 | 19173307720 | 0 | 100340120 | 3 | 56 | 18.6667 | 0 | 0 | 0.0000 | | sql/pid | 3 | 296007380 | 98669025 | 207822120 | 0 | 80668840 | 215338540 | 0 | 0 | 0.0000 | 1 | 5 | 5.0000 | | mysys/charset | 3 | 9642786060 | 3214262020 | 9606707000 | 9606707000 | 0 | 36079060 | 1 | 18710 | 18710.0000 | 0 | 0 | 0.0000 | | sql/global_ddl_log | 2 | 18142620 | 9071310 | 15014540 | 0 | 0 | 18142620 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | | archive/data | 1 | 8641260 | 8641260 | 8641260 | 0 | 0 | 8641260 | 0 | 0 | 0.0000 | 0 | 0 | 0.0000 | +-------------------------+--------+------------------+-------------+----------------+----------------+-----------------+-----------------+------------+------------+------------+-------------+---------------+-------------+ 16 rows in set (0.38 sec) 从这边来看,binlog IO压力比较大。

 

第三:innodb buffer 和锁相关情况

 

##看一下sysbench库对innod_buffer使用的情况,比如有多少个页,有多少页是脏页,有多少行给cache
mysql> select * from innodb_buffer_stats_by_schema;
+---------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+------------+-------+--------------+-----------+-------------+
| sysbench      | 163.80 MiB | 143.80 MiB | 10483 |        10483 |     10483 |      153684 |
| InnoDB System | 9.67 MiB   | 8.80 MiB   |   619 |          619 |       619 |        9908 |
| mysql         | 240.00 KiB | 7.75 KiB   |    15 |           15 |        15 |          97 |
+---------------+------------+------------+-------+--------------+-----------+-------------+
3 rows in set (2.20 sec)
##我们可以看表相关的数据
mysql> select * from innodb_buffer_stats_by_table;
+---------------+---------------------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name               | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+---------------------------+-----------+-----------+-------+--------------+-----------+-------------+
| sysbench      | sbtest10                  | 23.50 MiB | 20.91 MiB |  1504 |         1504 |      1504 |      100819 |
| sysbench      | sbtest1                   | 23.47 MiB | 20.90 MiB |  1502 |         1502 |      1502 |      100750 |
| InnoDB System | SYS_TABLES                | 22.14 MiB | 20.37 MiB |  1417 |         1417 |      1417 |       69711 |
| sysbench      | sbtest9                   | 18.27 MiB | 16.10 MiB |  1169 |         1169 |      1169 |       88908 |
| sysbench      | sbtest8                   | 14.31 MiB | 12.43 MiB |   916 |          916 |       916 |       81720 |
| sysbench      | sbtest6                   | 14.22 MiB | 12.40 MiB |   910 |          910 |       910 |       79381 |
| sysbench      | sbtest3                   | 14.17 MiB | 12.36 MiB |   907 |          907 |       907 |       79017 |
| sysbench      | sbtest7                   | 14.16 MiB | 12.34 MiB |   906 |          906 |       906 |       79234 |
| sysbench      | sbtest5                   | 14.09 MiB | 12.29 MiB |   902 |          902 |       902 |       78814 |
| sysbench      | sbtest4                   | 14.06 MiB | 12.28 MiB |   900 |          900 |       900 |       78797 |
| sysbench      | sbtest2                   | 14.00 MiB | 12.20 MiB |   896 |          896 |       896 |       78876 |
| InnoDB System | SYS_FOREIGN               | 32.00 KiB | 0 bytes   |     2 |            2 |         2 |           0 |
| InnoDB System | SYS_COLUMNS               | 16.00 KiB | 10.91 KiB |     1 |            1 |         1 |         173 |
| InnoDB System | SYS_DATAFILES             | 16.00 KiB | 1.52 KiB  |     1 |            1 |         1 |          30 |
| InnoDB System | SYS_FIELDS                | 16.00 KiB | 2.68 KiB  |     1 |            1 |         1 |          64 |
| InnoDB System | SYS_INDEXES               | 16.00 KiB | 3.49 KiB  |     1 |            1 |         1 |          50 |
| InnoDB System | SYS_TABLESPACES           | 16.00 KiB | 1.49 KiB  |     1 |            1 |         1 |          30 |
| mysql         | engine_cost               | 16.00 KiB | 112 bytes |     1 |            1 |         1 |           2 |
| mysql         | gtid_executed             | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | innodb_index_stats        | 16.00 KiB | 6.63 KiB  |     1 |            1 |         1 |          77 |
| mysql         | innodb_table_stats        | 16.00 KiB | 758 bytes |     1 |            1 |         1 |          12 |
| mysql         | plugin                    | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | servers                   | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | server_cost               | 16.00 KiB | 279 bytes |     1 |            1 |         1 |           6 |
| mysql         | slave_master_info         | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | slave_relay_log_info      | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | slave_worker_info         | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | time_zone                 | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | time_zone_leap_second     | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | time_zone_name            | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | time_zone_transition      | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
| mysql         | time_zone_transition_type | 16.00 KiB | 0 bytes   |     1 |            1 |         1 |           0 |
+---------------+---------------------------+-----------+-----------+-------+--------------+-----------+-------------+
32 rows in set (1.06 sec)

mysql> 

##查询锁相关的信息
mysql> select * from innodb_lock_waits;
Empty set (0.00 sec)

mysql> 
mysql> select * from innodb_lock_waits;
Empty set (0.00 sec)
居然结果集为空,那么用以前查锁的SQL,也是没有锁等待相关的信息
mysql> SELECT
    ->  r.trx_id waiting_trx_id,
    ->  r.trx_mysql_thread_id waiting_thread,
    ->  LEFT (r.trx_query, 20) waiting_query,
    ->  concat(
    ->   concat(lw.lock_type, ' '),
    ->   lw.lock_mode
    ->  ) waiting_for_lock,
    ->  b.trx_id blocking_trx_id,
    ->  b.trx_mysql_thread_id blocking_thread,
    ->  LEFT (b.trx_query, 20) blocking_query,
    ->  concat(
    ->   concat(lb.lock_type, ' '),
    ->   lb.lock_mode
    ->  ) blocking_lock
    -> FROM
    ->  information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    -> INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
    -> INNER JOIN information_schema.innodb_locks lw ON lw.lock_trx_id = r.trx_id
    -> INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = b.trx_id;
Empty set (0.03 sec)

mysql> SELECT * from information_schema.innodb_lock_waits w
Empty set (0.01 sec)


其实这个视图就是语句上面的那个表创建的
下面 是这个视图的
SELECT
 `r`.`trx_wait_started` AS `wait_started`,
 timediff(
  now(),
  `r`.`trx_wait_started`
 ) AS `wait_age`,
 timestampdiff(
  SECOND,
  `r`.`trx_wait_started`,
  now()
 ) AS `wait_age_secs`,
 `rl`.`lock_table` AS `locked_table`,
 `rl`.`lock_index` AS `locked_index`,
 `rl`.`lock_type` AS `locked_type`,
 `r`.`trx_id` AS `waiting_trx_id`,
 `r`.`trx_started` AS `waiting_trx_started`,
 timediff(now(), `r`.`trx_started`) AS `waiting_trx_age`,
 `r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,
 `r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,
 `r`.`trx_mysql_thread_id` AS `waiting_pid`,
 `r`.`trx_query` AS `waiting_query`,
 `rl`.`lock_id` AS `waiting_lock_id`,
 `rl`.`lock_mode` AS `waiting_lock_mode`,
 `b`.`trx_id` AS `blocking_trx_id`,
 `b`.`trx_mysql_thread_id` AS `blocking_pid`,
 `b`.`trx_query` AS `blocking_query`,
 `bl`.`lock_id` AS `blocking_lock_id`,
 `bl`.`lock_mode` AS `blocking_lock_mode`,
 `b`.`trx_started` AS `blocking_trx_started`,
 timediff(now(), `b`.`trx_started`) AS `blocking_trx_age`,
 `b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,
 `b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,
 concat(
  'KILL QUERY ',
  `b`.`trx_mysql_thread_id`
 ) AS `sql_kill_blocking_query`,
 concat(
  'KILL ',
  `b`.`trx_mysql_thread_id`
 ) AS `sql_kill_blocking_connection`
FROM
 `information_schema`.`innodb_lock_waits` `w`
JOIN `information_schema`.`innodb_trx` `b` ON `b`.`trx_id` = `w`.`blocking_trx_id`
JOIN `information_schema`.`innodb_trx` `r` ON `r`.`trx_id` = `w`.`requesting_trx_id`
JOIN `information_schema`.`innodb_locks` `bl` ON `bl`.`lock_id` = `w`.`blocking_lock_id`
JOIN `information_schema`.`innodb_locks` `rl` ON `rl`.`lock_id` = `w`.`requested_lock_id`
ORDER BY
 `r`.`trx_wait_started`

 

第四:索引相关的情况

 

##我们还可以来看一下索引相关的信息,查看基于索引 rows_selected总的查询了多少行,select_latency 总查询延时,rows_inserted 总的插入了多少行,insert_latency 插入延时,rows_updated 总的更新了多少行,
rows_deleted 总的删除了多少行,delete_latency 删除延时
mysql
> select * from schema_index_statistics; +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | sysbench | sbtest6 | PRIMARY | 5560278 | 29.86 s | 0 | 0 ps | 26668 | 2.36 s | 13333 | 0 ps | | sysbench | sbtest8 | PRIMARY | 5548180 | 29.83 s | 0 | 0 ps | 26606 | 2.24 s | 13302 | 0 ps | | sysbench | sbtest7 | PRIMARY | 5549019 | 28.25 s | 0 | 0 ps | 26614 | 2.22 s | 13307 | 0 ps | | sysbench | sbtest3 | PRIMARY | 5549019 | 25.26 s | 0 | 0 ps | 26614 | 3.49 s | 13306 | 0 ps | | sysbench | sbtest5 | PRIMARY | 5547357 | 25.72 s | 0 | 0 ps | 26606 | 2.64 s | 13302 | 0 ps | | sysbench | sbtest4 | PRIMARY | 5604063 | 25.28 s | 0 | 0 ps | 26878 | 2.44 s | 13439 | 0 ps | | sysbench | sbtest2 | PRIMARY | 5561944 | 25.14 s | 0 | 0 ps | 26674 | 2.27 s | 13336 | 0 ps | | sysbench | sbtest1 | PRIMARY | 5608648 | 24.71 s | 0 | 0 ps | 26898 | 2.67 s | 13448 | 0 ps | | sysbench | sbtest9 | PRIMARY | 5445603 | 23.38 s | 0 | 0 ps | 26118 | 2.29 s | 13058 | 0 ps | | sysbench | sbtest10 | PRIMARY | 5544849 | 18.25 s | 0 | 0 ps | 26594 | 2.92 s | 13296 | 0 ps | | sysbench | sbtest10 | k_10 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest9 | k_9 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest2 | k_2 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest7 | k_7 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest4 | k_4 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest6 | k_6 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest8 | k_8 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest1 | k_1 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest3 | k_3 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest5 | k_5 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sys | sys_config | PRIMARY | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ 21 rows in set (0.01 sec) mysql> 从上面看,二级索引没有用到,那么请放心,还有一个视图,可以看哪些库下,哪些没有使用到的索引:object_schema 库名 object_name 表名 index_name 索引名称 mysql> select * from schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | sysbench | sbtest1 | k_1 | | sysbench | sbtest10 | k_10 | | sysbench | sbtest2 | k_2 | | sysbench | sbtest3 | k_3 | | sysbench | sbtest4 | k_4 | | sysbench | sbtest5 | k_5 | | sysbench | sbtest6 | k_6 | | sysbench | sbtest7 | k_7 | | sysbench | sbtest8 | k_8 | | sysbench | sbtest9 | k_9 | +---------------+-------------+------------+ 10 rows in set (0.00 sec) 果然是,看到么,这些都是没有使用的索引。 那么我们来做基于二级索引的查询 mysql> explain select * from sysbench.sbtest1 where k>20000 and k < 300000 limit 10; +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ | 1 | SIMPLE | sbtest1 | NULL | range | k_1 | k_1 | 4 | NULL | 49356 | 100.00 | Using index condition; Using MRR | +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from sysbench.sbtest2 where k>20000 and k < 300000 limit 10; +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ | 1 | SIMPLE | sbtest2 | NULL | range | k_2 | k_2 | 4 | NULL | 49356 | 100.00 | Using index condition; Using MRR | +----+-------------+---------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from sysbench.sbtest1 where k>20000 and k < 300000 limit 10; +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 4 | 49641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 | | 5 | 49853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 | | 7 | 46713 | 99754685588-47576951480-32708622771-83861221370-03797298475-60503371617-50159644690-11488793570-28225419667-59109289014 | 19994000982-50192009482-63005057222-82729312489-25570376232 | | 10 | 49830 | 48090103407-09222928184-34050945574-85418069333-36966673537-23363106719-15284068881-04674238815-26203696337-24037044694 | 01495266405-82925129145-92643983850-90243995398-18709399387 | | 12 | 50016 | 74234360637-48574588774-94392661281-55267159983-87261567077-93953988073-73238443191-61462412385-80374300764-69242108888 | 11399505661-39569525290-74083577319-45975208227-87547792377 | | 13 | 49695 | 11498955389-75631629488-85767989076-95838361327-75745845491-06636682242-04442720409-63620680000-65777923254-84246739789 | 79529108858-38821864666-03539816919-68090429532-12191652597 | | 14 | 48679 | 80577254755-25715914552-67491941642-61787189596-01764534367-26123282523-31822523583-87325913348-83945913133-33321942990 | 27564894805-66292739248-81291800483-50568693575-98305077942 | | 15 | 43188 | 24572116324-52508797249-93080471635-19894665035-71650646618-84217684256-01633514832-46136473796-67731960381-56369759130 | 90405189049-64391027347-85099607773-42775551106-33828944071 | | 22 | 49790 | 14727371780-27787201378-78787463594-18460466846-21839479873-30447182067-93416020974-84136743014-58317107496-48981937169 | 96709539373-81997780696-95347904390-38078115633-62447355461 | | 24 | 50116 | 96225808974-56822595984-95377074482-83456476383-25408814447-92968603608-77649769299-95136600978-30286715144-34964058160 | 10255815850-80260680522-69787802778-01654410036-27346900110 | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 10 rows in set (0.08 sec) mysql> select * from schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | sysbench | sbtest10 | k_10 | | sysbench | sbtest2 | k_2 | | sysbench | sbtest3 | k_3 | | sysbench | sbtest4 | k_4 | | sysbench | sbtest5 | k_5 | | sysbench | sbtest6 | k_6 | | sysbench | sbtest7 | k_7 | | sysbench | sbtest8 | k_8 | | sysbench | sbtest9 | k_9 | +---------------+-------------+------------+ 9 rows in set (0.00 sec) 果然少了k_1这条记录,那么我们再来跑一条SQL mysql> select * from sysbench.sbtest2 where k>20000 and k < 300000 limit 10; +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 37372 | 54639105819-16259544371-38966759492-66999912337-13548132801-80544405444-79539314720-32068629147-90900768592-16318316772 | 96606684936-58047612149-96589063577-62157334542-37838281884 | | 4 | 49737 | 76432031163-71082225915-51747901141-58191524359-34226352669-76437191960-78998016630-94870264983-68822732782-16804829418 | 10598850124-46789233892-77449575307-15448579987-39538995991 | | 6 | 49609 | 68712691205-63107998159-64981423874-65117316605-12657286765-28478620831-00662908329-73373344241-31539201905-90207838601 | 46390249800-49241858889-88717353984-62437480033-27070657303 | | 7 | 43795 | 81945134047-13752863295-40177143001-51811603023-34115604442-58569202235-47907855956-52553436490-01199352836-10458918072 | 56484510344-55979250739-19585989738-90383563823-46493265207 | | 8 | 49684 | 34420533663-54038493821-46431897178-28576698061-64364226207-22045824272-09779719965-84080490519-86234598495-32047836208 | 73398780442-55087053058-09573601436-31976861664-93834409918 | | 9 | 49761 | 64421558336-96143555634-02934260184-52016406629-09713489670-00822229975-16093903942-64191433366-89911357749-86864843538 | 51328473205-38224917895-89735391293-17288582256-71538928494 | | 12 | 49598 | 09178092099-56872779304-51697919448-64097307346-95828899595-94230554314-43705628683-88895344345-11666676745-47969541122 | 61662005851-15114690306-98155697417-87100105017-96830315039 | | 15 | 49541 | 76900072319-35987654919-50048067261-24472113440-63910535111-26411257853-96055015877-26509202104-91799728109-82265279740 | 62293558682-82611871963-79993907787-79488415807-93911108058 | | 16 | 48829 | 04133140238-43572791555-75347202122-54422972690-64589127713-57063739125-44111218359-48928414356-02442608690-57065565592 | 79015166415-61414116745-70055176805-81602222449-06391339877 | | 18 | 50111 | 44187878005-24006418751-02070385596-63080429577-80152358864-08567618381-25204276585-54310685145-87741962441-37764491270 | 33463501619-79009660777-32042388639-26703142056-76420027918 | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 10 rows in set (3.56 sec) mysql> select * from schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | sysbench | sbtest10 | k_10 | | sysbench | sbtest3 | k_3 | | sysbench | sbtest4 | k_4 | | sysbench | sbtest5 | k_5 | | sysbench | sbtest6 | k_6 | | sysbench | sbtest7 | k_7 | | sysbench | sbtest8 | k_8 | | sysbench | sbtest9 | k_9 | +---------------+-------------+------------+ 8 rows in set (0.00 sec) 果然又少了k_2,这个时候我们再来回看一下schema_index_statistics视图,k_1,k_2 就有数据了,基于索引总共扫描了多少行,查询总延时,
从这个也可以看到sysbench 的oltp.lua脚本压测都是基于主键,跟实际场景有很大差异,不太建议用这个压测工具压测MySQL,用TPCC-MySQL来做压测比较贴合实际情况 mysql
> select * from schema_index_statistics; +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | sysbench | sbtest8 | PRIMARY | 10189391 | 41.50 s | 0 | 0 ps | 48866 | 3.70 s | 24432 | 0 ps | | sysbench | sbtest6 | PRIMARY | 10180534 | 41.05 s | 0 | 0 ps | 48826 | 3.87 s | 24412 | 0 ps | | sysbench | sbtest7 | PRIMARY | 10224006 | 39.66 s | 0 | 0 ps | 49036 | 3.73 s | 24518 | 0 ps | | sysbench | sbtest5 | PRIMARY | 10167297 | 37.13 s | 0 | 0 ps | 48763 | 4.12 s | 24380 | 0 ps | | sysbench | sbtest3 | PRIMARY | 10161878 | 36.30 s | 0 | 0 ps | 48738 | 4.96 s | 24368 | 0 ps | | sysbench | sbtest4 | PRIMARY | 10193564 | 36.42 s | 0 | 0 ps | 48890 | 3.89 s | 24444 | 0 ps | | sysbench | sbtest2 | PRIMARY | 10139356 | 36.15 s | 0 | 0 ps | 48626 | 3.74 s | 24311 | 0 ps | | sysbench | sbtest1 | PRIMARY | 10281967 | 35.75 s | 0 | 0 ps | 49312 | 4.17 s | 24655 | 0 ps | | sysbench | sbtest9 | PRIMARY | 10135185 | 34.59 s | 0 | 0 ps | 48610 | 3.81 s | 24304 | 0 ps | | sysbench | sbtest10 | PRIMARY | 10189088 | 29.17 s | 0 | 0 ps | 48868 | 4.40 s | 24432 | 0 ps | | sysbench | sbtest2 | k_2 | 59227 | 3.53 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest1 | k_1 | 59228 | 39.70 ms | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest10 | k_10 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest9 | k_9 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest7 | k_7 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest4 | k_4 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest6 | k_6 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest8 | k_8 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest3 | k_3 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sysbench | sbtest5 | k_5 | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | sys | sys_config | PRIMARY | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ 21 rows in set (0.01 sec) mysql>
这跟pt-index-usage只能分析慢查询日志来判断索引使用情况相对好一些

 

第五:SQL相关的情况

##查询有生成临时表的SQL:如exec_count 总的执行的次数 ,tmp_tables 总的生成临时表
mysql> select * from x$statement_analysis where db ='sysbench' and tmp_tables > 0 and query not like 'show%' ORDER BY tmp_disk_tables DESC LIMIT 3 \G
*************************** 1. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest10` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
full_scan:
exec_count: 243308
err_count: 0
warn_count: 0
total_latency: 421664798643000
max_latency: 13932957371000
avg_latency: 1733049000
lock_latency: 20795990000000
rows_sent: 24330900
rows_sent_avg: 100
rows_examined: 72992700
rows_examined_avg: 300
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 243311
tmp_disk_tables: 0
rows_sorted: 24330500
sort_merge_passes: 0
digest: 7bec897913ccc856e2ea999413cc9ad1
first_seen: 2016-06-19 11:25:23
last_seen: 2016-06-19 13:57:05
*************************** 2. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest9` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
full_scan:
exec_count: 242949
err_count: 0
warn_count: 0
total_latency: 392145676272000
max_latency: 5201577387000
avg_latency: 1614106000
lock_latency: 20798157000000
rows_sent: 24295400
rows_sent_avg: 100
rows_examined: 72886500
rows_examined_avg: 300
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 242954
tmp_disk_tables: 0
rows_sorted: 24295300
sort_merge_passes: 0
digest: 5326958ce3e23f6fea751ada9dfa05a4
first_seen: 2016-06-19 11:25:23
last_seen: 2016-06-19 13:57:05
*************************** 3. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest7` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
full_scan:
exec_count: 242815
err_count: 0
warn_count: 0
total_latency: 388029765111000
max_latency: 2019677047000
avg_latency: 1598046000
lock_latency: 20501920000000
rows_sent: 24282000
rows_sent_avg: 100
rows_examined: 72846600
rows_examined_avg: 300
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 242821
tmp_disk_tables: 0
rows_sorted: 24281600
sort_merge_passes: 0
digest: 9483723c685eb28e9cfc506b30578698
first_seen: 2016-06-19 11:25:24
last_seen: 2016-06-19 13:57:05
3 rows in set (0.00 sec)

mysql>
##还可以用这个视图,查看生成临时表的SQL执行情况,如:exec_count 执行了多少次,memory_tmp_tables 生成多少临时表,tmp_tables_to_disk_pct 硬盘临时表比例
mysql> select * from x$statements_with_temp_tables where db='sysbench' and query not like 'show%' limit 3\G;
*************************** 1. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest4` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
exec_count: 249558
total_latency: 405559669482000
memory_tmp_tables: 249563
disk_tmp_tables: 0
avg_tmp_tables_per_query: 1
tmp_tables_to_disk_pct: 0
first_seen: 2016-06-19 11:25:24
last_seen: 2016-06-19 14:00:36
digest: 884e86516aebcd28e0da39d6a7df164a
*************************** 2. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest5` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
exec_count: 249542
total_latency: 391186176853000
memory_tmp_tables: 249550
disk_tmp_tables: 0
avg_tmp_tables_per_query: 1
tmp_tables_to_disk_pct: 0
first_seen: 2016-06-19 11:25:24
last_seen: 2016-06-19 14:00:36
digest: bff86a3aa37ae0242218d5657499e5a2
*************************** 3. row ***************************
query: SELECT DISTINCTROW `c` FROM `sbtest10` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
db: sysbench
exec_count: 249459
total_latency: 428970366771000
memory_tmp_tables: 249462
disk_tmp_tables: 0
avg_tmp_tables_per_query: 1
tmp_tables_to_disk_pct: 0
first_seen: 2016-06-19 11:25:23
last_seen: 2016-06-19 14:00:36
digest: 7bec897913ccc856e2ea999413cc9ad1
3 rows in set (0.01 sec)


##查看有全表扫描的SQL ,exec_count 全表扫描SQL 总的执行了多少次,tmp_tables 全表扫描产生的临时表,tmp_disk_tables 全表扫描在磁盘中生成的临时表
mysql> SELECT * FROM statement_analysis WHERE db='sysbench' AND full_scan = '*' AND query not like 'show%' AND exec_count > 1\G
*************************** 1. row ***************************
query: SELECT * FROM `sbtest5`
db: sysbench
full_scan: *
exec_count: 2
err_count: 0
warn_count: 0
total_latency: 600.90 ms
max_latency: 456.60 ms
avg_latency: 300.45 ms
lock_latency: 473.00 us
rows_sent: 200000
rows_sent_avg: 100000
rows_examined: 200000
rows_examined_avg: 100000
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 146002080199f7b82624570fc9f622b1
first_seen: 2016-06-19 14:20:17
last_seen: 2016-06-19 14:21:01
*************************** 2. row ***************************
query: SELECT `QUERY_ID` , SUM ( `DUR ... ROFILING` GROUP BY `QUERY_ID`
db: sysbench
full_scan: *
exec_count: 5
err_count: 0
warn_count: 5
total_latency: 53.22 ms
max_latency: 48.88 ms
avg_latency: 10.64 ms
lock_latency: 1.19 ms
rows_sent: 62
rows_sent_avg: 12
rows_examined: 1280
rows_examined_avg: 256
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 10
tmp_disk_tables: 0
rows_sorted: 62
sort_merge_passes: 0
digest: c7d88fa65a7f375625dd708d7f208b15
first_seen: 2016-06-19 11:34:44
last_seen: 2016-06-19 14:20:17

 

 小结:

sys丰富的性能视图可以很方便诊断数据库,可以替代不少pt工具,因为SYS库下的视图是居于performance_schema ,有些时候可能会把这个shema优化,要用这个sys库的视图,就不能优化这个选项,所以配置文件不能设置performance_schema=off,同时可以看到,sysbench oltp.lua压测都是居于主键压测,要比较真实反应线上库性能,要用备份数据,在压测环境去压测,同时有关数据要脱敏。

 

posted @ 2016-06-19 15:05 文采飞扬 阅读(...) 评论(...) 编辑 收藏