Looking For Database Performance Bottlenecks And Optimizations Using The Sys Schema In MySQL 5.7

schema_auto_increment_columns.sql

/**
* Find the amount of auto-increment "space" has been used. This may can help identify
* tables that are running out of available ID values.
*/
SELECT
	t.table_name,
	t.column_name,
	-- The highest possible ID that can be created with this data-type.
	t.max_value,
	-- The last ID created in this table.
	t.auto_increment,
	-- The amount of "ID space" that has been used-up. When this hits 100%, things go
	-- "Boom"!
	CONCAT( ( t.auto_increment_ratio * 100 ), '%' )
FROM
	sys.schema_auto_increment_columns t
WHERE
	t.table_schema = @db
;

schema_redundant_indexes.sql

/**
* Find unnecessary indexes. Having to maintain an index is expensive from both a latency
* and a storage standpoint. If we can get rid of any redundant indexes, then we can save
* space and improve performance by dropping them.
*/
SELECT
	-- The unnecessarily indexed columns.
	t.table_name,
	t.redundant_index_name,
	t.redundant_index_columns,
	-- The index that is already handling the aforementioned columns.
	t.dominant_index_name,
	-- The SQL statement you can execute in order to drop the unnecessary index.
	t.sql_drop_index
FROM
	sys.schema_redundant_indexes t
WHERE
	t.table_schema = @db
;

schema_unused_indexes.sql

/**
* Find unused indexes. Having to maintain an index is expensive from both a latency and a
* storage standpoint. If we have indexes that we never use, then we can save space and
* improve performance by dropping them.
* 
* CAUTION: Unlike "redundant indexes", unused indexes are not deterministic. Meaning,
* this set of indexes is based on access patterns of the application. If your application
* database has not been running for a sufficient amount of time, then this list may not
* be indicative of the full breadth of index utilization.
*/
SELECT
	t.object_name,
	t.index_name
FROM
	sys.schema_unused_indexes t
WHERE
	t.object_schema = @db
;

snippet-1.sql

SELECT
	MyTable.id,
	MyTable.name
FROM
	my_table AS MyTable
;

x$schema_index_statistics.sql

/**
* Look at access and update performance for indexes.
*/
SELECT
	t.table_name,
	t.index_name,
	t.rows_selected,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.select_latency ) AS select_latency,
	t.rows_inserted,
	sys.format_time( t.insert_latency ) AS insert_latency,
	t.rows_updated,
	sys.format_time( t.update_latency ) AS update_latency,
	t.rows_deleted,
	sys.format_time( t.delete_latency ) AS delete_latency
FROM
	sys.x$schema_index_statistics t
WHERE
	t.table_schema = @db
ORDER BY
	t.select_latency DESC
;

x$schema_table_statistics.sql

/**
* Look at general latency statistics (how much overall time it takes) to perform various
* CRUD operations (Create, Read, Update, Delete) on your database tables.
*/
SELECT
	t.table_name,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	-- Number of rows read and total latency for read operations.
	t.rows_fetched,
	sys.format_time( t.fetch_latency ) AS fetch_latency,
	-- Number of rows inserted and total latency for insert operations.
	t.rows_inserted,
	sys.format_time( t.insert_latency ) AS insert_latency,
	-- Number of rows updated and total latency for update operations.
	t.rows_updated,
	sys.format_time( t.update_latency ) AS update_latency,
	-- Number of rows deleted and total latency for delete operations.
	t.rows_deleted,
	sys.format_time( t.delete_latency ) AS delete_latency
FROM
	sys.x$schema_table_statistics t
WHERE
	t.table_schema = @db
ORDER BY
	t.total_latency DESC
;

x$statement_analysis.sql

/**
* Find SQL queries that have the most latency (how much overall time it takes).
*/
SELECT
	-- The raw SQL statement being executed.
	t.query,
	t.exec_count,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	sys.format_time( t.max_latency ) AS max_latency,
	sys.format_time( t.avg_latency ) AS avg_latency,
	sys.format_time( t.lock_latency ) AS lock_latency,
	t.rows_sent,
	t.rows_sent_avg,
	t.rows_examined,
	-- The total number of internal in-memory temporary tables created by occurrences of
	-- this query.
	t.tmp_tables,
	-- The total number of internal on-disk temporary tables created by occurrences of
	-- this query.
	t.tmp_disk_tables,
	t.rows_sorted
FROM
	sys.x$statement_analysis t
WHERE
	t.db = @db
ORDER BY
	t.total_latency DESC
;

x$statements_with_full_table_scans.sql

/**
* Find SQL queries that perform full-table scans. These are queries in which all the rows
* in the table need to be read-in during query execution.
* 
* NOTE: For us, this mostly includes queries that involve "look up" tables, like lists of
* permission-types or status-types.
*/
SELECT
	t.query,
	t.exec_count,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	t.no_index_used_count,
	t.no_index_used_pct,
	t.rows_sent,
	t.rows_examined,
	t.rows_sent_avg,
	t.rows_examined_avg
FROM
	sys.x$statements_with_full_table_scans t
WHERE
	t.db = @db
ORDER BY
	t.no_index_used_pct DESC,
	t.no_index_used_count DESC
;

x$statements_with_runtimes_in_95th_percentile.sql

/**
* Find SQL queries with the highest latency in the 95th percentile. This means, that 95%
* of the query instances fall under the given latencies.
*/
SELECT
	t.query,
	t.exec_count,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	sys.format_time( t.max_latency ) AS max_latency,
	sys.format_time( t.avg_latency ) AS avg_latency,
	t.rows_sent,
	t.rows_sent_avg,
	t.rows_examined,
	t.rows_examined_avg
FROM
	sys.x$statements_with_runtimes_in_95th_percentile t
WHERE
	t.db = @db
ORDER BY
	t.avg_latency DESC
;

x$statements_with_sorting.sql

/**
* Find SQL queries that perform sorts. Sorting in the DB can have a negative affect on
* performance. Perhaps these queries can push sorting down into the application layer.
*/
SELECT
	t.query,
	t.exec_count,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	t.sorts_using_scans,
	t.sort_using_range,
	t.rows_sorted,
	t.avg_rows_sorted
FROM
	sys.x$statements_with_sorting t
WHERE
	t.db = @db
ORDER BY
	t.total_latency DESC
;

x$statements_with_temp_tables.sql

/**
* Find SQL queries that create in-memory or on-disk TEMP tables in order to calculate the
* results. Temp tables can put a lot of load on the database.
*/
SELECT
	t.query,
	t.exec_count,
	-- Latency in the "x$" tables are reported as picoseconds. This built-in function
	-- formats the raw time into human-friendly values, like "3.5 s" and "56.07 ms".
	sys.format_time( t.total_latency ) AS total_latency,
	-- The total number of internal in-memory temporary tables created by occurrences of
	-- this query.
	t.memory_tmp_tables,
	-- The total number of internal on-disk temporary tables created by occurrences of
	-- this query.
	t.disk_tmp_tables,
	t.avg_tmp_tables_per_query,
	t.tmp_tables_to_disk_pct
FROM
	sys.x$statements_with_temp_tables t
WHERE
	t.db = @db
ORDER BY
	t.total_latency DESC
;