Bug #56933 InnoDB does more writes in 5.5 than in 5.1 on buffer pool challenged workloads
Submitted: 22 Sep 2010 13:26 Modified: 9 Dec 2010 22:43
Reporter: Mikhail Izioumtchenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5 OS:Any
Assigned to: Inaam Rana CPU Architecture:Any

[22 Sep 2010 13:26] Mikhail Izioumtchenko
Description:
there's a performance regression in 5.5 as compared to 5.1 plugin, 6-10% on a dbt2 workload where the database is about 6G and the buffer pool is 2g.
Analysis showed that 5.5 does more writes.

How to repeat:
50 wh dbt2 with buffer pool smaller than the db size

Suggested fix:
please see private note(s)
[22 Sep 2010 13:37] Mikhail Izioumtchenko
checkpoint lag graph

Attachment: 29_0,1-dbstats.out-lag-Checkpoint.lag-0.png (image/png, text), 6.40 KiB.

[22 Sep 2010 13:39] Mikhail Izioumtchenko
modified db pages

Attachment: 29_0,1-dbstats.out-mdbp-Modified.db.pages-4.png (image/png, text), 5.81 KiB.

[22 Sep 2010 13:41] Mikhail Izioumtchenko
tps

Attachment: 29_0,1-mix.log-tps-n.png (image/png, text), 5.82 KiB.

[22 Sep 2010 13:42] Mikhail Izioumtchenko
vmstat blocks out

Attachment: 29_0,1-vmstat.out-bo-10.png (image/png, text), 4.92 KiB.

[22 Sep 2010 13:45] Mikhail Izioumtchenko
the files showed that in 5.5:

- we have less checkpoint lag. Note that 5.1 has it 0.8g on 2g total log size
which is plenty.
- we have fewer modified db pages
- we have more blocks written
- we have less performance overall and more of a stop and go pattern
[7 Oct 2010 12:06] Mikhail Izioumtchenko
the one line fix reinstates an unconditional one second sleep
in the outermost master thread loop. It is present in 5.1 but was lost during some early
5.5 work on the master thread. The result of that loss was that the master 
thread action became skewed towards background and flush loops which
in turn resulted in considerably more flushing leading to loss of performance.
[26 Oct 2010 0:22] John Russell
Added text to the changelog as suggested by James:

The master InnoDB background thread could sometimes cause transient
performance drops due to excessive flushing of modified pages.
[9 Nov 2010 19:44] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)
[13 Nov 2010 16:23] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)
[13 Nov 2010 16:42] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (pib:21)