MySQL 8.0 的发展越来越趋同与 Oracle, 为了更好的监控 MySQL 的一些相关指标, 出现了 SYS 视图, 用于监控.
1.MySQL 版本
- (root@localhost) [sys]> select @@version;
- +-----------+
- | @@version |
- +-----------+
- | 8.0.11 |
- +-----------+
- 1 row in set (0.01 sec)
2. 数据准备
- (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 的方式:
查看 performance Schema 表的视图
- (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 |
- ...................
查看表的数据访问量
- (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)
查看索引的冗余
- (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)
表自增 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)
监控全表扫描的 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
查看当前接入的会话
- (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)
查看当前接入的线程
- (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 |
- ........................................
查看 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)
知识点小注: 当页面数据太多的时候, 可以使用命令 (root@localhost) [sys]> pager more;
来源: http://www.linuxidc.com/Linux/2019-10/161194.htm