This article describes in detail the new SYS schema introduced by MySQL 5.7. Firstly, this paper briefly introduces the function and location of SYS schema, and secondly, introduces the view, function and stored procedure in SYS schema, and then, through two examples, demonstrates the use of SYS schema, so that we can understand the real benefits of SYS schema. Finally, it discusses the contents of the SYS schema that can be added. 1. SYS schema is a system library introduced in MySQL 5.7.7 that contains a series of views, functions, and stored procedures that focus on the ease of use of MySQL, for example, we can quickly know through the SYS schema, Which statements use temporary tables, which user requests the most IO, which thread consumes the most memory, which indexes are useless indexes, and so on. After introducing the SYS schema, MySQL's ease of use will be greatly improved, MySQL user analysis problems and positioning problems, will be more dependent on the SYS schema, reduce external tool usage. As I said earlier, the SYS schema contains a large number of views (only Sys_config is the InnoDB table), so where does the information for these views come from? The information in the view is derived from the statistics in the performance schema and the information schema. There is a good analogy in MySQL Server blog: For Linux users I like to compare Performance_schema To/proc, and SYS to Vmstat. In other words, Performa The NCE schema and the information schema provide the source of information, but the information is not well organized into useful information, so it does not work well. The SYS schema uses the information in the performance schema and the information schema to give the answer to the actual problem through the view. This is the role and purpose of the SYS schema, and why the SYS schema is worth taking some time to learn. 2. The views, functions, and stored procedures in the SYS schema can be quickly viewed through the following statements to see the views, functions, and stored procedures contained in the SYS schema show full tables from sys show function status where db = ' sy S '; Show procedure status where db = ' syS ' will then give all the views, functions, and stored procedures in turn, and perform a simple analysis in order to achieve the desired results. 2.1 Views The view in the SYS schema (and a table) is as follows, and it is easy to guess by name what is being done. Mysql> Select table_name, TABLE_TYPE, engine from information_schema.tables where table_schema = ' sys ' ORDER by table_ name;+-----------------------------------------------+------------+--------+| table_name | Table_type | Engine |+-----------------------------------------------+------------+--------+| Host_summary | VIEW | NULL | | Host_summary_by_file_io | VIEW | NULL | | Host_summary_by_file_io_type | VIEW | NULL | | Host_summary_by_stages | VIEW | NULL | | host_summary_by_statement_latency | VIEW | NULL | | Host_summary_by_statement_type | VIEW | NULL | | Innodb_buffer_stats_by_schema | VIEW | NULL | | innodb_buffer_stats_by_table | VIEW | NULL | | Innodb_lock_waits | VIEW | NULL | | io_by_thread_by_latency | VIEW | NULL | | Io_global_by_file_by_bytes | VIEW | NULL | | io_global_by_file_by_latency | VIEW | NULL | | Io_global_by_wait_by_bytes | VIEW | NULL | | io_global_by_wait_by_latency | VIEW | NULL | | Latest_file_io | VIEW | NULL | | Memory_by_host_by_current_bytes | VIEW | NULL | | Memory_by_thread_by_current_bytes | VIEW | NULL | | Memory_by_user_by_current_bytes | VIEW | NULL | | Memory_global_by_current_bytes | VIEW | NULL | | Memory_global_total | VIEW | NULL | | Metrics | VIEW | NULL | | Processlist | VIEW | NULL | | ps_check_lost_instrumentation | VIEW | NULL | | Schema_auto_increment_columns | VIEW | NULL | | Schema_index_statistics | VIEW | NULL | | Schema_object_overview | VIEW | NULL | | schema_redundant_indexes | VIEW | NULL | | Schema_tables_with_full_table_scans | VIEW | NULL | | Schema_table_lock_waits | VIEW | NULL | | Schema_table_statistics | VIEW | NULL | | Schema_table_statistics_with_buffer | VIEW | NULL | | schema_unused_indexes | VIEW | NULL | | Session | VIEW | NULL | | Session_ssl_status | VIEW | NULL | | statements_with_errors_or_warnings | VIEW | NULL | | Statements_with_full_table_scans | VIEW | NULL | | Statements_with_runtimes_in_95th_percentile | VIEW | NULL | | Statements_with_sorting | VIEW | NULL | | Statements_with_temp_tables | VIEW | NULL | | statement_analysis | VIEW | NULL | | Sys_config | BASE TABLE | InnoDB | | User_summary | VIEW | NULL | | User_summary_by_file_io | VIEW | NULL | | User_summary_by_file_io_type | VIEW | NULL | | User_summary_by_stages | VIEW | NULL | | user_summary_by_statement_latency | VIEW | NULL | | User_summary_by_statement_type | VIEW | NULL | | Version | VIEW | NULL | | waits_by_host_by_latency | VIEW | NULL | | waits_by_user_by_latency | VIEW | NULL | | waits_global_by_latency | VIEW | NULL | | wait_classes_global_by_avg_latency | VIEW | NULL | | wait_classes_global_by_latency | VIEW | NULL | There are no full views listed here, there are two forms for each view:mysql> select table_name from information_schema.tables where table_schema = ' sys ' and table_name like '%memory% '; +-------------------------------------+| TABLE_NAME |+-------------------------------------+| Memory_by_host_by_current_bytes | | Memory_by_thread_by_current_bytes | | Memory_by_user_by_current_bytes|| Memory_global_by_current_bytes | | Memory_global_total | | X$memory_by_host_by_current_bytes | | X$memory_by_thread_by_current_bytes | | X$memory_by_user_by_current_bytes | | X$memory_global_by_current_bytes | | X$memory_global_total |+-------------------------------------+10 rows in Set (0.00 sec) The former is a form of human reading that formats time units and byte units, Another form refers to the name of the view that begins with "x$", and is intended for easy tool processing. As shown below:mysql> select * from host_summary_by_file_io;+-------------+------+------------+| Host | iOS | Io_latency |+-------------+------+------------+| Background | 2143 | 266.48 ms | | 172.17.42.1 | 1748 | 116.52 ms |+-------------+------+------------+2 rows in Set (0.00 sec) mysql> SELECT * from x$host_summary_by_file_io;+ -------------+------+--------------+| Host | iOS | Io_latency |+-------------+------+--------------+| Background | 2148 | 266558291670 | | 172.17.42.1 | 1748 | 116518395300 |+-------------+------+--------------+2 rows in Set (0.00 sec) Let's look at the definition of Host_summary_by_file_io, :mysql> Show CREATE TABLE Host_summary_by_fi as shown belowle_io\g*************************** 1. Row *************************** view:host_summary_by_file_io Create view:create algorithm=temptable DEFINER= ' Mysql.sys ' @ ' localhost ' SQL SECURITY INVOKER VIEW ' host_summary_by_file_io ' as Select if (IsNull (' Performance_schema '. ') Events_waits_summary_by_host_by_event_name '. ' Host '), ' background ', ' performance_schema '. ' Events_waits_summary_by _host_by_event_name '. ' Host ') as ' host ', sum (' Performance_schema '. ' Events_waits_summary_by_host_by_event_name '. ' Count_star ') as ' iOS ', ' sys '. ' Format_time ' (sum (' Performance_schema '. ' Events_waits_summary_by_host_by_event_name '). ' sum_timer_wait ') as ' io_latency ' from ' performance_schema '. ' Events_waits_summary_by_host_by_event_name ' where (' Performance_schema '. ' Events_waits_summary_by_host_by_event_name '. ' Event_Name ' like ' wait/io/file/% ') group by if ( IsNull (' Performance_schema '. ' Events_waits_summary_by_host_by_event_name '. ' Host '), ' background ', ' Performance_ Schema '. ' Events_waits_summary_by_host_by_event_name '. ' Host ') Order BY sum (' performance_schema '. ' Events_waits_summary_by_host_by_event_name '. ' sum_timer_wait ') desccharacter_set_ Client:utf8collation_connection:utf8_general_ci1 row in Set (0.00 sec) can be seen, the definition is very complex, if not the SYS schema provides such a useful view, We can hardly get useful information from so much information ourselves. 2.2 Functions If you look closely at the SQL statement of the definition view above, you may notice that it uses a function called Sys.format_time, which is also published with the SYS schema to format the time unit. The Sys SCHEMA also contains some other useful functions, as shown below:mysql> Select Routine_schema, Routine_name, routine_type from ROUTINES where Routine_ Type= ' FUNCTION '; +----------------+----------------------------------+--------------+| Routine_schema | Routine_name | Routine_type |+----------------+----------------------------------+--------------+| SYS | Extract_schema_from_file_name | FUNCTION | | SYS | Extract_table_from_file_name | FUNCTION | | SYS | Format_bytes | FUNCTION | | SYS | Format_path | FUNCTION | | SYS | format_statement | FUNCTION | | SYS | Format_time | FUNCTION | | SYS | List_add | FUNCTION | | SYS | List_drop | FUNCTION | | SYS | ps_is_account_enabled | FUNCTION | | SYS | Ps_is_consumer_enabled | FUNCTION | | SYS | ps_is_instrument_default_enabled | FUNCTION | | SYS | ps_is_instrument_default_timed | FUNCTION | | SYS | ps_is_thread_instrumented | FUNCTION | | SYS | Ps_thread_account | FUNCTION | | SYS | ps_thread_id | FUNCTION | | SYS | Ps_thread_stack | FUNCTION | | SYS | Ps_thread_trx_info | FUNCTION | | SYS | Sys_get_config | FUNCTION | | SYS | Version_major | FUNCTION | | SYS | Version_minor | FUNCTION | | SYS | Version_patch | FUNCTION |+----------------+----------------------------------+--------------+21 rows in Set (0.00 sec) 2.3 The stored procedure below is the stored procedure that the SYS schema contains. As you can see, most of the stored procedures are PS, PS is shorthand for performance schema, these stored procedures are used to control the performance schema statistics behavior. For example, Ps_truncate_all_tables's role is to truncate all summary related tables in the performance schema. As shown below:mysql> Select Routine_schema, Routine_name, routine_type from ROUTINES where routine_type= ' PROCEDURE '; +----------------+-------------------------------------+--------------+| Routine_schema | Routine_name | Routine_type |+----------------+-------------------------------------+--------------+| SYS | create_synonym_db | PROCEDURE | | SYS | Diagnostics | PROCEDURE | | SYS | execute_prepared_stmt | PROCEDURE | | SYS | Ps_setup_disable_background_threads | PROCEDURE | | SYS | Ps_setup_disable_consumer | PROCEDURE | | SYS | ps_setup_disable_instrument | PROCEDURE | | SYS | Ps_setup_disable_thread | PROCEDURE | | SYS | Ps_setup_enable_background_threads | PROCEDURE | | SYS | Ps_setup_enable_consumer | PROCEDURE | | SYS | ps_setup_enable_instrument | PROCEDURE | | SYS | Ps_setup_enable_thread | PROCEDURE | | SYS | ps_setup_reload_saved | PROCEDURE | | SYS | Ps_setup_reset_to_default | PROCEDURE | | SYS | Ps_setup_save | PROCEDURE | | SYS | ps_setup_show_disabled | PROCEDURE | | SYS | Ps_setup_show_disabled_consumers | PROCEDURE | | SYS | ps_setup_show_disabled_instruments | PROCEDURE | | SYS | ps_setup_show_enabled | PROCEDURE | | SYS | Ps_setup_show_enabled_consumers | PROCEDURE | | SYS | ps_setup_show_enabled_instruments | PROCEDURE | | SYS | ps_statement_avg_lAtency_histogram | PROCEDURE | | SYS | Ps_trace_statement_digest | PROCEDURE | | SYS | Ps_trace_thread | PROCEDURE | | SYS | Ps_truncate_all_tables | PROCEDURE | | SYS | Statement_performance_analyzer | PROCEDURE | | SYS | table_exists | PROCEDURE |+----------------+-------------------------------------+--------------+26 rows in Set (0.00 sec) 3. The SYS schema example is a simple demonstration of the use of the SYS schema, which allows you to understand the real benefits of the SYS schema, and then discusses what the SYS schema can add. 3.1 SYS schema usage Let's take a look at several index-related examples: How to view redundant indexes in a database, before MySQL 5.7, need to use Percona Pt-duplicate-key-checker, now directly query Sys.schema_ Redundant_indexes is available: SELECT * from Sys.schema_redundant_indexes; How to get unused indexes before MySQL 5.7, we can also view unused indexes with the information schema: SELECT object_schema, object_name, index_name from Performance_schema.table_io_waits_summary_by_index_usage WHERE index_name is isn't NULL and Count_star = 0 ORDER by object_s Chema, object_name; With the SYS schema, direct query schema_unused_indexes can: SELECT * from Schema_unused_indexes; How to view SQL statements using full-table scans (MySQL 5.7 I don't know how to read the ⊙o⊙) ... ) with the SYS schemaLater, direct query Statements_with_full_table_scans can: SELECT * from statements_with_full_table_scans3.2 SYS schema can introduce the view I found that There are some very useful information that can be obtained through the information schema and the performance schema, but not in the SYS schema. In view of this, the individual feels that the view in the future SYS schema will continue to increase. Example: View table without primary key SELECT DISTINCT T.table_schema, t.table_name from Information_schema.tables as T left JOIN Information_schema . columns as C on t.table_schema = C.table_schema and T.table_name = c.table_name and C.column_key = "PRI" WHERE t.table_sc Hema not in (' Information_schema ', ' MySQL ', ' Performance_schema ') and C.table_name are NULL and t.table_type! = ' VIEW '; See who created the temp table SELECT user, host, event_name, Count_star as CNT, sum_created_tmp_disk_tables as Tmp_disk_tables, sum_created_ Tmp_tables as Tmp_tables from Performance_schema.events_statements_summary_by_account_by_event_name WHERE sum_ Created_tmp_disk_tables > 0 OR sum_created_tmp_tables > 0; User not properly shutting down the database connection SELECT Ess.user, Ess.host, (a.total_connections-a.current_connections)-Ess.count_star as Not_closeD, ((a.total_connections-a.current_connections)-Ess.count_star) */(A.total_connections-a.current_connections) As pct_not_closed from performance_schema.events_statements_summary_by_account_by_event_name ESS JOIN Performance_ Schema.accounts A on (Ess.user = a.user and ess.host = a.host) WHERE ess.event_name = ' statement/com/quit ' and (A.total_co nnections-a.current_connections) > Ess.count_star; These are useful information for adding to the SYS schema. 4. This paper introduces the function and composition of SYS schema in detail, and demonstrates the usability and insufficiency of SYS schema through several examples, I believe through this article, we can have a more complete understanding of SYS schema, and also willing to take the time to learn more about SYS Each view in the schema. In addition, the SYS schema can be applied on MySQL 5.6, see here for specific information.
MySQL sys table
