Bug #62019 SHOW PROCESSLIST should display something useful during RBR replay
Submitted: 28 Jul 2011 15:49 Modified: 24 Mar 2016 9:21
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S4 (Feature request)
Version:5.6.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: RBR, replication
Triage: Needs Triage: D5 (Feature request)

[28 Jul 2011 15:49] Mark Callaghan
Description:
This is the output from SHOW PROCESSLIST for a slave using 4 parallel SQL threads and RBR. With SBR it lists the statement being replayed. With RBR it lists much less. It could list the database and table name. When the informational event is supported it can also list the original SQL statement.

| 19 | system user |                 | NULL  | Connect |  334 | Waiting for master to send event     | NULL             |
| 81 | system user |                 | NULL  | Connect |    0 | Reading event from the relay log     | NULL             |
| 82 | system user |                 | NULL  | Connect | 6454 | System lock                          | NULL             |
| 83 | system user |                 | NULL  | Connect | 6454 | Opening tables                       | NULL             |
| 84 | system user |                 | NULL  | Connect | 6455 | System lock                          | NULL             |
| 85 | system user |                 | NULL  | Connect | 6454 | Waiting for an event from sql thread | NULL             

How to repeat:
run a slave with RBR, type "show processlist", try to guess at what the sql threads are doing

Suggested fix:
Display database/table name and the event type (INSERT, UPDATE, DELETE). When informational events are in the stream display the original SQL statement
[29 Jul 2011 4:12] Valeriy Kravchuk
Thank you for the feature request.
[19 Dec 2011 18:02] Simon Mudd
Additional suggestion(s) to fix:

If there's a performance penalty of updating the status very frequently then only do it every "n" rows, where "n" might be 100, 1000, .... (or perhaps configurable). That's trivial to implement and gives a sample of what is going on.

Also given a single statement is likely to update between 1 and a possibly very large "n" rows if possible perhaps indicate what the current "n" is. That would then potentially make it clear for example if a large single update statement is being executed or lots of single rowed updates.
[19 Dec 2011 18:07] Shane Bester
Similar to:

http://bugs.mysql.com/bug.php?id=26182
(patch: process percentage of a process -- contributed from Jeremy Cole)
[18 Jan 2013 8:27] Shane Bester
Internally, I filed:
Bug 16189134 - "SYSTEM LOCK" THREAD STATE IS NONSENSE.
[24 Mar 2016 9:21] Jon Stephens
This is fixed in the next major series of MySQL, currently tagged 5.8.

Closed.
[9 Jan 2017 8:59] Simon Mudd
So this is in 8.0.0 DMR ?
It would be good to clarify as 5.8 no longer exists.
Thanks.