MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage

Question

I have a site running on Amazon EC2 , which runs fast and contains some reasonably complex MySQL queries.

Part of that site is an API which serves out data to another site. These queries are very simple (one is literally returning one row from a table containing only that row).

Making these API calls from the external domain results in a 10-20 second delay. The data always returns fine, no errors are thrown. Nothing is shown in the logs.

Running any of these queries locally in PHPMyAdmin , they complete in around 0.007 seconds .

CPU load is 20%-30%. RAM usage is about 60%.

My problem is similar to:

This

I've added 'skip_name_resolve' to my my.cnf. This made no difference.

There is a lot of talk about MySQL and DNS , but the docs around that didn't really help me.

This seems like a problem with a simple solution , but I cannot work out what it is.

EDIT1.

To clarify , my MySQL queries are very very basic. One of them is literally just

SELECT * FROM aboutPage

Where aboutPage contains 1 row and 5 columns of tiny data. This runs lightning quick as expected in PHPMyAdmin locally , but has a slow load when accessed remotely.

The main app that runs the API also runs fast . Its literally just when trying to access data from another IP/DNS that its slow.

Answer 1

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner

Answer 2

It very well may be a server setup issue... but not the MySQL server. Based on the description of your problem so far, this doesn't sound like it's really related to MySQL. From MySQL's perspective, all of its incoming connections are coming from the same place -- the server running the API and phpMyAdmin.

In fact, it's hard to see how it's possible that it could be MySQL-related since nobody outside is actually connecting directly to MySQL.

The problem sounds like it is further up the stack. How about the web server... could it possibly be configured to do reverse DNS lookups for logging?

Is the API slow from everywhere external? If you don't know, then you need to test.

If you can modify the web site code that runs the API, add code to collect some benchmarks and drop debug messages somewhere. Look up the system time and write some kind of debug message or log entry when the request arrives, when the mysql connection object (whatever library you're using) is acquired, when you're connected to mysql, when you're ready to send the query, when you get the result set back... the differences between these timestamps will tell you where the bottleneck is.

Or, just as likely, they'll all be very close in time, which will tell you that there is no bottleneck and that the remote site is experiencing a delay in establishing its initial connection to you.

It's possible that the web server logs will reveal execution times for the API calls. How do those compare to the experience from outside?

Are you running an Elastic Load Balancer in front of your EC2 instance? If you are, did you configure it for any availability zones where you don't actually have instances deployed? That's a common error and will result in unexpected timeouts because of the way ELB works. Remove it from any availability zones where it isn't currently being used.