Proactive Monitoring and Anomaly Detection in MySQL Server Performance

Empower your MySQL server’s resilience and efficiency by embracing proactive performance monitoring. The ‘CheckPerformanceAnomalies’ stored procedure acts as your vigilant watchdog, meticulously scanning through critical performance metrics and flagging anomalies, ensuring that you stay one step ahead of potential issues. Elevate your database management game; transform challenges into opportunities for optimization, and guarantee seamless performance under all circumstances.

MinervaDB MySQL Performance Engineering

Creating a stored procedure for MySQL Server Performance anomaly detection and reporting requires a comprehensive understanding of MySQL’s performance metrics and system status. It involves monitoring various variables and status indicators to identify anomalies.

Here’s an example of a stored procedure that inspects specific performance indicators and records a report into a table if it spots any anomalies. This scenario will examine the Threads_connectedThreads_running, and Innodb_row_lock_time_avg variables. However, you can expand this to include any other relevant variables.

Begin by creating a table to keep the anomaly reports:

CREATE TABLE AnomalyReports (
id INT AUTO_INCREMENT PRIMARY KEY,
anomaly_time DATETIME DEFAULT CURRENT_TIMESTAMP,
description TEXT
);

Next, create the stored procedure:

DELIMITER //

CREATE PROCEDURE CheckPerformanceAnomalies()
BEGIN
DECLARE threads_connected INT;
DECLARE threads_running INT;
DECLARE innodb_row_lock_time_avg INT;
DECLARE threshold_threads_connected INT DEFAULT 100; — set your own threshold
DECLARE threshold_threads_running INT DEFAULT 20; — set your own threshold
DECLARE threshold_innodb_row_lock_time_avg INT DEFAULT 300; — set your own threshold (in milliseconds)

— Get the current status
SELECT VARIABLE_VALUE INTO threads_connected
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_connected’;

SELECT VARIABLE_VALUE INTO threads_running
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_running’;

SELECT VARIABLE_VALUE INTO innodb_row_lock_time_avg
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_row_lock_time_avg’;

— Check for anomalies
IF threads_connected > threshold_threads_connected THEN
INSERT INTO AnomalyReports (description)
VALUES (CONCAT(‘High number of connected threads: ‘, threads_connected));
END IF;

IF threads_running > threshold_threads_running THEN
INSERT INTO AnomalyReports (description)
VALUES (CONCAT(‘High number of running threads: ‘, threads_running));
END IF;

IF innodb_row_lock_time_avg > threshold_innodb_row_lock_time_avg THEN
INSERT INTO AnomalyReports (description)
VALUES (CONCAT(‘High average InnoDB row lock time: ‘, innodb_row_lock_time_avg, ‘ ms’));
END IF;

END //
DELIMITER ;

In this stored procedure, we extract the values of Threads_connected, Threads_running, and Innodb_row_lock_time_avg from the performance_schema.global_status table. We then compare these values to predefined thresholds. If any value exceeds its respective threshold, we insert a record into the AnomalyReports table.

You can call this procedure periodically to check for anomalies. For example:

CALL CheckPerformanceAnomalies();

Please note that the thresholds in this example (100 for Threads_connected, 20 for Threads_running, and 300 ms for Innodb_row_lock_time_avg) are arbitrary. They should be adjusted according to the normal operating parameters of your specific MySQL instance and workload. This procedure also assumes that the MySQL Performance Schema is enabled and configured to collect necessary metrics.

About Shiv Iyer 452 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.