Google Cloud SQL is slow

Question

I have a D0 size Cloud SQL instance. When I run a simple

select * from table

which has around 500 rows, it takes on average 100 ms to execute (as reported by SQL Prompt). Whereas on my local instance of MySQL 5.5, it takes only 1 ms. My dev machine has 2.9GHz dual-core Intel Core i7 and 8GB 1600MHz memory. I've read in an FAQ that performance of db depends on the size - larger instances have more RAM and CPU.

Is this reasonable to expect performance issues resolved with larger instance size? Or am I missing something else here?

Pentane

it's a cloud service. you HAVE to allow for network latency. the fastest DB in the universe will still be slow if your pipe leading up to it is just a couple tins cans and a string with people yelling 1's and 0's in them. – Yanirayank

make it 1000, 10000 rows and check if it scales linearly. if it does you have a problem. but i do not think it will, because of constant overhead (network latency). – Hilaire

I believe, SQL Prompt reports actual query execution time, not SQL query + network latency. With latency it's around 400 ms, as reported by Chrome Dev Tools. – Pentane

I have a view that does union of 4 tables. Locally, it takes 10 ms to execute select * from view, on Cloud SQL it's 600 ms, with latency it's 1 s. – Pentane

@mnagel, I made it 10000 rows. The same 100 ms to execute as report by SQL Prompt. – Pentane

In 2021 it is still EXTREMELY SLOW! – Azeotrope

Answer 1

Here's our update in January/2019.

Using Google Cloud SQL SECOND GENERATION with a 46GB database in a 4vcpu + 15GB RAM instance, we found it can be ridiculously slow even when compared to a dev macbook pro running the default mysql install with only 125MB of memory allocated to it:

Mysql: Google Cloud SQL with 10GB RAM is 20x slower than Macbook Pro configured with 125MB ram

Kid

This is a shame. – Nannana

I it is not any better as of March 2020. Shockingly. We have to migrate back to AWS RDS – Sumy

November 2020: Awful. Trying Kamatera. AWS is next. What a shame... – Extremadura

Okay, let's stop sending comments about date as it seems to never get fixed, I mean, it's year 2022 and SELECT 123; takes 478 mili-seconds (postgresql). – Docila

Answer 2

EDIT: April 10 2016

GAE now offers Second Generation cloud mysql where even a basic tier like 'db-g1-small' performs as fast as a D8 tier in the old Cloud SQL offering. It is also significantly cheaper. This seems to be a big milestone and there is no reason to resort to hacks and workarounds any longer.

You can refer to Cloud SQL pricing but the approximate minimum cost is around $20 per month.

ORIGINAL POST

Google just provisions the VM on a slow box for the D0 tier. You could choose D4 but RAM is not the main issue as much as the processor (they don't mention the GHz).

Network latency is not the problem. For e.g. the 0.05s below is the query execution time on the server only. Any amount of time thereafter could be spent in data transmission.

mysql> select * from tracking limit 5;
+--------------------------------+-----------+-----------+
| id                             | scan_date | status    |
+--------------------------------+-----------+-----------+
| 420006929400111899561510697350 | NULL      | Delivered |
| 420010859400111899561989496058 | NULL      | Delivered |
| 420019849400111899561989496331 | NULL      | Delivered |
| 420100109400111899561903290311 | NULL      | Delivered |
| 420100319400111899561944407020 | NULL      | Delivered |
+--------------------------------+-----------+-----------+
5 rows in set (0.05 sec)

Edit: March 2016

For several apps I no longer use Cloud SQL and use a remotely hosted basic MySql cluster instead since GAE opened outbound socket connections. Sounds crazy? Not according to the numbers - sending a query and getting data back over this socket connection is faster than a co-located D3.

Zoomorphism

Answer 3

Views were the cause of poor performance. Google runs their own flavor of MySQL engine which is optimized in a way that can hurt views. If you have many joins or/and unions expect views to run slow.

However, it's been almost a year since I posted this question and things might have changed. I haven't revisited views since we moved away from using them.

Pentane

Things haven't changed. It seems 200ms is the average response time, it's not bad though. – Alkyd

Answer 4

1. Where are you connecting to your Cloud SQL instance from?
2. The tier size will have a big effect on performance. You can change the tier of the instance temporarily to test it.

Alchemist

1. I'm connecting from SQL Prompt developers.google.com/cloud-sql/docs/sql_prompt in a browser. 2. I will try changing tier size and post the results. – Pentane

I switched to D32 instance (the largest one) and it takes 30 ms to query a table with two records. Querying table with 500 records takes 75 ms. I think it's unreasonable. Amazon RDS performs all these operations in less than 1 ms. Do you have any ideas why it might be? – Pentane

I also experienced the same problem. I ran a query of the form - "select * from <table>" with only 1 row and the query time as measured by sql prompt took 200 ms. – Laureen

Same problems with latency... simple statements needs 1 second where it localy (to the VM in GCE) it only 0,01 second... 100x less latency... – Straus

Answer 5

We also had the same problem. With a D16 instance, a simple website forum page would take >10s to load. I just talked with a GoogleCloud tech-support engineer, who confirmed that CloudSQL is not really ready for "performance" (as of summer 2015), and he recommended rewriting everything to use DataStore...

So, if you have pages that make dozen of small SQL queries, and a dataset that's too big to fit entirely in the cache, then CloudSQL is not a viable solution right now.

Rawden

This comes as a shock, been developing our hole system for months to use cloudSQL. Why do you say as of summer.. did it have better performance or has it started lagging behind other providers since then? Datastore does not fit our needs at all. Any suggestions? – Rowell

I'm also from Cloud Platform Support and this specific case appears to be have been a use-case specific recommendation, rather than a blanket global statement that "Datatsore > Cloud SQL". – Insolvent

Should you really make dozens of calls per page load? That seems an overall not ideal plan in any case. – Shetrit

@Rowell You could swap it out to use another hosted MySQL service—for instance, RDS. – Semaphore