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, 不需要将整个集群调度都停用了。