[google-cloud-sql-discuss] Re: Google Cloud SQL slow outputting query result

I would add on your to-do list like, implement a cache system, case your queries will not be constrained to I/O performance. Ideally, the entire data set can fit in 70% of RAM. And additionally, I recommend that you investigate non-Cloud SQL issues, like optimized queries by adding appropriate indexing, reduce the scanned data and avoid extra round trips. You can find some information regarding this in the following website[1].

Also, you can troubleshoot performance issues by following the instructions in the following documentation[2].

I hope this documentation could help you to determine the root cause of this performance issue.

[1] https://use-the-index-luke.com/

[2] https://cloud.google.com/sql/docs/postgres/diagnose-issues

On Wednesday, November 14, 2018 at 3:59:49 PM UTC-5, Nicola Donadoni wrote:

I am having a problem querying my database on Google Cloud SQL (mysql second generation): whenever I execute a query, its result is received in a relatively large amount of time, and I've noticed that it's a problem of fetching the data rather than executing the query, because this duration changes every time that I re-run the same query on the same underline data.

My SQL instance is a db-n1-standard-1 and CPU or RAM usage don't get even close to its hardware limitation. I have increased the disk space to 50GB (SSD) to allow higher throughput but nothing changed.

For example, if I query 6MB of data, I get an answer after ~600ms to ~1s when I would expect to receive it in about 300ms given that the lowest throughput on the SSD is 24MB/s and the network throughput is irrelevant at 250MB/s. This happens both when I connect to the SQL instance from my computer or from Google App Engine (I isolated the log for detecting exactly only the query + data transport time), DB is in Belgium, App Engine Server is in London.

If I run the query with the mysql profiling active, I can see that the actual query (the sum of all the durations of the steps) takes about 3ms so my guess is that there is problem transporting the data. I do use some JSON_OBJECT transformation when selecting but I guess that that should be already considered in some of the steps of the profiling result?

Can you please help me to identify and solve the problem?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b1ea6258-f062-4109-873a-744012342941%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.