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) like the following:? host listen for connected hosts? statements The total number of statements executed by the current host? statement_latency statement wait time (delay time)? Statement_avg_latency The average delay time for executing statements? table_scans Table Scan count? file_iosio total time? 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 this host? current_memory current Account allocated memory ? total_memory_allocated the total amount of memory allocated by this host 2.2 the host_summary_by_file_io_type?host host? Event_ Nameio event name? Total what happened to the host? Total_latency the overall latency of the IO event for this host? Max_latency the maximum delay time in the host IO event 2.3 the host_ Summary_by_file_io?host host? Total number of Iosio events? Io_latencyio Total delay Time 2.4 The host_summary_by_stages? Host hosts? event_namestage event name? totalstage event The total number of occurrences? total_latencystage What is the total delay time of the event? 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 what is the total delay time for this host? max_latency host maximum latency? lock_latency the lock delay time to wait for a 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 Total row count that the host is affected by the statement? full_ Scans total number of statements scanned 2.6 the host_summary_by_statement_type? host host? Statement the last statement event name? totalsql Total number of statements? The total number of total_latencysql statement delays? max_latency the maximum number of SQL statement delays? lock_ Latency the total number of lock latencies? The total number of rows returned by the rows_sent statement? The number of lines read by the SQL statement for the rows_examined through the storage engine? Total rows affected by the 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 bytes of data allocated to the current database? pages the total number of pages allocated 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 for the table? Pages assigned to the table? pages_hashed the number of hash pages assigned to the table? pages_old the old pages assigned to the table? The row cache count for rows_cached table 2.9 the innodb_lock_waits This table in fact from the view of the statement is INFORMATION_SCHEMA this database innodb_locks, Innodb_trx the integration of the two tables, can more clearly show the current instance of the lock situation wait_started lock wait time? How long has the wait_age lock been waiting for? wait_age_secs displays the time in seconds that the lock has been waiting (adding this column in 5.7.9)? locked_table locked table? locked_index locked index? locked_type lock type? waiting_trx_id waiting Transactions Id? waiting_trx _started wait for the transaction to start? How long has waiting_trx_age been waiting for a transaction? waiting_trx_rows_locked the number of rows that are waiting for the transaction to be locked? waiting_trx _rows_modified waiting for the number of row redefinition? waiting_pid the thread that is waiting for the transaction id? waiting_query the query that is waiting for the lock? waiting_lock_ ID waiting for lock id? waiting_lock_mode mode? blocking_trx_id blocking the transaction that waits for the lock id? blocking_pid the thread that is being locked id? Blocking_query the query that is being locked? BLOCKING_LOCK_ID is blocking the lock ID that is waiting for the lock. Blocking_lock_mode blocking lock mode? blocking_trx_started the time that the transaction started? blocking_trx_age the time the transaction that was blocked has been executed? blocking_trx_rows_locked the number of rows blocked by a transaction? blocking_trx_rows_modified the number of rows that are blocked by the transaction redefinition? sql_kill_blocking_ Querykill statement kills a running blocking transaction that is added in mysql5.7.9? sql_kill_blocking_connectionkill statement kills a running blocking transaction in a session is added in mysql5.7.9 2.10 the io_by_thread_by_latency This view is the main information that shows the IO wait time through the consumption of IO? user for the current thread, This value is the account that the thread is assigned to, which is the name of the thread for the background thread? The total number of totalio events? The total delay of the total_latencyio event? min_latency Single minimum IO event latency? avg_latency average io latency? max_latency Max io latency? thread_id thread id? processlist_id is the ID for the current thread. For backstage is null reprint from: http://www.cnblogs.com/jiangwenju/p/5157333.html
What is the SYS system database in MySQL?
