Use the following query to check actively running queries on the MySQL server. It is useful when there are DB issues.
select * from information_schema.PROCESSLIST where COMMAND <> 'sleep' order by time DESC limit 100;Use the following query to see which transactions are waiting and which transactions are blocking them:
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;Use the following query to see which queries are waiting and which queries are blocking them.
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread,
SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host,
SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port,
IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
b.trx_query AS blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESCUse the following query to find out unused indexes
select * from sys.schema_unused_indexes;SELECT
`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `object_schema`,
`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `object_name`,
`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name`
FROM
`performance_schema`.`table_io_waits_summary_by_index_usage`
WHERE
(( `table_io_waits_summary_by_index_usage`.`INDEX_NAME` IS NOT NULL)
AND ( `table_io_waits_summary_by_index_usage`.`COUNT_STAR` = 0)
AND ( `table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` <> 'mysql')
AND ( `table_io_waits_summary_by_index_usage`.`INDEX_NAME` <> 'PRIMARY'))
ORDER BY
`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA`,
`table_io_waits_summary_by_index_usage`.`OBJECT_NAME`
Use the following Query to find out duplicate indexes
select * from sys.schema_redundant_indexes;
SELECT
`redundant_keys`.`table_schema` AS `table_schema`,
`redundant_keys`.`table_name` AS `table_name`,
`redundant_keys`.`index_name` AS `redundant_index_name`,
`redundant_keys`.`index_columns` AS `redundant_index_columns`,
`redundant_keys`.`non_unique` AS `redundant_index_non_unique`,
`dominant_keys`.`index_name` AS `dominant_index_name`,
`dominant_keys`.`index_columns` AS `dominant_index_columns`,
`dominant_keys`.`non_unique` AS `dominant_index_non_unique`,
IF ((`redundant_keys`.`subpart_exists`,
OR `dominant_keys`.`subpart_exists`),
1,
0) AS `subpart_exists`,
concat('ALTER TABLE `', `redundant_keys`.`table_schema`, '`.`', `redundant_keys`.`table_name`, '` DROP INDEX `', `redundant_keys`.`index_name`, '`') AS `sql_drop_index`
FROM
(`sys`.`x$schema_flattened_keys` `redundant_keys` JOIN `sys`.`x$schema_flattened_keys` `dominant_keys` ON(((`redundant_keys`.`table_schema` = `dominant_keys`.`table_schema`) AND (`redundant_keys`.`table_name` = `dominant_keys`.`table_name`))))
WHERE
((`redundant_keys`.`index_name` <> `dominant_keys`.`index_name`)
AND (((`redundant_keys`.`index_columns` = `dominant_keys`.`index_columns`)
AND ((`redundant_keys`.`non_unique` > `dominant_keys`.`non_unique`)
OR ((`redundant_keys`.`non_unique` = `dominant_keys`.`non_unique`)
AND (IF((`redundant_keys`.`index_name` = 'PRIMARY'),'',`redundant_keys`.`index_name`) > IF((`dominant_keys`.`index_name` = 'PRIMARY'),'',`dominant_keys`.`index_name`)))))
OR ((locate(concat(`redundant_keys`.`index_columns`,','),`dominant_keys`.`index_columns`) = 1)
AND (`redundant_keys`.`non_unique` = 1))
OR ((locate(concat(`dominant_keys`.`index_columns`,','),`redundant_keys`.`index_columns`) = 1)
AND (`dominant_keys`.`non_unique` = 0))))Use the following query to find out the top 10 biggest tables in MySQL.
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;Use the following query to find out databases size
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
1 /*TABLE_SCHEMA = database() you can change database name */
/*TABLE_NAME= ''enter your table name '' you can use it if you need find out selected table*/
group by
TABLE_SCHEMA
ORDER BY
(DATA_LENGTH + INDEX_LENGTH + DATA_FREE ) DESC;Use the following query to see RECENT DEADLOCK DETECTION details
SHOW ENGINE INNODB STATUS;Use the following query to see FOREIGN KEY ERROR details
SHOW ENGINE INNODB STATUS;Use the following query to check the replication status
SHOW SLAVE STATUS;Use the following query to check event/query in Binlog file at position
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]Use the following query to get a list of master binlog files
SHOW BINARY LOGS;
SHOW MASTER LOGS;Use the following query to get current master binlog files
SHOW MASTER STATUS;Use the following query to find out RAM allocation in the Innodb_buffor_pool. It would be hange if you run it on production servers.
SELECT
SUBSTRING_INDEX(tables.name, '/', +1 ) AS schema_name,
SUBSTRING_INDEX(tables.name, '/', -1 ) AS table_name,
indexes.NAME AS index_name,
cached.N_CACHED_PAGES*(SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'innodb_page_size') AS cached_bytes
FROM
INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
INFORMATION_SCHEMA.INNODB_TABLES AS tables
WHERE
cached.INDEX_ID = indexes.INDEX_ID
AND indexes.TABLE_ID = tables.TABLE_ID;Use the following query for Monitoring Alter Execution
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;Use the following query for Monitoring InnoDB Mutex Waits
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%';Use the following query for Prepared Statement SQL Command Count
SELECT SQL_TEXT, SUM(COUNT_EXECUTE) FROM prepared_statements_instances GROUP BY SQL_TEXT;Please add queries in comment session which are useful for MySQL monitoring and debugging if I missed in the list.
Note: Please don’t add regular DDL, DML, DCL, TCL commands in the list.
