Diagnosing MySQL Server performance issues require a careful review of the main metrics, indexing, and configuration parameter (and more). MySQL Server offers many resources to understand what is the state of the database, such as the classic:

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS;

Not to mention the amount of information provided by information_schema and performance_schema: one can get easily lost having to deal with the amount of data MySQL Server can provide users for troubleshooting. Of all the assets that are available to audit the performance of the database, one of my favorites comes from the MySQL sys Schema, namely a set of objects that helps DBAs and developers interpret data collected by the Performance Schema. sys schema objects can be used for typical tuning and diagnosis use cases.

A tool of particular interest is the sys.diagnostics procedure, which aggregates information from several sources and includes configuration parameters, output from SHOW ENGINE INNODB STATUS , and much more (read the docs). You can capture the output of this procedure by running the following lines in the mysql command line client.

TEE diag.txt;
CALL sys.diagnostics(60, 60, 'current');
NOTEE;

The script dumps diagnostic information into the diag.txt file. Open and review it, you will feed it to the next script for automated analysis. The script can be run on the primary and replica copies of a replicated topology.

Automated analysis of MySQL Server metrics

While working closely with MySQL Server installations, a few years ago I developed a Python script for automatic analysis of MySQL status, variables, metrics, and statistics. You can find it in the MyRobot repository, so clone the repo and follow the instructions in the README file.

git clone https://github.com/mortensi/myrobot.git

You can invoke it and pass the diagnostic file as follows:

python3 myrobot.py diag.txt

Analysis of the diagnostic data

The script parses the diagnostic data, loads it into internal data structures, and uses it to evaluate several basic configurations and metrics of the MySQL Server. As an example, the script:

• print the MySQL version and the uptime
• prints the number of slow queries out of the total number of questions
• indicates if the binary log is enabled (it is enabled by default in MySQL Server 8.x)
• indicates the state of the replica in a replicated topology
• assess the thread cache, if it’s sufficient, or if threads are created over and over
• check if the maximum number of connections is sufficient
• parse the InnoDB storage engine status and report if the redo log and the buffer pool are well sized
• check the theoretical amount of memory that the Server can use
• check the use of buffers and non-indexed joins
• check the open files
• check if the table cache is sufficient
• check how many temporary tables are created on the disk
• check table scans and table locking (table locking applies only to MyISAM tables)