performanceeventsstored-proceduresmysql-8.0mysql-slow-query-log

MySQL 8.0 Stored Procedure Showing as Slow Query Due to Total Execution Time


I have a stored procedure that runs daily to archive data from a large table. It moves data in batches of 10,000 rows from table_name to table_archive_table, and deletes the moved rows from table_name.

Here is a simplified version:

CREATE PROCEDURE `sp_archive_table`(...)
BEGIN
    CREATE TABLE IF NOT EXISTS table_archive_table LIKE table_name;

    archive_loop: WHILE NOT done DO
        INSERT IGNORE INTO table_archive_table SELECT * FROM table_name ... LIMIT 10000;
        DELETE FROM table_name WHERE ... LIMIT 10000;
        DO SLEEP(1);
    END WHILE;
END;

In MySQL 5.7, this procedure runs quickly. Each INSERT and DELETE takes less than 1 second, and the slow query log is clean.

After upgrade to MySQL 8.0, the same procedure now appears in slow query logs with total execution time (e.g., 100 seconds for the entire procedure), even though each individual INSERT/DELETE is still fast (<1 second)

# Time: 2025-05-25T18:36:07.464192Z
# User@Host: user @ % [%]  Id: 815074
# Query_time: 366.560835  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 41864657
use db;
SET timestamp=1748198167;
call sp_archive_table(....);

It seems MySQL 8.0 records the total time from CALL sp_archive_table until the whole procedure finishes, and logs it as a slow query if the total time is longer than long_query_time.

Info:

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.13 sec)

mysql> show variables like '%_slow_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| log_slow_admin_statements   | OFF   |
| log_slow_extra              | OFF   |
| log_slow_replica_statements | OFF   |
| log_slow_slave_statements   | OFF   |
+-----------------------------+-------+
4 rows in set (0.14 sec)

Thanks in advance!


Solution

  • Yes, you can avoid logging the SLOW procedure with:
    SET long_query_time=500;call sp_archive_table(....);

    and this will not disturb the GLOBAL setting of long_query_time as you need it.