快乐学习
前程无忧、中华英才非你莫属!

MySQL运维内参读书笔记-Day2

、监控


工具篇


工具:天兔*(复制监控、表空间分析、数据库健康状态、告警系统、进程监控、慢查询分析、InnoDB监控、AWR报告


首选推荐,金融级别DBA都在用,还是开源的产品。好不好,不用我说了吧~


Monyog(非常轻量级,可以部署windows平台,告警触发、权限预警比较好用。亚马逊xx产品都在用)、


Zabbix(可定制化非常高,只要玩的好,什么都能监控,对MySQL的内部监控实现起来略微复杂,时间维度高,所以用它来监测MySQL不推荐)


spotlight on mysql (没怎么玩过,UI做的好~,做压测时候,可以用它来做报表截图,哈哈)

do DBA  tools、命令行式实时监控,支持ssh,弥补了只有报表没有具体数据的尴尬,远程抓取linux的数据,进行解析。类似于抓包了。

MySQL之前版本的监控,如果不使用工具,没有报警机制,预警的性能指标,24小时救火对DBA来说是身心疲惫,用开源产品缺点就是定制化困难,脚本好一点呢团队大呢,就是左一个脚本,右一个脚本。甚至有的用ruby,有的用python。有的甚至用java. 

如果脚本不好的DBA,也可以找移动端开发的童学,做一款移动监控的小程序,跟数据库做交互,是不是很爽。只要需求明确,关键监控指标数据接口给到开发,接入到小程序端,就可以随时随地收到服务器的告警了。在可控的的情况下,如果开发精力比较旺盛,可以定制一些自动化触发机制,最基本的让程序自动改变mysql的临时连接数。自动kill掉,滥用资源的大事务。如果后期我们的数据库日志足够庞大的时候,可以利用成熟的BI机制,AI机制。更好的让程序自动审核,防住拖库高危SQL..并且通知到相对应的开发。以及服务器硬件性能告警,做出自动扩容等措施。


内控篇

MySQL5.7以后监控是越来越成熟了,提供了比较强大的Performance sehema的库,内置了80多张表。很多的监控指标MySQL自己完成了,并且存入表里面,可以通过sql可以很直观的进行监控。

MySQL Performance Schema  用于监视MySQL服务器,且运行时消耗很少的性能。Performance Schema 收集数据库服务器性能参数,并且表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。Performance Schema 具有以下特征:

Performance Schema 提供了一种在服务器运行时检查服务器的内部执行的方法。它使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现。性能模式主要关注性能数据。这与用于检查元数据的INFORMATION_SCHEMA不同。

Performance Schema 事件特定于MySQL服务器的给定实例。 Performance Schema 表被视为本地服务器,并且对其进行的更改不会被复制或写入二进制日志。

Performance Schema 中的表是内存表,不使用磁盘存储,在 datadir 的 performance_schema 目录下,只有.frm表结构文件,没有数据文件。表内容在服务器启动时重新填充,并在服务器关闭时丢弃。

数据收集是通过修改服务器源代码来实现的。 不同于其他功能(如复制或Event Scheduler),不存在与Performance Schema相关联的单独线程。

服务器监控持续不中断地进行,花费很少。 开启Performance Schema不会使服务器不可用。

 

从MySQL5.6开始,Performance Schema 默认打开,这里讲述一些在数据库使用当中PERFORMANCE_SCHEMA的一些比较常用的功能。具体的信息可以查看(https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

阅读目录

CHARACTER_SETS

COLLATIONS

COLLATION_CHARACTER_SET_APPLICABILITY

COLUMNS

COLUMN_PRIVILEGES

ENGINES

EVENTS

FILES

GLOBAL_STATUS & SESSION_STATUS & GLOBAL_VARIABLES & SESSION_VARIABLES

KEY_COLUMN_USAGE

OPTIMIZER_TRACE

PARAMETERS

PARTITIONS

PLUGINS

PROCESSLIST

PROFILING

REFERENTIAL_CONSTRAINTS

ROUTINES

SCHEMATA

SCHEMA_PRIVILEGES

STATISTICS

TABLES

TABLESPACES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRIGGERS

USER_PRIVILEGES

VIEWS

InnoDB Tables

 

refman mysql 5.7

INFORMATION_SCHEMA提供了对数据库元数据的访问,MySQL服务器信息,如数据库或表的名称,列的数据类型,访问权限等。 有时也把这些信息叫做数据字典或系统目录。

每个数据库实例都会有一个 INFORMATION_SCHEMA 库,保存的是本实例下其他所有库的信息。INFORMATION_SCHEMA数据库包含多个只读表。 它们实际上是视图,而不是基础表,所以没有与它们关联的文件,并且你不能在它们上设置触发器。此外,数据库目录下也没有该库的目录。

虽然可以使用USE语句将INFORMATION_SCHEMA选择为缺省数据库,但只能读取表的内容,不能对它们执行INSERT,UPDATE或DELETE操作。

每个MySQL用户都可以访问 INFORMATION_SCHEMA,但是只能看到自己有权限的那些行。

CHARACTER_SETS

  • 字符集:对 Unicode 的一套编码。

  • collation:用于指定数据集如何排序,以及字符串的比对规则。

CHARACTER_SETS表提供了有关可用字符集的信息。

> desc CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
> select * from CHARACTER_SETS where CHARACTER_SET_NAME like 'utf8%';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+

COLLATIONS

COLLATIONS表提供有关每个字符集的排序规则的信息。

> desc COLLATIONS;

+——————+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+——————+————-+——+—–+———+——-+| COLLATION_NAME | varchar(32) | NO | | | || CHARACTER_SET_NAME | varchar(32) | NO | | | || ID | bigint(11) | NO | | 0 | || IS_DEFAULT | varchar(3) | NO | | | || IS_COMPILED | varchar(3) | NO | | | || SORTLEN | bigint(3) | NO | | 0 | |+——————+————-+——+—–+———+——-+

COLLATION_NAME is the collation name.

CHARACTER_SET_NAME is the name of the character set with which the collation is associated.

ID is the collation ID.

IS_DEFAULT indicates whether the collation is the default for its character set.

IS_COMPILED indicates whether the character set is compiled into the server.

SORTLEN is related to the amount of memory required to sort strings expressed in the character set.

每个character set会对应一定数量的collation:

> select * from COLLATIONS where COLLATION_NAME like 'utf8%general%';
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN 
|+--------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 || utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 || utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 
|+--------------------------+--------------------+-----+------------+-------------+---------+

ci 是 case insensitive 的缩写(大小写敏感), cs 是 case sensitive 的缩写。即,指定大小写是否敏感。

COLLATION_CHARACTER_SET_APPLICABILITY

COLLATION_CHARACTER_SET_APPLICABILITY表显示了哪种字符集适用于哪种排序方式。这些列相当于我们从SHOW COLLATION获得的前两个字段。

> desc COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------+-------------+------+-----+---------+-------+| 
Field | Type | Null | Key | Default | Extra 
|+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(32) | NO | | | || CHARACTER_SET_NAME | varchar(32) | NO | | | 
|+--------------------+-------------+------+-----+---------+-------+

COLUMNS

COLUMNS表提供表格中列的信息。

> desc COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+| 
Field | Type | Null | Key | Default | Extra 
|+--------------------------+---------------------+------+-----+---------+-------+| 
TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | || 
TABLE_NAME | varchar(64) | NO | | | || COLUMN_NAME | varchar(64) | NO | 
| | || ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | || COLUMN_DEFAULT | longtext | YES | | NULL | 
|| IS_NULLABLE | varchar(3) | NO | | | || DATA_TYPE | varchar(64) | NO | | | || CHARACTER_MAXIMUM_LENGTH 
| bigint(21) unsigned | YES | | NULL | || CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | 
|| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | || NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL |
 || DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | || CHARACTER_SET_NAME | varchar(32) | YES | | NULL | 
 || COLLATION_NAME | varchar(32) | YES | | NULL | || COLUMN_TYPE | longtext | NO | | NULL | || COLUMN_KEY | varchar(3) 
 | NO | | | || EXTRA | varchar(30) | NO | | | || PRIVILEGES | varchar(80) | NO | | | || COLUMN_COMMENT | varchar(1024) 
 | NO | | | || GENERATION_EXPRESSION | longtext | NO | | NULL | 
 |+--------------------------+---------------------+------+-----+---------+-------+21 rows in set (0.01 sec)

COLUMN_PRIVILEGES

COLUMN_PRIVILEGES表提供有关列权限的信息。 这些信息来自mysql.columns_priv授权表。

> desc COLUMN_PRIVILEGES;

+————–+————–+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+————–+————–+——+—–+———+——-+| GRANTEE | varchar(81) | NO | | | || TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | || TABLE_NAME | varchar(64) | NO | | | || COLUMN_NAME | varchar(64) | NO | | | || PRIVILEGE_TYPE | varchar(64) | NO | | | || IS_GRANTABLE | varchar(3) | NO | | | |+————–+————–+——+—–+———+——-+

PRIVILEGE_TYPE 可以包含一个(且只能有一个)这些值:SELECT,INSERT,UPDATE,REFERENCES。

如果用户具有GRANT OPTION权限,则IS_GRANTABLE为YES。 否则,IS_GRANTABLE 为NO。 输出不会将GRANT OPTION列为单独的权限。

ENGINES

ENGINES表提供了有关存储引擎的信息。

> desc ENGINES;

+————+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+————+————-+——+—–+———+——-+| ENGINE | varchar(64) | NO | | | || SUPPORT | varchar(8) | NO | | | || COMMENT | varchar(80) | NO | | | || TRANSACTIONS | varchar(3) | YES | | NULL | || XA | varchar(3) | YES | | NULL | || SAVEPOINTS | varchar(3) | YES | | NULL | |+————+————-+——+—–+———+——-+

ENGINES表是非标准表,其内容对应于SHOW ENGINES语句的列。

EVENTS

EVENTS表提供了有关定时事件(scheduled events)的信息,events类似与linux crontab计划任务,用于时间触发。通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程。

> desc events;

+——————–+—————+——+—–+———————+——-+| Field | Type | Null | Key | Default | Extra |+——————–+—————+——+—–+———————+——-+| EVENT_CATALOG | varchar(64) | NO | | | || EVENT_SCHEMA | varchar(64) | NO | | | || EVENT_NAME | varchar(64) | NO | | | || DEFINER | varchar(77) | NO | | | || TIME_ZONE | varchar(64) | NO | | | || EVENT_BODY | varchar(8) | NO | | | || EVENT_DEFINITION | longtext | NO | | NULL | || EVENT_TYPE | varchar(9) | NO | | | || EXECUTE_AT | datetime | YES | | NULL | || INTERVAL_VALUE | varchar(256) | YES | | NULL | || INTERVAL_FIELD | varchar(18) | YES | | NULL | || SQL_MODE | varchar(8192) | NO | | | || STARTS | datetime | YES | | NULL | || ENDS | datetime | YES | | NULL | || STATUS | varchar(18) | NO | | | || ON_COMPLETION | varchar(12) | NO | | | || CREATED | datetime | NO | | 00000000 00:00:00 | || LAST_ALTERED | datetime | NO | | 00000000 00:00:00 | || LAST_EXECUTED | datetime | YES | | NULL | || EVENT_COMMENT | varchar(64) | NO | | | || ORIGINATOR | bigint(10) | NO | | 0 | || CHARACTER_SET_CLIENT | varchar(32) | NO | | | || COLLATION_CONNECTION | varchar(32) | NO | | | || DATABASE_COLLATION | varchar(32) | NO | | | |+——————–+—————+——+—–+———————+——-+24 rows in set (0.00 sec)

• The EVENTS table is a nonstandard table.  # EVENTS表是非标准表

• EVENT_CATALOG: The value of this column is always def.

• EVENT_SCHEMA: # events 所属的库

• EVENT_NAME: # event 名称

• DEFINER:  # 创建EVENT的账号,'user_name'@'host_name'的格式

• TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

• EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.7, this is always SQL. This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.  # 不要与早期版本的同名项(现在是EVENT_DEFINITION)混淆

• EVENT_DEFINITION: # 时间需要执行的部分,即组成时间的Do子句。

• EVENT_TYPE:  #事件重复类型,一次(transient)或重复(repeating)。

• EXECUTE_AT: #对于一次性事件,这是在用于创建事件的CREATE EVENT语句的AT子句中指定的DATETIME值;如果事件的计时由EVERY子句而不是AT子句确定(也就是说,事件类型为repeating),则此列的值为NULL。

• INTERVAL_VALUE:  #对于定期事件,此列包含事件的EVERY子句的数字部分。 对于一次性事件(即其定时由AT子句确定的事件),此列为NULL。

• INTERVAL_FIELD:  #对于定期事件,此列包含EVERY子句的单位部分,用于管理事件的时间。因此,此列包含“YEAR”,“QUARTER”,“DAY”等值。对于一次性事件(即其定时由AT子句确定的事件),此列为NULL

• SQL_MODE:   #在创建或更改事件并在其下执行事件时有效的SQL模式。

• STARTS: #事件定义时包含了STARTS子句的事件,此列包含相应的DATETIME值。 与EXECUTE_AT列一样,此值可解析所使用的任何表达式。 如果没有影响事件时间的STARTS子句,则此列为NULL

• ENDS:  #事件定义时包含了ENDS子句的循环事件,此列包含相应的DATETIME值。 与EXECUTE_AT列一样,此值可解析所使用的任何表达式。 如果没有影响事件时间的ENDS子句,则此列为NULL。

• STATUS:  #ENABLE, DISABLE或SLAVESIDE_DISABLED 三个值之一。 SLAVESIDE_DISABLED表示事件的创建发生在另一个作为复制主服务器的MySQL服务器上,并被复制到当前作为从服务器的MySQL服务器上,但事件目前不在从服务器上执行。

• ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.

• CREATED:  #事件创建时间

• LAST_ALTERED:  #事件最后修改时间

• LAST_EXECUTED:  #事件最后一次执行的时间

• EVENT_COMMENT: #事件的注释

• ORIGINATOR:  #在其上创建事件的MySQL服务器的服务器ID; 用于复制。 默认值是0。

• CHARACTER_SET_CLIENT: #事件创建时character_set_client系统变量的session值

• COLLATION_CONNECTION: #事件创建时collation_connection系统变量的session值

• DATABASE_COLLATION:  #与事件关联的数据库的排序规则。

FILES

FILES表提供有关存储MySQL表空间数据的文件信息。

INFORMATION_SCHEMA.FILES提供有关InnoDB数据文件信息。在MySQL Cluster中,此表还提供有关存储MySQL Cluster磁盘数据表的文件信息。

> desc FILES;

+——————–+———————+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+——————–+———————+——+—–+———+——-+| FILE_ID | bigint(4) | NO | | 0 | || FILE_NAME | varchar(4000) | YES | | NULL | || FILE_TYPE | varchar(20) | NO | | | || TABLESPACE_NAME | varchar(64) | YES | | NULL | || TABLE_CATALOG | varchar(64) | NO | | | || TABLE_SCHEMA | varchar(64) | YES | | NULL | || TABLE_NAME | varchar(64) | YES | | NULL | || LOGFILE_GROUP_NAME | varchar(64) | YES | | NULL | || LOGFILE_GROUP_NUMBER | bigint(4) | YES | | NULL | || ENGINE | varchar(64) | NO | | | || FULLTEXT_KEYS | varchar(64) | YES | | NULL | || DELETED_ROWS | bigint(4) | YES | | NULL | || UPDATE_COUNT | bigint(4) | YES | | NULL | || FREE_EXTENTS | bigint(4) | YES | | NULL | || TOTAL_EXTENTS | bigint(4) | YES | | NULL | || EXTENT_SIZE | bigint(4) | NO | | 0 | || INITIAL_SIZE | bigint(21) unsigned | YES | | NULL | || MAXIMUM_SIZE | bigint(21) unsigned | YES | | NULL | || AUTOEXTEND_SIZE | bigint(21) unsigned | YES | | NULL | || CREATION_TIME | datetime | YES | | NULL | || LAST_UPDATE_TIME | datetime | YES | | NULL | || LAST_ACCESS_TIME | datetime | YES | | NULL | || RECOVER_TIME | bigint(4) | YES | | NULL | || TRANSACTION_COUNTER | bigint(4) | YES | | NULL | || VERSION | bigint(21) unsigned | YES | | NULL | || ROW_FORMAT | varchar(10) | YES | | NULL | || TABLE_ROWS | bigint(21) unsigned | YES | | NULL | || AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_FREE | bigint(21) unsigned | YES | | NULL | || CREATE_TIME | datetime | YES | | NULL | || UPDATE_TIME | datetime | YES | | NULL | || CHECK_TIME | datetime | YES | | NULL | || CHECKSUM | bigint(21) unsigned | YES | | NULL | || STATUS | varchar(20) | NO | | | || EXTRA | varchar(255) | YES | | NULL | |+——————–+———————+——+—–+———+——-+38 rows in set (0.00 sec)

InnoDB Notes

以下说明适用于InnoDB数据文件。 下面没有描述的INFORMATION_SCHEMA.FILES字段不适用于InnoDB并报告NULL值。

INFORMATION_CHEMA.FILES报告的数据来自于InnoDB打开文件的内存缓存。相比之下,INFORMATION_SCHEMA.INNODB_SYS_DATAFILES 从InnoDB SYS_DATAFILES内部数据字典表中报告数据。

INFORMATION_SCHEMA.FILES报告的数据包括临时表空间数据。此数据在内部SYS_DATAFILES数据字典表中不可得,因此不由INNODB_SYS_DATAFILES报告。

当使用innodb_undo_tablespaces配置单独的undo表空间时,INFORMATION_SCHEMA.FILES则会报告undo表空间的数据。

FILE_ID指表空间ID,也被称为space_id或fil_space_t :: id。

FILE_NAME  # FILE_NAME是数据文件的名称。 每个表文件和常规表空间都有一个.ibd文件扩展名。undo表空间的前缀为undo。 系统表空间的前缀是ibdata。 临时表空间由ibtmp作为前缀。 文件名包括文件路径,可能与MySQL数据目录(datadir)有关。

FILE_TYPE  # FILE_TYPE是表空间文件类型。 InnoDB文件有三种可能的文件类型。 TABLESPACE是用于保存表,索引或其他形式的用户数据的表空间文件类型。 TEMPORARY是临时表空间的文件类型。 UNDO LOG是undo日志表空间的文件类型,用于保存undo记录。 默认情况下,undo记录存储在系统表空间中。 可以使用innodb_undo_tablespaces选项来添加独立的undo日志表空间。

TABLESPACE_NAME  # TABLESPACE_NAME是表空间的SQL名称。 常规表空间(general tablespace)名称是SYS_TABLESPACES.NAME值。 对于其他表空间文件,名称以innodb_开头,例如innodb_system,innodb_undo和innodb_file_per_table。 file-per-table表空间名称格式是innodb_file_per_table _ ##,其中##是表空间ID。

ENGINE  # ENGINE是指存储引擎。对于InnoDB文件,值总是InnoDB。

innodb 表空间结构:page(页)—>  extent(区)—> segment(段)—> tablespace(表空间)

FREE_EXTENTS  # FREE_EXTENTS是当前数据文件中完全可用的区的数量。

TOTAL_EXTENTS # TOTAL_EXTENTS是当前数据文件中使用的完全区(full extents)的数量,文件末尾的局部区(partial extent)不计算在内。

EXTENT_SIZE  #对于4k,8k或16k页面大小的文件,EXTENT_SIZE是1048576(1MB)。 对于32k页面大小的文件,扩展大小为2097152字节(2MB),对于64k页面大小的文件,扩展大小为4194304(4MB)。 INFORMATION_SCHEMA.FILES不报告InnoDB页面大小。页面大小由innodb_page_size选项定义。扩展大小信息也可以从INNODB_SYS_TABLESPACES中检索到,其中FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID。

INITIAL_SIZE # INITIAL_SIZE是文件的初始大小,以字节为单位。

MAXIMUM_SIZE # MAXIMUM_SIZE是文件中允许的最大字节数。 除预定义的系统表空间数据文件外,所有数据文件的值均为NULL。 最大系统表空间文件大小由innodb_data_file_path定义。 最大临时表空间文件大小由innodb_temp_data_file_path定义。 预定义的系统表空间数据文件的NULL值表示没有明确定义文件大小限制。

AUTOEXTEND_SIZE  # AUTOEXTEND_SIZE是由innodb_data_file_path为系统表空间定义的自动扩展大小,或由临时表空间的innodb_temp_data_file_path定义。

DATA_FREE # DATA_FREE是整个表空间的可用空间总量(以字节为单位)。 预定义的系统表空间(包括系统表空间和临时表空间)可能有一个或多个数据文件。

STATUS  # STATUS默认为NORMAL。 InnoDB file-per-table表空间可能会报告IMPORTING,这表明表空间不可用。

NDB Notes

太多了,这里先不列举了,有兴趣的可以自行查看手册。

important

一旦某个extent(区)被使用,delete from 语句不会释放磁盘空间,drop 数据文件才能释放。

GLOBAL_STATUS & SESSION_STATUS & GLOBAL_VARIABLES & SESSION_VARIABLES

GLOBAL_STATUS和SESSION_STATUS表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL STATUS和SHOW SESSION STATUS语句产生的信息

GLOBAL_VARIABLES和SESSION_VARIABLES表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL VARIABLES和SHOW SESSION VARIABLES语句所产生的信息。

从MySQL 5.7.6开始,这里描述的4张表中的信息也可在Performance Schema中获取。 INFORMATION_SCHEMA表不推荐优先于Performance Schema表,并将在未来的MySQL版本中被删除。(从mysql 5.7.6开始 information_schema.global_status 已经开始被舍弃,为了兼容性,此时需要打开 show_compatibility_56

> desc GLOBAL_STATUS;

+————–+—————+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+————–+—————+——+—–+———+——-+| VARIABLE_NAME | varchar(64) | NO | | | || VARIABLE_VALUE | varchar(1024) | YES | | NULL | |+————–+—————+——+—–+———+——-+

KEY_COLUMN_USAGE

KEY_COLUMN_USAGE表描述了哪些键列有约束条件。

如果约束是一个外键,那么这是外键的列,而不是外键引用的列。

ORDINAL_POSITION的值是列在约束中的位置,而不是列在表中的位置。 列位置从1开始编号。

对于唯一主键约束,POSITION_IN_UNIQUE_CONSTRAINT的值为NULL。 对于外键约束,它是被引用的表的键中的序数位置。

OPTIMIZER_TRACE

OPTIMIZER_TRACE表提供了优化器跟踪功能产生的信息。要启用跟踪(tracking),使用optimizer_trace系统变量。

PARAMETERS

PARAMETERS表提供有关存储过程和函数参数的信息,以及有关存储函数的返回值的信息。 参数信息与mysql.proc表中param_list列的内容类似。

PARTITIONS

PARTITIONS表提供有关表分区的信息。 有关分区表的更多信息,请参见第21章分区。

> desc PARTITIONS;

+—————————–+———————+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+—————————–+———————+——+—–+———+——-+| TABLE_CATALOG | varchar(512) | NO | | | | always def

| TABLE_SCHEMA | varchar(64) | NO | | | | 分区所属的库

| TABLE_NAME | varchar(64) | NO | | | | 分区所属的表

| PARTITION_NAME | varchar(64) | YES | | NULL | | 分区名

| SUBPARTITION_NAME | varchar(64) | YES | | NULL | | 子分区名

| PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | 分区的编号

| SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | 子分区编号

| PARTITION_METHOD | varchar(18) | YES | | NULL | | 分区类型,RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY;

| SUBPARTITION_METHOD | varchar(12) | YES | | NULL | | 子分区类型

| PARTITION_EXPRESSION | longtext | YES | | NULL | | 分区函数的表达式

| SUBPARTITION_EXPRESSION | longtext | YES | | NULL | || PARTITION_DESCRIPTION | longtext | YES | | NULL | | 用于range和list,定义分区范围的值

| TABLE_ROWS | bigint(21) unsigned | NO | | 0 | | 该分区的行数

| AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | | 行的平均长度(字节)= DATA_LENGTH/ TABLE_ROWS

| DATA_LENGTH | bigint(21) unsigned | NO | | 0 | | 所有行的总长度(字节)

| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | 可以存储在此分区或子分区中的最大字节数

| INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | | 索引长度(字节)

| DATA_FREE | bigint(21) unsigned | NO | | 0 | | 分配给分区或子分区但未使用的字节数。

| CREATE_TIME | datetime | YES | | NULL | | 分区创建时间

| UPDATE_TIME | datetime | YES | | NULL | | 分区更新时间

| CHECK_TIME | datetime | YES | | NULL | || CHECKSUM | bigint(21) unsigned | YES | | NULL | || PARTITION_COMMENT | varchar(80) | NO | | | || NODEGROUP | varchar(12) | NO | | | || TABLESPACE_NAME | varchar(64) | YES | | NULL | | 分区所属的表空间

+—————————–+———————+——+—–+———+——-+25 rows in set (0.00 sec)

PLUGINS

PLUGINS表提供了有关服务器插件的信息。

> desc PLUGINS;

+———————-+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+———————-+————-+——+—–+———+——-+| PLUGIN_NAME | varchar(64) | NO | | | || PLUGIN_VERSION | varchar(20) | NO | | | || PLUGIN_STATUS | varchar(10) | NO | | | | 插件状态,ACTIVE,INACTIVE,DISABLED或DELETED

| PLUGIN_TYPE | varchar(80) | NO | | | || PLUGIN_TYPE_VERSION | varchar(20) | NO | | | || PLUGIN_LIBRARY | varchar(64) | YES | | NULL | | 插件共享库文件的名称,如server_audit.so,如果为NULL,则插件不能卸除

| PLUGIN_LIBRARY_VERSION | varchar(20) | YES | | NULL | || PLUGIN_AUTHOR | varchar(64) | YES | | NULL | || PLUGIN_DESCRIPTION | longtext | YES | | NULL | || PLUGIN_LICENSE | varchar(80) | YES | | NULL | || LOAD_OPTION | varchar(64) | NO | | | | 如何加载插件,值为OFF,ON,FORCE或FORCE_PLUS_PERMANENT

+———————-+————-+——+—–+———+——-+

PROCESSLIST

PROCESSLIST表提供有关正在运行的线程信息。

> desc PROCESSLIST;

+——-+———————+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+——-+———————+——+—–+———+——-+| ID | bigint(21) unsigned | NO | | 0 | || USER | varchar(32) | NO | | | || HOST | varchar(64) | NO | | | || DB | varchar(64) | YES | | NULL | || COMMAND | varchar(16) | NO | | | || TIME | int(7) | NO | | 0 | || STATE | varchar(64) | YES | | NULL | || INFO | longtext | YES | | NULL | |+——-+———————+——+—–+———+——-+

PROFILING

PROFILING表提供语句分析信息。 其内容对应于SHOW PROFILES和SHOW PROFILE声明所产生的信息。

warning:INFORMATION_SCHEMA.PROFILING 在以后的版本将被移除,请使用 Performance Schema 代替。

REFERENTIAL_CONSTRAINTS

REFERENTIAL_CONSTRAINTS表提供有关外键的信息。

ROUTINES

ROUTINES表提供有关routines(存储过程procedures和函数functions)的信息。 ROUTINES表不包括用户定义的函数(UDF)。

SCHEMATA

schema是一个数据库,所以SCHEMATA表提供有关数据库的信息。

SCHEMA_PRIVILEGES

SCHEMA_PRIVILEGES表提供有关schema(数据库)特权的信息。 这些信息来自mysql.db授权表。

STATISTICS

STATISTICS表提供有关表索引的信息。

TABLES

TABLES表提供有关数据库中表的信息。

TABLESPACES

TABLESPACES表提供有关active表空间的信息。

INFORMATION_SCHEMA.TABLESPACES表不提供有关InnoDB表空间的信息。对于InnoDB表空间元数据,请参阅INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES。 从MySQL 5.7.8开始,INFORMATION_SCHEMA.FILES表还提供了InnoDB表空间的元数据。

TABLE_CONSTRAINTS

TABLE_CONSTRAINTS表描述哪些表有约束。

CONSTRAINT_TYPE值可以是UNIQUE,PRIMARY KEY或FOREIGN KEY。

当Non_unique字段为0时,UNIQUE和PRIMARY KEY信息与从SHOW INDEX输出中的Key_name字段获得的信息大致相同。

CONSTRAINT_TYPE列可以包含以下值之一:UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK。 这是一个CHAR(不是ENUM)列。 直到我们支持CHECK,CHECK值才可用。

TABLE_PRIVILEGES

TABLE_PRIVILEGES表提供有关表特权的信息。 这些信息来自mysql.tables_priv授权表。

PRIVILEGE_TYPE可以包含一个(且只能一个)这些值:SELECT,INSERT,UPDATE,REFERENCES,ALTER,INDEX,DROP,CREATE VIEW。

TRIGGERS

TRIGGERS表提供关于触发器的信息。 要查看有关表触发器的信息,您必须具有该表的TRIGGER权限。

USER_PRIVILEGES

USER_PRIVILEGES表提供有关全局权限的信息。 这些信息来自mysql.user授权表。

VIEWS

VIEWS表提供关于数据库中视图的信息。 您必须具有SHOW VIEW权限才能访问此表

InnoDB Tables

另外还有 INNODB 相关的表:

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

相关信息可查询官方手册。

MySQL5.7中的mysql  performance和sys 监控参数

performance schema有如下功能:

①:元数据锁:

  对于了解会话之间元数据锁的依赖关系至关重要。从MySQL5.7.3开始,就可以通过metadata_locks表来了解元数据锁的相关信息;

   –哪些会话拥有哪些元数据锁

   –哪些会话正在等待元数据锁

   –哪些请求由于死锁被杀掉,或者锁等待超时而被放弃

②:进度跟踪:

  跟踪长时间操作的进度(比如alter table),从MySQL5.7.7开始,performance schema自动提供了语句进度信息。我们可以通过events_stages_current表来查看当前事件的进度信息;

③:事务:

  监控服务层和存储引擎层事务的全部方面。从MySQL5.7.3开始,新增了 events_transactions_current表,可以通过setup_consumers、setup_instruments表打开事务监控,通过该表查询到当前事务的状态。如果线上数据库遇到undo log大量增长、数据库性能急剧下降的情况,可以通过该表查询当前是否存在处于未提交状态的事务。如果发现的确有大量事务的state处于active,这时可以确定数据库有大量的事务未提交;

④:内存使用:

  提供内存使用信息统计,有利于了解和调整服务器的内存消耗。从MySQL5.7.2开始,performance schema新增内存有关的统计信息,分别从账户、访问主机、线程、用户及事件的角度统计了内存的使用过程;

⑤:存储程序:

  存储过程、存储方法、事件调度器和表触发器的检测器。在MySQL5.7中的setup_objects表中,新增了event、function、procedure、trigger的检测器。performance schema用于检测该表中匹配object_schema和object_name的对象;

  

2、sys schema介绍:

  在MySQL5.7中新增的sys schema。是由一系列对象(视图、存储过程、存储方法、表和触发器)组成的schema,它本身不采集和存储什么信息,而是将performance_schema 和 information_schema中的数据以更容易理解的方式总结出来归纳为“视图”。

—sys schema可用于典型的调优和诊断用例,这些对象包括如下三个:

①:将性能模式数据汇总到更容易理解的视图;

②:诸如性能模式配置和生成诊断报告等操作的存储过程

③:用于查询性能模式配置并提供格式化服务的存储函数

—sys schema在查询中的功能,可以查看数据库服务资源的使用情况?哪些主机对数据库服务器的访问量最大?实例上的内存使用情况?

3、sys schema里面的表的分类:

①:主机相关信息:

   以host_summary开头的视图,主要汇总了IO延迟的信息,从主机、文件事件类型、语句类型等角度展示文件IO的信息;

②:innodb相关信息:

   以innodb开头的视图,汇总了innodb buffer page信息和事务等待innodb锁信息;

③:IO使用情况:

   以IO开头的视图,总结了IO使用者的信息,包括等待IO的情况、IO使用量情况,从各个角度分组展示;

④:内存使用情况:

   以memory开头的视图,从主机、线程、用户、事件角度展示内存使用情况;

⑤:连接与会话信息:

   其中,processlist 和 session相关的视图,总结了会话相关的信息;

⑥:表相关信息:

   以schema_table开头的视图,从全表扫描、innodb缓冲池等方面展示了表统计信息;

⑦:索引信息:

   其中包含index的视图,统计了索引使用的情况,以及重复索引和未使用的索引情况;

⑧:语句相关信息:

   以statement开头的视图,统计的规范化后的语句使用情况,包括错误数、警告数、执行全表扫描的、使用临时表、执行排序等信息;

⑨:用户相关信息:

   以user开头的视图,统计了用户使用的文件IO,执行的语句统计信息等;

⑨:等待事件相关信息:

   以wait开头的视图,从主机和事件角度展示等待类事件的延迟情况;

4、sys  schema使用列子:

—查看表的访问量:(可以监控每张表访问量的情况,或者监控某个库的访问量的变化)

select table_schema,table_name,sum(io_read_requests+io_write_requests) from schema_table_statistics;

select table_schema,table_name,io_read_requests+io_write_requests as io_total from schema_table_statistics;

—冗余索引和未使用索引的检查:(schema_redundant_indexes和schema_unused_indexes查看索引的情况)

select * from sys.schema_redundant_indexesG

select * from sys.schema_unused_indexes;

(如果有冗余索引和长期未使用的索引,应该及时清理,)

—查看表自增ID使用情况:

select * from schema_auto_increment_columnsG 

(可以使用schema_auto_increment_columns视图,就能很简单的查到每个表的自增量使用情况,甚至可以精确到某个表的自增量情况)

—监控全表扫描的sql语句:

select * from sys.statements_with_full_table_scans where db='test2'G

(使用statements_with_full_table_scans视图可以查看哪些表查询使用了全表扫描,其中exec_count是执行的次数,等信息)

—查看实例消耗的磁盘I/O情况:()

select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;

(查看io_global_by_file_by_bytes视图可以检查磁盘I/O消耗过大的原因,定位问题)

根据去哪网DBA的总结,开启MySQL sys Schema的风险:因为它的数据来源是 infomation_schema 和 performance_schema.

其中performance_schema 表的数据收集服务,对MySQL服务器的性能消耗是巨大的。到底它对性能影响多大,这里参考去哪网的基准测试工具 (Sysbench)的压测结果,来给大家一个参考。

这个到底开不开启,还需要自己亲自压测。毕竟服务器,mysql分支版本,业务场景有很大不同。  去哪用的是5.7.14进行的压测,压测结果是是确实在TPS上下降了百分之10左右的结果,如果业务能受得了,那就开启。

告警:线上环境,业务繁忙,千万不要直接sql查询performance表的相关监控信息,严重会导致业务请求被堵塞。操作须谨慎。

来源参考:

http://blog.51cto.com/fengfeng688/1952334

http://www.cnblogs.com/waynechou/p/information_schema.html#_label23

打赏

未经允许不得转载:同乐学堂 » MySQL运维内参读书笔记-Day2

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!