Evaluating the Shift to MySQL 8 and Google Cloud SQL Enterprise Plus

At SpareRoom, our MySQL database, a foundational element of our infrastructure, has been hosted on Google Cloud SQL for approximately five years. A significant recent development involved upgrading to MySQL 8, which presented us with an opportunity to transition from the older N1 machine family Enterprise instances to the newer N2 machine family Enterprise Plus instances.

Enterprise Plus, while more expensive and featuring specific configuration requirements (vCPUs in powers of two, fixed 8GB RAM/vCPU), came with general promises of enhanced performance. The lack of concrete benchmarks made it difficult to determine if the increased cost was justifiable and what the optimal instance size should be. This situation necessitated conducting our own performance benchmarks.

Here’s an overview of the benchmark process and findings:

• Benchmark Setup:
  • Focus on critical workloads: top queries, batch DELETEs, and schema changes.
• Real-World Performance:
• Final Verdict:

The Benchmark Setup

Our benchmarking strategy targeted workloads that represent real operational challenges for us:

• High-Volume SELECT Queries: These are rapid queries (data fits in memory) that generate peak load, such as searches, views, and clicks.
• Batch DELETE Operations: Involving the archival and clearing of extensive log tables, these operations must be completed within tight nightly maintenance windows.
• Schema Modifications: Database migrations that typically consume the longest pipeline durations.

Our existing baseline was a 24 vCPU / 90GB RAM Enterprise instance. The default upgrade path suggested a 32 vCPU / 256GB Enterprise Plus instance, which was financially unfeasible due to its more than doubled cost. Instead, we compared our baseline against several alternatives:

• Enterprise Plus 16x vCPU: Considered the most practical candidate, tested with and without Data Cache.
• Enterprise Plus 8x vCPU: Used as a scaling baseline.
• Enterprise 28x vCPU: Chosen to roughly match the cost of the 16x Enterprise Plus instance while retaining the baseline 90GB RAM.
• Enterprise 16x vCPU: For a direct comparison with the 16x Enterprise Plus.

The Data Cache (DC) mentioned above is an optional, locally attached SSD storage designed to boost read performance. The detailed specifications for each instance type are presented below:

All benchmarks were conducted from a high-performance 64 vCPU C4D VM located in the same region and zone as the Cloud SQL servers.

Top-5 Queries Performance

Using Cloud SQL Query Insights, we identified our top five most frequent queries. These were then subjected to a weighted benchmark, balanced by their typical execution duration and using randomized bound variables. The MCE::Benchmark Perl module was utilized to simulate varying loads across multiple threads. Data Cache showed inconsistent results for in-memory SELECTs and was thus excluded from this specific analysis.

After warming up the database, benchmarks were run at 12 threads to simulate normal peak usage (below 50% load) and at 32 threads to assess full load behavior.

At medium load (12 threads):

• Enterprise Plus 16x demonstrated approximately 45% faster performance compared to Enterprise 24x/28x.
• Enterprise Plus 8x, despite its smaller size, matched the performance of the larger Enterprise instances, nearing full load.

At high load (32 threads):

• Enterprise 28x, with 75% more cores, eventually matched Enterprise Plus 16x under extreme load, but Enterprise Plus still showed no disadvantage.
• Enterprise Plus 8x maintained parity with Enterprise 16x, while being about 17% more cost-effective.

DELETEs and Schema Changes Performance

The next phase measured the performance of deleting millions of rows (in batches of 25k), modifying column definitions, and adding indices. For these operations, the CPU type and storage performance proved more critical than the core count.

• DELETE operations: Enterprise Plus instances were 25-40% faster, with the benefit varying based on Data Cache activation.
• Schema changes: Enterprise Plus showed a more modest but consistent edge of 5-15% in speed.

Real-World Performance Analysis

Based on the benchmark results, we migrated most of our databases to smaller Enterprise Plus instances, specifically transitioning the 24x Enterprise instance to a 16x Enterprise Plus. Using Query Insights, we compared the performance of our heaviest queries from the week prior to the upgrade with the week following. Traffic levels were comparable across both weeks, and the 32 query types analyzed represented approximately 360 million calls in each respective period.

The average performance improvement observed was around 35%. This figure reflects the actual query execution time, contrasting with the 45% improvement seen in the synthetic benchmark’s total round-trip time. The difference is likely attributable to network latency, which also saw slight improvements on Enterprise Plus.

Future analyses will delve deeper into latency and performance differences, including comparisons with non-x86 options.

Verdict

Despite a roughly 20% increase in cost for Enterprise Plus instances due to their rigid configuration options, the tangible performance gains, coupled with additional benefits such as a better SLA, near-zero downtime maintenance, 35-day Point-in-Time recovery, and an index advisor, made the migration a clear success.

For organizations currently using Cloud SQL Enterprise and considering an upgrade:

• Do not assume you need an equal or greater core count as dictated by the default upgrade path. Smaller Enterprise Plus instances can often outperform larger Enterprise instances; consider rightsizing after the upgrade.
• Anticipate a measurable real-world performance enhancement.
• The improved reliability, maintenance features, and other added functionalities are significant advantages.