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

The main content of this article is "how to check the running status of mysql", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to check the running status of mysql.

View the most recent top sql

View some recent sql statements executed by the database through the performance_schema.events_statements_ statements table:

(root@localhost) [(none)] > SELECT thread_id, event_name, source, sys.format_time (timer_wait), sys.format_time (lock_time), sql_text, current_schema, message_text, rows_affected, rows_sent Rows_examined FROM performance_schema.events_statements_history WHERE current_schema! = 'performance_schema' ORDER BY timer_wait DESC limit 10\ G * * 1. Row * * thread_id: 561166 Event_name: statement/sql/select source: socket_connection.cc:101sys.format_time (timer_wait): 53.64 ms sys.format_time (lock_time): 429.00 us sql_text: select * from oa_v_position_list current_schema: oa_2016 message_text: NULL rows_affected: 0 rows_sent: 4 rows_examined: 18051 event_name * 2. Row * * thread_id: 153896 event_name: statement/sql/select Source: socket_connection.cc:101sys.format_time (timer_wait): 51.76 ms sys.format_time (lock_time): 96.00 us sql_text: select count (id) as num from formmain_2477 where (field0003 = 'SJCL-201911008' and ifnull (field0003) '0')! =' 0') current_schema: oa_2016 message_text: NULL rows_affected: 0 rows_sent: 1 rows_examined: 222 row * 3. *.

We can use the performance_schema.events_statements_summary_by_ digest table to query the statistical top sql statement:

[(none)] > SELECT schema_name, digest_text, count_star, sys.format_time (sum_timer_wait) AS sum_time, sys.format_time (min_timer_wait) AS min_time, sys.format_time (avg_timer_wait) AS avg_time, sys.format_time (max_timer_wait) AS min_time Sys.format_time (sum_lock_time) AS sum_lock_time, sum_rows_affected, sum_rows_sent Sum_rows_examinedFROM performance_schema.events_statements_summary_by_digestWHERE schema_name IS NOT NULLORDER BY count_star DESC limit 10\ G * * 1. Row * * schema_name: oa_2016 digest_text: SET `autoco Mmit` =? Count_star: 1604399319 sum_time: 13.57 h min_time: 2.00 ns avg_time: 30.46 us min_time: 39.87 ssum_ lock_time: 0 pssum_rows_affected: 0 summed rows _ sent: 0sum_rows_examined: 0 hours * 2. Row * * schema_name: oa_2016 digest_text: COMMIT count_star: 368723348 sum_time: 4.15h min_time: 10.07 us avg_time: 40.52 us min_time: 21.54 ssum_ lock_time: 40.27 ssum_rows_affected: 0 summed rows _ sent: 0 summed rows Examined: 0neighbors * 3. Row * * schema_name: oa_2016 digest_text: SELECT * FROM `jk_JOB_ DETAILS` WHERE `SCHED_ NAME` =? AND `JOB_ NAME` =? AND `JOB_ group` =? Count_star: 361183117 sum_time: 19.88 h min_time: 5.00 ns avg_time: 198.10 us min_time: 4.97 s sum_lock_time: 4.93 hsum_rows_affected: 0 sum_rows_sent: 361182289sum_rows_examined: 361182289.

Tip: the sql recorded by performance_schema.events_statements_summary_by_digest is incomplete and only 1024 bytes are intercepted by default, so the data provided by this table can only be counted as a supplement to slow log analysis. If you need full sql text, you also have to rely on slow log analysis.

View recent failed SQLmysql > SELECT thread_id, event_name, source, sys.format_time (timer_wait), sys.format_time (lock_time), sql_text, current_schema, message_text, rows_affected, rows_sent Rows_examined FROM performance_schema.events_statements_history WHERE errors > 0\ G * * 1. Row * * thread_id: 6172541 event_name: statement/sql/select Source: socket_connection.cc:101sys.format_time (timer_wait): 135.89 us sys.format_time (lock_time): 0 ps sql_text: SELECT id Waybill_num,oms_order_status FROM store_order WHERE is_sync_css_status_end IS NULL OR isometric syncs cssss statusendurance needs 01' ORDER BY create_time ASC Css_search_time ASC LIMIT 0200 current_schema: oms message_text: Table 'oms.store_order' doesn't exist rows_affected: 0 rows_sent: 0 rows_examined: 01 row in set (0.02 sec) to see what the MDL lock is waiting for

