Home / server / Questions / 666995
Accepted
David542
Asked: 2015-02-12 23:49:43 +0800 CST

Debugging a mysql connection

  • 772

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.
mysql

1 Answers

  1. Best Answer

    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)
    • 2