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
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
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

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Containers Trend Report: Explore the current state of containers, containerization strategies, and modernizing architecture.

Low-Code Development: Learn the concepts of low code, features + use cases for professional devs, and the low-code implementation process.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Trending

  • Using Unblocked to Fix a Service That Nobody Owns
  • Demystifying Virtual Thread Performance: Unveiling the Truth Beyond the Buzz
  • Enhancing Observability With AI/ML
  • Making Spring AI and OpenAI GPT Useful With RAG on Your Own Documents
  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.

Jan Lindstrom user avatar by
Jan Lindstrom
·
Nov. 08, 16 · Opinion
Like (3)
Save
Tweet
Share
3.8K 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.


Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • 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
  • support@dzone.com

Let's be friends: