Download - All IT eBooks

Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
4 rows in set (0.00 sec)
The slave SQL thread is listed in row 2 and is run by system user, a special user that
executes slave queries. I now run the following query in a parallel connection, before
starting the slave, to demonstrate how the SQL thread waits for other threads to finish
before it can execute the update:
SELECT * FROM t1 FOR UPDATE;
After taking this SHOW PROCESSLIST output, I rolled back the parallel query, so the SQL
thread could successfully finish the query:
slave> SHOW PROCESSLIST\G
***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 267
State: Waiting for master
Info: NULL
***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 168
State: Slave has read all
Info: NULL
...
1. row ***************************
to send event
2. row ***************************
relay log; waiting for the slave I/O thread to update it
You can also encounter a situation where the slave SQL thread is holding locks on rows
that your application is trying to access. To see when this happens, examine the output
of SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS.
The solution to both of these situations is either to wait until the active thread finishes
or to roll back a user transaction if it waits for a lock for a long time.
Effectively Using MySQL Troubleshooting Tools
To end this chapter, I want to repeat the descriptions of tools we used and describe
some of their useful features that I bypassed before.
94 | Chapter 2: You Are Not Alone: Concurrency Issues