前两天因为去南昌出了个短差,有几天没有更新了,有时间就动起来。今天就总结一下mysql数据库的几个系统库。
首先说说sys库,mysql5.6开始推出了sys系统库,提供了一些直接访问系统库如performance_schema的一些示图,简化了一些查询监控操作,大部分数据来自performance_schema(简称PS), 因此我们只要好好研究sys系统示图即可,对于performance_schema即用sys替代。少量数据来自information_schema(简称IS)。下面就简要从几个系统库用示例的方式介绍一下常用示例场景:
SYS
首先看看sys示库下面有哪些表
SYS示图
mysql> select database();
+------------+
| database() |
+------------+
| sys |
+------------+
1 row in set (0.00 sec)
mysql>
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 |
+-----------------------------------------------+
我们可以看到有带X$的,也有不带x$的,带X$的主要是没加工过的没有被处理的,而不带X$的一般经过了加工处理,查询起来更加直观。
下面列举一些常用查询示例
查询版本相关信息
mysql> select * from sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.1 | 5.7.22-log |
+-------------+---------------+
1 row in set (0.00 sec)
会话相关
查询当前正在执行的语句进度信息
select * fromsession where conn_id != connection_id() and trx_state ='ACTIVE';
用户与连接:
查看每个客户端IP过来的连接消耗资源情况。
查看每个用户资源消耗情况
查看当前有多少个连接连进来:
select host,current_connections,statementsfrom host_summary\G;
MySQL内部有多个线程在运行,线程类型及数量。
select user, count(*) from processlistgroup user;
当前正在执行的sql会话信息:
select conn_id, user, current_statement,last_statement from session\G;
文件IO相关统计
根据file_io统计
mysql> select * from host_summary_by_file_io;
+------------+------+------------+
| host | ios | io_latency |
+------------+------+------------+
| background | 2365 | 330.04 ms |
| localhost | 915 | 64.59 ms |
+------------+------+------------+
IO请求前三名的文件:
select * from io_global_by_file_by_bytes order bytotal desc limit 3\G;
事务与锁相关:
select * from innodb_lock_waits\G
select * from schema_table_lock_waits\G;
INNODB引擎相关:
查询innodb_buffer_pool中的热数据
select * from innodb_buffer_stats_by_schema;
select * from innodb_buffer_stats_by_table limit3;
pages是指在buffer pool中的page数量;pages_old指在LRU 列表中处于后37%位置的page。
当出现buffer page不够用时,就会征用这些page所占的空间。37%是默认位置,具体可以自定义。
查询每张表在内存中消耗情况:
select * from innodb_buffer_stats_by_table limit3;
总共分配了多少内存
select * from memory_global_total\G
查询每个连接分配了多少内存
selectb.user,
current_count_used,
current_allocated,
current_avg_alloc,
current_max_alloc,
total_allocated,
current_statement
frommemory_by_thread_by_current_bytes a,sessionb
wherea.thread_id=b.thd_id G;
索引相关:
冗余索引和没有用过的索引
select * from schema_redundant_indexes;
select * from schema_unused_indexes;
查询表自增字段监控相关
select * fromschema_auto_increment_columns;
查看使用了全表扫描、文件排序、临时表的语句:
mysql> show tables like 'statements%';
+---------------------------------------------+
| Tables_in_sys (statements%) |
+---------------------------------------------+
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
|statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
+---------------------------------------------+
索引使用情况:
select * from schema_index_statistics wheretable_name='employees'\G
Informatino_schema
而对于IS里面基本存储的是一些基本信息如数据库表、示图、触发器等信息,还有包括字符集、文件信息、分区参数等一些元数据,基本上mysql很信息在这个IS库里都能查到.
查询IS下面有哪些表。
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.00 sec)
这里我们对它做一个简单介绍:
前三张表:
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY
是和字符集相关的
COLUMNS 相当于oracle里面的dba_columns即表和列的对应关系,即哪些表有哪些列。
FILES 相当于oracle里面的dba_data_files记录的是数据文件和表空间的对应关系。
PROCESSLIST 记录的是线程信息,相当于show processlist的输出
TABLES 相当于oracle里面的dba_tables,存储表的相关信息
VIEWS 相当于oracle里面的dba_views,存储示图相关信息
而接下来的三个表则非常重要,
innodb_trx 打印innodb内核中的当前活跃(ACTIVE)事务
innodb_locks 打印当前状态产生的innodb锁仅在有锁等待时打印
innodb_lock_waits 打印当前状态产生的innodb锁等待仅在有锁等待时打印
通常我们查询哪些事务或者锁等待时必须查这三个表。
USER_PRIVILEGES 与权限相关
MYSQL
mysql> use mysql
Reading table information for completion of tableand column names
You can turn off this feature to get a quickerstartup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Mysql里面我经常用到的就是下面三个
User用户相关的
Innodb_table_stats和innodb_index_stats统计信息相关的
由于本人是oracle出身的,慢慢地感觉到mysql越来越像oracle了从之前的hint,到现在的sys系统库,说不定过一段时间都有mysql awr报告供我们分析了,还有就是hash_join,希望mysql也赶紧支持吧。另外mysql没有一个牛逼的解析缓存,每次都是硬解析,所以能在程序中解决的问题就不要放到数据库中来,数据库就是个存放数据的仓库,这也许就是它的设计思路,所以不要放动则几十上百行的代码放进mysql里面来,这个真的不适合我。
洗洗睡觉了….