A trap for those new to the MySQL Performance Schema is the expectation that thread_id in tables such as events_statements_current matches the id you find in the MySQL processlist. This is NOT TRUE.
If we look at the INFORMATION_SCHEMA.PROCESSLIST table we will find information like:
mysql> select id,db,command,state from information_schema.processlist order by id; -----------+--------------------+---------+------------------------------------------------------------------+ | id | db | command | state | +-----------+--------------------+---------+------------------------------------------------------------------+ | 1 | NULL | Connect | Slave has read all relay log; waiting for the slave I/O thread t | | 2 | NULL | Connect | Waiting for master to send event | | 5 | NULL | Sleep | | | 34404870 | NULL | Sleep | | | 106759077 | performance_schema | Sleep | | | 106904549 | performance_schema | Query | executing | | 107004213 | abc | Sleep | | | 107004600 | abc | Sleep | | | 107004877 | abc | Sleep | | | 107004937 | abc | Sleep | | | 107004989 | abc | Sleep | | | 107005164 | abc | Sleep | | | 107005305 | abc | Sleep | | | 107005310 | abc | Sleep | | | 107005314 | abc | Sleep | | | 107005316 | abc | Sleep | | | 107005317 | abc | Sleep | | | 107005321 | abc | Sleep | | +-----------+--------------------+---------+------------------------------------------------------------------+
However, if I wanted to look at the two slave threads in performance_schema.events_statements_current I will find no matching data.
mysql> select * from performance_schema.events_statements_current where thread_id=1; Empty set (0.00 sec) mysql> select * from performance_schema.events_statements_current where thread_id=2; Empty set (0.00 sec)
In the performance schema, you need to use the threads table in order to determine a match between the processlist and the threads. If we look at the table, you will find information about all the BACKGROUND threads in MySQL.
mysql> select thread_id,name,type,processlist_id from threads; +-----------+----------------------------------------+------------+----------------+ | thread_id | name | type | processlist_id | +-----------+----------------------------------------+------------+----------------+ | 1 | thread/sql/main | BACKGROUND | NULL | | 2 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 3 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 4 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 5 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 6 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 7 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 8 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 9 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 10 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 11 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 12 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 13 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 14 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 15 | thread/innodb/io_handler_thread | BACKGROUND | NULL | | 17 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | | 18 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | | 19 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | | 20 | thread/innodb/srv_master_thread | BACKGROUND | NULL | | 21 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | | 22 | thread/innodb/page_cleaner_thread | BACKGROUND | NULL | | 23 | thread/sql/signal_handler | BACKGROUND | NULL | | 24 | thread/sql/slave_io | BACKGROUND | NULL | | 25 | thread/sql/slave_sql | BACKGROUND | NULL | | 28 | thread/sql/one_connection | FOREGROUND | 5 | | 107013952 | thread/sql/one_connection | FOREGROUND | 107013929 | | 107013989 | thread/sql/one_connection | FOREGROUND | 107013966 | | 106759100 | thread/sql/one_connection | FOREGROUND | 106759077 | | 107014180 | thread/sql/one_connection | FOREGROUND | 107014157 | | 107014291 | thread/sql/one_connection | FOREGROUND | 107014268 | | 106904572 | thread/sql/one_connection | FOREGROUND | 106904549 | | 107014443 | thread/sql/one_connection | FOREGROUND | 107014420 | | 107014490 | thread/sql/one_connection | FOREGROUND | 107014467 | | 107014491 | thread/sql/one_connection | FOREGROUND | 107014468 | | 107014493 | thread/sql/one_connection | FOREGROUND | 107014470 | | 107014497 | thread/sql/one_connection | FOREGROUND | 107014474 | | 107014498 | thread/sql/one_connection | FOREGROUND | 107014475 | | 34404893 | thread/sql/one_connection | FOREGROUND | 34404870 | | 107013233 | thread/sql/one_connection | FOREGROUND | 107013210 | +-----------+----------------------------------------+------------+----------------+ 39 rows in set (0.00 sec)
While it’s great the performance schema can track the statements executed in the Slave SQL thread (I’ll discuss that later), it’s disappointing that the processlist_id, the one thing that can join our two sources of data other, has not value for the slave threads.
mysql> select * from threads where thread_id=25G
*************************** 1. row ***************************
THREAD_ID: 25
NAME: thread/sql/slave_sql
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: Slave has read all relay log; waiting for the slave I/O thread t
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
1 row in set (0.00 sec)
For normal queries we can get a relationship between the processlist and the performance schema with:
select p.id,p.db,p.command,p.state,s.*
from information_schema.processlist p
inner join performance_schema.threads t on p.id = t.processlist_id
inner join performance_schema. events_statements_current s using (thread_id)
where p.command='Query'G
...
*************************** 2. row ***************************
id: 106904549
db: performance_schema
command: Query
state: executing
THREAD_ID: 106904572
EVENT_ID: 78
END_EVENT_ID: NULL
EVENT_NAME: statement/sql/select
SOURCE: mysqld.cc:931
TIMER_START: 268504759200898000
TIMER_END: NULL
TIMER_WAIT: NULL
LOCK_TIME: 580000000
SQL_TEXT: select p.id,p.db,p.command,p.state,s.* from information_schema.processlist p inner join performance_schema.threads t on p.id = t.processlist_id inner join performance_schema. events_statements_current s using (thread_id) where p.command='Query'
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: performance_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 1
CREATED_TMP_TABLES: 1
SELECT_FULL_JOIN: 2
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
