Google Cloud SQL slow outputting query result
up vote
0
down vote
favorite
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?
mysql
google-cloud-sql asked Nov 8 at 17:45
Nicola Donadoni
63
add a comment |
up vote
0
down vote
favorite
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?
mysql
google-cloud-sql asked Nov 8 at 17:45
Nicola Donadoni
63
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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?
mysql
google-cloud-sql asked Nov 8 at 17:45
Nicola Donadoni
63
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?
mysql
google-cloud-sql mysql
google-cloud-sql asked Nov 8 at 17:45
Nicola Donadoni
63
asked Nov 8 at 17:45
Nicola Donadoni
63
asked Nov 8 at 17:45
Nicola Donadoni
63
asked Nov 8 at 17:45
Nicola Donadoni
63
asked Nov 8 at 17:45
Nicola Donadoni
63
63
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53213403%2fgoogle-cloud-sql-slow-outputting-query-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
