Small innodb_page_size as a performance boost for SSD

In this blog post, we’ll discuss how a small innodb_page_size can create a performance boost for SSD.

In my previous post Testing Samsung storage in tpcc-mysql benchmark of Percona Server I compared different Samsung devices. Most solid state drives (SSDs) use 4KiB as an internal page size, and the InnoDB default page size is 16KiB. I wondered how using a different innodb_page_size might affect the overall performance.

Fortunately, MySQL 5.7 comes with the option innodb_page_size, so you can set different InnoDB page sizes than the standard 16KiB. This option is still quite inconvenient to use, however. You can’t change innodb_page_size for the existing database. Instead, you need to create a brand new database with a different innodb_page_size and reload whole data set. This is a serious showstopper for production adoption. Specifying innodb_page_size for individual tables or indexes would be a welcome addition, and you could change it with a simple ALTER TABLE foo page_size=4k.

Anyway, this doesn’t stop us from using innodb_page_size=4k in the testing environment. Let’s see how it affects the results using the same conditions described in my previous post.

performance boost for SSD

Again we see that the PM1725 outperforms the SM863 when we have a limited memory, and the result is almost equal when we have plenty of memory.

But what about innodb_page_size 4k vs 16k.?

Here is a direct comparison chart:

performance boost for SSD

Tabular results (in NOTPM, more is better):

Buffer Pool, GiBpm1725_16kpm1725_4ksam850_16ksam850_4ksam863_16ksam863_4kpm1725 4k/16k
542427.5773287.071931.542682.2914709.6948841.041.73
1578991.67134466.862750.856587.7231655.1893880.361.70
25108077.56173988.055156.7210817.2356777.82133215.301.61
35122582.17195116.808986.1511922.5993828.48164281.551.59
45127828.82209513.6512136.5120316.91123979.99192215.271.64
55130724.59216793.9919547.8124476.74127971.30212647.971.66
65131901.38224729.3227653.9423989.01131020.07220569.861.70
75133184.70229089.6138210.9423457.18131410.40223103.071.72
85133058.50227588.1839669.9024400.27131657.16227295.541.71
95133553.49226241.4139519.1824327.22132882.29223963.991.69
105134021.26224831.8139631.0324273.07132126.29222796.251.68
115134037.09225632.8039469.3424073.36132683.55221446.901.68

 

It’s interesting to see that 4k pages help to improve the performance up to 70%, but only for the PM1725 and SM863. For the low-end Samsung 850 Pro, using a 4k innodb_page_size actually makes things worse when using a high amount of memory.

I think a 70% performance gain is too significant to ignore, even if manipulating innodb_page_size requires extra work. I think it is worthwhile to evaluate if using different innodb_page_size settings help a fast SSD under your workload.

And hopefully MySQL 8.0 makes it easier to use different page sizes!

Share this post

Comments (11)

  • Mark Callaghan Reply

    How many clients did you use for these tests? (c=???)

    4k page size with 2X compression might not be efficient when storage sectors are 4k.

    August 10, 2016 at 8:34 pm
  • Fadi El-Eter (itoctopus) Reply

    Hi Vadim,

    The test results are very interesting. A 70% increase in performance (even 20% for that simple change) is not bad.

    Most of our clients are with the same host – and they use Crucial SSDs. Do you have any idea whether Crucial SSDs will perform better on a lower innodb page size?

    Another quick question: If we change the page size, will copying an existing database (using phpMyAdmin, for example) to a new database do the trick for the new database to use the new page size?

    August 10, 2016 at 10:46 pm
    • Vadim Tkachenko Reply

      I would put Crucial SSD on the same level as Samsung 850 Pro, so the benefit, as you can see from the data, may not be obvious.

      Copying data via mysqldump should work and will use new innodb_page_size.

      August 11, 2016 at 12:23 pm
  • Matthew Boehm Reply

    Unfortunately, a bug exists (https://github.com/codership/galera/issues/398) in Galera that prevents 4K pages from working in Galera-based setups.

    August 11, 2016 at 4:45 pm
  • Twirrim Reply

    I’ve been trying to find the internal page size of the 850 Pro, but haven’t found it yet. It looks like the 840 Pro had a 8kb page size, but it’s not clear if that continued on to the 850 Pro. If someone has an 850 Pro handy and is bored, maybe that would be worth a quick experiment as the page size?

    It certainly seems like the internal page size is something we should be aware of with storage and DB configuration, maybe pressure can be put on manufacturers to start including it in technical specs?

    August 11, 2016 at 6:44 pm
  • Peter Zaitsev Reply

    Mark,

    You’re right – if you’re using Innodb Compression 4K base page is unlikely to be the good choice. Though we do not see Innodb compression (any of them) being used too frequently.

    If using compression 8K base page size with 4K compressed page size might be good idea if 2x compression is routinely reached

    August 11, 2016 at 9:57 pm
  • Peter Zaitsev Reply

    Vadim,

    As much as I would like to see different page size options for different tables (and perhaps even indexes) I would only like to see it if it can be done without significant code complication so we’re not getting another Innodb Compression which introduced a lot of extra complexity to the code and took years to stabilize

    August 11, 2016 at 9:59 pm
  • Peter Zaitsev Reply

    Vadim,

    Did you look at the database size on disk with 4K and 16K pages ? Typically you should see some increase in space usage which is what can cause additional IO.

    August 11, 2016 at 10:02 pm
    • Vadim Tkachenko Reply

      Peter,

      There are size for 100 warehouses

      16K page size
      -rw-r—– 1 root root 2189426688 Aug 8 08:10 customer.ibd
      -rw-r—– 1 root root 3212836864 Aug 8 08:10 order_line.ibd
      -rw-r—– 1 root root 3753902080 Aug 8 08:10 stock.ibd

      8K page size
      -rw-r—– 1 root root 2369781760 Aug 12 01:11 customer.ibd
      -rw-r—– 1 root root 3309305856 Aug 12 01:11 order_line.ibd
      -rw-r—– 1 root root 3967811584 Aug 12 01:11 stock.ibd

      4K page size
      -rw-r—– 1 root root 2885681152 Aug 13 06:34 customer.ibd
      -rw-r—– 1 root root 3581935616 Aug 13 06:34 order_line.ibd
      -rw-r—– 1 root root 4647288832 Aug 13 06:34 stock.ibd

      August 12, 2016 at 6:45 pm
  • Peter Zaitsev Reply

    Hi Vadim,

    So the database size on disk is 21% larger for 4K pages compared to 16K pages and 5% larger for 8K pages compared to 16K pages, which means less of it fits in memory. You also perhaps have more overhead from buffer pool when using 4K page sizes hence having less OS cache available. Both these should cause negative impact on performance.

    August 13, 2016 at 11:14 am

Leave a Reply