Configure MySQL on Compute Engine

Compute Engine offers a range of different instance types and storage optionsfor reading and writing data from your MySQL databases. To ensure that youachieve the best performance and cost for your database workloads, we recommendrunning on newer-generation infrastructure as a service (IaaS) products.

The following configuration recommendations take into account that MySQLworkloads are often used in read-heavy systems, such as online transactionprocessing (OLTP) or the database backing a typical web application. They alsoaccount for common configuration choices, such as using version 8.0 or later ofMySQL and using theInnoDB storage engine.For performance-sensitive workloads, you might need to adjust yourconfigurations to fit. We recommend using this guide as a starting point foryour deployment, and then testing with your actual workload to validate thatyour configuration meets your needs.

Choose your virtual machine (VM)

For MySQL workloads, we recommend using the latest generation of C and N machinefamilies, as they include shapes that work well for most practical MySQL configurations. For anintroduction to these machine series, see the followingGoogle Cloud blog post.These machine families useTitanium and are based onrecent generations of Intel, AMD, and Axion processors.

Focus on performance

For performance-sensitive workloads, such as business-critical MySQL databases,we recommend the latestC4 andC4A instances if they're available in your region. If you can't access them, theC3 andC3D instances offer a similar focus on performance.

These instances offer the lowest and most consistent latency for compute-boundoperations, and include the following useful features for performance-focusedworkloads:

If you're using a C4A, C3, or C3D instance, you can also use Local solid-statedrives (Local SSDs) to meet specific performance requirements.

Optimize for cost

For workloads where your primary priority is optimizing cost, such as MySQLdatabases with low to medium levels of traffic or databases used in testing ordevelopment environments, we recommend that you use the latest N4 instances.These instances use Compute Engine's next generationdynamic resource management to optimize your total cost while maintainingsolid performance, without the strong guarantees that C4, C4A, C3, and C3Doffer. For more details, seeNext generation dynamic resource management.

Configure your VM's size

For any VM that you use, it's important to choose the right VM size for thelevel of MySQL performance that you're aiming for.

If you're aiming for high write transaction per second (TPS) performance, themain factor to consider is your block storage. For more details, seeConfigure block storage,following on this page.

If you're aiming for high read queries per second (QPS) performance, we stronglyrecommend that you use MySQL's RAM-based buffer pool to cache hot data andreduce disk accesses. To maximize these benefits, take the following steps:

  • Choose a VM size that ensures that theworking set, or total amount ofdata that your database processes at once, fits into the buffer pool.
  • Size the buffer pool to use most of the RAM on the VM.

To minimize the cost of sizing your VM like this, we recommend using a VM with ahigh ratio of RAM to virtual CPUs (vCPUs), to avoid paying for vCPUs that youdon't use.

For an ideal balance for most MySQL workloads, determine your workload's workingset, then choose the smallesthighmem instance shape that fits that workingset in RAM.highmem instance shapes have about 8 GB of RAM per vCPU. Thisgives you enough memory to cache a large working set, while keeping enough CPUto handle a high query load.

For workloads with large working sets but low query rates, using N4 instances,you can further optimize your total cost by usingcustom machine types withextended memory to further increase the RAM-to-vCPU ratio.

Configure your VM's network bandwidth

For most MySQL use cases, you can stick to the default network bandwidth limitsfor your instance. If this works for your needs, you don't need to upgrade toTier_1 networking.

Configure block storage

Google Cloud Hyperdisk is the only generation of durable block storage available forrecent Compute Engine VM families. We believe that Hyperdisk Balanced is the bestfit for the vast majority of MySQL workloads. For more information aboutHyperdisk, visit theHyperdisk documentation.

Google Cloud Hyperdisk

Hyperdisk Balanced offers the following features:

  • Solid state drive (SSD) latency at low cost
  • High-performance configurations for applications that need it
  • Better than 99.999% durability to protect against the industry-wide risk of hardware failure and silentdata corruption
  • Encryption of all Hyperdisk data at rest withGoogle-managed or customer-managed encryption keys

Select your performance level

With Hyperdisk Balanced, you select your performance level independently from the storagesize for the disk, so you can optimize your database's performance while onlypaying for the input/output (I/O) resources that your workload needs. If a MySQLdatabase's buffer pool is larger than its working set, then during steady-stateoperations, it can serve almost all read queries out of the buffer pool, withouttouching the disk.

To select a performance level for your Hyperdisk volume, consideryour MySQL write workload, with a particular emphasis on the following:

  • Access toInnoDB redo logs
  • Subsequent updates toInnoDB data files and indexes

