南大通用GBase 8a的后台(daemon)事件调度进程event_scheduler介绍

GBase 8a支持数据库内的定时任务事件,其调度程序由数据库的daemon进程负责,本文简单介绍相关参数 event_scheduler 使用方法,以及从数据库日志等查看事件的相关信息。

参考

如下是和用户编写的事件内容有关的参考

GBase 8a数据库定时任务调度功能event测试
GBase 8a集群暂停定时任务event disable

样例

如下Id=1的进程就是后台的事件调度线程,注意其Command列是Daemon。

gbase> show processlist;
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
| Id | User            | Host             | db   | Command | Time | State                  | Info             |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost        | NULL | Daemon  |    5 | Waiting for event lock | NULL             |
|  6 | gbase           | 10.0.2.115:39820 | NULL | Sleep   |    4 |                        | NULL             |
|  7 | root            | localhost        | NULL | Query   |    0 | NULL                   | show processlist |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)

事件调度功能的查看、启用和关闭

通过event_scheduler参数打开或关闭事件调度功能. 注意该参数是集群一个调度节点执行,所有调度节点同时生效。

查看当前事件调度

通过show命令可以查看event_scheduler的当前值:ON=打开, OFF=关闭。

gbase> show variables like '%scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (Elapsed: 00:00:00.01)

关闭事件调度功能

通过设置该参数为0,可以关闭事件调度功能。

gbase> set global event_scheduler=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.89)

gbase> show processlist;
+----+------+------------------+------+---------+------+-------+------------------+
| Id | User | Host             | db   | Command | Time | State | Info             |
+----+------+------------------+------+---------+------+-------+------------------+
|  7 | root | localhost        | NULL | Query   |    0 | NULL  | show processlist |
|  9 | root | 10.0.2.101:51572 | NULL | Sleep   |    3 |       | NULL             |
+----+------+------------------+------+---------+------+-------+------------------+
2 rows in set (Elapsed: 00:00:00.00)

启用事件调动功能

通过设置event_scheduler参数为1,启用事件调度功能。

gbase> set global event_scheduler=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> show processlist;
+----+-----------------+------------------+--------+---------+------+-----------------------------+------------------+
| Id | User            | Host             | db     | Command | Time | State                       | Info             |
+----+-----------------+------------------+--------+---------+------+-----------------------------+------------------+
|  7 | root            | localhost        | testdb | Query   |    0 | NULL                        | show processlist |
| 48 | event_scheduler | localhost        | NULL   | Daemon  |    1 | Waiting for next activation | NULL             |
| 49 | root            | 10.0.2.101:51978 | NULL   | Sleep   |    3 |                             | NULL             |
+----+-----------------+------------------+--------+---------+------+-----------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)

注意事件调度进程的ID不是固定的,如果数据库开启了定时任务(默认),且数据库刚启动,那么会使用第一个ID=1。

拿到事件调度任务的线程号

通过show detail命令,可以看到ID号和线程号。如下图的Id=1,Tid=8460。在数据库内部,还有一个线程也是负责这个,只是在show processlist里没有显示,但在后面的pstack信息里能看到。

gbase> show detail processlist;
+----+------+-----------------+-----------+------+---------+------+------------------------+------+------+-------------------------+
| Id | Tid  | User            | Host      | db   | Command | Time | State                  | Lock | Wait | Info                    |
+----+------+-----------------+-----------+------+---------+------+------------------------+------+------+-------------------------+
|  1 | 8460 | event_scheduler | localhost | NULL | Daemon  |   55 | Waiting for event lock | NULL | NULL | NULL                    |
|  5 | 8518 | root            | localhost | NULL | Query   |    0 | NULL                   | NULL | NULL | show detail processlist |
+----+------+-----------------+-----------+------+---------+------+------------------------+------+------+-------------------------+
2 rows in set (Elapsed: 00:00:00.00)

数据库启停system.log

服务首次启动时,会输出事件调度的日志,如下例子显示ID是2, 和前一节介绍的ID=1对不上,但实际上2个都是内部调度进程。

220330 10:28:28 [Note] Express is ready for connections.
socket: '/tmp/gcluster_5258.sock' port: 5258 283f8db1
220330 10:28:28 [Note] Event Scheduler: scheduler thread started with id 2
CBO load statistics begin...
CBO load statistics OK.
CBO load statistics used time 00:00:00.044.

中间参数启停时的system日志输出

可以看到关闭参数时,kill掉了线程Id 2和1。 启动时,有2个新线程Id为 47和48。

