Shulou(Shulou.com)06/01 Report--

Performance_ schema libraries commonly used SQL:

View tables that do not have primary keys:

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_schema NOT IN ('information_schema',' mysql', 'performance_schema')

AND c.table_name IS NULL AND t.table_type! = 'VIEW'

For example:

Mysql > 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_schema NOT IN ('information_schema',' mysql', 'performance_schema')

-> AND c.table_name IS NULL AND t.table_type! = 'VIEW'

+-- +

| | table_schema | table_name |

+-- +

| | S85 | dsf |

| | test | innodb_lock_monitor |

| | test | innodb_monitor |

| | test | innodb_table_monitor |

| | test | innodb_tablespace_monitor |

| | zhwp102 | t_orgpriority |

| | zhwp102 | t_task_ext |

| | zhwp102 | t_web_common |

| | zhwp111 | t_orgpriority |

| | zhwp111 | t_task_ext |

| | zhwp111 | t_web_common |

| | zhwp111 | t_weibo |

| | zhwp_prod | t_orgpriority |

| | zhwp_prod | t_task_ext |

| | zhwp_prod | t_web_common |

| | zhwp_prod | t_weibo |

| | zhwpzj111 | t_orgpriority |

| | zhwpzj111 | t_task_ext |

| | zhwpzj111 | t_web_common |

| | zhwpzj111 | t_weibo |

+-- +

20 rows in set (1 min 27.55 sec)

No primary key:

Mysql > desc S85.dsf

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | sourceDay | date | YES | | NULL |

| | sourceTime | datetime | NO | | CURRENT_TIMESTAMP |

| | affections | smallint (5) unsigned | NO | | 1 | |

+-- +

3 rows in set (0.00 sec)

See who created the temporary 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

Users who do not properly close 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) * 100 /

(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 an on (ess.user = a.user and ess.host = a.host)

WHERE ess.event_name = 'statement/com/quit'

AND (a.total_connections-a.current_connections) > ess.count_star

DDL metadata lock tracking

1. Turn on tracking:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE

NAME = 'wait/lock/metadata/sql/mdl'

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE

NAME = 'global_instrumentation'

two。 Query metadata lock:

Select * from performance_schema.metadata_locks

Select * from performance_schema.metadata_locks where LOCK_STATUS like 'PENDING%'

Select ID from information_schema.processlist where Info like'% 20190416%'\ G

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,processlist_id

FROM performance_schema.metadata_locks mdl

INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id

WHERE processlist_id @ @ pseudo_thread_id

3. Turn off tracking:

UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE

NAME = 'wait/lock/metadata/sql/mdl'

DDL execution progress tracking

1. Turn on tracking:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE' stage/innodb/alter%'

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE'% stages%'

two。 Check the progress of DDL execution:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED, (WORK_COMPLETED/WORK_ESTIMATED) * 100

As COMPLETED FROM performance_schema.events_stages_current

Common SQL for sys libraries:

View table visits

Select table_schema,table_name,sum (io_read_requests+io_write_requests) io from sys.schema_table_statistics

Group by table_schema,table_name order by io desc limit 10

Check the database connection

Select * from sys.processlist\ G

Select * from sys.session limit 10\ G

Select * from sys.x$processlist\ G

Select * from sys.x$session\ G

View redundant indexes

Select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name

Dominant_index_columns from sys.schema_redundant_indexes

View unused indexes

Select * from sys.schema_unused_indexes

Table self-adding ID monitoring

Select * from sys.schema_auto_increment_columns limit 10

View the files that actually consume disk IO

Select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10

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.