Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the sys schema view in MySQL5.7, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Summary of sys schema views

The sys schema view contains many views of Performance Schema tables that are summarized in various ways, most of which appear in pairs so that one member of each set of views has the same name as the other, with an X$ prefix. For example, the host_summary_by_file_io view summarizes the files grouped by host. Views without the X$ prefix provide more friendly and easy-to-read data, while views with the X$ prefix provide raw data, more for other tools that need to process the data.

Views can be divided into the following categories according to the display information.

Host-related information: the view that starts with host_summary mainly summarizes the information of IO latency, showing the information of file IO from the perspective of host, file event type, statement type and so on.

InnoDB related information: the view starting with innodb summarizes innodb buffer page information and transaction waiting InnoDB lock information.

IO usage: the view that starts with io summarizes the information of io users, including waiting for io O, IIO O usage, and grouping from various angles.

Memory usage: a view that starts with memory to show memory usage from the perspective of hosts, threads, users, and events.

Connection and session information: the views related to processlist and session summarize the session-related information.

Table related information: views that start with schema_table show table statistics in terms of full table scans, InnoDB buffer pools, and so on.

Index information: it contains views of index, counting index usage, as well as duplicate and unused indexes.

Statement related information: the view starting with statement, statistics of the normalized statement usage, including the number of errors, warnings, full table scan, the use of temporary tables, sorting and other information.

User-related information: the view that begins with user counts the files used by users, IO, statements executed, and so on.

Wait event related information: the view starting with wait shows the latency of waiting events from the host and event perspective.

Application scenario of sys schema focus View

View the number of visits to the table

Click (here) to collapse or open

Mysql > select table_schema,table_name,io_read_requests+io_write_requests as total from schema_table_statistics

+-+

| | table_schema | table_name | total | |

+-+

| | sys | sys_config | 19 | |

| | dedecms | dede_member_group | 13 | |

| | dedecms | dede_scores | 12 | |

| | dedecms | dede_ratings | 12 | |

| | dedecms | dede_pwd_tmp | 12 | |

| | dedecms | dede_purview | 12 | |

| | dedecms | dede_plus | 12 | |

We can monitor the changes in the number of visits to each table, or the changes in the number of visits to a library, and so on. If a library or a table changes, DBA can know the access of each table in a timely manner.

Redundant indexes and unused index checks

Click (here) to collapse or open

Mysql > select * from sys.schema_redundant_indexes\ G

* * 1. Row *

Table_schema: dedecms

Table_name: dede_member_group

Redundant_index_name: id

Redundant_index_columns: id

Redundant_index_non_unique: 1

Dominant_index_name: PRIMARY

Dominant_index_columns: id

Dominant_index_non_unique: 0

Subpart_exists: 0

Sql_drop_index: ALTER TABLE `dedecms`.`dede _ member_ group`DROP INDEX `id`

For redundant indexes, DBA can clean up in time to reduce disk pressure and improve database performance.

Table self-adding ID monitoring

Click (here) to collapse or open

Mysql > select * from sys.schema_auto_increment_columns\ G

* * 1. Row *

Table_schema: dedecms

Table_name: dede_sys_enum

Column_name: id

Data_type: smallint

Column_type: smallint (5) unsigned

Is_signed: 0

Is_unsigned: 1

Max_value: 65535

Auto_increment: 20040

Auto_increment_ratio: 0.3058

* 2. Row * *

Table_schema: dedecms

Table_name: dede_member_tj

Column_name: mid

Data_type: mediumint

Column_type: mediumint (8)

Is_signed: 1

Is_unsigned: 0

Max_value: 8388607

Auto_increment: 247352

Auto_increment_ratio: 0.0295

It shows the self-increment column name, data type, current usage, maximum value and utilization rate of the table, which greatly facilitates DBA to quickly understand the usage of database self-increment.

Monitor SQL statements for full table scans

Click (here) to collapse or open

Mysql > select * from sys.statements_with_full_table_scans\ G

* * 1. Row *

Query: SELECT * FROM `sys_ config` LIMIT?,...

Db: sys

Exec_count: 1

Total_latency: 74.62 ms

No_index_used_count: 1

No_good_index_used_count: 0

No_index_used_pct: 100

Rows_sent: 6

Rows_examined: 6

Rows_sent_avg: 6

Rows_examined_avg: 6

First_seen: 2018-03-21 08:52:47

Last_seen: 2018-03-21 08:52:47

Digest: befd5e5f4382f78675bbc86d495dfac2

* 2. Row * *

Query: SELECT `performance_ schema`. ... Name`. `SUM_TIMER_ WAIT` DESC

Db: sys

Exec_count: 2

Total_latency: 644.19 ms

No_index_used_count: 2

No_good_index_used_count: 0

No_index_used_pct: 100

Rows_sent: 155

Rows_examined: 1481

Rows_sent_avg: 78

Rows_examined_avg: 741

First_seen: 2018-03-22 03:27:54

Last_seen: 2018-03-22 03:44:09

Digest: 6f58edd9cee71845f592cf5347f8ecd7

* 3. Row * *

Query: SELECT * FROM `INNODB_SYS_TABLESPACES

You can see the number of times these statements were executed from the query results, and the number of times the index was not used.

Click (here) to collapse or open

Mysql > select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10

+-+ +

| | file | avg_io |

+-+ +

| | @ @ datadir/mysql/db.MYD | 1464 | |

| | @ @ datadir/sys/io_global_by_wait_by_latency.frm | 1015 | |

| | @ @ datadir/sys/user_summary.frm | 958 |

| | @ @ datadir/sys/x@0024schema_table_statistics_with_buffer.frm | 955 |

| | @ @ datadir/mysql/tables_priv.MYD | 947 |

| | @ @ datadir/sys/x@0024io_global_by_wait_by_bytes.frm | 943 |

| | @ @ datadir/sys/host_summary_by_statement_type.frm | 911 |

| | @ @ datadir/sys/user_summary_by_statement_type.frm | 904 |

| | @ @ datadir/sys/x@0024user_summary.frm | 898 |

| | @ @ datadir/sys/io_by_thread_by_latency.frm | 897 |

+-+ +

DBA can use this query to get an overview of where disk IO is consumed and which files are consumed the most.

Operational risk

Although these views facilitate the monitoring of the database by DBA, it is not recommended that a large number of online deployments complete some monitoring by querying tables or views in sys or performance_schema, because when querying this information, MySQL will consume a lot of resources to collect relevant information, which may seriously cause business requests to be blocked and cause failures.

The above is all the content of the article "how to use sys schema View in MySQL5.7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.