220330 10:29:05 [Note] Event Scheduler: Killing the scheduler thread, thread id 2
220330 10:29:05 [Note] Event Scheduler: Waiting for the scheduler thread to reply
220330 10:29:05 [Note] Event Scheduler: Stopped
220330 10:29:05 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
220330 10:29:05 [Note] Event Scheduler: Waiting for the scheduler thread to reply
220330 10:29:05 [Note] Event Scheduler: Stopped
220330 13:34:44 [Note] Event Scheduler: scheduler thread started with id 47
220330 13:34:46 [Note] Event Scheduler: scheduler thread started with id 48

查看pstack查看事件调度线程信息

通过pstack `pidof gclusterd` 拿到线程信息。查看LWP 8640的事件调度线程。同时,还有个8641的内部线程,一致在查询当前符合条件要求的事件信息:get_top_for_execution_if_time。 这与前面分析的有2个事件调度线程是一致的。

Thread 16 (Thread 0x7f8fedcb8700 (LWP 8460)):
#0  0x00007f90485b166d in nanosleep () from /lib64/libc.so.6
#1  0x00007f90485b1504 in sleep () from /lib64/libc.so.6
#2  0x0000000000cf8afa in Event_gc_scheduler::run(THD*) ()
#3  0x0000000000cf6d8e in event_scheduler_thread ()
#4  0x00007f904d3f6dc5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007f90485ea73d in clone () from /lib64/libc.so.6
。。。。
Thread 14 (Thread 0x7f8feccb6700 (LWP 8461)):
#0  0x00007f904d3faa82 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x0000000000cfe58e in Event_queue::get_top_for_execution_if_time(THD*, Event_queue_element_for_exec**) ()
#2  0x0000000000cf7bd3 in Event_scheduler::run_internal(THD*) ()
#3  0x0000000000cf7ca6 in Event_scheduler::run(THD*) ()
#4  0x0000000000cf6d8e in event_scheduler_thread ()
#5  0x00007f904d3f6dc5 in start_thread () from /lib64/libpthread.so.0
#6  0x00007f90485ea73d in clone () from /lib64/libc.so.6

事件执行日志

创建事件

参考 GBase 8a数据库定时任务调度功能event测试, 创建一个10秒后执行的事件,然后看相关日志输出

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

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

查看事件当前状态

通过show events获得当前库所有event的状态信息,我们关注test_event的Status部分,其从创建后的ENABLED转变成执行完成的DISABLED,最后该Event由于只执行一次,被删除了。

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_event                                                       | root@%                                                                                                                                                                                            | +08:00    | ONE TIME | 2022-03-30 13:53:00 | NULL           | NULL           | NULL   | NULL | ENABLED |            1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+--------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

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_event                                                       | root@%                                                                                                                                                                                            | +08:00    | ONE TIME | 2022-03-30 13:53:00 | NULL           | NULL           | NULL   | NULL | DISABLED |            1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.01)

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

数据库express日志

如下是gcluster的express.log的对应事件部分,可以看到在13:52:50创建了Event(testdb.test_event), 在13:53:00s删除了该event。

2022-03-30 13:52:50.278 [DDL][WARN ][S:7][Q:64]<GetValidTid|1090>:get table id 0 for : vc00001.testdb.test_event
2022-03-30 13:53:00.455 [DDL][WARN ][S:62][Q:74]<ProcessDDLResultForVc|5728>:.test_event's tableId is 0.

查看执行结果

tevent表里记录了执行事件的结果。

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2022-03-30 13:53:00 |
+---------------------+
1 row in set (Elapsed: 00:00:00.02)

多个调度节点,哪个当前为Master

因为事件支持全局的,所以必须只能有一个master节点,否则多个节点同时发起全局事件,将造成结果错误。

通过 gcadmin showlock能看到哪个节点拿到了event的master锁。 如下10.0.2.115节点拿到了锁。

[gbase@gbase_rh7_001 ~]$ gcadmin showlock
 +==================================================================================================+
 |                                          GCLUSTER LOCK                                           |
 +==================================================================================================+
 +---------------------------------------------+----------+--------------+--------------+------+----+
 |                  Lock name                  |  owner   |   content    | create time  |locked|type|
 +---------------------------------------------+----------+--------------+--------------+------+----+
 |                gc-event-lock                |10.0.2.115|global master |20220330175200| TRUE | E  |
 +---------------------------------------------+----------+--------------+--------------+------+----+
 |                gc-event-lock                |10.0.2.101|global master |20220330175207|FALSE | E  |
。。。。。。。

总结

GBase 8a可以通过event_scheduler参数来关闭或启动全集群的事件调度功能。每个事件的创建和销毁都会在【主调度】节点的express.log留下日志信息。

如果只是部分事件的停用,可以参考 GBase 8a集群暂停定时任务event disable, 不需要将整个集群调度都停用了。