*MySQL环境版本:
(root@localhost) [sys]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.01 sec)
**数据准备:
(root@localhost) [ztest]> show create table zstudent;
| Table | Create Table
+------------------------------------+
| zstudent | CREATE TABLE `zstudent` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------------------------------------+
1 row in set (0.00 sec)
创建索引:
(root@localhost) [ztest]> create index idx_stu_name on zstudent (stu_name);
(root@localhost) [ztest]> create unique index idx_stu_name2 on zstudent (stu_name);
*sys包含了很多总结performance Schema表的视图，这些视图成对出现，并且有些以x$前缀出现。查看并使用sys的方式:
(root@localhost) [sys]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| ztest |
+--------------------+
5 rows in set (0.01 sec)
(root@localhost) [sys]> use sys
Database changed
(root@localhost) [sys]> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
...................
1.查看表的数据访问量
(root@localhost) [sys]> select table_schema,table_name,io_read_requests,io_write_requests from schema_table_statistics where table_schema='ztest';
+--------------+------------+------------------+-------------------+
| table_schema | table_name | io_read_requests | io_write_requests |
+--------------+------------+------------------+-------------------+
| ztest | zstudent | 0 | 14 |
| ztest | zstudent2 | 0 | 7 |
+--------------+------------+------------------+-------------------+
2 rows in set (0.05 sec)
2.查看索引的冗余
(root@localhost) [sys]> select * from sys.schema_redundant_indexes \G
*************************** 1. row ***************************
table_schema: ztest
table_name: zstudent
redundant_index_name: idx_stu_name
redundant_index_columns: stu_name
redundant_index_non_unique: 1
dominant_index_name: idx_stu_name2
dominant_index_columns: stu_name
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `ztest`.`zstudent` DROP INDEX `idx_stu_name`
1 row in set (0.01 sec)
3.表自增ID监控
(root@localhost) [sys]> select * From schema_auto_increment_columns\G;
*************************** 1. row ***************************
table_schema: ztest
table_name: zstudent
column_name: stu_id
data_type: int
column_type: int(11)
is_signed: 1
is_unsigned: 0
max_value: 2147483647
auto_increment: 6
auto_increment_ratio: 0.0000
1 row in set (0.04 sec)
4.监控全表扫描的SQL语句
(root@localhost) [sys]> select * from statements_with_full_table_scans where db='ztest' \G
*************************** 1. row ***************************
query: SELECT * FROM `zstudent`
db: ztest
exec_count: 2
total_latency: 6.58 m
no_index_used_count: 2
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 10
rows_examined: 10
rows_sent_avg: 5
rows_examined_avg: 5
first_seen: 2018-05-19 17:03:03.306527
last_seen: 2018-05-19 18:36:51.142365
digest: 8800ada0600ed0790d89b6ab22e5bab762c3698d308346bb542c9b2c377c4114
5.查看当前接入的会话
(root@localhost) [sys]> select thd_id,conn_id,user,command,current_statement,current_memory From x$session\G;
*************************** 1. row ***************************
thd_id: 68
conn_id: 28
user: root@localhost
command: Sleep
current_statement: NULL
current_memory: 34950
*************************** 2. row ***************************
thd_id: 67
conn_id: 27
user: root@localhost
command: Query
current_statement: select thd_id,conn_id,user,command,current_statement,current_memory From x$session
current_memory: 1207007
*************************** 3. row ***************************
thd_id: 45
conn_id: 4
user: sql/event_scheduler
command: Sleep
current_statement: NULL
current_memory: 16569
3 rows in set (0.17 sec)
*current_memory ---The number of bytes allocated by the thread（当前这个线程所需要分配的内存，单位为byte）
6.查看当前接入的线程
(root@localhost) [sys]> select thd_id,conn_id,user,db,command,current_statement from x$processlist;
+--------+---------+--------------------------------------+-------+---------+----------------------------------------------------------------------------+
| thd_id | conn_id | user | db | command | current_statement |
+--------+---------+--------------------------------------+-------+---------+----------------------------------------------------------------------------+
| 27 | NULL | innodb/srv_master_thread | NULL | NULL | NULL |
| 29 | NULL | innodb/dict_stats_thread | NULL | NULL | NULL |
| 30 | NULL | innodb/fts_optimize_thread | NULL | NULL | NULL |
| 47 | 6 | sql/compress_gtid_table | NULL | Daemon | NULL |
| 34 | NULL | mysqlx/acceptor_network | NULL | NULL | NULL |
| 37 | NULL | innodb/srv_purge_thread | NULL | NULL | NULL |
| 38 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 39 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 41 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 1 | NULL | sql/main | mysql | NULL | NULL |
| 68 | 28 | root@localhost | ztest | Sleep | NULL |
| 67 | 27 | root@localhost | sys | Query | select thd_id,conn_id,user,db,command,current_statement from x$processlist |
| 40 | NULL | innodb/srv_purge_thread | NULL | NULL | NULL |
| 42 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 43 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 44 | NULL | innodb/srv_worker_thread | NULL | NULL | NULL |
| 45 | 4 | sql/event_scheduler | NULL | Sleep | NULL |
| 32 | NULL | mysqlx/worker | NULL | NULL | NULL |
| 31 | NULL | mysqlx/worker | NULL | NULL | NULL |
| 46 | NULL | sql/signal_handler | NULL | NULL | NULL |
| 28 | NULL | innodb/buf_dump_thread | NULL | NULL | NULL |
........................................
7.查看MySQL实例消耗的磁盘IO
(root@localhost) [sys]> select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
+----------------------------------------------+--------+
| file | avg_io |
+----------------------------------------------+--------+
| @@innodb_log_group_home_dir/ib_logfile0 | 642.62 |
| /data/mysqldata/3306/binlog/mysql-bin.000002 | 299 |
| @@innodb_data_home_dir/ibdata1 | 240.8 |
| /data/mysqldata/3306/slow_statement.log | 212 |
| /data/mysqldata/3306/binlog/mysql-bin.000001 | 119 |
| @@basedir/share/english/errmsg.sys | 83.65 |
| @@innodb_data_home_dir/ibtmp1 | 61.47 |
| @@datadir/undo_001 | 32.18 |
| @@datadir/undo_002 | 32.18 |
| @@datadir/mysql.ibd | 32.09 |
+----------------------------------------------+--------+
10 rows in set (0.49 sec)
解释:由于是一个测试库，所以读写IO的负载都没有，基本在innodb_log日志自己的刷新之上。
知识点小注：当页面数据太多的时候，可以使用命令(root@localhost) [sys]> pager more;
