0

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?

8
  • Yes. 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.
    – Akina
    Commented Feb 18, 2020 at 15:55
  • @Akina Ok, but besides implicit limitations (memory, pools etc), there is no direct limitation of the number of currently running events? I cannot set some variable like "max_event_runnings = 100" and be sure no more than 100 events would be run at the same time?
    – Xenos
    Commented Feb 18, 2020 at 16:21
  • there is no direct limitation of the number of currently running events? 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...
    – Akina
    Commented Feb 18, 2020 at 16:39
  • Alrght, thanks! you may make an answer from it if you want me to accept it.
    – Xenos
    Commented Feb 19, 2020 at 8:51
  • I'll tell you what I tell cron users who run a process that may take longer than the cycle time: "Don't". Events and Crons should be much faster than the frequency given. Else don't use Event or Cron, but simply have a process that loops forever.
    – Rick James
    Commented Feb 23, 2020 at 19:24

1 Answer 1

1

the running events are not part of the connections limit. Then, what is the limit? Is there one?

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.

there is no direct limitation of the number of currently running events?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.