Filip Zelic
Filip Zelic

Reputation: 110

Google Cloud: MySQL queries very slow

After deploy on google cloud my app had very big latency, over 5s. After searching for a problem, find out that queries on MySQL database are the problem and taking really long to execute.

Here are some examples from same database compared on different servers. The commands are issued through ssh directly on sql server:

select * from tbl1;
local - 54343 rows in set (0.14 sec)
shared hosting - 54343 rows in set (0.89 sec)
google - 54343 rows in set (26.73 sec)

select * from tbl2;
local - 132 rows in set (0.00 sec)
shared hosting - 132 rows in set (0.01 sec)
google - 132 rows in set (0.20 sec)

select * from tbl3 inner join tbl4 ON tbl4.tbl3_id = tbl3.id;
local - 746 rows in set (0.00 sec)
shared hosting - 746 rows in set (0.12 sec)
google - 746 rows in set (0.95 sec)

I am using D1, but also tried D32 tier but didn't get much better results. I am using default setup (flags etc.). What do you think that could cause a problem ? Also I have tried async file system repc., turn off some logging flags.

Upvotes: 3

Views: 1293

Answers (1)

Trent Lloyd
Trent Lloyd

Reputation: 1892

Given your local query times are so short, even for a very large number of rows, I suspect the Query Cache may be at play here.

Try your queries both locally and remote using SQL_NO_CACHE like this:

select sql_no_cache * from tbl3 inner join tbl4 ON tbl4.tbl3_id = tbl3.id;

If that evens out the times, likely query cache is not configured on the Google server. Enabling it might give you a good start for now, however, if you have queries taking multiple seconds regularly likely you need to look at optimising them.

8.8.1 Optimizing Queries with EXPLAIN

Upvotes: 1

Related Questions