mysql> show engine performance_schema status; +--------------------+--------------------------------------------------------------+----------+ | Type | Name | Status | +--------------------+--------------------------------------------------------------+----------+ | performance_schema | events_waits_current.row_size | 184 | | performance_schema | events_waits_current.row_count | 2148 | | performance_schema | events_waits_history.row_size | 184 | | performance_schema | events_waits_history.row_count | 3580 | ... | performance_schema | (table_share_hash).size | 556 | | performance_schema | (user_hash).count | 2 | | performance_schema | (user_hash).size | 100 | | performance_schema | host_cache.size | 336 | | performance_schema | performance_schema.memory | 73698616 | +--------------------+--------------------------------------------------------------+----------+ 155 rows in set (0.10 sec) mysql> show variables like 'perf%'; +--------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------+-------+ | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 5000 | | performance_schema_events_stages_history_long_size | 1000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 1000 | | performance_schema_events_statements_history_size | 10 | ... -- -- THREADS -- mysql> select THREAD_ID, NAME, PROCESSLIST_COMMAND CONNAMD, PROCESSLIST_STATE STATE, INSTRUMENTED from threads; +-----------+----------------------------------------+---------+--------------+--------------+ | THREAD_ID | NAME | CONNAMD | STATE | INSTRUMENTED | +-----------+----------------------------------------+---------+--------------+--------------+ | 1 | thread/sql/main | NULL | System lock | YES | | 2 | thread/innodb/io_handler_thread | NULL | NULL | YES | | 3 | thread/innodb/io_handler_thread | NULL | NULL | YES | | 4 | thread/innodb/io_handler_thread | NULL | NULL | YES | | 5 | thread/innodb/io_handler_thread | NULL | NULL | YES | | 7 | thread/innodb/srv_lock_timeout_thread | NULL | NULL | YES | | 8 | thread/innodb/srv_error_monitor_thread | NULL | NULL | YES | | 9 | thread/innodb/srv_monitor_thread | NULL | NULL | YES | | 10 | thread/innodb/srv_master_thread | NULL | NULL | YES | | 11 | thread/innodb/srv_purge_thread | NULL | NULL | YES | | 12 | thread/innodb/page_cleaner_thread | NULL | NULL | YES | | 13 | thread/sql/signal_handler | NULL | NULL | YES | | 14 | thread/sql/manager | NULL | NULL | YES | | 15 | thread/sql/one_connection | Query | Sending data | YES | +-----------+----------------------------------------+---------+--------------+--------------+ 14 rows in set (0.01 sec) mysql> show tables like '%file%'; +---------------------------------------+ | Tables_in_performance_schema (%file%) | +---------------------------------------+ | file_instances | | file_summary_by_event_name | | file_summary_by_instance | +---------------------------------------+ 3 rows in set (0.01 sec) -- -- file_summary_by_event_name -- mysql> select EVENT_NAME FILE, COUNT_READ 'Reads', COUNT_WRITE Writes, COUNT_MISC Misc, (COUNT_MISC + COUNT_WRITE + COUNT_READ) as SUM_IO from file_summary_by_event_name order by 5 desc limit 5; +--------------------------------------+-------+--------+------+--------+ | FILE | Reads | Writes | Misc | SUM_IO | +--------------------------------------+-------+--------+------+--------+ | wait/io/file/myisam/kfile | 42 | 5423 | 84 | 5549 | | wait/io/file/myisam/dfile | 6 | 5401 | 81 | 5488 | | wait/io/file/sql/FRM | 511 | 95 | 459 | 1065 | | wait/io/file/innodb/innodb_data_file | 153 | 2 | 16 | 171 | | wait/io/file/innodb/innodb_log_file | 6 | 5 | 9 | 20 | +--------------------------------------+-------+--------+------+--------+ 5 rows in set (0.00 sec) -- -- file_summary_by_instance -- mysql> select FILE_NAME FILE, COUNT_READ 'Reads', COUNT_WRITE Writes, COUNT_MISC Misc, (COUNT_MISC + COUNT_WRITE + COUNT_READ) as SUM_IO from file_summary_by_instance order by 5 desc limit 5; +------------------------------------------------+-------+--------+------+--------+ | FILE | Reads | Writes | Misc | SUM_IO | +------------------------------------------------+-------+--------+------+--------+ | /apps/mysql5610/data/world/City.MYI | 2 | 4091 | 6 | 4099 | | /apps/mysql5610/data/world/City.MYD | 0 | 4079 | 4 | 4083 | | /apps/mysql5610/data/world/CountryLanguage.MYI | 2 | 996 | 6 | 1004 | | /apps/mysql5610/data/world/CountryLanguage.MYD | 0 | 984 | 4 | 988 | | /apps/mysql5610/data/world/Country.MYI | 2 | 261 | 6 | 269 | +------------------------------------------------+-------+--------+------+--------+ 5 rows in set (0.00 sec) mysql> show tables like '%table%'; +----------------------------------------+ | Tables_in_performance_schema (%table%) | +----------------------------------------+ | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +----------------------------------------+ 3 rows in set (0.00 sec) -- -- table_lock_waits_summary_by_table -- mysql> select object_schema, object_name, count_star, sum_timer_wait from table_lock_waits_summary_by_table order by 3 desc limit 5; +---------------+-----------------------+------------+----------------+ | object_schema | object_name | count_star | sum_timer_wait | +---------------+-----------------------+------------+----------------+ | world | City | 8158 | 5595381220 | | world | CountryLanguage | 1968 | 1472584620 | | world | Country | 478 | 530457850 | | mysql | db | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | +---------------+-----------------------+------------+----------------+ 5 rows in set (0.00 sec) mysql> select OBJECT_NAME 'TABLE', SUM_TIMER_READ ReadTM, SUM_TIMER_WRITE WriteTM, SUM_TIMER_WAIT WaitTM, from table_lock_waits_summary_by_table order by 5 desc limit 5; +-----------------------+--------+------------+------------+ | TABLE | ReadTM | WriteTM | WaitTM | +-----------------------+--------+------------+------------+ | City | 0 | 5595381220 | 5595381220 | | CountryLanguage | 0 | 1472584620 | 1472584620 | | Country | 0 | 530457850 | 530457850 | | db | 0 | 0 | 0 | | time_zone_leap_second | 0 | 0 | 0 | +-----------------------+--------+------------+------------+ 5 rows in set (0.00 sec) -- -- table_io_waits_summary_by_table -- mysql> select object_name, count_star from table_io_waits_summary_by_table order by 2 desc limit 5; +-----------------------+------------+ | object_name | count_star | +-----------------------+------------+ | City | 4079 | | CountryLanguage | 984 | | Country | 239 | | db | 0 | | time_zone_leap_second | 0 | +-----------------------+------------+ 5 rows in set (0.00 sec) mysql> select object_name, COUNT_STAR 'ALL', COUNT_READ 'Read', COUNT_WRITE 'Write', COUNT_FETCH 'Fetch', COUNT_INSERT 'Insert', COUNT_UPDATE 'Update', COUNT_DELETE 'Delete' from table_io_waits_summary_by_table order by 2 desc limit 5; +-----------------------+-------+-------+-------+-------+--------+--------+--------+ | object_name | ALL | Read | Write | Fetch | Insert | Update | Delete | +-----------------------+-------+-------+-------+-------+--------+--------+--------+ | City | 57119 | 53040 | 4079 | 53040 | 4079 | 0 | 0 | | CountryLanguage | 984 | 0 | 984 | 0 | 984 | 0 | 0 | | Country | 239 | 0 | 239 | 0 | 239 | 0 | 0 | | db | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | time_zone_leap_second | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------------------+-------+-------+-------+-------+--------+--------+--------+ 5 rows in set (0.01 sec) -- -- table_io_waits_summary_by_index_usage -- mysql> select object_name, index_name, count_star from table_io_waits_summary_by_index_usage order by 3 desc limit 5; +-----------------+------------+------------+ | object_name | index_name | count_star | +-----------------+------------+------------+ | City | NULL | 4079 | | CountryLanguage | NULL | 984 | | Country | NULL | 239 | | user | PRIMARY | 0 | | db | PRIMARY | 0 | +-----------------+------------+------------+ 5 rows in set (0.00 sec) mysql> show tables like '%statement%'; +----------------------------------------------------+ | Tables_in_performance_schema (%statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +----------------------------------------------------+ 9 rows in set (0.01 sec) -- -- events_statements_summary_by_digest -- mysql> select DIGEST, SUM_TIMER_WAIT, COUNT_STAR from events_statements_summary_by_digest order by 2 desc limit 5; +----------------------------------+----------------+------------+ | DIGEST | SUM_TIMER_WAIT | COUNT_STAR | +----------------------------------+----------------+------------+ | 3446e3917bb482239c600b82d11c5fbd | 1190151136000 | 4079 | | 5b73fb799e6d1f3b6dfad6703d3585ff | 535491523000 | 1 | | 381b61a89e42a158fb1fa7dc28a41d58 | 475904871000 | 1 | | 9a87df04c042c77e4b1257ddb50d3d7a | 462504270000 | 1 | | 080f6e3cd00d3a2a2bd2ee75d7d46ba0 | 331546640000 | 984 | +----------------------------------+----------------+------------+ 5 rows in set (0.00 sec) mysql> select LEFT(DIGEST_TEXT, 40) 'QUERY' , SUM_TIMER_WAIT, COUNT_STAR from events_statements_summary_by_digest order by 2 desc limit 5; +------------------------------------------+----------------+------------+ | QUERY | SUM_TIMER_WAIT | COUNT_STAR | +------------------------------------------+----------------+------------+ | INSERT INTO `City` VALUES (...) | 1190151136000 | 4079 | | SELECT `DIGEST_TEXT` , `SUM_TIMER_WAIT` | 535491523000 | 1 | | CREATE TABLE `City` ( `ID` INTEGER (?) N | 475904871000 | 1 | | CREATE TABLE `Country` ( `Code` CHARACTE | 462504270000 | 1 | | INSERT INTO `CountryLanguage` VALUES (.. | 331546640000 | 984 | +------------------------------------------+----------------+------------+ 5 rows in set (0.00 sec) mysql> select * from events_statements_summary_by_digest where DIGEST = '3446e3917bb482239c600b82d11c5fbd' \G *************************** 1. row *************************** SCHEMA_NAME: world DIGEST: 3446e3917bb482239c600b82d11c5fbd DIGEST_TEXT: INSERT INTO `City` VALUES (...) COUNT_STAR: 4079 SUM_TIMER_WAIT: 1190151136000 MIN_TIMER_WAIT: 52062000 AVG_TIMER_WAIT: 291775000 MAX_TIMER_WAIT: 70573035000 SUM_LOCK_TIME: 212172000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 4079 SUM_ROWS_SENT: 0 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2013-10-09 14:40:48 LAST_SEEN: 2013-10-09 14:40:50 1 row in set (0.17 sec) mysql> select CountryCode, count(*) from world.City group by 1 order by 2 desc limit 5; +-------------+----------+ | CountryCode | count(*) | +-------------+----------+ | CHN | 363 | | IND | 341 | | USA | 274 | | BRA | 250 | | JPN | 248 | +-------------+----------+ 5 rows in set (0.00 sec) mysql> select LEFT(DIGEST_TEXT, 30) 'QUERY' , SUM_TIMER_WAIT, COUNT_STAR, digest from events_statements_summary_by_digest where SCHEMA_NAME = 'world' and DIGEST_TEXT like 'SELECT%' order by 2 desc; +--------------------------------+----------------+------------+----------------------------------+ | QUERY | SUM_TIMER_WAIT | COUNT_STAR | digest | +--------------------------------+----------------+------------+----------------------------------+ | SELECT `CountryCode` , COUNT ( | 23737211000 | 7 | 55a485fb363c6886d58dda0e5b50d489 | | SELECT @ @ version_comment LIM | 740185000 | 2 | 2ba1a243802b10f96fb2facbfa4d3a05 | +--------------------------------+----------------+------------+----------------------------------+ 2 rows in set (0.00 sec) mysql> select * from events_statements_summary_by_digest where DIGEST = '55a485fb363c6886d58dda0e5b50d489' \G *************************** 1. row *************************** SCHEMA_NAME: performance_schema DIGEST: 55a485fb363c6886d58dda0e5b50d489 DIGEST_TEXT: SELECT `CountryCode` , COUNT ( * ) FROM `world` . `City` GROUP BY ? ORDER BY ? DESC LIMIT ? COUNT_STAR: 6 SUM_TIMER_WAIT: 239776187000 MIN_TIMER_WAIT: 2778661000 AVG_TIMER_WAIT: 39962697000 MAX_TIMER_WAIT: 139000119000 SUM_LOCK_TIME: 1424000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 30 <============= !! SUM_ROWS_EXAMINED: 25896 <============= !! SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 6 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 6 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 30 SUM_SORT_SCAN: 6 SUM_NO_INDEX_USED: 6 <=== !! SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2013-10-09 21:11:02 LAST_SEEN: 2013-10-09 21:19:01 mysql> show tables like '%waits%'; +-----------------------------------------------+ | Tables_in_performance_schema (%waits%) | +-----------------------------------------------+ | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +-----------------------------------------------+ 12 rows in set (0.00 sec) -- -- events_waits_summary_global_by_event_name -- mysql> select event_name, count_star, sum_timer_wait/1000000000000 sec from events_waits_summary_global_by_event_name order by 3 desc limit 5; +----------------------------------+------------+------------+ | event_name | count_star | sec | +----------------------------------+------------+------------+ | idle | 5640 | 52912.7860 | | wait/synch/cond/sql/COND_manager | 87 | 5920.9862 | | wait/io/file/sql/FRM | 1066 | 0.4151 | | wait/io/table/sql/handler | 58342 | 0.3903 | | wait/io/file/myisam/dfile | 6014 | 0.3651 | +----------------------------------+------------+------------+ 5 rows in set (0.00 sec) mysql> select event_name, count_star, sum_timer_wait/ 1000000000000 sec from events_waits_summary_global_by_event_name order by 3 desc limit 5; +---------------------------------------------+------------+-------------+ | event_name | count_star | sec | +---------------------------------------------+------------+-------------+ | wait/io/file/innodb/innodb_data_file | 16735150 | 337030.6622 | | idle | 789 | 1537.9175 | | wait/synch/rwlock/innodb/index_tree_rw_lock | 35776371 | 199.6533 | | wait/synch/rwlock/innodb/fil_space_latch | 117220 | 83.7359 | | wait/synch/mutex/innodb/log_sys_mutex | 37133308 | 65.3447 | +---------------------------------------------+------------+-------------+ 5 rows in set (0.03 sec) # /etc/STATsrv/bin/mysqlWAITS.sh 5 WAIT-event Waits/sec Time/sec TotTM/Wait CurTM/Wait db-server-online 1 1 0 0 wait/synch/rwlock/innodb/hash_table_locks 167903.20 365081.59 2.14 2.17 wait/synch/mutex/sql/THD::LOCK_thd_data 105180.40 118265.60 1.12 1.12 wait/synch/mutex/sql/THD::LOCK_query_plan 88678.60 90092.80 1.00 1.02 wait/synch/mutex/innodb/fil_system_mutex 85044.80 277734.41 5.00 3.27 wait/synch/mutex/innodb/buf_pool_mutex 82602.80 115897.60 1.52 1.40 wait/synch/mutex/innodb/srv_threads_mutex 7060.80 2446.40 0.26 0.35 wait/synch/mutex/sql/LOCK_table_cache 33032.40 57222.40 1.70 1.73 wait/synch/mutex/mysys/THR_LOCK::mutex 33031.00 39318.40 1.18 1.19 wait/synch/mutex/innodb/log_sys_mutex 31833.60 653977.62 25.12 20.54 wait/synch/rwlock/innodb/index_tree_rw_lock 30833.40 1005772.81 61.29 32.62 wait/synch/rwlock/sql/LOCK_grant 17379.00 49105.60 2.69 2.83 wait/synch/mutex/innodb/lock_mutex 15958.00 27180.80 1.95 1.70 wait/synch/rwlock/innodb/btr_search_latch 15638.60 57257.60 3.64 3.66 wait/io/file/innodb/innodb_data_file 14801.00 1911868672.00 190143.75 129169.56 ...