Bug #76252 | InnoDB ROW event handling too zelous for large transactions on table without PK | ||
---|---|---|---|
Submitted: | 11 Mar 2015 1:02 | ||
Reporter: | Andrii Nikitin | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.6.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 Mar 2015 1:02]
Andrii Nikitin
[26 Mar 2015 13:08]
Andrii Nikitin
I believe problem may be related to something like in bug #67044 , because event processing is much faster when the table has no secondary index at all.
[12 Feb 2016 10:56]
ADITYA ANANTHAPADMANABHA
Posted by developer: PROBLEM ------------- Since the tables mentioned in the bug don't have any PK defined on them we are forced to create a hidden DB_ROW_ID column for them .The problem is that we don't log this hidden column in bin log so the DB_ROW_ID's generated for the table in slave is different than the master.Therefore while applying row log event (updates)we cannot depend on the cluster index of the salve and we are forced to do full table scans for updates. This is the reason for being slow. To fix this problem we have to expose the hidden column and write it in bin log for it to be replicated in slave too which is too risky for GA versions. There are work logs WL7313 and WL7314 present which plan to do it.
[28 Apr 2017 21:13]
Jörg Brühe
I have reproduced this problem in versions 5.5.44, 5.6.24, and 5.7.17. My test was to create an InnoDB table without any index or key, insert many rows, and then issue a "delete" without a where condition. In all three versions, the number of "InnoDB buffer pool read requests" during the delete on the master was proportional to the number of rows, but on the slave to the square of the number of rows. With 200,000 rows, the master requested between 11 (5.5, 5.6) and 8 pages (5.7) per row deleted, but the slave requested 1,900 pages per row deleted! AIUI, as there was no PK, InnoDB must have assigned the DB_ROW_ID in entry sequence, on both the master and the slave, and this should also be the order in the B-tree. The "delete" on the master must have found the rows in that same sequence, deleted them, and therefore written the binlog entry in that sequence. When processing these binlog entries, the slave should always find the (currently) first row as a match for that log entry, and so could delete it. Why does it need to scan the complete table after finding a matching record? If I created any index on the slave (not on the master) before the delete, the slave did use it. If the selectivity was bad (129 distinct values in 200 k rows), the slave requested even more pages (2,800 .. 3,150 per row). With better selectivity (10,000 distinct values), the request count fell to about 50 per row, with a unique index it was 19 .. 22, with a primary key it was about 9 page requests per row deleted. My test setup uses additional mechanisms not available in "mysqltest", but I can provide the status counters if that is of any help.