DZone
Performance Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Performance Zone > Small innodb_page_size As a Performance Boost for SSD

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.

Vadim Tkachenko user avatar by
Vadim Tkachenko
·
Aug. 12, 16 · Performance Zone · Opinion
Like (1)
Save
Tweet
3.21K Views

Join the DZone community and get the full member experience.

Join For Free

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!

Boost (C++ libraries) Memory (storage engine) Database POST (HTTP) InnoDB MySQL Comparison (grammar) Data (computing) Production (computer science)

Published at DZone with permission of Vadim Tkachenko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • TURN Time Into Value
  • Automatically Creating Microservices Architecture Diagrams
  • Pull Request vs. Merge Request
  • Mocking the java.time API for Better Testability

Comments

Performance Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo