Google Cloud SQL - Cost of Synchronous replication
Google Cloud SQL, which runs a hosted MySQL, gives you the option of synchronous vs asynchronous replication when deploying an instance.
While synchronous replication does guarantee that your data will always remain in a consistent state, we took it for a spin to see just how well it works in real life.
System under test
- DB Instance region - us-east1
- DB Instance Tier - D8 (4 GB RAM)
- Query server region - AWS EC2 US-East 1
MySQL version - 5.6
Table schema -
CREATE TABLE `appPings` ( `appId` char(24) NOT NULL, `lastProcessTime` int(11) NOT NULL, PRIMARY KEY (`appId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- Insert statement under test -
INSERT INTO appPings (appId, lastProcessTime) VALUES (?,?) ON DUPLICATE KEY UPDATE lastProcessTime = VALUES(lastProcessTime)
- Rows in table
(The following screenshots are from the DripStat dashboard)
With Synchronous Replication
With Asynchronous Replication
With Synchronous replication we see an avg of almost 87 ms vs 34 ms for Asynchronous replication. That is quite a high cost indeed!
Note that most of the INSERT statements result in updates, so we are not inserting 400 rows a minute. The number of rows stays constant at around 500 throughout this test.
Read Only queries
To test if only writes are affected by the replication, we even had a
SELECT query happening on a different table around all this time.
The SELECT query was not affected at all by the change in replication mode.
Even assuming that of the 34ms taken while in asynchronous replication mode is all lost in network, using synchronous replication mode added over 52ms to our insert statements! This is an enormous overhead. It means we can't even do 20 writes a second.
We have thus concluded that Google Cloud SQL's synchronous replication mode is simply too slow to be of use in real world production scenarios. We will be using the asynchronous replication mode even though we would have much preferred to have the the data consistency guarantees offered by synchronous replication.