Outside of steady-state operations, database maintenance events can also causespikier disk performance. The frequency with which this occurs tends to scalewith your database's write workload, so it's more likely in situations such aspost-crash recovery using redo logs or a backup system that copies itself byreading all database changes since the last backup.

Size your disk

There are three common strategies for sizing your disk performance limits:

  1. Use the default configuration. Each disk comes with at least 3,000input/output per second (IOPS) and 140 MiBps of throughput. This suffices forbasic MySQL workloads and operating system (OS) boot volumes. If your usecase outgrows this, you canmodify the provisioned I/O performance on-demand without stopping your workload.
  2. Measure your existing usage. If your database is already running inanother environment, record its disk IOPS and throughput at a granularity ofone minute or less. After you have one to two weeks of data, so that yoursample set includes some fluctuation in load and normal maintenance events,select a high-percentile value from that dataset, and add a small buffer toaccount for organic growth or unexpected usage.
  3. Estimate your needs, and then modify them later. If you don't have anexisting data source, you might have to estimate your performance needsinitially, and then tune them further after deployment. We recommendprovisioning a higher value than you think you'll need initially, so thatyour workload doesn't encounter performance bottlenecks, and then eventuallyreducing the provisioned performance to fit your workload.
Caution: To avoid performance bottlenecks, we recommend testing with your actualworkload, or a simulation of it, before you put it into production.

Increase your disk's performance

You can increase each Hyperdisk Balanced disk's performance up to a maximum of 160,000 IOPSand 2,400 MBps of throughput. The size of your VM helps determineHyperdisk's maximum performance limits, so if you want very highHyperdisk performance, you might need to increase your VM'snumber of cores. If your most demanding workloads need higher disk performancethan a single Hyperdisk Balanced disk can provide, you can use one of the following methodsto stripe together multiple Hyperdisk Balanced disks:

As you scale your MySQL databases, you can dynamically increase the capacity andperformance of your disks without downtime. This helps the performance of onlineanalytical processing (OLAP)-style workloads doing large complex joins whichcan't fit in RAM and spill to disk. In rare cases, MySQL workloads which requireextremely low storage latency and can tolerate data loss can store their fulldataset on Local SSD. You can also use the following hybrid solutions to improveread latency and limit reductions in durability:

  • Mirror your dataset between a Hyperdisk and a Local SSD.
  • Use a volume manager to configure Local SSD as a cache for data stored on anunderlying Hyperdisk.

Take advantage of additional Hyperdisk features

Hyperdisk also gives you the following features, which canaugment or simplify on-premises high availability and disaster recoveryworkflows:

For more information about configuring these features with MySQL forCompute Engine, see thehigh availability section that follows on this page.

Local SSDs

Some Compute Engine machine families let you use Local SSDs instead ofHyperdisk. These aren't durable storage, but MySQL workloadsoften use them to storetemporary tablespaces.

For information on using Local SSDs for scaling MySQL databases, seeDynamic disk resizing, which follows on this page.

Additional Compute Engine features

You can use the following Compute Engine features to help optimize yourMySQL deployment.

Cloud Monitoring

To monitor your VM's performance and usage of infrastructure services, useGoogle Cloud console. On theVM Instances page, in theObservability tab,you can monitor performance-related metrics like CPU and memory utilization,networking bandwidth, and provisioned performance of your instances. Similarly,on theDisks page, in theObservability tab, you can monitor thethroughput and IOPS of your disk volumes.

To customize the performance metrics that you see, use Cloud Monitoring tobuild queries. You can select the specific performance metrics that you want toview for your infrastructure services. For MySQL-specific metrics,Compute Engine offers aMySQL workload plug-in.

Best practices for configuring your operating system

  • Use an appropriate file system. Google focuses on optimizing for Linux'sext4 and XFS file systems; however, most file systems are appropriate foruse with MySQL.
  • Turn off Transparent Huge Pages (THP) in your base operating systemconfiguration. For steps to turn off THP, see theTHP documentation.
  • If you're using Linux, use therelatime, andlazytime flags for filesystem mount configuration. This reduces performance overheads associatedwith updating theatime,mtime, andctime values on files when they'reread, modified, or have their metadata changed.

Best practices for configuring MySQL

