Running events on same time
Hi. Could someone confirm in some way about running multiple events at same time. Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped." I have simulated events triggering at same time, running with delays to get all kinds of overlapping schedules, writing to same table from the event and not missed a single one. The information_schema.events tables column last_executed has - depending on scheduled time - valid values and I can see in the processlist events running at same time. Version I'm running is 10.6.10 on Linux. Tried to cross check against the MySQL documentation but couldn't find anything about this there. So what situation does the documentation refer to or it this something fixed long time ago?
kpesio--- via discuss <discuss@lists.mariadb.org> writes:
Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped."
I have simulated events triggering at same time, running with delays to get all kinds of overlapping schedules, writing to same table from
Usually in event systems, this kind of condition refers to what happens when an event is set to schedule repeatedly (say once every second), and the duration of one event is longer than the repeat interval (say 1.5 seconds). In this case, the condition means that every second event will be skipped, since the prior event trigger _of that same event_ is still running.
the event and not missed a single one. The information_schema.events tables column last_executed has - depending on scheduled time - valid values and I can see in the processlist events running at same time.
I don't know the details of the MariaDB event scheduler, but it sounds like you were testing overlap of different events. You can try setting up a single repeatedly scheduled event that has duration longer than the repeat interval (eg. using SLEEP()), and then see if the different instances of such an event overlap or if they are skipped. Hope this helps, - Kristian.
Hi! Hi! On Tue, Aug 15, 2023 at 11:24 PM kpesio--- via discuss <discuss@lists.mariadb.org> wrote:
Hi. Could someone confirm in some way about running multiple events at same time.
Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped."
Events are put in a queue, sorted by next execution time There are no skipping of events for which execution time has already expired. This can be verified by looking at: bool Event_queue::get_top_for_execution_if_time() As Kristian pointed out, if there next execution time for an event put back in queue is lower than the current time, it's next execution time will be adjusted. However older events should be executed before this one. The next execution time is calculated in Event_queue_element::compute_next_execution_time(). Sorry, but this code is a bit complex so I don't have time to go into details now. However I am reasonable sure that there will be no starving of old events. I will fix the documentation to be more clear about this Regards, Monty
Hi! On Wed, Aug 16, 2023 at 2:50 PM Michael Widenius <michael.widenius@gmail.com> wrote:
Hi!
Hi!
On Tue, Aug 15, 2023 at 11:24 PM kpesio--- via discuss <discuss@lists.mariadb.org> wrote:
Hi. Could someone confirm in some way about running multiple events at same time.
Confirmed. Here is a test case that proves this: drop table if exists t1; drop event ev1; drop event ev2; SET GLOBAL event_scheduler=ON; set @n=now(); select @n, addtime(@n,1); CREATE TABLE t1 (a INT); CREATE EVENT ev1 ON SCHEDULE AT ADDTIME(@n,2) DO INSERT INTO t1 VALUES (10+sleep(10)); show warnings; CREATE EVENT ev2 ON SCHEDULE AT ADDTIME(@n,3) DO INSERT INTO t1 VALUES (13); select sleep(12); select * from t1; The last select returns rows 10 & 13 Regards, Monty
Hi Monty and Kristian. Thanks for clarifying. We aren't looking at triggering events every second but around every 10 minutes but multiple events can start at same time and same event can overlap another running one. And tested also what happens if an event is triggered every 2 seconds and runs for over 2 seconds (with random sleep length). And it works; had over 10 of the same event processes running and they were triggered at right times. So confirmed that the same event can be started before the previous ends. Thanks! -- Kaj
Hi Monty. Saw the documentation was updated, but now I'm even more puzzled. This is the test I come up with. A bit long but if covers all situations I'm looking at. DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, a VARCHAR(20), b DATETIME ); DELIMITER // CREATE OR REPLACE PROCEDURE abc1 (IN x VARCHAR(10)) BEGIN SET @n=SECOND(NOW()); INSERT INTO t1 (a,b) VALUES (CONCAT(x,' start: ',@n),NOW()); SELECT x,SLEEP(10); INSERT INTO t1 (a,b) VALUES (CONCAT(x,' end: ',@n),NOW()); END; // CREATE OR REPLACE PROCEDURE abc2 (IN x VARCHAR(10)) BEGIN SET @n=SECOND(NOW()); INSERT INTO t1 (a,b) VALUES (CONCAT(x,' start: ',@n),NOW()); SELECT x,SLEEP(10); INSERT INTO t1 (a,b) VALUES (CONCAT(x,' end: ',@n),NOW()); END; // DROP EVENT IF EXISTS ev1; CREATE EVENT ev1 ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:00' DO CALL abc1('ev1') // DROP EVENT IF EXISTS ev2; CREATE EVENT ev2 ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:01' DO CALL abc1('ev2') // DROP EVENT IF EXISTS ev3; CREATE EVENT ev3 ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:00' DO CALL abc1('ev3') // DROP EVENT IF EXISTS ev4; CREATE EVENT ev4 ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:01' DO CALL abc2('ev4') // DELIMITER ; select sleep(20); show processlist; SELECT * FROM t1; DROP EVENT IF EXISTS ev4; DROP EVENT IF EXISTS ev3; DROP EVENT IF EXISTS ev2; DROP EVENT IF EXISTS ev1; So we got two procedures and 4 events. - ev1 and ev3 is triggered every 2 seconds (on even seconds) and calls procedure abc1 which takes 10 seconds to run. - ev2 is triggered every 2 seconds (on odd seconds) and calls same procedure abc1. - ev4 is triggered every 2 seconds (on odd seconds) and calls another procedure abc2 which also takes 10 seconds to run. The procedures logs to table when ended and started with the calling event name and an identifier (the seconds). So I got long running events trying to overlap each other and two procedures just in case events would behave differently in this case. And everything seems to work as expected. Processlist shows the different overlapping processes running (showing just relavant info). And there are same events running at same time, because they got called every 2 seconds. Id Time Info 28358 9 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28359 9 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28360 8 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28361 8 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28362 7 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28363 7 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28364 6 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28365 6 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28366 5 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28367 5 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28368 4 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28369 4 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28370 3 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28371 3 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28372 2 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28373 2 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28374 1 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28375 1 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28376 0 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) 28377 0 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE 'utf8mb4_unicode_520_ci'),SLEEP(10) And when checking table t1, we can see events starting at same time. So line (id) 1 is event3 starting at seconds :50, and it ends 10 seconds later as id 23. id a b 1 ev3 start: 50 2023-08-17 23:29:50 2 ev1 start: 50 2023-08-17 23:29:50 3 ev2 start: 51 2023-08-17 23:29:51 4 ev4 start: 51 2023-08-17 23:29:51 5 ev3 start: 52 2023-08-17 23:29:52 6 ev1 start: 52 2023-08-17 23:29:52 7 ev4 start: 53 2023-08-17 23:29:53 8 ev2 start: 53 2023-08-17 23:29:53 9 ev1 start: 54 2023-08-17 23:29:54 10 ev3 start: 54 2023-08-17 23:29:54 11 ev4 start: 55 2023-08-17 23:29:55 12 ev2 start: 55 2023-08-17 23:29:55 13 ev1 start: 56 2023-08-17 23:29:56 14 ev3 start: 56 2023-08-17 23:29:56 15 ev4 start: 57 2023-08-17 23:29:57 16 ev2 start: 57 2023-08-17 23:29:57 17 ev3 start: 58 2023-08-17 23:29:58 18 ev1 start: 58 2023-08-17 23:29:58 19 ev2 start: 59 2023-08-17 23:29:59 20 ev4 start: 59 2023-08-17 23:29:59 21 ev3 start: 0 2023-08-17 23:30:00 22 ev1 start: 0 2023-08-17 23:30:00 23 ev1 end: 50 2023-08-17 23:30:00 24 ev3 end: 50 2023-08-17 23:30:00 25 ev2 end: 51 2023-08-17 23:30:01 26 ev4 end: 51 2023-08-17 23:30:01 27 ev2 start: 1 2023-08-17 23:30:01 28 ev4 start: 1 2023-08-17 23:30:01 29 ev1 start: 2 2023-08-17 23:30:02 30 ev3 start: 2 2023-08-17 23:30:02 31 ev1 end: 52 2023-08-17 23:30:02 So multiple events at same time overlapping each other seems to be working or do I test this somehow wrong? -- Kaj
participants (3)
-
kpesio＠gmail.com -
Kristian Nielsen -
Michael Widenius