Learn to use the Sys library of MySQL 5.7 (Part 1)

MySQL 5.7 introduces the sys library to help DBA analyze some problems. The sys library contains some stored procedures, views, functions, etc.

View or table: used for summary display of results and configuration persistence

Stored procedure: used for control and collection of Performance schema.

Function: Configuration and data formatting of Performance schema.

Today I will mainly explain the content related to views. Other content depends on your needs.

Data sources in the Sys library

All data sources in the Sys library come from: performance_schema. The goal is to reduce the complexity of Performance_schema so that DBA can better read the content in this library. Let the DBA understand the running status of the DB faster.

Check the version of the sys library

select * from sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.1           | 5.7.14-log      |
+-------------+---------------+

There are two tables under the Sys library

• Start with letters: Suitable For human reading, the display is a formatted number starting with

• x$: Suitable for tools to collect data, original data

root@localhost [sys]>select host,statements, statement_latency,statement_avg_latency from host_summary;  
+-----------+------------+-------------------+-----------------------+
| host      | statements | statement_latency | statement_avg_latency |
+-----------+------------+-------------------+-----------------------+
| localhost |         92 | 72.24 ms          | 785.21 us             |
+-----------+------------+-------------------+-----------------------+
1 row in set (0.01 sec)
root@localhost [sys]>select host,statements, statement_latency,statement_avg_latency from x$host_summary;
+-----------+------------+-------------------+-----------------------+
| host      | statements | statement_latency | statement_avg_latency |
+-----------+------------+-------------------+-----------------------+
| localhost |         91 |       63268768000 |        695261186.8132 |
+-----------+------------+-------------------+-----------------------+
1 row in set (0.01 sec)

Next, let’s take a look at the viewing direction that sys can support:

select substring_index(table_name,"_",1) ,count(*) from  information_schema.tables where TABLE_SCHEMA='sys' 
and table_name not like 'x$%' group by substring_index(table_name,"_",1);
+-----------------------------------+----------+
| substring_index(table_name,"_",1) | count(*) |
+-----------------------------------+----------+
| host                              |        6 |
| innodb                            |        3 |
| io                                |        5 |
| latest                            |        1 |
| memory                            |        5 |
| metrics                           |        1 |
| processlist                       |        1 |
| ps                                |        1 |
| schema                            |        9 |
| session                           |        2 |
| statement                         |        1 |
| statements                        |        5 |
| sys                               |        1 |
| user                              |        6 |
| version                           |        1 |
| wait                              |        2 |
| waits                             |        3 |
+-----------------------------------+----------+
17 rows in set (0.00 sec)

A brief introduction to each type of table

sys_ begins with the configuration table in the library:

sys_config Configuration for sys schema library

##View:

host: IP group related statistical information

innodb: innodb buffer related Information

io: IO-related information displayed in different dimensions within the data

memory: Display memory usage by IP, connection, user, allocated type grouping and total occupation

metrics: DB's internal statistical values ​​

processlist: Thread-related information (including internal threads and user connections)

ps_: Some variables without tool statistics (not seen to exist Value)

schema: Information related to the table structure, such as: auto-increment, index, type of each field in the table, waiting locks, etc.

session: Information related to user connection

statement: Statistical information based on statements (re-store)

statements_: Error statements, full table scan, long running time, equal sorting (emphasis)

user_ : Similar to the ones starting with host_, but with user group statistics

wait : Waiting for events, more professional and difficult to understand.

waits: Some delay events calculated based on IP and user groups have certain reference value.

The above is the content of learning how to use the Sys library of MySQL 5.7 (Part 1). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!