Cloud SQL or VM Instance to host MySQL Database

Question

I have a website and i am confused where to host its database.

Google Cloud SQL D1 tier has 0.5 GB RAM and its cost is $1.46 per day.

GCE n1-standard-2 has 7.5 GB RAM and its cost is 1.68 per day.

I am hosting my current database on Cloud SQL and the performance goes down when concurrent active connections goes up. It must because of low RAM of cloud sql.

I can set up mysql server on VM Instance and can give remote access to external servers. Also, Cloud SQL has limitation on maximum connections.

So, why should i host it on Cloud SQL?

My main concern is performance.

Nimbus

Answer 1

Google Cloud SQL is a service that delivers fully managed MySQL databases. Google takes care of applying patches and updates, managing backups, and configuring replication. Installing MySQL on a Compute Engine VM will shift the maintenance and management of MySQL to you.

Every Cloud SQL instance is regionally available (https://cloud.google.com/sql/sla) and includes seven days of free backups. Data is automatically encrypted and replicated in many geographic locations and failover between copies is handled automatically.

Concerning cost, you are not be charged for your Cloud SQL instances when your database is inactive. A database instance is inactive if there are no connections for 15 minutes for "Per Use" billing and 12 hours for "Package" billing. "Per Use" and "Package" billing options help users find savings based on their database usage. Put together, Cloud SQL users with small, infrequently accessed databases often pay less than $1.00 per month, while users running large, frequently accessed databases pay more.

You may be able to outperform Cloud SQL by tuning a MySQL install on GCE specifically for your application, but I recommend factoring in features like automatic failover and backups since these can become management headaches.

Waterresistant

Thank you Brett for the insights. I know Cloud SQL will do maintenance and management on its own. But the problem i am facing is: There are db queries which involve multiple thousands of rows to be processed. On MySQL on GCE, it runs fine but on Cloud SQL it starts increasing Active Connections and ultimately the Server crashes and shows 500 error. I think it's because of low RAM of Cloud SQL instance. Any advice? – Nimbus

Deeper analysis is needed to determine why you are seeing the 500 error. That shouldn't happen. However, you might also look at Cloud SQL Second Generation instances, which scale higher than First Generation, as it sounds like you have a large, active database. – Waterresistant

@VitulGoyal Did you come right with a second gen? Or did you decide to use GCE? What was your findings? – Wehrle

@Wehrle I tried using Cloud SQL but i was getting frequent 500 Errors. So, i switched to GCE and now i manage backups on my own (using cron jobs which are then transferred to Cloud Storage) and it is running fine. Also, i found bugs in my SQL statements which were creating multiple Active Connections resulting in 500 error. So i think if i shift back to Cloud SQL, it would run fine on that too but i am feeling a bit lazy to do that :P. Also, i felt that Cloud SQL was a bit pricy for me than GCE so i am not switching back. Let me know if you need to know anything more :) – Nimbus

@VitulGoyal Thanks for response. Performance wise, have you found that using the VM's internal IP helps speed things up, due to Google SQL only allowing external IP, or is that negligible? The pending connection and active connection limits of 1st gen seem terrible? (100, 250-depending on tier), Hosting mysql on GCE help with that? Also I know GAE has a 12 connection limit for each instance, does GCE have something similar that you know about? – Wehrle

@Wehrle i did not find any performance issues using GCE. It was similar to Cloud SQL (i think even better than Cloud SQL). 250 Active Connections are more than enough if you have thousands of users on website. MySQL on GCE also has a limit of 250 connections. I have no idea about connection limits on GAE. – Nimbus

@VitulGoyal Thank you. My load tests have given a max of 280 RPS, users 1500. The issue is something with db, was hoping higher limit would solve the issue. But if you say it should handle so much, must be come config or something that blocking. Thanks – Wehrle

@BrettH. is this still correct? Instances are billed by active connections? – Maclay

My answer from 2015 referred to Cloud SQL First Generation. Cloud SQL Second Generation was released in 2016 and, earlier this year, First Generation was decommissioned. Cloud SQL Second Generation's billing model is different and instances are billed while they are running. Second Gen instances do not stop automatically based on activity (active connections). – Waterresistant