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 published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.

It seems that all queries, but especially select statements, are performing very slow. If I check this issue on my local PC, it is working very well. But when I try to get data from RDS instance, it is slow. Some of the select statements takes 2-3 seconds to fetch data.

I have properly tuned up all table indexes, and normalized/de-normalized as required. I have made all necessary settings on RDS custom parameter group eg. max_connection, buffer etc. (don't know if I am something missing), but it has not helped. Can someone help me figure out the issue?

share|improve this question
add comment

3 Answers

Make sure your security group (firewall) only allow connections from your IPs to MySQL port.

In your AWS control panel check monitoring tab for RDS CPU, Reads, writes etc.

Make sure you are using InnoDB (because it has better locking-level and concurrency) which could be your issue (compared to your lonely local development environment).

share|improve this answer
    
MyISAM tables may explain efficiency issues. RDS is configured for InnoDB. –  ypercube Apr 16 '13 at 16:03
    
@ypercube sometimes when you import dumps into RDS, the dump may contain CREATE TABLE ... ENGINE=MyISAM –  muayyad alsadi Apr 18 '13 at 13:31
    
I meant that the default configuration is towards InnoDB tables. You can have MyISAM (and other engines) but you have to change the configuration accordingly, for efficiency. –  ypercube Apr 18 '13 at 13:33
add comment

You can profile your MySQL queries using Show Profile

SET PROFILING = 1;

SET profiling_history_size = 0;

SET profiling_history_size = 15;

YOUR QUERY;

SHOW PROFILES;

SELECT state, ROUND(SUM(duration),5) AS duration (summed) in sec FROM information_schema.profiling WHERE query_id = GROUP BY state ORDER BY duration (summed) in sec DESC;

SET PROFILING = 0;

SQLyog-MySQL GUI has a feature which shows detailed profiling of a query along with EXPLAIN PLAN, analyze the output and change your query accordingly. It helps in identifying which thread state takes how much time.

share|improve this answer
add comment

When you create an RDS instance, you pick an instance size, a database size, and optionally determine if you need specific IOPS.

I would start by looking at the RDS monitors, and see if there are any parameters that are hitting limits. You may need to increase the instance size or IOPS to increase performance.

share|improve this answer
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.