DBeaver as a MySQL Workbench Replacement : Performance Queries

— -MySQL Workbench Performance Reports

— –From Performance / Performance Reports

— –(Administration Tab → Performance Section → Performance Reports)

— ================================================================================

— MEMORY

— ================================================================================

— Buffer pool

SHOW STATUS LIKE ‘Innodb_buffer_pool_bytes%’;

— Top memory by event

select * from sys.`x$memory_global_by_current_bytes`;

— Top memory by user

SELECT * FROM sys.`x$memory_by_user_by_current_bytes`;

— Top memory by host

select * from sys.`x$memory_by_host_by_current_bytes`;

— Top memory by thread

select * from sys.`x$memory_by_thread_by_current_bytes`;

— ================================================================================

— HOT SPOTS FOR I/O

— ================================================================================

— Top file I/O activity report

select * from sys.`x$io_global_by_file_by_bytes`;

— Top I/O file by time

select * from sys.`x$io_global_by_file_by_latency`;

— Top I/O by event category

select * from sys.`x$io_global_by_wait_by_bytes`;

— Top I/O in time by event category

select * from sys.`x$io_global_by_wait_by_latency`;

— Top I/O time by user thread

select * from sys.`x$io_by_thread_by_latency`;

— ================================================================================

— HIGH-COST SQL STATEMENTS

— ================================================================================

— Statement Analysis

select * from sys.`x$statement_analysis`;

— Statements in highest 5 percent by runtime

select * from sys.`x$statements_with_runtimes_in_95th_percentile`;

— Using temp tables

select * from sys.`statements_with_temp_tables`;

— With sorting

select * from sys.`statements_with_sorting`;

— Full table scans

select * from sys.`statements_with_full_table_scans`;

— Errors or Warnings

select * from sys.`statements_with_errors_or_warnings`;

— ================================================================================

— DATABASE SCHEMA STATISTICS

— ================================================================================

— Schema Object Overview (High Overhead)

select * from sys.`schema_object_overview`;

— Schema index statistics

select * from sys.`x$schema_index_statistics`;

— Schema table statistics

select * from sys.`x$schema_table_statistics`;

— Schema table statistics (with InnoDB Buffer)

select * from sys.`x$schema_table_statistics_with_buffer`;

— Tables with full table scans

select * from sys.`schema_tables_with_full_table_scans`;

— Unused indexes

select * from sys.`schema_unused_indexes`;

— ================================================================================

— WAIT EVENT TIMES EXPERT

— ================================================================================

— Global waits by time

select * from sys.`x$waits_global_by_latency`;

— Waits by user by time

select * from sys.`x$waits_by_user_by_latency`;

— Wait Classes by time

select * from sys.`x$wait_classes_global_by_latency`;

— Wait Classes by Average Time

select * from sys.`x$wait_classes_global_by_avg_latency`;

— ================================================================================

— INNODB STATISTICS

— ================================================================================

— InnoDB Buffer Stats by schema

select * from sys.`x$innodb_buffer_stats_by_schema`;

— InnoDB Buffer Stats by table

select * from sys.`x$innodb_buffer_stats_by_table`;

— =============================================================================

— USER RESOURCE USE

— ================================================================================

— Overview

select * from sys.`x$user_summary`;

— I/O Statistics

select * from sys.`x$user_summary_by_file_io_type`;

SELECT * FROM sys.`x$user_summary_by_statement_type`;

— ============================ END ============================================