InnoDB Page Size: Does it matter? Benchmarking performance

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 effects  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 traditional hard disk, InnoDB page size configuration has relatively small effect on overall performance using 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 non volatile memory (NVM), 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

InnoDB page size setting has no significant performance effect on this benchmark. In both SSD and NVM devices there a is small decrease of 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 effect 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.