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> show events;
Empty set (Elapsed: 00:00:00.00)

阶段性结论

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

如果想保留这种一次性的event们可以通过 ON COMPLETION [NOT] PRESERVE指示

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表示每个节点各自执行,会出现并发。

参考

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注