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

This article mainly explains "the arrangement of SQL commands commonly used in MySQL sys library". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn the "MySQL sys library commonly used SQL commands to organize" it!

View the current connection:

Select host, current_connections,statements from sys.host_summary

View the currently executing SQL:

Select conn_id, user, current_statement, last_statement from sys.session

Check out the most executed TOP 10 SQL in the system:

Select * from sys.statement_analysis order by exec_count desc limit 10\ G

Check which table in the system has the most IO:

Select * from sys.io_global_by_file_by_bytes limit 10

Check which table is accessed most frequently in the system:

Select * from sys.statement_analysis order by exec_count desc limit 10\ G

See which statements have serious delays:

Select * from sys.statement_analysis order by avg_latency desc limit 10\ G

View unused indexes in the system:

Select * from sys.schema_unused_indexes

Check the redundant indexes in the system:

Select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns from sys.schema_redundant_indexes

Which SQL statements use disk temporary tables:

Select db, query, tmp_tables,tmp_disk_tables from sys.statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables+tmp_disk_tables) desc limit 20

See which table takes up the most buffer pool:

Select * from sys.innodb_buffer_stats_by_table order by pages desc limit 10\ G

See how much buffer pool each library consumes:

Select * from sys.innodb_buffer_stats_by_schema

See how much memory is allocated per connection:

Select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from sys.memory_by_thread_by_current_bytes a dint sys. Session b where a.thread_id = b.thd_id

Check the type and number of threads within MySQL:

Select user, count (*) from sys.processlist group by user

View the self-increment ID of the table:

Select * from sys.schema_auto_increment_columns limit 10

Attached: detailed explanation of sys library view and index

View description

Host_summary,x $host_summary statistics activity statements, file I / O, connections, etc., grouped by hosts.

Host_summary_by_file_io,x $host_summary_by_file_io file IO

File I / O for host_summary_by_file_io_type,x $host_summary_by_file_io_type hosts and event types

Host_summary_by_stages,x $host_summary_by_stages execution information according to the statement phase classified by the host

Host_summary_by_statement_latency,x $host_summary_by_statement_latency statistics by statements classified by host

Host_summary_by_statement_type,x $host_summary_by_statement_type statement information executed by host and SQL

Innodb_buffer_stats_by_schema,x $innodb_buffer_stats_by_schema statistics InnoDB buffer information by schema

Innodb_buffer_stats_by_table,x $innodb_buffer_stats_by_table statistics InnoDB buffer information by schema and table

Innodb_lock_waits,x $innodb_lock_waits InnoDB Lock Information

Io_by_thread_by_latency,x $io_by_thread_by_latency thread consumes IO

Io_global_by_file_by_bytes,x $io_global_by_file_by_bytes file IO consumption size information

Io_global_by_file_by_latency,x $io_global_by_file_by_latency file IO delay information

Io_global_by_wait_by_bytes,x $io_global_by_wait_by_bytes global I / O consumption by size (bytes)

Delay information consumed by io_global_by_wait_by_latency,x $io_global_by_wait_by_latency IO

Latest_file_io,x $latest_file_io recently used file I / O information

Memory_by_host_by_current_bytes,x $memory_by_host_by_current_bytes host memory usage information

Memory_by_thread_by_current_bytes,x $memory_by_thread_by_current_bytes thread usage memory information

Memory_by_user_by_current_bytes,x $memory_by_user_by_current_bytes user memory usage information

Type of memory_global_by_current_bytes,x $memory_global_by_current_bytes memory usage allocation

Memory_global_total,x $memory_global_total memory statistics

Indicator description

Processlist,x $processlist Processlist process Information

Information about ps_check_lost_instrumentation 's missing performance mode tool

Schema_auto_increment_columns AUTO_INCREMENT self-growing column information

Schema_index_statistics,x $schema_index_statistics Index Statistics

Schema_object_overview object types for each schema

Schema_redundant_indexes duplicate / redundant index

Schema_table_lock_waits,x $schema_table_lock_waits waits for MDL's session

Schema_table_statistics,x $schema_table_statistics table statistics

Schema_table_statistics_with_buffer,x $schema_table_statistics_with_buffer table statistics, including InnoDB buffer pool statistics

Schema_tables_with_full_table_scans,x $schema_tables_with_full_table_scans tables accessed by all tables

Indexes not used by schema_unused_indexes

Processlis information for session,x $session user session

Session_ssl_status SSL connection Information

Statement_analysis,x $statement_analysis SQL statement summary statistics

Statements_with_errors_or_warnings,x $statements_with_errors_or_warnings contains SQL for errors and warnings

Statements_with_full_table_scans,x $statements_with_full_table_scans full table scan statement when executing

Statements_with_runtimes_in_95th_percentile,X $statements_with_runtimes_in_95th_percentile SQL with a long average running time

SQL statement sorted by statements_with_sorting,x $statements_with_sorting

Statements_with_temp_tables,x $statements_with_temp_tables uses temporary watch SQL Yuxi

User_summary,x $user_summary user statements and active connection information

User_summary_by_file_io,x $user_summary_by_file_io user related file I / O information

User_summary_by_file_io_type,x $user_summary_by_file_io_type user related file FI / O type information

User_summary_by_stages,x $user_summary_by_stages user phase event and delay information

User_summary_by_statement_latency,x $user_summary_by_statement_latency to count SQL statement information

User_summary_by_statement_type,x $user_summary_by_statement_type SQL statement information executed by user and event (event)

Average delay of wa_ wait_classes_global_by_avg_latency event type classification

Delay statistics for wait_classes_global_by_latency,x $wait_classes_global_by_latency event types

Waits_by_host_by_latency,x $waits_by_host_by_latency misses you according to the events classified by host

Waits_by_user_by_latency,x $waits_by_user_by_latency events by user

Waits_global_by_latency,x $waits_global_by_latency event information by event

At this point, I believe that everyone on the "MySQL sys library commonly used SQL command finishing" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.