We recommend that you use the following configuration settings for MySQL.

  • Use a recent version of MySQL. Google focuses on optimizing for MySQLversion 8.0 and later versions.

  • Increase the size of the buffer pool. MySQL uses its buffer pool toimprove read performance by caching data in RAM, reducing disk accesses. Bydefault, MySQL's buffer pool size is 128MiB, which is too small for mostpractical use cases. We recommend that you increase the size ofinnodb_buffer_pool_size to be larger than the size of theworking set that your application accesses in the database. This usually consists of thefollowing steps:

    1. Measure or estimate the size of your working set on a running copy ofyour MySQL instance.
    2. Choose a virtual machine (VM) size and shape with enough RAM to fit thatworking set.
    3. Configure the size of the buffer pool on the VM to take up the majorityof the available RAM.

  • Turn on the doublewrite buffer. MySQL has adoublewrite buffer that helps protect againsttorn writes, a failure mode where a write thatcovers multiple blocks on disk might only be partially committed if ahardware or power failure occurs in the middle of the write. To benefit fromthis protection, turn oninnodb_doublewrite.

    Note: In some scenarios, it's safe to turn off the doublewrite buffer. Formore information, seeTurn off the MySQL doublewrite buffer,following on this page.

  • Set the value ofinnodb_flush_log_at_trx_commit to1. This ensures that write transactions are durable on disk whenthey're committed.

  • To reduce performance overhead, specify a value forinnodb_flush_method.For MySQL version 8.0.14 and later versions, set the value ofinnodb_flush_method toO_DIRECT_NO_FSYNC, which is optimal, but onlypresent in these versions. For MySQL versions earlier than 8.0.14, set thevalue ofinnodb_flush_method toO_DIRECT.

  • In high-availability replication scenarios, set the value of the primarydatabase instance'ssync_binlog to1. MySQL uses its binary log to communicate changes from theprimary database to the secondary database, so this ensures that the binarylogs are committed at transaction commit time, with the lowest possiblereplication lag and recovery point objective (RPO) between the databases.

  • When using MySQL on C-series machine families, turn oninnodb_numa_interleave.This ensures that MySQL's buffer pool can take advantage of non-uniformmemory access (NUMA) policies.

When to turn off the doublewrite buffer

MySQL's doublewrite buffer, which protects against torn writes, has aperformance overhead of up to 25% for MySQL write transactions, which couldpotentially impact transaction latency. Google Cloud Hyperdisk also offers tornwrite protection, so if you're using MySQL to write directly to an ext4 filesystem running on Hyperdisk, you can safely turn off thedoublewrite buffer.

However, for Hyperdisk's torn write protection to be effective,you must configure the file system and other intermediate software layersbetween the database and the disk to avoid introducing torn writes above thedisk layer. The following list provides examples of configurations which mightintroduce torn writes above the Hyperdisk layer:

While you can set up the preceding configurations so that they don't introducetorn writes, we don't recommend that you turn off the doublewrite buffer whenusing them, because of the difficulty of validating that a given configurationis safe.

(Optional) Turn off the doublewrite buffer

To turn off the doublewrite buffer, complete the following steps:

  1. On the ext4 file system, you must enable thebigalloc feature and configure the file system's cluster size to 16KiB, or a largerpower of 2 multiple of 16KiB. This ensures MySQL's writes won't be broken upinto separate IOs by the file system before being issued to Hyperdisk.Failing to raise the limit or using any value smaller than 16KiB won'tprotect against torn writes. As an example with 16KiB cluster size, this isconfigured at file system creation time:

    mkfs.ext4-Obigalloc-C16384/dev/<device-name>

  2. Disableinnodb_doublewrite and setinnodb_flush_method toO_DIRECT orO_DIRECT_NO_FSYNC (depending on your version of MySQL asdescribed above).

Configure high availability (HA) and a backup solution

We strongly recommend that you protect all of your critical MySQL workloads byconfiguring high availability (HA) and backup solutions for them. For both HAand backup, the following factors are most important:

HA solutions generally target near-zero RTO and RPO, but only protect againstinfrastructure failures. Backup solutions target longer RTO and RPO windows, butprovide coverage for a larger set of failure scenarios, such as the following:

  • Accidental data deletion
  • Ransomware attacks
  • Natural disasters

Configure high availability (HA)

HA features use storage and compute redundancy to ensure that your MySQLdatabase has reduced downtime in the event of a host failure or outage, lettingclient applications access its data even when an instance or zone isunavailable.

MySQL allows replication in the following modes:

  • Asynchronous mode. In asynchronous mode, the primary acknowledges writetransactions as soon as they're committed locally, so if there's an outageon the primary, then a small amount of recently-written data might be lostduring failover, as the RPO is close to zero, but not actually zero.
  • Semisynchronous mode. In semisynchronous mode, the primary waits toacknowledge the transaction until a configurable number of replicas hasacknowledged receipt of the transaction. This greatly increases the chancethat no data loss occurs during an unplanned failover, as the RPO iseffectively zero.

For both modes, RTO is determined by how quickly health checks do the following:

  1. Identify a failed instance.
  2. Trigger failover.
  3. Notify clients that the failover instance is now the primary, by using thedomain name system (DNS) or another way of identifying the database server.

