• MYSQL PERFORMANCE_SCHEMA HINTS


    ACCOUNTS NOT PROPERLY CLOSING CONNECTIONS [ 1 ]

    Works since 5.6

    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 a 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
    ;
    
    

    UNUSED INDEXES [ 2 ]

    Works since 5.6

    SELECT object_schema, object_name, index_name
      FROM performance_schema.table_io_waits_summary_by_index_usage
     WHERE index_name IS NOT NULL
       AND count_star = 0
     ORDER BY object_schema, object_name
    ;
    
    

    WHO CREATED TEMPORARY (DISK) TABLES

    Works since 5.6

    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
    ;
    
    
    SELECT schema_name, substr(digest_text, 1, 40) AS statement, 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_digest
     WHERE sum_created_tmp_disk_tables > 0
        OR sum_created_tmp_tables > 0
    ;
    
    

    ACCOUNTS WHICH NEVER CONNECTED SINCE LAST START-UP [ 3 ]

    Works since 5.6

    SELECT DISTINCT m_u.user, m_u.host
      FROM mysql.user m_u
      LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND m_u.host = ps_a.host
     WHERE ps_a.user IS NULL
    ORDER BY m_u.user
    ;
    
    

    USERS WHICH NEVER CONNECTED SINCE LAST START-UP

    Works since 5.6

    SELECT DISTINCT m_u.user
      FROM mysql.user m_u
      LEFT JOIN performance_schema.users ps_u ON m_u.user = ps_u.user
     WHERE ps_u.user IS NULL
     ORDER BY m_u.user
    ;
    
    

    TOTALLY UNUSED ACCOUNTS (NEVER CONNECTED SINCE LAST RESTART AND NOT USED TO CHECK STORED PROGRAM OR VIEW PRIVILEGES) SINCE LAST START-UP

    Works since 5.6

    SELECT DISTINCT m_u.user, m_u.host
      FROM mysql.user m_u
      LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host
      LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER'
      LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER'
      LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host)
      LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host)
     WHERE ps_a.user IS NULL
       AND is_v.definer IS NULL
       AND is_r.definer IS NULL
       AND is_e.definer IS NULL
       AND is_t.definer IS NULL
     ORDER BY m_u.user, m_u.host
    ;
    
    

    SHOW FULL PROCESSLIST

    Works since 5.5 (5.1?)

    But with filter on Sleep and sorting by time to find the evil query...

    SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS info
      FROM information_schema.processlist
     WHERE command NOT IN ('Sleep', 'Binlog Dump')
     ORDER BY time ASC
    ;
    
    

    Non blocking version, since 5.6:

    SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db
         , PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info
      FROM performance_schema.threads
     WHERE PROCESSLIST_ID IS NOT NULL
       AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
     ORDER BY PROCESSLIST_TIME ASC
    ;
    
    

    STORAGE ENGINES PER SCHEMA

    For defining backup strategy, preparing migration to InnoDB or Galera Cluster for MySQL, etc.

    Works since 5.5 (5.1?)

    SELECT table_schema AS `schema`, engine, COUNT(*) AS `tables`
         , ROUND(SUM(data_length)/1024/1024, 0) AS data_mb, ROUND(SUM(index_length)/1024/1024, 0) index_mb
      FROM information_schema.tables
     WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
       AND engine IS NOT NULL
     GROUP BY table_schema, engine
    ;
    +---------------------+--------+--------+---------+----------+
    | schema              | engine | tables | data_mb | index_mb |
    +---------------------+--------+--------+---------+----------+
    | mantis              | MyISAM |     31 |       0 |        0 |
    | mpm                 | InnoDB |      3 |       0 |        0 |
    | mysql_sequences     | InnoDB |      2 |       0 |        0 |
    | mysql_sequences     | MEMORY |      1 |       0 |        0 |
    | otrs                | InnoDB |     73 |      13 |        4 |
    | quartz              | InnoDB |     12 |       0 |        0 |
    | tracking            | MyISAM |      1 |       0 |        0 |
    +---------------------+--------+--------+---------+----------+
    
    

    TABLES WITHOUT A PRIMARY KEY

    Galera Cluster, InnoDB, M/S replication with row based replication does not work well with tables without a Primary Key. To find those the following query helps:

    Works since 5.5 (5.1?)

    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         |
    +--------------+--------------------+
    | test         | t_wo_pk            |
    | test         | t_wo_pk_with_Index |
    +--------------+--------------------+
    
    

    BAD SQL QUERIES OF USERS

    Sometimes it could be interesting to find users who do evil SQL Queries which do for examplecreated_tmp_disk_tablesselect_full_joinselect_range_check or sort_merge_passes. Those can be found as follows:

    SELECT user, host, event_name
         , sum_created_tmp_disk_tables AS tmp_disk_tables
         , sum_select_full_join AS full_join
         , sum_select_range_check AS range_check
         , sum_sort_merge_passes AS sort_merge
      FROM performance_schema.events_statements_summary_by_account_by_event_name
     WHERE sum_created_tmp_disk_tables > 0
        OR sum_select_full_join > 0
        OR sum_select_range_check > 0
        OR sum_sort_merge_passes > 0
     ORDER BY sum_sort_merge_passes DESC
     LIMIT 10
    ;        
    
    +-------+-------------+---------------+-----------------+-----------+-------------+------------+
    | user  | host        | event_name    | tmp_disk_tables | full_join | range_check | sort_merge |
    +-------+-------------+---------------+-----------------+-----------+-------------+------------+
    | user1 | 192.168.0.3 | insert_select |               0 |      7033 |           0 |      10947 |
    | user2 | 192.168.0.4 | insert_select |               0 |      6837 |           0 |      10792 |
    | user1 | 192.168.0.1 | select        |        10742308 |      2095 |       23061 |         16 |
    | user2 | 192.168.0.2 | select        |        10958067 |      2639 |       23162 |         14 |
    +-------+-------------+---------------+-----------------+-----------+-------------+------------+
    
    

    SHOW PROFILE IN PERFORMANCE_SCHEMA

    On systems with heavy traffic the PERFORMANCE_SCHEMA tables might be too small.

    mysql> SHOW GLOBAL VARIABLES LIKE 'perf%events%stage%hist%long%';
    +----------------------------------------------------+-------+
    | Variable_name                                      | Value |
    +----------------------------------------------------+-------+
    | performance_schema_events_stages_history_long_size | 10000 |
    +----------------------------------------------------+-------+
    
    mysql> pager grep history_long
    mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
    | performance_schema | events_waits_history_long.row_size       | 184      |
    | performance_schema | events_waits_history_long.row_count      | 1000     |
    | performance_schema | events_waits_history_long.memory         | 184000   |
    | performance_schema | events_stages_history_long.row_size      | 88       |
    | performance_schema | events_stages_history_long.row_count     | 1000     |
    | performance_schema | events_stages_history_long.memory        | 88000    |
    | performance_schema | events_statements_history_long.row_size  | 3024     |
    | performance_schema | events_statements_history_long.row_count | 1000     |
    | performance_schema | events_statements_history_long.memory    | 3024000  |
    
    mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
     WHERE NAME LIKE '%statement/%';
    mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
     WHERE NAME LIKE '%stage/%';
    mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
     WHERE NAME LIKE '%events_statements_%';
    mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
     WHERE NAME LIKE '%events_stages_%';
    
    mysql> SELECT ;
    
    mysql> SELECT eshl.event_id AS Query_ID, TRUNCATE(eshl.timer_wait/1000000000000, 6) as Duration
         , LEFT(eshl.sql_text, 120) AS Query
      FROM performance_schema.events_statements_history_long AS eshl
      JOIN performance_schema.threads AS t ON t.thread_id = eshl.thread_id
     WHERE t.processlist_id = CONNECTION_ID();
    +----------+-----------+-------------------------+
    | Query_ID | Duration  | Query                   |
    +----------+-----------+-------------------------+
    |       12 | 13.560737 | select * from test.test |
    +----------+-----------+-------------------------+
    
    mysql> SELECT event_name AS Stage, TRUNCATE(timer_wait/1000000000000,6) AS Duration 
      FROM performance_schema.events_stages_history_long
     WHERE nesting_event_id = 12;
    +--------------------------------+-----------+
    | Stage                          | Duration  |
    +--------------------------------+-----------+
    | stage/sql/starting             |  0.000043 |
    | stage/sql/checking permissions |  0.000004 |
    | stage/sql/Opening tables       |  0.002700 |
    | stage/sql/init                 |  0.000025 |
    | stage/sql/System lock          |  0.000009 |
    | stage/sql/optimizing           |  0.000002 |
    | stage/sql/statistics           |  0.000014 |
    | stage/sql/preparing            |  0.000013 |
    | stage/sql/executing            |  0.000000 |
    | stage/sql/Sending data         | 13.557683 |
    | stage/sql/end                  |  0.000002 |
    | stage/sql/query end            |  0.000008 |
    | stage/sql/closing tables       |  0.000006 |
    | stage/sql/freeing items        |  0.000215 |
    | stage/sql/cleaning up          |  0.000001 |
    +--------------------------------+-----------+
    
    

    SELECTINSERTUPDATE AND DELETE PER TABLE

    Sometimes it is interesting to know how many SELECTINSERTUPDATE or DELETE (DML) statementes have been exectuted against a specifict table (for example for OPTIMZE TABLE). This can be found as follows:

    Works since MySQL 5.6

    SELECT object_type, object_schema, object_name
         , count_star, count_read, count_write, count_fetch
         , count_insert, count_update, count_delete
      FROM performance_schema.table_io_waits_summary_by_table
     WHERE count_star > 0
    ;
    
    SELECT object_type, object_schema, object_name, index_name
         , count_star, count_read, count_write, count_fetch
         , count_insert, count_update, count_delete
      FROM performance_schema.table_io_waits_summary_by_index_usage
     WHERE count_star > 0
    ;
    
    

    TOP LONG RUNNING QUERIES

    Works since MySQL 5.6

    UPDATE setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';
    
    SELECT left(digest_text, 64)
         , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms
         , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms
         , ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms
         , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms
         , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms
         , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms
         , ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms
         , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms
         , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms
         , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms
         , ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms
         , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms
         , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen
         , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen
         , COUNT(*) as cnt
      FROM events_statements_history_long
      JOIN information_schema.global_status AS isgs
     WHERE isgs.variable_name = 'UPTIME'
     GROUP BY LEFT(digest_text,64)
     ORDER BY tot_exec_ms DESC
    ;
    
    +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
    | left(digest_text, 64)                                            | tot_exec_ms | tot_wait_ms | tot_lock_ms | first_seen          | last_seen           | cnt |
    +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
    | INSERT INTO `test` SELECT ? , DATA , ? FROM `test`               |     50493.5 |     50493.5 |        26.3 | 2015-11-12 16:41:35 | 2015-11-12 16:42:04 |  20 |
    | SELECT LEFT ( `digest_text` , ? ) , `ROUND` ( SUM ( `timer_end`  |     14434.6 |     14434.6 |        25.8 | 2015-11-12 16:48:44 | 2015-11-12 17:07:15 |   6 |
    | SELECT * FROM `test`                                             |      7483.0 |      7483.0 |         0.2 | 2015-11-12 16:41:16 | 2015-11-12 16:42:34 |   2 |
    | SHOW ENGINE INNODB STATUS                                        |      1912.4 |      1912.4 |         0.0 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
    | SHOW GLOBAL VARIABLES                                            |      1091.1 |      1091.1 |        68.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
    | SHOW GLOBAL STATUS                                               |       638.7 |       638.7 |        40.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
    | SELECT LEFT ( `digest_text` , ? ) , SUM ( `timer_end` - `timer_s |       356.2 |       356.2 |        42.4 | 2015-11-12 16:42:38 | 2015-11-12 16:45:00 |   6 |
    | SELECT `digest_text` , SUM ( `timer_end` - `timer_start` ) / ? A |       325.3 |       325.3 |         0.4 | 2015-11-12 16:40:44 | 2015-11-12 16:42:18 |   3 |
    | SELECT `DIGEST_TEXT` , ( `TIMER_END` - `TIMER_START` ) / ? AS `e |       163.2 |       163.2 |         1.0 | 2015-11-12 16:37:44 | 2015-11-12 16:39:22 |   9 |
    | SELECT LOWER ( REPLACE ( trx_state , ?, ... ) ) AS state , COUNT |       133.9 |       133.9 |        80.2 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
    +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
    
    

    TABLES NEVER WRITTEN TO

    If you want to find tables which it was never written to (or read from) since last instance restart you can use the following query. Works since MySQL 5.6

    Caution:

    • count_read can only be taken as argument if a physical backup method (NOT mysqldump) is used.
    • If count_write is 0 it does not necessarily mean that there was no write statement (no matching write)!
    • If tables are empty SELECT statements are not counted (count_read = 0).
    SELECT t.table_schema, t.table_name, t.table_rows, tio.count_read, tio.count_write
      FROM information_schema.tables AS t
      JOIN performance_schema.table_io_waits_summary_by_table AS tio
        ON tio.object_schema = t.table_schema AND tio.object_name = t.table_name
     WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
       AND tio.count_write = 0
     ORDER BY t.table_schema, t.table_name
    ;
    http://www.fromdual.com/mysql-performance-schema-hints
  • 相关阅读:
    Python2 升级Python3
    'builtin_function_or_method' object has no attribute 'translate'
    antd-mobile的DatePicker分钟精度半小时
    Windows10远程报错:由于CredSSP加密Oracle修正
    Nginx配置
    MySQL之索引优化
    Ubuntu下删除VMware的方法
    控制窗体的显示和隐藏
    os模块
    树莓派 使用3.5耳机口输出音频
  • 原文地址:https://www.cnblogs.com/future2012lg/p/5953526.html
Copyright © 2020-2023  润新知