Bug #75540 Major regression having many row versions
Submitted: 17 Jan 2015 21:07 Modified: 29 Jan 2015 1:33
Reporter: Peter Zaitsev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[17 Jan 2015 21:07] Peter Zaitsev
Description:
In certain cases the Write performance of MySQL will fall sharply when there are many row versions and transaction with consistent view is open:

More details can be found
http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-...

How to repeat:
Run Following Sysbench 0.5 test:

sysbench  --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=80000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb  -- test=/usr/share/doc/sysbench/tests/db/update_index.lua run

Adjust table size as needed so workload is completely in-memory.  Use large enough log files so it is not flush bound.

Concurrently run the following:

select avg(length(c)) from sbtest1;
begin;
select avg(length(c)) from sbtest1;
select sleep(300);
commit;

Sysbench write performance will drop 5x-10x even when sleep statement is running
[20 Jan 2015 20:37] Sveta Smirnova
Thank you for the report.

Which exact version of 5.6 do you use? Please also provide all InnoDB-related and replication-related options you used.
[21 Jan 2015 5:11] MySQL Verification Team
I've seen this many times. you must first get your "history list length" into the millions.  this can be done by continuously updating a row many times in a transaction while tests are running.
[21 Jan 2015 14:34] MySQL Verification Team
This is a very well known bottleneck that required changes in current design. The new design has been described in several WorkLog entries for the purpose.

It will require a new UNDO log design to get fixed. Purge will need to skip the blocking transaction and delete the UNDO entries that are no longer needed. It is contained in several WL # entries, like 5742.

Verified as a feature request.
[29 Jan 2015 1:33] Peter Zaitsev
Thank you Sinisa!