# check whether the instrments (collector) of the MDL lock waiting event is enabled mysql > SELECT * FROM performance_schema.setup_instrumentsWHERE name LIKE'% metadata/sql/mdl%' +-- + | NAME | ENABLED | TIMED | +-+ | wait/lock/metadata/sql/ Mdl | NO | NO | +-+ 1 row in set (0.00 sec) # enable instruments (collector) mysql > update performance_schema.setup_instruments set ENABLED='YES' where name like'% metadata/sql/mdl%' related to MDL lock waiting events Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > update performance_schema.setup_instruments set TIMED='YES' where name like'% metadata/sql/mdl%';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from performance_schema.setup_instruments where name like'% metadata/sql/mdl%' +-- + | NAME | ENABLED | TIMED | +-+ | wait/lock/metadata/sql/ Mdl | YES | YES | +-+ 1 row in set (0.01sec) # then use the sys.schema_table_lock_wait view to query (Note: please simulate a session transaction without committing If the DDL operation occurs in another session, you can see what the MDL lock is waiting for. Mysql > select * from sys.schema_table_lock_waits\ G to see what is the hot spot data in innodb_buffer_pool

Mysql > select * from sys.innodb_buffer_stats_by_table order by allocated desc limit 10 +-+-+ | object_schema | object_name | | allocated | data | pages | pages_hashed | pages_old | rows_cached | +-| -- +-+ | mysql | help_keyword | 96.00 KiB | 43.99 KiB | 6 | 6 | 6 | 283 | | InnoDB System | SYS_COLUMNS | 80.00 KiB | 44.50 KiB | 5 | 5 | 696 | | mdm | employee_jz | 64.00 KiB | 19.77 KiB | 4 | 4 | 4 | 177 | | mysql | innodb_index_stats | 64.00 KiB | 28.20 KiB | 4 | 4 | 4 | 288 | check whether a table in the database uses the foreign key mysql > SELECT * FROM information_schema.key_column_usageWHERE constraint_schema = 'oms' AND referenced_table_schema IS NOT NULL\ G * * 1. Row * * CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: oms CONSTRAINT_NAME: qrtz_blob_triggers_ibfk_1 TABLE_CATALOG: def TABLE_SCHEMA: oms TABLE_NAME: qrtz_blob_triggers COLUMN_NAME: SCHED_NAME ORDINAL_POSITION: 1POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: oms REFERENCED_TABLE_NAME: qrtz_triggers REFERENCED_COLUMN_NAME: SCHED_NAME** * 2. Row * * CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: oms

The use of foreign keys is usually prohibited in development specifications.

See what indexes each table has.

Mysql > SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITYFROM information_schema.STATISTICSGROUP BY TABLE_SCHEMA, TABLE_NAME limit 100 +-+-+ | TABLE_SCHEMA | TABLE_NAME | | INDEX_NAME | COLUMN_NAME | CARDINALITY | +-+-| -+ | mdm | department | index_unique_department | deptid | 381 | | mdm | employee | PRIMARY | sn | 1544 | mdm | employee_jz | index_unique_employee | sn | 1626 |. Check whether the database has partitioned tables mysql > select * from information_schema.partitions where partition_name is not null;Empty set (0.02 sec) statistics of the mysql system library. Table # stores the statistics of tables and indexes to disk. Default is (root@localhost) [mysql] > show variables like 'innodb_stats_persistent'. +-+-+ | Variable_name | Value | +-+-+ | innodb_stats_persistent | ON | +-- -- + 1 row in set (0.00 sec) # persistence information is stored in the following two tables in the mysql database: (root@localhost) [mysql] > show tables from mysql like'% stats%' +-- + | Tables_in_mysql (% stats%) | +-- + | innodb_index_stats | | innodb_table_stats | +-+ 2 rows in set (0. 00 sec) # innodb_stats_auto_recalc variable controls whether automatic recalculation of statistics is enabled It is on by default. If enabled, the statistics automatic recalculation function (root@localhost) [mysql] > show variables like 'innodb_stats_auto_recalc'-> will be triggered when the amount of data in the table exceeds 10%. +-- +-+ | Variable_name | Value | +-+-+ | innodb_stats_auto_recalc | ON | +-+ -+ 1 row in set (0.00 sec) so far I believe you have a deeper understanding of "how to check the running status of mysql". You might as well do it in practice. 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.