完美世界小说下载,完美世界有声小说全集,古风名字

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案

有趣的Events_Statements_Current表問題

什么是events_statements_current表

在GreatSQL中，PFS下有一張內(nèi)存表: events_statements_current，看到這個(gè)名稱"xxx_current"，小白如我可能會(huì)認(rèn)為這張表中的數(shù)據(jù)就是當(dāng)前系統(tǒng)的活躍(active)語(yǔ)句。該表的描述如下(有部分省略):

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)

直接使用當(dāng)前語(yǔ)句表的問題

直接做個(gè)小實(shí)驗(yàn)，終端1：

SELECT sleep(1);

終端2：

USE performance_schema;

-- 查看最近用戶語(yǔ)句
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';

輸出:

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)

反復(fù)執(zhí)行上述語(yǔ)句，發(fā)現(xiàn)終端1當(dāng)前執(zhí)行的語(yǔ)句都是：SELECT sleep(1)，看起來(lái)有點(diǎn)像BUG呀。

真的是這樣嗎? 來(lái)細(xì)看一下文檔的定義：

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

發(fā)現(xiàn)了一些關(guān)鍵信息: "showing the current status of the thread's most recent monitored statement event"，也就是說：保存的是線程最近被檢測(cè)到的語(yǔ)句事件，并非當(dāng)前正在處于執(zhí)行狀態(tài)的語(yǔ)句

獲取當(dāng)前活躍的語(yǔ)句方法

為了了解這個(gè)問題的解決方案，來(lái)看看具體實(shí)現(xiàn)的代碼：

void pfs_end_statement_vc(PSI_statement_locker *locker, void *stmt_da) {
  PSI_statement_locker_state *state =
      reinterpret_cast(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;
      ...
    }
  }

說明，在語(yǔ)句運(yùn)行結(jié)束的時(shí)候，PFS會(huì)更新記錄：

m_timer_end 運(yùn)行結(jié)束時(shí)間
m_end_event_id 語(yǔ)句結(jié)束事件ID

繼續(xù)閱讀代碼，發(fā)現(xiàn)除了在：pfs_start_statement_vc中對(duì)該值進(jìn)行初始化外，修改該值的代碼僅有這一處，基本可以得出結(jié)論：m_timer_end > 0 或 m_end_event_id >0 代表語(yǔ)句運(yùn)行已經(jīng)結(jié)束。

整個(gè)簡(jiǎn)易工具測(cè)一下:

. ./setenv

FOR i IN $(seq 1 1000000)
DO
mysql -h127.0.0.1 -P3306 -uroot <USE performance_schema;

-- 打印所有的進(jìn)行中用戶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秒跑一下
DONE

運(yùn)行結(jié)果:

*************************** 1. row ***************************
              THREAD_ID: 15082
               EVENT_ID: 127
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:95
            TIMER_START: 19879433851621000
              TIMER_END: 19880288626029000
             TIMER_WAIT: 854774408000
              LOCK_TIME: 0
               SQL_TEXT: select sleep(1), 3
...

將工具SQL替換成

-- 移除原來(lái)的 s.end_event_id is NULL
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.timer_end = 0 \G

再次運(yùn)行：

## 真的是啥也沒有啊，真的是啥也沒有啊，

再仔細(xì)看看文檔 (https://dev.mysql.com/doc/refman/5.7/en/performance-schema-events-statements-current-table.html):

TIMER_START, TIMER_END, TIMER_WAIT

Timing information for the event. The unit for these values is picoseconds (trillionths of a second). 
The TIMER_START and TIMER_END values indicate when event timing started and ended. TIMER_WAIT is the event elapsed time (duration).

If an event has not finished, TIMER_END is the current timer value and TIMER_WAIT is the time elapsed so far (TIMER_END ? TIMER_START).

重要信息: 當(dāng)事件沒有完成，TIMER_END會(huì)持續(xù)參考當(dāng)前時(shí)間更新

與想象的很不一樣，無(wú)奈打開源碼，看看有什么線索

//storage/perfschema/table_events_statements.cc:315(table_events_statements_common)
  if (m_row.m_end_event_id == 0) {
    timer_end = get_statement_timer();
  } else {
    timer_end = statement->m_timer_end;
  }

換句話說：運(yùn)行未結(jié)束時(shí)，statement->m_timer_end的值確實(shí)是0，但是timer_end賦值用的是語(yǔ)句計(jì)時(shí)器的當(dāng)前時(shí)間。

測(cè)試結(jié)論：events_statements_current可以用于描述當(dāng)前活躍語(yǔ)句，活躍這個(gè)條件可以使用：END_EVENT_ID IS NULL來(lái)過濾。

文章名稱：有趣的Events_Statements_Current表問題
網(wǎng)頁(yè)URL：http://fisionsoft.com.cn/article/dphgpdg.html

新聞中心

什么是events_statements_current表

直接使用當(dāng)前語(yǔ)句表的問題

獲取當(dāng)前活躍的語(yǔ)句方法

其他資訊