MySQL slave sql thread command type in 8.0
8.0부터 slave sql thread의 command type 표시 내용이 다르다. 뭔가 남몰래 바뀐 느낌. 로깅을 위해서 버전별로 확인해두자.
Thread Command Values
- https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
- Connect: A replica is connected to its source.
- Query : The thread is executing a statement.
- sql thread가 relay log다 적용하고 기다리는 상태일 때, processlist의 Command 값이 connect type에서 query type으로 바뀌었다.
sql thread command type by version
8.0
- Command: Query
root@localhost:(none) 17:06:03>show processlist;
+--------+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+------------------+
| 9 | system user | connecting host | NULL | Connect | 3708210 | Waiting for master to send event | NULL |
| 10 | system user | | NULL | Query | 4 | Slave has read all relay log; waiting for more updates | NULL |
...
+--------+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+------------------+
root@localhost:(none) 17:06:07>select * from sys.session where user='sql/slave_sql'\G
*************************** 1. row ***************************
thd_id: 50
conn_id: 10
user: sql/slave_sql
db: NULL
command: Query
state: Slave has read all relay log; waiting for more updates
time: 2
current_statement: COMMIT
statement_latency: NULL
progress: NULL
lock_latency: 0 ps
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: COMMIT
last_statement_latency: 20.34 us
current_memory: 358.76 KiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 252.43 us
trx_state: COMMITTED
trx_autocommit: NO
pid: NULL
program_name: NULL
1 row in set (0.07 sec)
5.7
- Command: Connect
root@localhost:(none) 17:38:29>show processlist;
+---------+-------------+---------------------+------+-------------+----------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+---------------------+------+-------------+----------+---------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 13050192 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 2 | Slave has read all relay log; waiting for more updates | NULL |
...
+---------+-------------+---------------------+------+-------------+----------+---------------------------------------------------------------+------------------+
root@localhost:(none) 17:38:31>select * from sys.session where user='sql/slave_sql'\G
*************************** 1. row ***************************
thd_id: 24
conn_id: 2
user: sql/slave_sql
db: marketdata
command: Connect
state: Slave has read all relay log; waiting for more updates
time: 1
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 0 ps
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: COMMIT
last_statement_latency: 10.20 us
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: NULL
program_name: NULL
1 row in set (0.07 sec)
5.6
- Command: Connect
root@localhost:(none) 17:43:00>show processlist;
+----------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------+
| 47706 | system user | | NULL | Connect | 38544103 | Waiting for master to send event | NULL |
| 47707 | system user | | NULL | Connect | 2 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
...
+----------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------+
5.5
- Command: Connect
root@localhost:(none) 17:52:47>show processlist;
+---------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------+
| 115123 | system user | | NULL | Connect | 16960606 | Waiting for master to send event | NULL |
| 115124 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
...
+---------+-------------+----------------------+------+-------------+----------+-----------------------------------------------------------------------------+------------------++
5.1
root@localhost:(none) 17:53:21>show processlist;
+----------+-------------+--------------------+-------+-------------+----------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+--------------------+-------+-------------+----------+-----------------------------------------------------------------------+------------------+
| 30041321 | system user | | NULL | Connect | 3116655 | Waiting for master to send event | NULL |
| 30041322 | system user | | NULL | Connect | 2 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
...
+----------+-------------+--------------------+-------+-------------+----------+-----------------------------------------------------------------------+------------------+
Conclusion
- 5.1부터 쭉 Connect type이었는데. 갑자기 8.0에서 바뀜..-.- 흥