DZone
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
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • The Ultimate Guide To Repair MySQL Database

Trending

  • Building V1 Gen-AI Products at Scale: Technical Product Patterns That Work
  • WebAssembly (Wasm) and AI at the Edge: The New Frontier for Real-Time Applications
  • Docker Model Runner: Running AI Models Locally Made Simple
  • Effective Exception Handling in Java and Spring Boot Applications
  1. DZone
  2. Data Engineering
  3. Databases
  4. Does InnoDB Page Size Matter?

Does InnoDB Page Size Matter?

Jan Lindstrom tests the InnoDB page size setting on a certain benchmark to determine if it has a significant performance effect.

By 
Jan Lindstrom user avatar
Jan Lindstrom
·
Nov. 08, 16 · Opinion
Likes (3)
Comment
Save
Tweet
Share
4.3K Views

Join the DZone community and get the full member experience.

Join For Free

From MariaDB 10.1, there is a feature where the InnoDB page size can be configured to be larger than the default 16K for normal, uncompressed tables. However, there has been little performance results that show whether the page size really affects the transaction performance or response time. In this blog, we study effects of page size on three different storage devices using the same benchmark(s). These devices are:

  • Traditional hard disk.
  • SSD (Tree Intel X25-E Extreme SSDSA2SH032 G1GN 2.5-inch 32GB SATA II SLC Internal Solid State Drive as RAID-0).
  • FusionIO NVM device (ioMemory SX300-1600 with VSL driver 4.2.1 build 1137 and NVMFS 1.1.1).

Results from different devices should not be compared to each other, as there are other variables like device bandwidth and different file systems. Instead, we will look at page size effect on each device separately. 

I will use sysbench v0.5 as the benchmark with the following parameters varying the num-threads from 8 to 512:

./sysbench --test=tests/db/oltp.lua --mysql-table-engine=innodb --oltp-test-mode=complex --oltp-read-only=off --oltp-table-size=10000000 --max-requests=1000000000 --num-threads=<n> --max-time=10800 --mysql-socket=/mnt/dfs/db/mysql.sock --mysql-user=root run 

Hard Disk

In a traditional hard disk, InnoDB page size configuration has a relatively small effect on overall performance using the sysbench benchmark as seen in Figure 1.

Figure 1: Hard disk performance with different page sizes and number of threads.

Similarly, there is no significant difference on average response time with different page size settings (Figure 2).

Figure 2. Hard Disk average response time with different page sizes and number of threads.

SSD

Looking at SSD in Figure 3, there seems to be a small decrease in performance if a 64K page size is used, although this decrease is not very significant. There are no differences when a 32K page size is used.

Figure 3: SSD performance with different page sizes and number of threads.

A similar difference is seen on average response times (Figure 4). Using a page size of 32K has no noticeable difference to the default 16K. However, the 64K setting has slightly longer response times, but the difference is not significant. 
 

Figure 4: SSD average response time with different page sizes and number of threads.

NVM

In nonvolatile memory (NVM), the device page size seems to have a very small effect (Figure 5). Similarly to SSD, a 64K page size has decreased performance, but the difference is smaller compared to SSD.

Figure 5: NVM performance with different page sizes and number of threads.

Similarly, an average response time comparison of a 64K setup has slightly increased average response times compared to other configurations, but this difference is not significant (Figure 6).

Figure 6: NVM average response time with different page sizes and number of threads.

Summary

The InnoDB page size setting has no significant performance effect on this benchmark. In both SSD and NVM devices, there is a small decrease in performance and increased average response time when a 64K setting is used. However, this benchmark does not prove that there would not be a significant effect on different workloads. So, if applications have a need for bigger page sizes, it should be first and foremost be benchmarked to test systems on how page size settings affect application workload. These results clearly show only that with sysbench, like an OLTP workload, there is no significant benefit or disadvantage using different page sizes in InnoDB.

InnoDB

Published at DZone with permission of Jan Lindstrom, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • The Ultimate Guide To Repair MySQL Database

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

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

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: