Bug #81368 multithreaded purge should hash out work to threads by table
Submitted: 10 May 2016 17:13 Modified: 29 Oct 2018 18:08
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[10 May 2016 17:13] Domas Mituzas 
Description:
currently multithreaded purge distributes work to worker threads without much intelligence, thus frequently causing all purge threads contend on a same b-tree latch. 

that means that if there's a thread deleting lots of rows from a table, there will be 4 other threads contending with it. 

as purge reorganizes pages a lot, that causes incredible index contention where innodb is not able to delete more than few thousand rows a second even with in-memory workload (if e.g. compression is used). 

using replication-like sharding of parallel work would address this issue a bit by reducing thrashing and cpu spent contending for b-tree latches. 

How to repeat:
DELETE from table;

Suggested fix:
shard purge work by table
[24 May 2016 14:13] MySQL Verification Team 
Domas,

I find your idea perfectly acceptable.

Verified as a performance improvement request.
[4 Oct 2016 7:13] zhai weixiang 
This bug was fixed in MySQL 8.0
ref: http://dev.mysql.com/worklog/task/?id=9387
[29 Oct 2018 18:04] Domas Mituzas 
closing, if it is really in 8.0
[29 Oct 2018 18:08] Domas Mituzas 
addressed?