31

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?

andriys
  • 2,202
  • 2
  • 19
  • 24
  • 2
    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. – Marc B Sep 04 '13 at 15:40
  • 2
    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). – mnagel Sep 04 '13 at 15:51
  • 3
    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. – andriys Sep 04 '13 at 15:57
  • 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. – andriys Sep 04 '13 at 16:01
  • @mnagel, I made it 10000 rows. The same 100 ms to execute as report by SQL Prompt. – andriys Sep 04 '13 at 16:11
  • In 2021 it is still EXTREMELY SLOW! – WebDev-SysAdmin Jun 24 '21 at 01:03

5 Answers5

13

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

sandre89
  • 5,218
  • 2
  • 43
  • 64
6

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.

Fakeer
  • 985
  • 1
  • 13
  • 29
6

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.

Riccardo
  • 1,104
  • 13
  • 22
andriys
  • 2,202
  • 2
  • 19
  • 24
3
  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.
Joe Faith
  • 618
  • 3
  • 5
  • 1. I'm connecting from SQL Prompt https://developers.google.com/cloud-sql/docs/sql_prompt in a browser. 2. I will try changing tier size and post the results. – andriys Sep 04 '13 at 17:45
  • 6
    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? – andriys Sep 05 '13 at 14:36
  • I also experienced the same problem. I ran a query of the form - "select * from " with only 1 row and the query time as measured by sql prompt took 200 ms.
     – Sathya Apr 27 '14 at 15:27
  • 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... – aholbreich Mar 23 '15 at 22:08
3

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.

stephane
  • 807
  • 7
  • 9
  • 1
    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? – cfl Aug 31 '15 at 10:11
  • 1
    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". – Nick Sep 28 '15 at 23:10
  • 1
    Should you really make dozens of calls per page load? That seems an overall not ideal plan in any case. – bwawok Oct 26 '15 at 20:00
  • 2
    @cfl You could swap it out to use another hosted MySQL service—for instance, RDS. – Joe Mornin Jan 22 '16 at 19:53