Mysql之自带四库之sys库 |
您所在的位置：网站首页 › mysql默认的数据库有哪些 › Mysql之自带四库之sys库 |
|
一、sys库介绍
Mysql5.7版本自带4个数据库，information_schema、mysql、performance_schema、sys。Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。sys_开头是库里的配置表，sys_config用于sys schema库的配置。 Sys库下有两种表： 字母开头： 适合人阅读，显示是格式化的数x$开头 ： 适合工具采集数据，原始类数据sys系统库支持MySQL 5.6或更高版本，要完全访问sys系统库，用户必须具有以下权限： 对所有sys表和视图具有SELECT权限对所有sys存储过程和函数具有EXECUTE权限对sys_config表具有INSERT、UPDATE权限对某些特定的sys系统库存储过程和函数需要额外权限，如，ps_setup_save()存储过程，需要临时表相关的权限 二、表清单mysql> 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 | | io_global_by_file_by_bytes | | io_global_by_file_by_latency | | io_global_by_wait_by_bytes | | io_global_by_wait_by_latency | | latest_file_io | | memory_by_host_by_current_bytes | | memory_by_thread_by_current_bytes | | memory_by_user_by_current_bytes | | memory_global_by_current_bytes | | memory_global_total | | metrics | | processlist | | ps_check_lost_instrumentation | | schema_auto_increment_columns | | schema_index_statistics | | schema_object_overview | | schema_redundant_indexes | | schema_table_lock_waits | | schema_table_statistics | | schema_table_statistics_with_buffer | | schema_tables_with_full_table_scans | | schema_unused_indexes | | session | | session_ssl_status | | statement_analysis | | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | | sys_config | | user_summary | | user_summary_by_file_io | | user_summary_by_file_io_type | | user_summary_by_stages | | user_summary_by_statement_latency | | user_summary_by_statement_type | | version | | wait_classes_global_by_avg_latency | | wait_classes_global_by_latency | | waits_by_host_by_latency | | waits_by_user_by_latency | | waits_global_by_latency | | x$host_summary | | x$host_summary_by_file_io | | x$host_summary_by_file_io_type | | x$host_summary_by_stages | | x$host_summary_by_statement_latency | | x$host_summary_by_statement_type | | x$innodb_buffer_stats_by_schema | | x$innodb_buffer_stats_by_table | | x$innodb_lock_waits | | x$io_by_thread_by_latency | | x$io_global_by_file_by_bytes | | x$io_global_by_file_by_latency | | x$io_global_by_wait_by_bytes | | x$io_global_by_wait_by_latency | | x$latest_file_io | | x$memory_by_host_by_current_bytes | | x$memory_by_thread_by_current_bytes | | x$memory_by_user_by_current_bytes | | x$memory_global_by_current_bytes | | x$memory_global_total | | x$processlist | | x$ps_digest_95th_percentile_by_avg_us | | x$ps_digest_avg_latency_distribution | | x$ps_schema_table_statistics_io | | x$schema_flattened_keys | | x$schema_index_statistics | | x$schema_table_lock_waits | | x$schema_table_statistics | | x$schema_table_statistics_with_buffer | | x$schema_tables_with_full_table_scans | | x$session | | x$statement_analysis | | x$statements_with_errors_or_warnings | | x$statements_with_full_table_scans | | x$statements_with_runtimes_in_95th_percentile | | x$statements_with_sorting | | x$statements_with_temp_tables | | x$user_summary | | x$user_summary_by_file_io | | x$user_summary_by_file_io_type | | x$user_summary_by_stages | | x$user_summary_by_statement_latency | | x$user_summary_by_statement_type | | x$wait_classes_global_by_avg_latency | | x$wait_classes_global_by_latency | | x$waits_by_host_by_latency | | x$waits_by_user_by_latency | | x$waits_global_by_latency | ±----------------------------------------------+ 101 rows in set (0.01 sec) 三、分表介绍 1、视图表分类介绍 host : 以IP分组相关的统计信息innodb : innodb buffer 相关信息io : 数据内不同维度展的IO相关的信息memory : 以IP，连接，用户，分配的类型分组及总的占用显示内存的使用metrics : DB的内部的统计值processlist : 线程相关的信息(包含内部线程及用户连接）ps_ : 没有工具统计的一些变量（没看出来存在的价值）schema : 表结构相关的信息，例如： 自增，索引， 表里的每个字段类型，等待的锁等等session : 用户连接相关的信息statement : 基于语句的统计信息（重店）statements_ : 出错的语句，进行全表扫描， 运行时间超长，排序相等（重点）user_ : 和host_开头的相似，只是以用户分组统计wait : 等待事件，比较专业，难看懂。waits : 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值。 2、表格字段说明host_summary 字段名意义host从哪个服务器上连过来。如果是NULL，表示内部的进程Statements这台服务器共执行了多少语句Statement_latency这台服务器发来等待语句执行的时间Statement_avg_latency该服务器等待语句执行的平均时间Table_scans该服务器扫描表的次数（非全表）File_io该服务器IO事件请求的次数File_io_latency该服务器请求等待IO的时间Current_connections该服务器当前的连接数Total_connections该服务器总连接DB共连接多少次Unique_user该服务器上有几个不同用户名的账户连接过来Current_memory该服务器上当前连接等占用的内存Total_memory_allocated该服务器上的请求总共使用的内存Io_global_by_file_by_bytes 字段名意义File被操作的文件名Count_read总共有多少次读Total_read总共读了多少字节Avg_read平均每次读多少字节Count_write总共多少次写Total_written总共写了多少字节Avg_write平均每次写的字节大学Total读和写总共的IO大学Write_pct写占total里的百分比User_summary 字段名意义User客户端连接过来的用户名。如果是NULL，表示内部进程Statements该用户执行了多少SQLStatement_latency该用户执行SQL的总延迟时间Statement_avg_latency该用户执行SQL的平均延迟时间Table_scans该用户执行SQL时扫描表的次数File_ios该用户请求操作用掉的IOFile_io_latency该用户请求操作的IO总延迟时间Current_connections该用户当前的连接数Total_connections该用户总的连接数Unique_hosts该用户从几个唯一的机器连接过来Current_memory该用户当前占用的内存Total_memory_allocated该用户总共申请到的内存（累加值）Memory_global_total Total_allocated server总共分配出去的内存（应该是server层）| Memory_by_thread_by_current_bytes 字段名意义Thread_id内部线程ID可以和session中的thd_id关联User这个线程是哪个用户创建的Current_count_used当前使用的内存块还没有释放Current_allocated当前分配的内存大小（字节）而且没有被释放出来Current_avg_alloc平均分配的blocksCurrent_max_alloc当前线程分配的最多内存Total_allocated当前连总共分配的内存大小Statement_analysis 字段名意义Query归一化的SQL样子Db在哪个DB中执行。NULL表示在任何DBFull_scan全表扫描的次数Exec_count该SQL执行的总次数Err_count发生错误的次数Warn_count发生警告的次数Total_latency总共发生延迟的实际Max_latency最大延迟时间Avg_latency平均延迟时间Lock_latency因锁等待占用的总时间Rows_sent执行该SQL返回的总行数Rows_sent_avg执行该SQL平均返回的行数Tmp_tables该SQL形成内存临时表的总次数Tmp_disk_tables该SQL形成文件临时表的总次数Rows_sorted该SQL总共排序的行数Sort_merge_passes用于排序中合并的总次数Digest该语句的hash值First_screen该SQL最早出现的时间Last_screen该SQL最近出现的时间Processlist && session 字段名意义Thd_id内部线程IDConn_id连接的ID，对应show processlist中的ID列User该线程创建的用户名Db连接的DB，如果NULL表示后台线程CommandClient发起命令的类型提示State命令的状态Time基于上面的state停留的实际Current_statement该线程执行的语句Statement_latency语句运行总共占用时间Progress该语句运行完成的百分比Lock_latency该语句用于锁等待的时间Rows_examined该语句扫描的次数Rows_send该语句返回的行数Rows_affected该语句影响到的行数（写入语句）Tmp_tables形成内存临时表的次数Tmp_disk_tables形成磁盘临时表的次数Full_scan全表扫描的次数Trx_state当前事务的状态Pid对应到系统里的pidProgram_name连接进来的标识名Session和processlist视图基本一样，只是把后台线程过滤掉。 Innodb_buffer_stats_by_schema 字段名意义Object_schema库名Allocated基于库分配的buffer pool大小Data基于schema实际缓存的数据大小Pages当前schema缓存的page数Pages_hashedBuffer pool中进行hash 索引的pagePages_oldBuffer pool中的旧页，可能被置换出去Rows_cachedBuffer pool中以行为单位的缓存Innodb_buffer_stats_by_table 和innodb_buffer_stats_by_schema基本一致。只是比上面多了个object_name指定表名。 四、使用示例 1、查询资源使用情况mysql> Select * from host_summary limit 1\G *************************** 1. row *************************** host: 192.168.0.124 statements: 4 statement_latency: 234.17 us statement_avg_latency: 58.54 us table_scans: 0 file_ios: 0 file_io_latency: 0 ps current_connections: 0 total_connections: 2 unique_users: 1 current_memory: 0 bytes total_memory_allocated: 0 bytes 1 row in set (0.03 sec) mysql> Select * from memory_global_total; ±----------------+ | total_allocated | ±----------------+ | 143.38 MiB | ±----------------+ 1 row in set (0.01 sec) 说明：内存部分，不包括innodbbuffer pool，只是server 层申请的内存 mysql> Select * from io_global_by_file_by_bytes limit 1\G *************************** 1. row *************************** file: @@datadir/ibdata1 count_read: 235 total_read: 5.69 MiB avg_read: 24.78 KiB count_write: 2129 total_written: 63.44 MiB avg_write: 30.51 KiB total: 69.12 MiB write_pct: 91.77 1 row in set (0.01 sec) mysql> Select * from user_summary limit 1\G *************************** 1. row *************************** user: root statements: 375314 statement_latency: 4.32 h statement_avg_latency: 41.40 ms table_scans: 157158 file_ios: 142646 file_io_latency: 2.97 m current_connections: 1 total_connections: 8043 unique_hosts: 2 current_memory: 0 bytes total_memory_allocated: 0 bytes 1 row in set (0.01 sec) 2、查询连接及发送的SQL情况mysql> select host, current_connections,statements from host_summary; ±--------------±--------------------±-----------+ | host | current_connections | statements | ±--------------±--------------------±-----------+ | 192.168.0.124 | 0 | 4 | | localhost | 1 | 187728 | ±--------------±--------------------±-----------+ 2 rows in set (0.01 sec) mysql> select conn_id, user, current_statement, last_statement from session; ±--------±---------------±------------------------------------------------------------------±---------------+ | conn_id | user | current_statement | last_statement | ±--------±---------------±------------------------------------------------------------------±---------------+ | 10052 | root@localhost | select conn_id, user, current_ … t, last_statement from session | NULL | ±--------±---------------±------------------------------------------------------------------±---------------+ 1 row in set (0.08 sec) 3、查询系统里执行最多的TOP 10 SQLmysql> select * from statement_analysis order by exec_count desc limit 10\G; *************************** 1. row *************************** query: INSERT INTO t1 VALUES (…) db: mysqlslap full_scan: exec_count: 98041 err_count: 0 warn_count: 0 total_latency: 47.17 m max_latency: 1.98 s avg_latency: 28.87 ms lock_latency: 7.68 s rows_sent: 0 rows_sent_avg: 0 rows_examined: 0 rows_examined_avg: 0 rows_affected: 98041 rows_affected_avg: 1 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: 946acd9cdfe59ae300b995cf0698c7ce first_seen: 2021-02-10 11:30:55 last_seen: 2021-02-10 11:54:28 *************************** 2. row *************************** query: SELECT intcol1 , charcol1 FROM t1 db: mysqlslap full_scan: * … 4、查询IO最高的表mysql> select * from io_global_by_file_by_bytes limit 10; ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+ | @@datadir/ibdata1 | 235 | 5.69 MiB | 24.78 KiB | 2129 | 63.44 MiB | 30.51 KiB | 69.12 MiB | 91.77 | | @@datadir/ib_logfile1 | 2 | 64.50 KiB | 32.25 KiB | 9972 | 25.25 MiB | 2.59 KiB | 25.31 MiB | 99.75 | | @@datadir/ib_logfile0 | 5 | 4.00 KiB | 819 bytes | 5901 | 15.30 MiB | 2.66 KiB | 15.31 MiB | 99.97 | | @@datadir/ibtmp1 | 0 | 0 bytes | 0 bytes | 72 | 12.94 MiB | 184.00 KiB | 12.94 MiB | 100.00 | | @@datadir/test2/fi_finance_detail_old.ibd | 545 | 8.52 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 8.52 MiB | 0.00 | | @@datadir/test2/fi_finance_detail.ibd | 454 | 7.09 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 7.09 MiB | 0.00 | | @@datadir/test2/macro_data.ibd | 259 | 4.05 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 4.05 MiB | 0.00 | | @@datadir/test2/stock_daily.ibd | 191 | 2.98 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 2.98 MiB | 0.00 | | @@datadir/test2/macro_data_copy1.ibd | 190 | 2.97 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 2.97 MiB | 0.00 | | @@datadir/mysql/innodb_index_stats.ibd | 6 | 96.00 KiB | 16.00 KiB | 36 | 576.00 KiB | 16.00 KiB | 672.00 KiB | 85.71 | ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+ 10 rows in set (0.02 sec) 5、查询延迟比较严重语句mysql> select * from statement_analysis order by avg_latency desc limit 2; ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ | query | db | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | rows_affected | rows_affected_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest | first_seen | last_seen | ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ | GRANT ALL PRIVILEGES ON `test1` . * TO ? @? | NULL | | 1 | 0 | 0 | 923.08 us | 923.08 us | 923.08 us | 461.00 us | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | b59b9848088068e714ba6de1bd813e9d | 2021-02-09 16:47:08 | 2021-02-09 16:47:08 | | SELECT `t` . `THREAD_ID` AS `t … _NUMBER_OF_BYTES_USED` ) DESC | sys | * | 3 | 0 | 0 | 274.54 ms | 116.47 ms | 91.51 ms | 6.10 ms | 20 | 7 | 30939 | 10313 | 0 | 0 | 15 | 7 | 200 | 0 | b3ea91361b876a2dba55fdce3df2ee23 | 2021-02-13 07:59:57 | 2021-02-13 08:10:46 | ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ 2 rows in set (0.00 sec) … 6、查询使用了磁盘临时表的SQL语句mysql> select db, query, tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20; ±-------------------±------------------------------------------------------------------±-----------±----------------+ | db | query | tmp_tables | tmp_disk_tables | ±-------------------±------------------------------------------------------------------±-----------±----------------+ | sys | SELECT `t` . `THREAD_ID` AS `t … _NUMBER_OF_BYTES_USED` ) DESC | 15 | 7 | | sys | SELECT IF ( `isnull` ( `perfor … _host_by_event_name` GROUP BY | 16 | 2 | | sys | SELECT IF ( `isnull` ( `perfor … _user_by_event_name` GROUP BY | 9 | 1 | | sys | SELECT IF ( ( `locate` ( ? , ` … . `COMPRESSED_SIZE` ) ) DESC | 4 | 3 | | sys | SELECT IF ( ( `locate` ( ? , ` … . `COMPRESSED_SIZE` ) ) DESC | 4 | 3 | | sys | SHOW TABLES | 5 | 0 | | NULL | SHOW SCHEMAS | 4 | 0 | | NULL | SHOW VARIABLES LIKE ? | 4 | 0 | | sys | SHOW TABLES LIKE ? | 4 | 0 | | sys | SHOW SCHEMAS | 3 | 0 | | test2 | SHOW TABLES | 2 | 0 | | mysql | SHOW SCHEMAS | 2 | 0 | | test1 | SHOW TABLES | 2 | 0 | | performance_schema | SHOW TABLES | 2 | 0 | | sys | SELECT `sys` . `format_bytes` … summary_global_by_event_name` | 1 | 1 | | test2 | SHOW SCHEMAS | 1 | 0 | | mysql | SHOW TABLES | 1 | 0 | | test1 | SHOW SCHEMAS | 1 | 0 | | sys | SHOW VARIABLES LIKE ? | 1 | 0 | | performance_schema | SHOW SCHEMAS | 1 | 0 | ±-------------------±------------------------------------------------------------------±-----------±----------------+ 20 rows in set (0.00 sec) 7、查询占用了最多的buffer pool的表mysql> select * from innodb_buffer_stats_by_table order by pages desc limit 10; ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ | test2 | fi_finance_detail_old | 8.48 MiB | 7.73 MiB | 543 | 0 | 201 | 23448 | | test2 | fi_finance_detail | 7.06 MiB | 6.46 MiB | 452 | 0 | 75 | 17179 | | test2 | macro_data | 4.02 MiB | 3.68 MiB | 257 | 0 | 225 | 45098 | | test2 | stock_daily | 2.95 MiB | 2.68 MiB | 189 | 0 | 189 | 49946 | | test2 | macro_data_copy1 | 2.94 MiB | 2.69 MiB | 188 | 0 | 188 | 34261 | | InnoDB System | SYS_TABLES | 1.45 MiB | 1.27 MiB | 93 | 0 | 1 | 4234 | | test1 | tb_9002 | 416.00 KiB | 316.07 KiB | 26 | 0 | 26 | 1352 | | test2 | macro_policy | 288.00 KiB | 224.03 KiB | 18 | 0 | 18 | 155 | | mysql | help_topic | 176.00 KiB | 126.44 KiB | 11 | 0 | 11 | 239 | | test1 | tb_9001 | 144.00 KiB | 96.00 KiB | 9 | 0 | 9 | 295 | ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ 10 rows in set (0.03 sec) 8、查询每个库占用多少buffer poolmysql> select * from innodb_buffer_stats_by_schema; ±--------------±-----------±-----------±------±-------------±----------±------------+ | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached | ±--------------±-----------±-----------±------±-------------±----------±------------+ | test2 | 26.42 MiB | 23.85 MiB | 1691 | 0 | 928 | 86357 | | test1 | 672.00 KiB | 464.52 KiB | 42 | 0 | 42 | 840 | | mysql | 656.00 KiB | 245.75 KiB | 41 | 0 | 36 | 1267 | | InnoDB System | 304.00 KiB | 98.92 KiB | 19 | 0 | 7 | 235 | | sys | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 | ±--------------±-----------±-----------±------±-------------±----------±------------+ 5 rows in set (0.07 sec) 9、查询每个连接分配多少内存mysql> select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id; ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | current_statement | ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | select b.user, current_count_u … b where a.thread_id = b.thd_id | ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ 1 row in set (0.12 sec) 10、查询MySQL内部现在有多个线程在运行mysql> select user, count() from processlist group by user; ±--------------------------------±---------+ | user | count() | ±--------------------------------±---------+ | innodb/buf_dump_thread | 1 | | innodb/dict_stats_thread | 1 | | innodb/io_ibuf_thread | 1 | | innodb/io_log_thread | 1 | | innodb/io_read_thread | 4 | | innodb/io_write_thread | 4 | | innodb/page_cleaner_thread | 1 | | innodb/srv_error_monitor_thread | 1 | | innodb/srv_lock_timeout_thread | 1 | | innodb/srv_master_thread | 1 | | innodb/srv_monitor_thread | 1 | | innodb/srv_purge_thread | 1 | | innodb/srv_worker_thread | 3 | | root@localhost | 1 | | sql/compress_gtid_table | 1 | | sql/main | 1 | | sql/signal_handler | 1 | | sql/thread_timer_notifier | 1 | ±--------------------------------±---------+ 18 rows in set (0.08 sec) |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |