At SpareRoom, MySQL is a core part of our infrastructure and it's been running on Google Cloud SQL for about five years. Earlier this year we faced a major change: upgrading to MySQL 8. This gave us the option of switching from Enterprise instances (old N1 machine family), to Enterprise Plus, which features the newer N2 machines.
Enterprise Plus is more expensive, has very specific configuration options (vCPU in powers of 2, fixed 8GB RAM/vCPU), several new features, but rather vague promises of performance improvements. Without clear benchmarks, it wasn’t obvious whether the extra cost was justified, or what the right size of instance should be.
This was obviously the time to set up my own benchmarks.
Quick table of contents:
The Benchmark Setup
As I always do, I focused on workloads that reflect real-world pain points for us:
- Top queries: quick (tables fit in memory) but very high-volume SELECTs (searches, views, clicks, etc.) that drive peak load.
- Batch deletes: archiving and clearing out large log tables, which must fit limited nightly windows.
- Schema changes: migrations that tie up our pipeline for the longest.
Our baseline DB was a 24 vCPU / 90GB RAM Enterprise instance. The upgrade tool defaults to "next up", so 32 vCPU / 256GB Enterprise Plus (more than double the cost, so not viable). Instead, we compared our baseline to:
- Enterprise Plus 16x vCPU (most reasonable candidate, DC on/off versions)
- Enterprise Plus 8x vCPU (scaling baseline)
- Enterprise 28x vCPU (rough cost match to 16x Plus when keeping baseline 90GB RAM)
- Enterprise 16x vCPU (direct comparison to 16x Plus)
DC above is Data Cache, an optional locally attached SSD storage that can improve read performance. All the above in a table:
| Instance | vCPUs | Machine | RAM (GB) | Storage (GB) | IOPS | Price/h |
|---|---|---|---|---|---|---|
| Enterprise x16 | 16 | N1 | 64 | 1500 | 25,000 | $1.64 |
| Enterprise x24 | 24 | N1 | 90 | 1500 | 25,000 | $1.97 |
| Enterprise x28 | 28 | N1 | 90 | 1500 | 25,000 | $2.40 |
| Enterprise Plus x8 | 8 | N2 | 64 | 1500 | 15,000 | $1.36 |
| Enterprise Plus x16 | 16 | N2 | 128 | 1500 | 25,000 | $2.37 |
| Enterprise Plus x16 DC | 16 | N2 | 128 | 1500 + 750 (DC) | 25,000 | $2.54 |
Benchmarks were run from a fast 64 vCPU C4D VM in the same region and zone as the Cloud SQL servers.
Top-5 Queries
Using Cloud SQL Query Insights, I pulled our top-5 queries and ran them in a weighted benchmark (queries balanced by duration) with randomized bound variables. The benchmark ran with my MCE::Benchmark Perl module, which loads it on multiple threads to simulate any amount of load. Results with Data Cache on vs off were inconsistent and since in-memory SELECTs are not supposed to benefit from it anyway, I excluded it from analysis.
After warming up the DB, I ran the benchmark on 12 threads to see performance when the DB is at around or below 50% load (i.e. normal operation at peak usage) as well as 32 threads to get an idea of full load behaviour:
At medium load (12 threads):
- Enterprise Plus 16x was ~45% faster than Enterprise 24x/28x.
- Enterprise Plus 8x was small enough to be close to full load, but still kept up with the big Enterprise instances.
At high load (32 threads):
- With 75% more cores, Enterprise 28 finally caught up at high load. But even in this scenario, which is the worst case for Enterprise Plus, it did not show a disadvantage.
- The Enterprise Plus 8x kept up with Enterprise 16x (while costing ~17% less).
DELETEs and Schema Changes
Deleting millions of rows (25k at a time in a loop), modifying the definition of a column and adding an index were measured next. The number of cores has very little importance (especially for the schema changes), it's more CPU type and storage that matters:
- DELETEs are 25-40% faster on Enterprise Plus, depending on whether you have Data Cache enabled.
- Schema changes are at a more modest 5-15% difference, with Enterprise Plus always having a small edge.
Real World Performance
Armed with the above results, we switched most of our DBs to (smaller) Enterprise Plus instances. The above Enterprise 24x became an Enterprise Plus 16x. Using Query Insights I saved the performance of the heaviest queries during the last week of Enterprise and compared them with the performance of the same queries during the first week of Enterprise Plus to calculate performance gains per query. The two weeks were reasonably comparable in traffic and the 32 query types plotted below represent about 360 million calls for each respective week:
The average performance gain is about 35%. This is the actual execution time, vs the total round-trip time of the synthetic benchmark above that was closer to 45%. Some of the gap is likely due to network latency, which also improved slightly on Enterprise Plus.
I will follow up with a post exploring latency and performance differences further, along with comparing non-x86 options.
Verdict
Overall, Enterprise Plus cost us ~20% more (due to the limited configuration options). However, the performance gains, as well as the extra benefits (better SLA, near-zero downtime maintenance, 35 day Point-in-Time recovery, index advisor and more), made it a clear win.
If you’re running Cloud SQL on Enterprise and considering the move:
- Don’t assume you need the same or higher core count like the upgrade forces you to. Smaller Enterprise Plus can outperform bigger Enterprise, so downsize after your upgrade.
- Expect a tangible real-world performance boost.
- The reliability/maintenance and other features are a nice bonus on top.
Top comments (0)