This document describes how to use persistent disk with 16 KB physical block size for performance improvements to a MySQL database.
Write-heavy MySQL workloads usually benefit from disabling
InnoDB doublewrite buffer.
InnoDB performs doublewrite during the dirty page flushing
process so it can recover possible torn pages.
However, if there is an end-to-end 16 KB atomic write path to ensure that a 16 KB data page won't be partially committed to the disk or torn write, then there is no need to perform doublewrite. When doublewrite is disabled, the database's dirty page flushing capability is essentially doubled, reducing the frequency in which the database falls into a sync flush state, which leads to a more stable, and possibly increased performance.
Before you begin
- If you want to use the command-line examples in this guide:
- If you want to use the API examples in this guide, set up API access.
- Read about persistent disks.
Building a 16 KB atomic write path from database to block device
You can build an end-to-end 16 KB atomic write path from the database to the
block device leveraging a 16 KB persistent disk, so you can safely disable the
doublewrite feature in MySQL/
InnoDB and achieve a more stable and better
performance for a high-write load.
Create a 16 KB block size persistent disk and attach it to your VM. The 16 KB persistent disk provides 16 KB write atomicity at the physical block level.
Although optional, it is recommended that you configure your MySQL instance to store data files only to the 16 KB persistent disk. Store log files, especially redo log and binlogs, to a 4 KB persistent disk that is attached to the same VM. This ensures log file writes continue to be high performance because small log writes on 16 KB persistent disk might trigger lots of read-modify-writes, which are slower.
Format the 16 KB disk using the ext4 file system with the BigAlloc option and set the cluster size to 16 KB. Here is an example
mkfscommand with BigAlloc option specified:
mkfs.ext4 -O bigalloc -C 16384 [...other options…]
Using BigAlloc with 16 KB as a cluster size, ensures that the file system allocates files that align with the 16 KB boundary on the disk.
When you create the VM instance, choose an OS image from the Google Container-Optimized OS image families in the
gcloudcommand to see a list of all available
gcloud compute images list --project cos-cloud --no-standard-images
Select version 67 or higher. For better results, consider choosing an image from the
cosimage ensures that writes are not improperly split across 16 KB boundary by layers in between the file system and the physical block device layer. The
cosimage qualification process has built-in tests to ensure this result.
max_sectors_kbare configured properly in the OS:
max_segments >= max_sectors_kb/4
These two variables are already configured in all Compute Engine VMs, if you don't have a script changing these two variables after VM creation then you don't need to do anything here.
You can query these two constants in the OS under this path:
noneas the I/O scheduler for the 16 KB persistent disk.
echo "none" > /sys/block/sd<drive_letter>/queue/scheduler
Disable I/O requests merging in kernel for the 16 KB persistent disk.
echo 2 > /sys/block/sd<drive_letter>/queue/nomerges
O_DIRECT. Set (or add)
Now you can safely turn off the
This method is not the only approach you can take to ensure end-to-end 16 KB atomic writes using a 16 KB block device. For example, if you configure your database to use the block device directly as a raw device without using a filesystem, then you can skip the steps above that describe configuration of the file system.
- Learn how to create a 16 KB physical block size persistent disk
- Read more about MySQL performance benchmarking tools.
- Learn more about adding a persistent disk.