Shulou(Shulou.com)06/01 Report--

New mysql sys schema statement in mysql 5.7.The content of this article comes from "Internal reference of MySQL Operation and maintenance". The notes read by the author are for study only.

The following are the new features of MySQL 5.7. improvements to Performance Schema. 1. Metabase lock select * from performance_schema.metadata_locks

From this table, you can see: a), which sessions have metadata locks; b), which sessions are waiting for metadata locks; c), which requests are discarded because the deadlock was killed, or the lock wait timed out.

2. Process tracking select * from performance_schema.events_stages_current

This table allows you to track the progress of long-term operations (such as ALTER TABLE): note: stages means phase. 3. View uncommitted transactions starting from mysql 5.7. add the events_transactions_current table to check the status of transactions on the frontline. If the online database encounters a large increase in undo log and the database performance declines sharply, you can use this table to check whether there are any uncommitted transactions. If you do find that the state of a large number of transactions is in the active state, you can determine that the transaction of the database has not been committed.

Second, the introduction of SYS library

Performation schema is not easy to use, and mysql 5.7uses the sys library to solve this problem. The mysql sys library itself does not collect and store any information, but instead summarizes the archived views of performance schema and infomation schema data in a more understandable way. In the sys library, views without the x$ prefix provide more friendly and readable data; views with the x$ prefix provide raw data that needs to be processed to look good.

1) Host-related information: views starting with host_summary show file IO latency information from the point of view of host, file time type and statement type; 2), innodb buffer pool and lock related information: views starting with innodb summarize innodb buffer page information and transaction waiting for locks; 3) io usage: views starting with io summarize information about io users, including waiting for io and io usage 4) memory usage: the view starting with memory shows memory usage from the point of view of hosts, threads, users, events; 5), connection and session information: processlist and session summarize connection and session information; 6), table related information: views starting with schema_table show table statistics from the aspects of full table scan, innodb buffer pool, etc. 7) Index information: including views of index, statistics of index usage, repeated indexes and unused indexes; 8) statement related information: views starting with statement, statistics of normalized statement usage, including errors, warnings, full table scans, temporary tables, sorting, etc. 9), user-related information: the view starting with user, statistics of the user's use of the file IO, statements executed, etc.; 10), waiting event related information: the view starting with wait shows the delay of waiting events from the perspective of hosts and events; 11), lock information: innodb_lock_waits and schema_table_lock_waits show lock information

Focus views and application scenarios 1. View table traffic scenarios: view the number of reads and writes to each table

+-+ | table_schema | table_name | io_to_request | +-+-- -+ | oa_2016 | form_trigger_record | | oa_2016 | form_log | | oa_2016 | ctp_content_all | | oa_2016 | org_relationship |

2. Redundant indexes and unused indexes

Through the schema_index_statistics and schema_redundant_indexes views in the sys library, you can see which indexes are not used or underutilized.

3. Table self-adding id monitoring scenario: know which table has a self-increasing primary key And monitor whether the self-added primary key is about to exceed the threshold (root@localhost) [sys] > * * table_schema: oa_2016 table_name: ctp_content_all column_name: id data_type: column_type: is_signed: is_unsigned: Max_value: auto_increment: auto_increment_ratio:

4. View the disk IO consumed by the instance

(root@localhost) [sys] > +-- +-+ | file | avg_io | +- -- +-+ | @ @ datadir/oa_2016/portal_link_space | @ @ datadir/oa_2016/pro_eipusercustomsort | @ @ datadir/oa_2016/ctp_dr_url_map | @ @ datadir/oa_2016/office_auto_applyinfo | @ @ datadir/oa_2016/edoc_exchange_turn_rec | @ @ datadir/oa_2016/portal_portlet_property | @ @ datadir/oa_2016 / showpost_info | @ @ datadir/oa_2016/cip_agent | @ @ datadir/oa_2016/thirdparty_portal_config | @ @ datadir/oa_2016/portal_link_option+---+-+DBA can be used to get a general idea of where disk IO is consumed. Which files consume the most. Based on this information, DBA can optimize for a table or a library.

5 、 Monitor the sql statement of full table scan to view the sql statement of full table scan through sys.statements_with_full_table_scans: root@localhost) [sys] > * * query: db: oa_2016 exec_count: total_latency: no_index_used_count: no_good_index_used_count: No_index_used_pct: rows_sent: rows_examined: rows_sent_avg: rows_examined_avg: first_seen: last_seen: digest: you can see from above The sql statement was executed five times in total, none of which used an index, consuming a total of 997.89 us.

6. The view of the operational risk sys library comes from performace_schema and information_schema. We know that after performace_schema is enabled, Database performance will degrade by 10%: (root@localhost) [sys] > show variables +-- +-+ | Variable_name | Value | +- -+-+ | performance_schema | ON |

Therefore, we should be careful when querying sys or performance_schema.

7 refer to the book "mysql Operation and maintenance Internal reference"

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.