Bug #69029 Arbitrary flushing performance limitation is bad
Submitted: 22 Apr 2013 3:04 Modified: 25 Apr 2013 4:55
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any

[22 Apr 2013 3:04] Domas Mituzas
Description:
From the code:

        /* This not nearly as bad as it looks. There is only
        page_cleaner thread which does background flushing
        in batches therefore it is unlikely to be a contention
        point. The only exception is when a user thread is
        forced to do a flush batch because of a sync
        checkpoint. */
        os_thread_sleep(TRX_DOUBLEWRITE_BATCH_POLL_DELAY);

Actually, this is quite bad.
Maximum doublewrite batch is 120 entries, delay is 10ms, so the maximum speed at which page flushing can be done in this path is quite limited.

Just by changing this constant from 10ms to 0.1ms, I got my page flushing performance go from 50MB/s to 200MB/s+.

How to repeat:
Shut down a server with lots of dirty pages, keep wondering why it is so slow. Read the code, try to believe that it is not nearly as bad as it looks.

Suggested fix:
Avoid long sleeps. Condition variables or tiny sleeps may be better idea.
[22 Apr 2013 7:24] Shane Bester
Thanks for the report!
[25 Apr 2013 4:55] Erlend Dahl
This has been fixed in 5.6.12 and 5.7.2.