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

How to treat mysql 5.7sys database table, for this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Introduction

Mysql introduces the performance_schema database from mysql 5.6.It provides great convenience for monitoring and tuning the database. However, some data in performance_schema database are still extensive and difficult to use, so database students need to reaggregate and develop. Mysql 5.7The built-in database sys is added to redevelop and encapsulate the related tables in performance_schema database. It is convenient for operation and maintenance partners to use directly, which greatly improves the efficiency of operation and maintenance.

The sys database consists of a series of tables. The following lists some important tables for everyone to get started, so that they can have an intuitive feeling.

Sys database table

Host_ chart

-

Displays the number of SQL statements grouped by host name, the number of file IO, that is, the latency of the file IO, the current number of connection sessions, the number of database users corresponding to the connection, and the corresponding number of memory allocations

(note: from this we can know the load distribution of each host, and through the basic vertical comparison, we can know the changing trend of the load of each host node.)

-

Mysql > select * from host_summary +- -+-+ | host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_ Latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated | +- -+-+ | localhost | 17264 | | 7.35s | 425.55 us | 14512 | 1.07s | 5 | 33 | 2 | 0 bytes | 0 bytes | | three57 | 10 | 1.00m | 6.00s | 0 | | | 12 | 326.38 us | 0 | 1 | 0 bytes | 0 bytes | | two57 | 10 | 1.00m | 6.00s | 0 | 12 | 44.42 us | 0 | | | 1 | 1 | 0 bytes | 0 bytes | +-+-| -+- -+ 3 rows in set (0.01sec)

Host_summary_by_file_ io table

-

Grouping based on host names, showing the number of IO and IO latency per host name

-

Mysql > select * from host_summary_by_file_io +-+ | host | ios | io_latency | +-+ | background | 3333 | 1.14s | | localhost | 7256 | 536.72 ms | | three57 | 12 | 326.38 us | | two57 | 12 | 44.42 us | | +-+ 4 rows in set (0.01 sec) |

Host_summary_by_file_io_ type table

-

Which sub-component produces the most IO under a host, and then carries on the concrete analysis

-

Mysql > select * from host_summary_by_file_io_type +-+-+ | host | event_name | total | total_latency | max _ latency | +-+-+ | background | wait/io/file/innodb/innodb_log_file | 323 | 738.16 ms | 19 .08 ms | | background | wait/io/file/innodb/innodb_data_file | 1423 | 380.97 ms | 21.19 ms | | background | wait/io/file/sql/binlog_index | 31 | 12.76 ms | 11.59 ms | | background | wait/io/file/sql/binlog | 31 | 6.52 ms | 2.14 ms | | background | wait/io/file/sql/FRM | | | 1404 | 951.13 us | 29.74 us | | background | wait/io/file/sql/casetest | 15 | 399.98 us | 340.60 us | | background | wait/io/file/myisam/kfile | 41 | 93.75 us | 33.20 us | | background | wait/io/file/sql/ERRMSG | 5 | 59.83 us | 25.11 us | | | background | wait/io/file/myisam/dfile | 53 | 53.63 us | 4.03 us | | background | wait/io/file/mysys/cnf | 5 | 18.89 us | 6.34 us | | background | wait/io/file/sql/pid | 3 | 16.42 us | 10.14 us | | background | wait/io/file/mysys/charset | | | 3 | 13.50 us | 6.53 us | | background | wait/io/file/sql/global_ddl_log | 2 | 3.15 us | 1.87 us | | localhost | wait/io/file/innodb/innodb_log_file | 74 | 182.02 ms | 16.42 ms | | localhost | wait/io/file/sql/binlog | 95 | 180.14 ms | | | 15.37 ms | | localhost | wait/io/file/sql/file_parser | 438 | 76.83 ms | 7.99 ms | | localhost | wait/io/file/innodb/innodb_data_file | 47 | 35.92 ms | 8.78 ms | | localhost | wait/io/file/sql/FRM | 2511 | 24.19 ms | 10.98 ms | | localhost | wait/io/file/csv/ | Metadata | 8 | 10.64 ms | 6.13 ms |

Host_summary_by_statement_ Latency table

-

The delay of each host, as well as the maximum delay, the components of the delay

-

Mysql > select * from sys.host_summary_by_statement_latency +- -- + | host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +-- -+ | two57 | 10 | 1.00m | 1.00m | 0 ps | 5 | 0 | 0 | three57 | 10 | 1.00m | 1.00m | 0 ps | 5 | 0 | 0 | 0 | localhost | 9455 | 3.73s | 2.07s | 100.57 ms | 3521 | 179048 | 21 | 197s | background | 0 | 0 ps | 0 | 0 | | | 0 | 0 | +-- | -+-+ 4 rows in set (0.01sec)

Memory_by_thread_by_current_ Bytes table

-

Performance comparison of memory allocation for each thread (Note: various MYSQL threads: IO READ THREAD,IO WRITE THREAD

PAGE_CLEANER THREAD,IBUF THREAD,WORKER_THREAD,MONITOR THREAD,LOCK TIMEOUT

THREAD,DUMP THREAD, accepting thread for group replication and authenticated broadcast thread for group replication), SQL thread for SLAVE

MAIN THREAD

(note: in this way, you can know which thread consumes the most memory. By making a vertical comparison, you will know the thread consumption history and whether there are performance problems.

-

Mysql > select * from sys.memory_by_thread_by_current_bytes +- -+-+ | thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | +-- -+-+ | 5 | innodb/io _ read_thread | 0 | 0 bytes | 0 bytes | | 6 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | | 7 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | | 8 | innodb/io_write_thread | 0 bytes | 0 bytes | 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | | 10 | innodb/page_cleaner_thread | 0 | 0 bytes | 0 bytes | | | 11 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | | 12 | innodb/io_log_thread | 0 | 0 bytes | 0 bytes | | | 13 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | | 15 | innodb/srv_master_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 16 | innodb/srv_purge_thread | 0 | 0 bytes | 0 bytes | | 17 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | | | 0 bytes | | 18 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | | 19 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | | | 0 bytes | | 20 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | | 21 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | | 0 bytes | | 22 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | | 23 | innodb/dict_stats_thread | 0 | 0 bytes | 0 | Bytes | | 24 | innodb/buf_dump_thread | 0 | 0 bytes | 0 bytes | | 25 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | | | 0 bytes | | 26 | sql/compress_gtid_table | 0 | 0 bytes | 0 bytes | | 31 | group_rpl/THD_applier_module_receiver | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 32 | group_rpl/THD_certifier_broadcast | 0 | 0 bytes | 0 bytes | | 33 | sql/slave_sql | 0 | 0 bytes | 0 bytes | 0 bytes | | | 0 bytes | | 66 | root@localhost | 0 | 0 bytes | 0 bytes | | 67 | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | | | 0 bytes | | 68 | root@localhost | 0 | 0 bytes | 0 bytes | | 1 | sql/main | 0 | 0 bytes | 0 bytes | | | 0 bytes | 0 bytes | | 2 | sql/thread_timer_notifier | 0 | 0 bytes | 0 bytes | | 3 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | | | 0 bytes | 0 bytes | | 4 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | +-| -+-+ 31 rows in set (0 .04 sec) this is the answer to the question about how to view mysql 5.7 sys database tables. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

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.