Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Jan 30 '13 at 19:54

This question came from our site for professional system and network administrators.

    
What type of data is it querying? Is it binary? –  ponsfonze Jan 30 '13 at 18:27
    
The API... is that over HTTP? The remote site is connecting to your web server, not to MySQL directly, correct? Is the web server providing the API actually the same machine that's running PHPMyAdmin and the same machine running MySQL or if not, how are they separated? –  Michael - sqlbot Jan 30 '13 at 20:39
    
The API runs over HTTP yes, and returns JSON/JSONP. The API app itself accesses the MySQL server. The web server running the API is the same machine running PHPMyAdmin. –  user156809 Jan 31 '13 at 10:58
add comment

2 Answers 2

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

share|improve this answer
    
Thanks for this. As I expected , nothing gets written to the slow query log. Even with a long_query_time of 3 seconds. I don't think its a slow query , I think its a server setup issue, hence why I added it to ServerFault. Running the query in PhpMyAdmin they take c.0.0006 seconds etc. –  user156809 Jan 31 '13 at 10:57
add comment

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.

share|improve this answer
    
Thanks for the help. I'm pretty sure you are correct, that this is a server issue not a MySQL/DB issue (I don't know why this was migrated from SF). I rolled back to a previous snapshot (code identical, just server setup older) and the issue dissapeared. I re-did my VirtualHosts setup and pruned my .htaccess but I'm still non the wiser as to what caused it though. –  user156809 Feb 1 '13 at 9:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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