October 24, 2014

Adaptive flushing in MySQL 5.6

As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e.
http://www.percona.com/blog/2011/09/18/disaster-mysql-5-5-flushing/
http://www.percona.com/blog/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/
http://www.percona.com/blog/2011/01/03/mysql-5-5-8-in-search-of-stability/

In MySQL 5.6 there was implemented a new flushing logic, so I decided to check what do we have now.


For experiment I took tpcc-mysql workload (3 hours runs), 2500W ( ~250GB of data), on Dell PowerEdge 720 box with 150GB buffer pool and with Fusion-io ioDrive 640GB card. InnoDB log file size 2x2000M for MySQL 5.5, and 2x4GB, 2x8GB for MySQL 5.6.

For an initial experiment, let’s see what the result we are getting in MySQL 5.5 ( MySQL 5.5.27):

As you can see there is prolonged periods of time (up to few minutes) when throughput drops to 0, and the system is kind of locked up, not being able to process queries.

So, how it is changed in MySQL 5.6.6:

I should admit there is no more drops to 0, and system is able to maintain 1000 trx / 10 sec in the lowest points, so indeed it is an improvement.
However from a stability standpoint it is far from being perfect.

Another improvement that comes with MySQL 5.6 it is a support of big InnoDB log files, so let’s check if bigger log files helps there and decrease flushing pressure.

You may see that bigger log files, again, improve overall performance, but not stability. With 2x8G logs, there is no sharp peak like with 2000M – 4G logs, but there I guess we have a flushing of dirty pages (I did not gather stats to check it).

As a conclusion: MySQL 5.6 definitely has an improved flushing, but I’d like to see more stable results, so there is a room for improvement.

Relevant MySQL options:

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. James Day says:

    Vadim, you may find that adjusting innodb_flushing_avg_loops is of value. It’s set quite low by default but was introduced to smooth the fluctuations in flushing workload. I expect that most heavily loaded production servers will benefit from a value higher than 30, likely several thousand. It’s particularly likely to be of value if you find the server getting close to or reaching async flushing.

    It’s also worth noting that we default to innodb_buffer_pool_instances = 8 but 1 may be better in cases where async flushing ends up being triggered, a case you’ve often tested with. More instances can be significantly slower when there’s async flushing going on. We don’t expect encountering async flushing to be routine – incidents involving it have been gradually decreasing over time as we’ve improved InnoDB through the versions – so we’ve set the default for the more common cases.

    Views are my own, for an official Oracle view consult a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle.

  2. Vadim, innodb_flush_log_at_trx_commit = 2, isn’t that “cheating”? It accepts that a server crash will lose some data.

    Why not show the results when using innodb_flush_log_at_trx_commit = 1 which is full ACID compliant, but will obviously not perform as well? It’ll also probably not smooth out the results because of the extra syncs to disk. That said many people will want to use this value and therefore seeing how much you gain by accepting a “small loss” of data vs expecting the committed data to be on disk will be quite interesting.

  3. Dimitri says:

    Vadim, thanks for sharing this!

    To be honest, my impression is that there is something wrong with your setup that the flushing activity is unable to follow the REDO log write speed.. – so more details will be more helpful (there are new counters available from the METRICS table about flushing activity, as well Checkpoint Age graphs and I/O stats from your server)..

    Don’t know if 2500W volume changing things too much comparing to 500W, is it?.. – on 500W test I’ve got just completely stable results in the past (ex.: http://dimitrik.free.fr/blog/archives/2012/04/mysql-performance-55-and-56labs-tpcclike.html).

    Also, you did not mention how many users did you run in your test.

    Anyway, I’m very curious to know any cases / workloads where new adaptive flushing in MySQL 5.6 is not giving a stable performance results.

    Thank you!

    Rgds,
    -Dimitri

  4. James,

    Thank you for suggestions, I will give it a shot with innodb_flushing_avg_loops

  5. Simon,

    I do not think it is a cheating. For this particular research I do not think innodb_flush_log_at_trx_commit =2 makes any difference.

    And, if you put log files to RAID with BBU, you won’t see practical difference with innodb_flush_log_at_trx_commit =1 vs 2

  6. Dimitri,

    I think 500W vs 2500W makes two very different cases.
    It is 50GB vs 250GB , and if you fit it in memory, it comes to how fast you can flush 50GB vs 250GB of data.

    I am using 32 user session.

    Now, in this case I was using Fusion-io, which is able to handle very intensive writes/sec.
    I expect this will be much worse if I go with regular RAID setup (I am planning to run it later)

  7. Dimitri,

    I’m surprised by the statement – “To be honest, my impression is that there is something wrong with your setup that the flushing activity is unable to follow the REDO log write speed” – is not this is exactly something you should expect for conventional hard drives where sequential writes for redo logs is very fast while flushing is random IO which can be quite limited? I think for many workloads with data fitting in memory and “random” modifications you should see flushing to be unable to keep up with log files. I think this is fine to have this as a limit but I still would like to see system performance to stabilize at certain value in this case

  8. Dimitri says:

    @Vadim: not agree there will be driven by a speed of 250GB writing.. — the flushing is driven by REDO logs writes, and in your case having 10K TPS is less than twice bigger from what I’ve observed on 500W & SSD, which is reaching something like 6K TPS, which is involving flushing of 8000 pages/sec.. Don’t know what level was in your case, the true answer may give only METRICS counters during your test.

    @Peter: it can be true when REDO and DATA are placed on different storage boxes, while here, if I understand well, the same Fusion-io card was used for both, so it’s quite strange..

    but again, to get an idea about what is going on, METRICS stats should be collected.

    Rgds,
    -Dimitri

Speak Your Mind

*