Google Cloud SQL VERY SLOW

Question

I am thinking to migrate my website to Google Cloud SQL and I signed up for a free account (D32).

Upon testing on a table with 23k records the performances were very poor so I read that if I move from the free account to a full paid account I would have access to faster CPU and HDD... so I did.

performances are still VERY POOR.

I am running my own MySQL server for years now, upgrading as needed to handle more and more connections and to gain raw speed (needed because of a legacy application). I highly optimize tables, configuration, and heavy use of query cache, etc...

A few pages of our legacy system have over 1.5k of queries per page, currently I was able to push the mysql query time (execution and pulling of the data) down to 3.6seconds for all those queries, meaning that MySQL takes about 0.0024 seconds to execute the queries and return the values.. not the greatest but acceptable for those pages.

I upload a table involved in those many queries to Google Cloud SQL. I notices that the INSERT already takes SECONDS to execute instead than milliseconds.. but I think that it might be the sync vs async setting. I change it to async and the execution time for the insert doesn't feel like it changes. for now not a big problem, I am only testing queries for now.

I run a simple select * FROM <table> and I notice that it takes over 6 seconds.. I think that maybe the query cache needs to build.. i try again and this times it takes 4 seconds (excluding network traffic). I run the same query on my backup server after a restart and with no connections at all, and it takes less than 1 second.. running it again, 0.06 seconds.

Maybe the problem is the cache, too big... let's try a smaller subset

select * from <table> limit 5;

• to my server: 0.00 seconds
• GCS: 0.04

so I decide to try a dumb select on an empty table, no records at all, just created with only 1 field

• to my server: 0.00 seconds
• GCS: 0.03

profiling doesn't give any insights except that the query cache is not running on Google Cloud SQL and that the queries execution seems faster but .. is not...

My Server:

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000225 |
| Waiting for query cache lock   | 0.000116 |
| init                           | 0.000115 |
| checking query cache for query | 0.000131 |
| checking permissions           | 0.000117 |
| Opening tables                 | 0.000124 |
| init                           | 0.000129 |
| System lock                    | 0.000124 |
| Waiting for query cache lock   | 0.000114 |
| System lock                    | 0.000126 |
| optimizing                     | 0.000117 |
| statistics                     | 0.000127 |
| executing                      | 0.000129 |
| end                            | 0.000117 |
| query end                      | 0.000116 |
| closing tables                 | 0.000120 |
| freeing items                  | 0.000120 |
| Waiting for query cache lock   | 0.000140 |
| freeing items                  | 0.000228 |
| Waiting for query cache lock   | 0.000120 |
| freeing items                  | 0.000121 |
| storing result in query cache  | 0.000116 |
| cleaning up                    | 0.000124 |
+--------------------------------+----------+
23 rows in set, 1 warning (0.00 sec)

Google Cloud SQL:

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000061 |
| checking permissions | 0.000012 |
| Opening tables       | 0.000115 |
| System lock          | 0.000019 |
| init                 | 0.000023 |
| optimizing           | 0.000008 |
| statistics           | 0.000012 |
| preparing            | 0.000005 |
| executing            | 0.000021 |
| end                  | 0.000024 |
| query end            | 0.000007 |
| closing tables       | 0.000030 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000006 |
| cleaning up          | 0.000005 |
+----------------------+----------+
15 rows in set (0.03 sec)

keep in mind that I connect to both server remotely from a server located in VA and my server is located in Texas (even if it should not matter that much).

What am I doing wrong ? why simple queries take this long ? am I missing or not understanding something here ?

As of right now I won't be able to use Google Cloud SQL because a page with 1500 queries will take way too long (circa 45 seconds)

Beneficial

Can you please contact as at [email protected] with instructions on how to replicate this issue? If the network latency is not an issue and the data is in RAM the Google Cloud SQL should perform well. – Mikaelamikal

I did email to that same email address but right after I posted this message but i haven't got a response yet.. – Beneficial

I just pick up the case. I apologize that nobody did that already. :-( – Mikaelamikal

It's ok, if it gets resolved I will be happy to use it for all or mysql needs – Beneficial

Any update on this? Query cache seems like an obvious thing to enable. would like to know if it is supported / when it will be / etc. – Blent

they pretty much say that you have to use their internal app engine to get good results because of the hoops to get to your server. it makes sense but won't be feasible for my situation.. – Beneficial

@Beneficial Pls has this been resolved did a search and saw this post want to know because my company is willing to use migrate our applications to the cloud and im recommending google cloud using google cloud sql. – Flagrant

@Flagrant we tested amazon and got much better results, still great latency but much better than google. – Beneficial

This question is far too broad to operate as an effective Stack Overflow Q&A. I recommended closing with a flag just now. There are better places elsewhere on the web where the discussion of Cloud SQL use case and performance is discussed. – Calamander

The question is "how do you guys do it? This is my configuration, those are my tests, how can I make it work?" – Beneficial

experienced exact same results. pages that took 1 second to load took 15 after migrating to GCS. found buggy code that repeated queries hundreds of times, but this only took the load time down to 4 seconds. From 1 second to 4 just by moving to GCS. No cpu spikes, no IO spikes, no cpu usage on the web app machine. – Ratepayer

Note a helpful answer about why not to use query cache and how Cloud SQL is optimized differently. – Kings

Answer 1

I know this question is old but....

CloudSQL has poor support for MyISAM tables, it's recommend to use InnoDB.

We had poor performance when migrating a legacy app, after reading through the doc's and contacting the paid support, we had to migrate the tables into InnoDB; No query cache was also a killer.

You may also find later on you'll need to tweak the mysql conf via the 'flags' in the google console. An example being 'wait_timeout' is set too high by default (imo.)

Hope this helps someone :)

Thundersquall

Answer 2

Query cache is not as yet a feature of Cloud SQL. This may explain the results. However, I recommend closing this question as it is quite broad and doesn't fit the format of a neat and tidy Q&A. There are just too many variables not mentioned in the Q&A and it doesn't appear clear what a decisive "answer" would look like to the very general question of optimization when there are so many variables at play.

Calamander