DBeaver as a MySQL Workbench Replacement : Performance Queries

 "Hey, Dave! "Is there any way to get the old MySQL Workbench performance information from  DBeaver?"  An old acquaintance asked me this question this morning. The queries running without an index and the high-cost SQL statement information were what was wanted. 

Yup. The good news is that all that information on the instance's performance is all SQL. And that SQL is pretty simple and runs on the MySQL server.  You can grab a copy below of  MySQLWorkbenchPerformanceQuries4DBeaver.sql or download from https://github.com/davestokes/HandySQL

For those not in the know, these are short scripts with varying levels of utility for reporting information about a MySQL instance. I put them into a single .sql file to help diagnose trouble tickets. And you can run all these queries at once in DBeaver with ATL + X.

For those new to MySQL administration, save a copy somewhere safe because someday you may need to know about memory, I/O hotspots, or buffer stats. 

-- -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 ============================================

Comments

Post a Comment

Popular posts from this blog

Migrating From MySQL To PostgreSQL in Five Simple Steps

Image
There are many reasons you may want to migrate from MySQL to PostgreSQL, which we will skip over for brevity.  I see many questions about moving tables and data, and sadly, the answers range from sloppy to incredibly complicated.  Each database migration is unique, as issues range from heavily relying on a vendor's feature to extremely complex schemas that require attention to minute detail. Step 1 This may seem obvious, but the first step is access to the original 'source' database. You will need FULL admin access to the database.  If you do not have full access for security, administrative, or other reasons, you will most likely not receive all the desired data.  We will use DBeaver Enterprise to connect to the source database. We need to choose a MySQL database driver Configure the connection We will be porting the World database. In this example, we will port the MySQL World database. This dataset has been used for decades in MySQL documentation, training, and ex...
Read more

Village SQL - A MySQL Fork With Easy Extensions

 I was happy to see a new MySQL fork appear. Village SQL  bills itself as 'A drop-in replacement for MySQL with extensions for the agentic AI era'. Mainstream Oracle MySQL does have Plug-ins, but I have found them less easy to develop and use than other implementations, such as PostgreSQL extensions. So how does it work? Head over to  https://github.com/villagesql/villagesql-server and follow the directions to clone and build the code. You will see detailed instructions for Linux and Mac builds. I followed the directions, let the make run overnight (slow hampsters in my testbed Ubuntu laptop).   The instructions were complete and easy to follow. First Run $ ~/build/villagesql/bin/mysql -u root -h 127.0.0.1 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.4.6-villagesql0.0.1-dev Source distribution Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corp...
Read more

Do You Need An AI Assistant?

Image
 Artificial Intelligence has been over hyped the last several years. But one of the bright, shining spots for AI is acting as an assistant. a super-powerful IDE, when working with a database. Example You want to find your top three customers in the Sakila database. This database has been used for decades in the MySQL world. As your question in English (or another language) The '@ai' directs the rest of the prompt to the DBeaver Artificial Intelligence Assistant See the generated SQL Here is the generated Structured Query Language And get the results. Now we have the top three customers without having to write any SQL directly Fixing Mistakes Occasionally, you may 'fat finger' a query.  I will take the query from above and change first_name to just first .   SELECT c . customer_id , c . first , c . last_name , SUM ( p . amount ) AS total_revenue FROM customer AS c JOIN payment AS p ON c . customer_id = p . customer_id GROUP BY ...
Read more