mysql5.7 adds the SYS system database, which provides a quick overview of the system's meta-data. This library does make it easy for DBAs to discover a lot of information about a database, and it can help to solve performance bottlenecks. This library is the default in mysql5.7, which can be imported manually above the mysql5.6 version, and what does the database package find on GitHub by itself? This library is a view of the form of Information_schema and Performance_schema together, query a more understandable data stored procedures can perform some performance aspects of configuration, You can also get some performance diagnostic report content store function can query some performance information analyze each view and table before you explain: about band without x$, remove x$ with the same name their data is the same, the difference is that the units without x$ more in line with direct reading has been converted, and with x$ The original unit (most of which should be MySQL default) is used for some tools to exist, and the following is a detailed analysis of the SYS library in conjunction with the official MySQL manual
1. Table 1.1 Sys_config Table This is the only table that exists on this system library. First look at the table structure
| CREATE TABLE ' sys_config ' (' variable ' varchar) not NULL, ' value ' varchar (+) DEFAULT NULL, ' set_time ' timestamp N OT null default Current_timestamp on UPDATE current_timestamp, ' set_by ' varchar (+) default NULL, PRIMARY KEY (' Variabl E ')) Engine=innodb DEFAULT Charset=utf8 |
Variable configuration option name
The value of the configuration option values set_time the line configuration modification time set_by The line configuration information modifier, if from the installation has not been modified, then this data should be null table in the default data
| Variable |
Value |
Set_time |
Set_by |
| Diagnostics.allow_i_s_tables |
OFF |
2015-11-20 16:04:38 |
[email protected] |
| Diagnostics.include_raw |
OFF |
2015-11-20 16:04:38 |
[email protected] |
| Statement_performance_analyzer.limit |
100 |
2015-11-20 16:04:38 |
[email protected] |
| Statement_performance_analyzer.view |
|
2015-11-20 16:04:38 |
[email protected] |
| Statement_truncate_len |
64 |
2016-01-22 17:00:16 |
[email protected] |
The session variable for the value above is the Variable field in the @sys.+ table, for example: @sys .statement_truncate_len can set @sys. Statement_ Truncate_len = 32 temporarily changes the value, this value is used throughout the session, and if you want to restore the default value of using the table, you only need to set the session value to null;set @sys. Statement_truncate_len = null; diagnostics.allow_i_s_tables diagnostics.include_raw these two values default to OFF, the former if Open means allow diagnostics () The stored procedure performs a scan of the Information_schema.tables table, and if there are many tables, it can be very expensive, and the latter will output raw data from the metrics view. Diagnostics () The specific contents of the next face diagnostics () explanation. statement_performance_analyzer.limit View the maximum number of rows returned when there is no limit restriction Statement_performance_analyzer.view (slightly) the above parameter is mysql5.7.9 join statement_truncate_len the maximum length of the return value via the format_statement () function This table is not the default option and there is a @sys.debug parameter that can be manually added to insert into sys_config (variable, value) values (' Debug ' , ' on '); update sys_config set value = ' OFF ' WHERE variable = ' Debug '; set @sys .debug = null; For details, please refer to the official documentation, no introduction here about this table there are two triggers 1.1.1 sys_config_insert_set_ User TriggerIf you add a new line through the INSERT statement, the trigger will set the Set_by column as the current operator 1.1.2 sys_config_update_set_user Trigger If you add a new row through the UPDATE statement, the trigger will set the Set_by column to the current operator 2. View the following sections only describe the content of the view that does not contain x$ 2.1 host_ Summary (host profile) resembles the following: host listens for connected hosts statements The total number of statements executed by the current host statement_latency statement wait Time (latency) Statement_avg_latency execution Statement Average delay time table_scans table scan count file_iosio time total file_io_latency file IO delay · Current_connections Current number of connections total_connections total number of links unique_users the number of unique users for the host current_memory the memory allocated by the current account total_memory_allocated The total amount of memory allocated by this host 2.2 the host_summary_by_file_io_type host host ·event_ The Nameio event name Total The event that occurs on this host total_latency the host IO event Total latency max_latency The maximum latency time in the host iOS event 2.3 the host_ Summary_by_file_io Host host Iosio event total io_latencyio Total delay Time 2.4 The host_summary_by_stages· Host host event_namestage event name totalstage event The total number of occurrences total_latencystage Event total delay Time avg_latencYstage event average delay time 2.5 the host_summary_by_statement_latency host host · Total number of statements for this host total_latency the total delay time of this host max_latency the maximum delay time of the host lock_latency the lock delay time to wait for the lock rows _sent the total number of rows returned by the host through the statement rows_examined the number of lines that are returned by the statement on the storage engine rows_affected The number of total rows affected by the host through the statement full_ Scans total number of statements scanned 2.6 the host_summary_by_statement_type host host Statement last statement event name totalsql Total number of statements total_latencysql statement Total delay max_latency maximum number of SQL statement delays lock_ Total number of latency lock latencies The total number of rows returned by the rows_sent statement rows_examined The row count that is affected by the SQL statement that stores the engine's read rows rows_affected statement Full_scans full table Scan statement events Total 2.7 the innodb_buffer_stats_by_schema This table is the InnoDB cache of the InnoDB engine through the database object_schema database name allocated The total number of bytes allocated to the current database data The number of data bytes allocated to the current database · pages the total number of pages assigned to the current database pages_hashed the number of hash pages assigned to the current database pages_old the old number of pages assigned to the current database rows_cached Number of rows in the current database cache 2.8 the innodb_buffer_stats_by_table thisTable is a INNODB cache of InnoDB engines per table
Object_schema database name object_name table name allocated the total number of bytes allocated to the table data The number of data bytes allocated to the table pages the pages assigned to the table pages_hashed the hash pages assigned to the table · Pages_old the number of row caches allocated to the table for the old page rows_cached table
MySQL 5.7 New Database sys parsing (i)
