Managing high-volume write operations in MySQL has long been a pain point for database administrators and engineers alike. Getting optimal performance typically requires deep expertise in InnoDB internals and constant manual adjustments. With Cloud SQL for MySQL Enterprise Plus edition, this complexity is now handled automatically through a capability called optimized writes — an intelligent system that continuously monitors your workload patterns and infrastructure health to fine-tune MySQL settings on the fly. The result? Lower write latency and higher throughput, all without you lifting a finger.
This feature comes enabled out of the box on every Enterprise Plus instance. In this article, I will break down how each optimization works under the hood and walk you through a hands-on benchmark so you can see the performance gains for yourself.
How Optimized Writes Works Behind the Scenes
The optimized writes capability bundles together five smart optimization layers. Each one targets a specific aspect of MySQL’s write path, automatically adjusting parameters and internal behaviors based on what your instance actually needs at any given moment:
Smart Purge Scheduling- Intelligently tunes the number of purge threads (innodb_purge_threads) so that background cleanup tasks don’t compete with your active queries for system resources.
Dynamic I/O Throttling- Monitors real-time workload intensity and adjusts I/O capacity limits (innodb_io_capacity, innodb_io_capacity_max) accordingly, preventing storage bottlenecks when traffic suddenly surges.
Parallel I/O Distribution- Spreads I/O operations across multiple internal locks (mutexes), eliminating single-point contention and allowing the storage layer to scale with demanding workloads.
Optimized Crash Recovery- Improves how temporary data is managed and accelerates dirty page flushing, which dramatically shortens restart times and makes it safe to configure larger redo logs for better sustained performance.
Intelligent Cache Preloading- Takes advantage of spare disk bandwidth to proactively load frequently-accessed data pages into memory after an instance restart, minimizing the warm-up period and delivering consistent performance faster.
Based on my benchmark test, Cloud SQL for MySQL Enterprise Plus with optimized writes achieves up to 3x the write throughput of the standard Enterprise edition, along with substantially reduced latency across the board.
Measuring the Performance Difference Yourself
Curious how much of an improvement you’ll see with your specific workload? You can run your own benchmarks using sysbench, a widely-used database performance testing tool. The steps below provide a complete, repeatable methodology — feel free to adjust the instance sizes and parameters to match your production environment.
Step 1: Create Database Instances
To study the performance improvement, first create three different classes of machines:
- Enterprise edition (ee)
- Enterprise Plus edition (ee+) without optimized writes
- Enterprise Plus edition (ee+) with optimized writes
# Set project variable
export PROJECT="your-project-id"
#------------------------------------------------------
# mysql-ee-standard - Enterprise Edition baseline
gcloud sql instances create mysql-ee-standard \
--database-version="MYSQL_8_0_39" \
--edition=ENTERPRISE \
--availability-type=zonal \
--zone="us-east4-b" \
--cpu=48 \
--memory=312GB \
--storage-size=2500 \
--storage-type=SSD \
--network=projects/${PROJECT}/global/networks/default \
--no-assign-ip \
--enable-google-private-path \
--no-enable-bin-log \
--database-flags="table_open_cache=180000,innodb_flush_neighbors=0,innodb_adaptive_hash_index=off,max_prepared_stmt_count=800000"
#------------------------------------------------------
# mysql-eeplus-baseline - Enterprise Plus WITHOUT optimized writes
# Note: This configuration is for comparison only and not recommended for production
# Without optimized write, larger redo log size will cause longer recovery time
gcloud sql instances create mysql-eeplus-baseline \
--database-version="MYSQL_8_0_39" \
--edition=ENTERPRISE_PLUS \
--availability-type=zonal \
--zone="us-east4-b" \
--tier=db-perf-optimized-N-48 \
--storage-size=2500 \
--storage-type=SSD \
--network=projects/${PROJECT}/global/networks/default \
--no-assign-ip \
--enable-google-private-path \
--no-enable-bin-log \
--database-flags="innodb_cloudsql_optimized_write=off,innodb_log_file_size=1073741824,table_open_cache=180000,innodb_flush_neighbors=0,innodb_adaptive_hash_index=off,max_prepared_stmt_count=800000"
#------------------------------------------------------
# mysql-eeplus-optimized - Enterprise Plus WITH optimized writes (default)
gcloud sql instances create mysql-eeplus-optimized \
--database-version="MYSQL_8_0_39" \
--edition=ENTERPRISE_PLUS \
--availability-type=zonal \
--zone="us-east4-b" \
--tier=db-perf-optimized-N-48 \
--storage-size=2500 \
--storage-type=SSD \
--network=projects/${PROJECT}/global/networks/default \
--no-assign-ip \
--enable-google-private-path \
--no-enable-bin-log \
--database-flags="table_open_cache=180000,innodb_flush_neighbors=0,innodb_adaptive_hash_index=off,max_prepared_stmt_count=800000"
Step 2: Create Client Instances
Next, create VMs for the client instances. Client instances are placed in the same region but different zone to simulate realistic network conditions.
# Create three client instances for parallel benchmarking
gcloud compute instances create sysbench-client-1 \
--zone="us-east4-c" \
--machine-type=n2-highmem-16 \
--boot-disk-size=15GB \
--create-disk=name=sysbench-data-1,size=120GB \
--network=projects/${PROJECT}/global/networks/default
gcloud compute instances create sysbench-client-2 \
--zone="us-east4-c" \
--machine-type=n2-highmem-16 \
--boot-disk-size=15GB \
--create-disk=name=sysbench-data-2,size=120GB \
--network=projects/${PROJECT}/global/networks/default
gcloud compute instances create sysbench-client-3 \
--zone="us-east4-c" \
--machine-type=n2-highmem-16 \
--boot-disk-size=15GB \
--create-disk=name=sysbench-data-3,size=120GB \
--network=projects/${PROJECT}/global/networks/default
# Note: Best practice is to benchmark all instances simultaneously using separate clients.
Then, log in to the client instances and execute following commands to install sysbench and mysql client:
gcloud compute ssh sysbench-client-1 --zone us-east4-c
sudo apt-get --purge remove sysbench
# Check for latest repo here https://dev.mysql.com/downloads/repo/apt/
wget https://repo.mysql.com//mysql-apt-config_0.8.36-1_all.deb
# Command will prompt for selecting the mysql version. Select version 8.0 or 8.4
sudo apt install ./mysql-apt-config_0.8.36-1_all.deb
sudo apt update
sudo apt -y install make automake libtool pkg-config libaio-dev
sudo apt -y install libssl-dev zlib1g-dev
sudo apt -y install libmysqlclient-dev
sudo apt -y install mysql-community-client-core
sudo apt -y install git
mkdir ~/sysbench
git clone https://github.com/akopytov/sysbench
cd sysbench
./autogen.sh
./configure --prefix=$HOME/sysbench/installed
make -j 16
make install
cd ~/
rm mysql-apt-config_0.8.36-1_all.deb
# Note: We use the latest sysbench binaries with all the fixes.
Step 3: Run the Benchmarking Workload
Finally, you can run the sysbench write benchmark using the following script:
mkdir benchmark-results
cd benchmark-results
# TODO: EDIT THE USER/HOST/PASSWORD in the script below
# Copy the script and replace the HOST IP address with the database instance IP
nohup ./write-workload.sh &
#!/bin/bash
# write-workload.sh
HOST=YOUR_DATABASE_IP_ADDRESS
sysbenchdir="$HOME/sysbench/installed/"
mysqlclidir="/usr/bin"
mysqluser="root"
mysqlpasswd="your_secure_password"
rows=("10000000")
for (( k=1; k<=4; k+=1 ))
do
for row in ${rows[@]};
do
#------------
# Phase 1: Load test data
$mysqlclidir/mysql -u $mysqluser -h $HOST -e "drop database if exists perftest; create database if not exists perftest;"
$sysbenchdir/bin/sysbench --threads=80 --mysql-host=$HOST --mysql-port=3306 --mysql-db=perftest --mysql-user=$mysqluser --mysql-password=$mysqlpasswd $sysbenchdir/share/sysbench/oltp_insert.lua --tables=80 --table-size=$row prepare
$sysbenchdir/bin/sysbench --threads=96 --events=8000000 --time=0 --report-interval=10 --mysql-host=$HOST --mysql-port=3306 --mysql-db=perftest --mysql-user=$mysqluser --mysql-password=$mysqlpasswd $sysbenchdir/share/sysbench/oltp_update_index.lua --tables=80 --table-size=$row run
sleep 300
#------------
# Phase 2: Execute write benchmarking workload
for (( i=96; i<=384; i*=2 ))
do
$sysbenchdir/bin/sysbench --threads=$i --time=3600 --report-interval=10 --mysql-host=$HOST --mysql-port=3306 --mysql-db=perftest --mysql-user=$mysqluser --mysql-password=$mysqlpasswd $sysbenchdir/share/sysbench/oltp_write_only.lua --tables=80 --table-size=$row run
sleep 120
done
$mysqlclidir/mysql -u root -h $HOST -e "drop database if exists perftest;"
done
done
Once the benchmark completes, you’ll have throughput and latency numbers from all three Cloud SQL instances. The results should clearly show the performance benefits of Enterprise Plus edition, with optimized writes delivering even greater improvements. Keep in mind that actual numbers can vary depending on your specific machine configurations and workload characteristics.
Get Started with Optimized Writes Today
The good news? You don’t need to flip any switches. Optimized writes is automatically enabled on all new and existing Enterprise Plus instances. If you’re still running on Enterprise edition, now is a great time to upgrade to Enterprise Plus and start benefiting from these performance enhancements right away.
Unlocking 3x Write Performance: A Deep Dive into Cloud SQL MySQL Optimizations was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/unlocking-3x-write-performance-a-deep-dive-into-cloud-sql-mysql-optimizations-69a504856170?source=rss—-e52cf94d98af—4
About the Author
