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

dnsMySQL

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.

Best Answer

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