Is there a limit to how many "instance" of a scheduled event MySQL can run? I have set this up:
CREATE TABLE IF NOT EXISTS t (c1 INT UNSIGNED NOT NULL) ENGINE=INNODB;
SELECT * FROM t;
DELIMITER $$
DROP EVENT IF EXISTS e_second;
CREATE EVENT e_second ON SCHEDULE EVERY 1 SECOND
DO BEGIN
INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
SELECT SLEEP(1000);
INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
END$$
DELIMITER ;
SELECT * FROM t;
SHOW PROCESSLIST;
SHOW VARIABLES LIKE '%connect%';
This creates a dumb event started every second, that runs for 1000 seconds. In my query log, I indeed have:
2020-02-18T15:47:00.289050Z 1016 Query INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430801Z 1216 Query INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430950Z 1216 Query SELECT SLEEP(200)
2020-02-18T15:47:01.290518Z 1017 Query INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:01.430943Z 1217 Query INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
...
So the event is run every second, and hangs (on purpose). Now, the processlist shows (after a while):
"559" "root-heidisql" "127.0.0.1" "test_sql" "Connect" "702" "User sleep" "SELECT SLEEP(1000)"
"560" "root-heidisql" "127.0.0.1" "test_sql" "Connect" "701" "User sleep" "SELECT SLEEP(1000)"
"561" "root-heidisql" "127.0.0.1" "test_sql" "Connect" "700" "User sleep" "SELECT SLEEP(1000)"
"562" "root-heidisql" "127.0.0.1" "test_sql" "Connect" "699" "User sleep" "SELECT SLEEP(1000)"
"563" "root-heidisql" "127.0.0.1" "test_sql" "Connect" "698" "User sleep" "SELECT SLEEP(1000)"
...
So I have about 528 rows in this processlist now. But, my connections limit is 151
as shown by showing variables:
"max_connections" "151"
So clearly, the running events are not part of the connections limit. Then, what is the limit? Is there one?
It feels to me that at some point, some mysql queue/list/whatever will be full of the pending events… Is this case documented somewhere?
Best Answer
Connection limit means outer connections, event scheduler is not counted. But there is a lot of another limited resources - limited by some settings or by some physical conditions. And it is too problematic to find what resource limits the server.
Yes, such limitation not exists. MySQL treates an event as a trigger which fires on a timer event - and there is no limitation on the number of triggers in the system.
In general the event procedure must check does its previous instance have already finished its work... by using some flag field/value in a service table, for example, or by some another mark.