日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

什么是events_statements_current表

在GreatSQL中，PFS下有一張內(nèi)存表: events_statements_current，看到這個名稱"xxx_current"，小白如我可能會認為這張表中的數(shù)據(jù)就是當前系統(tǒng)的活躍(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：

SELECT sleep(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';

輸出:

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í)行上述語句，發(fā)現(xiàn)終端1當前執(zhí)行的語句都是：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 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"，也就是說：保存的是線程最近被檢測到的語句事件，并非當前正在處于執(zhí)行狀態(tài)的語句

獲取當前活躍的語句方法

為了了解這個問題的解決方案，來看看具體實現(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;
      ...
    }
  }

說明，在語句運行結(jié)束的時候，PFS會更新記錄：

• m_timer_end 運行結(jié)束時間
• m_end_event_id 語句結(jié)束事件ID

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

整個簡易工具測一下:

. ./setenv

FOR i IN $(seq 1 1000000)
DO
mysql -h127.0.0.1 -P3306 -uroot <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秒跑一下
DONE

運行結(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替換成

-- 移除原來的 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

再次運行：

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

再仔細看看文檔 (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).

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

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

//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;
  }

換句話說：運行未結(jié)束時，statement->m_timer_end的值確實是0，但是timer_end賦值用的是語句計時器的當前時間。

測試結(jié)論：events_statements_current可以用于描述當前活躍語句，活躍這個條件可以使用：END_EVENT_ID IS NULL來過濾。

網(wǎng)頁標題：有趣的Events_Statements_Current表問題
本文路徑：http://www.5511xx.com/article/dphgpdg.html