GBase 8a数据库定时任务调度功能event测试

GBase 8a数据库内部支持定时任务event功能,可以实现内部任务调度。但一般建议还是外部调度,比如通过crontab 或者客户自己的Timer功能等,数据库还是聚焦在数据存储和查询上。

注意:当前版本并没有对大规模event做支持,一般用于内部清理垃圾,做审计日志归档用,所有的event加起来,不要超过10个,因为内部的event线程数好像是10个。超过了就不会有调度了。 所以最好多用一次性的,执行完了,再创建下一个。

因为是集群,GBase 8a的定时任务event支持两种模式,本地Local和全局global(默认)。

完整语法

CREATE [DEFINER = { user | CURRENT_USER }] EVENT
[IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[global|local]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ... | EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK |
SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE
| HOUR_SECOND | MINUTE_SECOND }

创建任务表,用于记录测试结果

gbase> create table tevent(d datetime);
Query OK, 0 rows affected (Elapsed: 00:00:00.61)

创建最简单的一次性event

该event只运行一次,且马上运行。

gbase> create event test_event1 on schedule at current_timestamp do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

查看运行结果

gbase> select * from tevent;
+---------------------+
| d |
+---------------------+
| 2020-08-15 19:55:53 |
+---------------------+
1 row in set (Elapsed: 00:00:00.02)

立即运行的event可能不会运行

因为创建时间和调度时间的偏差,如上的event,有较大几率出现刚创建完,还没运行,就发现过期了,直接就被删除了,导致event根本没有被执行。请在开始运行时间做精确指定,或者延后一段时间,请参考后面的例子。

gbase> create event test_event2 on schedule at current_timestamp do insert into tevent values(now());
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.10)

gbase> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1588 | 10.0.2.202:5258 - Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

创建延迟执行的event

通过在current_timestamp增加一部分时间,来延迟执行。如有固定时间,请参考后面的定时执行。

gbase> create event test_event2 on schedule at current_timestamp+interval 10 second do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.46)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
+---------------------+
3 rows in set (Elapsed: 00:00:00.03)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
| 2021-10-23 15:53:15 |
+---------------------+
4 rows in set (Elapsed: 00:00:00.03)

创建定点运行的event

如下event,使用了绝对时间’2021-10-23 15:56:13’来执行event

gbase> create event test_event2 on schedule at '2021-10-23 15:56:13'  do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
| 2021-10-23 15:53:15 |
+---------------------+
4 rows in set (Elapsed: 00:00:00.03)

gbase> select now(),t.* from tevent t;
+---------------------+---------------------+
| now()               | d                   |
+---------------------+---------------------+
| 2021-10-23 15:56:25 | 2021-10-23 15:35:54 |
| 2021-10-23 15:56:25 | 2021-10-23 15:47:47 |
| 2021-10-23 15:56:25 | 2021-10-23 15:48:50 |
| 2021-10-23 15:56:25 | 2021-10-23 15:53:15 |
| 2021-10-23 15:56:25 | 2021-10-23 15:56:13 |
+---------------------+---------------------+
5 rows in set (Elapsed: 00:00:00.03)

查看event是否还存在

注意:如果event运行中间报错,则event不会被自动删除。

gbase> show events;
Empty set (Elapsed: 00:00:00.00)

阶段性结论

计划任务执行结果和预期相符。

如果想保留这种一次性的event们可以通过 ON COMPLETION [NOT] PRESERVE指示。否则event在执行完成后,会【自动删除】

gbase> create event test_event1 on schedule at current_timestamp on completion preserve do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2020-08-15 19:55:53 |
| 2020-08-15 20:02:17 |
+---------------------+
2 rows in set (Elapsed: 00:00:00.04)

gbase> show events;
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
| Vc           | Db                                                               | Name                                                             | Definer                                                                       | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Execute mode | character_set_client | collation_connection | Database Collation |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
| vcname000001 | testdb                                                           | test_event1                                                      | root@%                                                                        | +08:00    | ONE TIME | 2020-08-15 20:02:17 | NULL           | NULL           | NULL   | NULL | DISABLED |            1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

可以看到event被保留下来了,当然也意味着,如果你下次使用相同名字的event,你需要自行清理。

创建周期性定时任务

gbase> create event test_event_loop on schedule every 2 second ends current_timestamp+interval 10 second  do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.33)

设置了每2秒一次,为了避免死循环,我们设置了10秒后结束,且自动删除event。

执行结果


gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2020-08-15 19:55:53 |
| 2020-08-15 20:02:17 |
| 2020-08-15 20:11:12 |
| 2020-08-15 20:11:14 |
| 2020-08-15 20:11:16 |
| 2020-08-15 20:11:18 |
| 2020-08-15 20:11:20 |
| 2020-08-15 20:11:22 |
+---------------------+
8 rows in set (Elapsed: 00:00:00.02)

其它说明

global 是默认的,代表在整个集群,有多个管理节点时,只会执行一次。 而local表示每个节点各自执行,会出现并发。

参考

发表评论

您的电子邮箱地址不会被公开。