0

I am trying to figure out how to diagnose a mysql connection issue.

  • When I run a group of queries locally on my machine it takes about 1s to run.
  • When I run that same group of queries on an AWS machine that has an RDS-backed mysql instance, it takes roughly 8x longer, 8s to run. The machines are roughly comparable in size.

Two questions here:

  • How would I go about diagnosing what the issue is? It isn't the query or database, as they are the same in both cases. For example, what are some tools or commands that I could use to pinpoint the possible issue?
  • What are some ways to speed up the connection from my ec2 server to the rds server? I was under the impression that an rds-backed instance was relatively the same in speed (if not faster) than having the db on the server.
  • Just a clarification thing: When you say you run a group of queries on your local machine - does that mean the actual DB server is also on the local machine or are you somehow accessing the RDS from the local machine? – MrMajestyk Feb 12 '15 at 8:09
  • @MrMajestyk no local machine + local database. vs. AWS ec2 instance + rds – David542 Feb 12 '15 at 16:40
2

You should use profiling.

set profiling=1;

Then do your queries in the same session. Then look for profiles:

show profiles;

And display them:

show profile for query X;, where X - is a number of profile.

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000042 |
| Waiting for query cache lock   | 0.000011 |
| init                           | 0.000008 |
| checking query cache for query | 0.000081 |
| checking permissions           | 0.000017 |
| Opening tables                 | 0.000143 |
| init                           | 0.000038 |
| System lock                    | 0.000021 |
| optimizing                     | 0.000020 |
| executing                      | 0.000021 |
| end                            | 0.000013 |
| query end                      | 0.000010 |
| closing tables                 | 0.000027 |
| freeing items                  | 0.000082 |
| cleaning up                    | 0.000198 |
+--------------------------------+----------+
15 rows in set, 1 warning (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.