How to use triggered alarms in MySQL to monitor database performance?

How to use triggered alarms to monitor database performance in MySQL?

In daily operation and maintenance work, it is crucial to ensure the normal operation of the database. Performance monitoring is an effective means to detect and resolve potential problems in a timely manner. This article will introduce how to use triggers in MySQL to monitor database performance and trigger alarms when anomalies are discovered.

1. Create a performance monitoring table

We first need to create a table for recording performance monitoring information. This table can contain the following fields: monitoring time, database name, query statement, execution time, number of returned rows, etc. The sample code is as follows:

CREATE TABLE performance_monitor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    monitor_time DATETIME NOT NULL,
    database_name VARCHAR(50) NOT NULL,
    query_text TEXT,
    execution_time INT,
    row_count INT
);

2. Create a trigger

Next, we can create a trigger to insert relevant information into the performance monitoring table every time a query statement is executed. . Triggers can monitor the execution of SELECT, INSERT, UPDATE, and DELETE statements. The following takes the SELECT statement as an example to demonstrate how to create a trigger.

DELIMITER $$
CREATE TRIGGER monitor_select_query
AFTER SELECT ON your_table
FOR EACH ROW
BEGIN
    DECLARE execution_time INT;
    DECLARE start_time DATETIME;
    DECLARE end_time DATETIME;
    
    SET start_time = NOW();
    
    -- 执行查询语句
    SELECT * FROM your_table;
    
    SET end_time = NOW();
    SET execution_time = TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
    
    -- 将性能监控信息插入表中
    INSERT INTO performance_monitor (monitor_time, database_name, query_text, execution_time, row_count)
    VALUES (NOW(), DATABASE(), 'SELECT * FROM your_table', execution_time, (SELECT COUNT(*) FROM your_table));
END$$
DELIMITER ;

Through the above code, every time a SELECT statement is executed, the trigger will automatically record relevant information and insert it into the performance monitoring table.

3. Trigger the alarm mechanism

Once performance abnormalities are monitored, we need to trigger the alarm mechanism to promptly notify the operation and maintenance personnel. Here we can use events in MySQL to achieve this. The following is a sample code for detecting query execution time exceeding 500 milliseconds in the performance_monitor table and sending an email alert.

DELIMITER $$
CREATE EVENT check_performance_event
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    DECLARE execution_time INT;
    
    -- 查询性能监控表中的数据
    SELECT execution_time INTO execution_time
    FROM performance_monitor
    WHERE execution_time > 500;
    
    -- 发送邮件报警
    IF execution_time IS NOT NULL THEN
        -- 发送邮件的代码替换成实际的邮件报警逻辑
        SELECT CONCAT('执行时间超过500毫秒的查询数量：', COUNT(*)) INTO @msg
        FROM performance_monitor
        WHERE execution_time > 500;
        
        SET @subject = 'MySQL性能报警';
        SET @recipients = 'admin@example.com';
        SET @body = CONCAT('性能监控发现以下问题：', @msg);
        
        CALL send_email(@subject, @recipients, @body);
    END IF;
END$$
DELIMITER ;

The above code will create an event, check the performance monitoring table every minute for queries that take more than 500 milliseconds to execute, and send an email alert.

By creating triggers and events, we can easily implement database performance monitoring and alarm functions. When performance abnormalities are monitored, an alarm is issued in a timely manner, which helps to quickly respond and solve the problem and ensure the normal operation of the database.

The above is the detailed content of How to use triggered alarms in MySQL to monitor database performance?. For more information, please follow other related articles on the PHP Chinese website!