In either replication mode, you must have a failover instance to replicate to.You can locate that instance in any of the following places:

  • The same zone that the primary instance is located in
  • A different zone within the region that the primary is located in
  • A different region than the primary is located in

To maintain high availability even during zonal outages, we recommend thefollowing configuration:

  • Locate your primary and failover instances in different zones, whetheror not they're within the same region.
  • Use asynchronous replication. This is because, if you're usingsemisynchronous replication, locating your primary and failover instances inseparate zones can cause high latency for write transaction commits.
  • If you require zero RPO, use Hyperdisk Balanced High Availability, which lets you synchronouslyreplicate a disk across two zones in the same region. For details, seeGoogle's guide on providing HA services using Hyperdisk High Availability.When you configure Hyperdisk Balanced High Availability, we recommend integrating withStateful Managed Instance Groups to diagnose instance health issues and automate recovery actions.

Configure a backup and data resilience plan

Backup and data resilience plans help to prevent data loss during failures likeaccidental data deletion, ransomware attacks, and natural disasters. You canalso use them as cold storage for compliance and auditing requirements. ForMySQL, there are many backup methodologies to choose from, some of which act atthe database level, and some of which act at the storage volume level. As youselect a methodology, you should primarily consider your RTO and RPOrequirements.

Back up at the database level

For database-level backups, consider using the following options that MySQLprovides:

For more information about MySQL's database-level backup options, seeBackup and Recovery in the MySQL documentation.

For any of these options, you must have a secondary storage system to copy thebackup data into. We recommend the following tools:

Use Hyperdisk to snapshot and clone at the storage level

For storage-level backups, we recommend using Hyperdisk productsto snapshot, clone, and otherwise capture a point-in-time view of your MySQLdatabase. The RPO for this approach depends on how frequently you take snapshotsof your database, and the RTO depends on which specific solution you use.

If fast recovery is important to you, and you only require backup coveragewithin a zone, we recommend that you use Hyperdisk'sinstant snapshots. Instant snapshots capture data at a specific point in timeincrementally, and can rapidly restore the data to a newHyperdisk volume throughdisk cloning,providing an RTO of minutes. They let you recover data when a disk's contentshave been overwritten, deleted, or corrupted, and are available locally in thesame zone or region as the source disk. For more information, seeAbout instant snapshots

For disaster recovery scenarios, in which data must be stored with higherredundancy than the original disk, and in a separate location to make sure thata single disaster doesn't affect all replicas of the data, we recommend that youuse Hyperdisk'sarchive and standard disk snapshots. Archiveand standard disk snapshots create a copy of the data in the disk at a point intime and store it with high redundancy in an immutable format. When you createmultiple snapshots of a disk, such as with asnapshot schedule,Hyperdisk only stores incremental changes. Archive and standarddisk snapshots are a good fit if you can tolerate higher RTO, because the datacopy from snapshot storage back into VM storage can mean that they take a longertime to restore. For more information, seeCreate archive and standard disk snapshots.

Hyperdisk's instant snapshots and its archive and standardsnapshots are both crash-consistent within a single disk. This means that whenyou restore from a snapshot, your MySQL database must run the normalInnoDB recovery steps to bring its logs and data files back to a consistent state. Depending on theconfiguration of your InnoDB redo log, this can lengthen the RTO. The followingpatterns can further complicate your efforts to create a consistent databasesnapshot:

  • Your MySQL database files are spread across multiple volumes.
  • You're using Linux software RAID utilities, such asmdadm.
  • You've separated MySQL's configured storage locations across filesystems ondifferent disks.

To create a snapshot which doesn't require recovery after a snapshot restore,complete the following steps:

  1. Temporarily lock write access to the MySQL database.
  2. Flush all in-progress buffers to disk by using theLOCK INSTANCE FOR BACKUP andFLUSH TABLES WITH READ LOCK commands.
  3. Initiate the snapshot operations.

  4. For multi-disk scenarios, after you've flushed at the MySQL level, executethesyncandfsfreeze commands on the server to flush all in-progress writes to diskand pause new incoming writes at the file system level.

    Note: The MySQL commands andfsfreeze prevent data modification whilethey're in effect, so while the snapshot operation is in progress, to allowyour database to continue write operations, you must release the respectivelocks in reverse acquisition order.

After you've captured the initial snapshot of your database, you don't need tocontinue locking your disk, because Hyperdisk rapidly capturesthe point-in-time view and then can asynchronously process any subsequentstorage copying steps. If you need these steps for snapshot consistency, and youwant to remove this write impact on the primary database, you can also runbackup against a database replica rather than the primary database.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.