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
• Status: Closed
• 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:[e-mail]-20101109182959-otkxq8vo2dcd13la) (version source revid:[e-mail]-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:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)

[13 Nov 2010 16:42] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20101113160336-atmtmfb3mzm4pz4i) (version source revid:[e-mail]-20100928081234-22qbm6cwht521484) (pib:21)