什么是有趣events_statements_current表

在GreatSQL中，PFS下有一张内存表: events_statements_current，表问看到这个名称"xxx_current"，有趣小白如我可能会认为这张表中的表问数据就是当前系统的活跃(active)语句。该表的有趣描述如下(有部分省略):复制mysql> desc events_statements_current;+-------------------------+------------------------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------------------+------------------------------------------------+------+-----+---------+-------+| THREAD_ID | bigint unsigned | NO | PRI | NULL | || EVENT_ID | bigint unsigned | NO | PRI | NULL | || END_EVENT_ID | bigint unsigned | YES | | NULL | || EVENT_NAME | varchar(128) | NO | | NULL | || SOURCE | varchar(64) | YES | | NULL | || TIMER_START | bigint unsigned | YES | | NULL | || TIMER_END | bigint unsigned | YES | | NULL | || TIMER_WAIT | bigint unsigned | YES | | NULL | || LOCK_TIME | bigint unsigned | NO | | NULL | || SQL_TEXT | longtext | YES | | NULL | || DIGEST | varchar(64) | YES | | NULL | || DIGEST_TEXT | longtext | YES | | NULL | || CURRENT_SCHEMA | varchar(64) | YES | | NULL | |

...

| MYSQL_ERRNO | int | YES | | NULL | || RETURNED_SQLSTATE | varchar(5) | YES | | NULL | || MESSAGE_TEXT | varchar(128) | YES | | NULL | |

...

+-------------------------+------------------------------------------------+------+-----+---------+-------+44 rows in set (0.01 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.

直接使用当前语句表的问题

直接做个小实验，终端1：

复制SELECT sleep(1);1.

终端2：

复制USE performance_schema;-- 查看最近用户语句SELECT s.thread_id,表问 s.sql_text FROM events_statements_current s,

threads t

WHERE s.thread_id = t.thread_id AND t.type = FOREGROUND;1.2.3.4.5.

输出:

复制mysql> SELECT s.thread_id, s.sql_text FROM events_statements_current s, threads t WHERE s.thread_id = t.thread_id AND t.type = FOREGROUND;+-----------+--------------------------------------------------------------------------------------------------------------------------------------+| thread_id | sql_text |+-----------+--------------------------------------------------------------------------------------------------------------------------------------+| 58 | select sleep(1) || 1849 | SELECT s.thread_id, s.sql_text FROM events_statements_current s, threads t WHERE s.thread_id = t.thread_id AND t.type = FOREGROUND |+-----------+--------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)1.2.3.4.5.6.7.8.

反复执行上述语句，发现终端1当前执行的有趣语句都是WordPress模板：SELECT sleep(1)，看起来有点像BUG呀 。表问

真的有趣是这样吗? 来细看一下文档的定义：

The events_statements_current table contains current statement events. The table stores one row per thread showing the current status of the threads most recent monitored statement event, so there is no system variable for configuring the table size.

发现了一些关键信息: "showing the current status of the threads most recent monitored statement event"，也就是表问说：保存的是线程最近被检测到的语句事件，并非当前正在处于执行状态的有趣语句

获取当前活跃的站群服务器语句方法

为了了解这个问题的解决方案，来看看具体实现的表问代码：

复制void pfs_end_statement_vc(PSI_statement_locker *locker, void *stmt_da) { PSI_statement_locker_state *state = reinterpret_cast<PSI_statement_locker_state *>(locker);

...

if (state->m_discarded) { return; }

...

// storage/perfschema/pfs.cc:6462 if (flags & STATE_FLAG_TIMED) { timer_end = get_statement_timer(); wait_time = timer_end - state->m_timer_start; }

...

if (flags & STATE_FLAG_THREAD) {

...

if(flags & STATE_FLAG_EVENT) {

...

// storage/perfschema/pfs.cc:6528 pfs->m_timer_end = timer_end; pfs->m_cpu_time = cpu_time; pfs->m_end_event_id = thread->m_event_id;

...

} }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.

说明，在语句运行结束的有趣时候，PFS会更新记录：

m_timer_end 运行结束时间m_end_event_id 语句结束事件ID

继续阅读代码，表问发现除了在：pfs_start_statement_vc中对该值进行初始化外，有趣修改该值的代码仅有这一处，基本可以得出结论：m_timer_end > 0 或 m_end_event_id >0 代表语句运行已经结束。

整个简易工具测一下:

复制. ./

setenv

FOR i IN $(seq 1 1000000)

DO

mysql -h127.0.0.1 -P3306 -uroot <<

EOF

USE performance_schema;-- 打印所有的进行中用户SQL(除了自身)SELECT s.* FROM events_statements_current s, threads t WHERE s.thread_id = t.thread_id AND t.type = FOREGROUND AND sql_text like %FOREGROUND% = FALSE AND s.end_event_id IS NULL

\G

EOF

sleep 0.1 # 每0.1

秒跑一下

DONE1.2.3.4.5.6.7.8.9.10.11.12.

运行结果:

复制

免费源码下载