Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

After spending several days researching, I have placed a website on a c1.medium instance, Amazon Linux, and the MySQL database on a db.m1.instance. The RDS is running MySQL version 5.6.13. I have allocated 100 GB for the DB instance and have set the provided IOPS at 1,000. The website is photo based, permits user uploads and at peak hours has 400+ visitors.

Once I enabled the slow query logging I found the issue appears to be with the wp_options table, which when looking into phpmyadmin I found contains information on the WordPress plug-ins and theme. Ex:

SET timestamp=1390186963;

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Time: 140120 3:04:17

User@Host: xxxx Id: 744

Query_time: 49.248039 Lock_time: 0.000180 Rows_sent: 485 Rows_examined: 538

After experimenting with a few of the DB parameters I set the query_cache_type to 1 and the query_cache_size to 64MB. I was hoping that enabling the caching would stop the database from repeatedly calling the wp_options table, but that unfortunately doesn’t appear to be the case. Any suggestions? What would be the next steps to take to figure out the cause of this issue? When looking at the CloudWatch metrics the hardware appears to be sufficient, but maybe not?

Below are screenshots of the CloudWatch metrics for both instances.

EC2: http://s1038.photobucket.com/user/Panoramic2/media/EC2CloudWatchMetrics-2_zpsb28259d1.png.html?sort=3&o=0

RDS: http://s1038.photobucket.com/user/Panoramic2/media/RDSCloudWatchMetrics-2_zps6b782ac7.png.html?sort=3&o=1

share|improve this question
    
I run a big WP install and know this very headache. This is by design: WordPress starts up with this query because it needs your site options. Unfortunately it's easy as a developer to stash a lot of rows (vs. large arrays of data) and I'm guilty of slowing things down as well. The only way to avoid it is to avoid WP by offloading to static/cached pages. We have a trick where we check for cached pages in wp-config before letting it load anything. –  editor Jan 24 at 1:54
    
No way this query should take 49 seconds. Something is horribly wrong with RDS here. Even an out of the box, unconfigured MySQL wouldn't perform that badly. –  Michael Hampton Jan 24 at 2:09
    
@editor How would you recommend offloading to static / cached pages? –  panoramic Jan 24 at 6:05
    
@MichaelHampton Something is certainly horribly wrong! Haha. Any suggestions on how to pinpoint the problem? –  panoramic Jan 24 at 6:06
    
Memcache-based approach (highly reccomended, even if just running locally): wordpress.org/plugins/batcache Static HTML approach: wordpress.org/plugins/w3-total-cache –  editor Jan 27 at 14:36

2 Answers 2

1,000 IOPS is way to much provisioned.

IOPS are interesting when you read/write a lot on disk. Wordpress should use 90% read and 10% write. You should hit the memory cache most of the time.

If you're database and queries are correctly set you wouldn't need that much IOPS.

Considering the number of people using Wordpress (I don't know that particular plugin) I would bet that queries are correctly configured.

RDS is limited to InnoDB engine. That engine relies on a parameter called innodb_buffer_pool_size for caching data in memory. This is the one to look after at first. Fortunatly this value is automatically set by RDS (a factor of the memory you have in your RDS instance).

49 ms is not that slow. I bet your problem is elsewhere. Try a tool that will analyze your slow queries (order and aggregate them) : http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

To grab the slow query log file : http://www.palominodb.com/blog/2011/10/20/exporting-mysqlslowlog-table-slow-query-log-format

Most of the time you don't need to play with the query_cache parameter. If you set too much value you could even hit a performance penalty (each time you change a data you need to invalidate the cache for the according query impacted).

Finally the Cloudwatch graphs show that your DB is sleeping but your web server is using too much CPU.

The bottleneck here is definitly not your DB.

share|improve this answer
    
Would you recommend that I not use Provisioned IOPS and switch to standard instead? Or should I reduce reduce the number of IOPS? I'm under the impression that the slow query log is in seconds not milliseconds. –  panoramic Jan 24 at 6:19
    
If you don't mind wasting your money you could leave the 1000 iops otherwise you could decrease to 200 iops. Check disk latency and queue length. Did you try to run the query manually in mysql client and see how long it takes ? –  Pestouille Jan 24 at 19:42
    
Thanks, In general I try to avoid wasting money, I'll certainly decrease the IOPS as it is not helping. When running the query from the MySQL client the time is essentially nothing. Anywhere from 0.02 seconds to 0.04 seconds. What would be the next step? –  panoramic Jan 25 at 0:10
    
I can see that you have lot of network bandwdith used on the web front. If you saturate the link on the Apache/Nginx server maybe you don't have enough bandwidth to query your DB or to serve additional requests (leaving the feeling that your website is really slow). I also can imagine that the slow query could occur during the maintenance/backup window. You need to know how many times you hit slow queries. Finally maybe the best thing you could do is to take consulting for that task. –  Pestouille Jan 27 at 16:00
Query_time: 49.248039 Lock_time: 0.000180 Rows_sent: 485 Rows_examined: 538

This, from your slow query log, implies that it took 49 seconds to execute this query. Try running

CREATE INDEX wp_options_autoload ON wp_options (autoload);

And then try loading your pages again.

Though with only 538 rows in the table, that's an extremely long time for that query to run.

share|improve this answer
    
I created an index i1 that included option_name and option_value. –  panoramic Jan 25 at 0:14
    
Create index i1 on wp_options(autoload, option_name, option_value(50)); When I ran explain select option_name, option_value from wp_options where autoload = 'yes'. 468 rows were in the set. –  panoramic Jan 25 at 0:17
    
The number of rows aren't going to change. It's the amount of time that should change. –  Glen Solsberry Jan 25 at 0:43

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.