1

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

RDS

5
  • 1
    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, 2014 at 1:54
  • 2
    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. Jan 24, 2014 at 2:09
  • @editor How would you recommend offloading to static / cached pages?
    – panoramic
    Jan 24, 2014 at 6:05
  • @MichaelHampton Something is certainly horribly wrong! Haha. Any suggestions on how to pinpoint the problem?
    – panoramic
    Jan 24, 2014 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, 2014 at 14:36

3 Answers 3

1
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.

4
  • I created an index i1 that included option_name and option_value.
    – panoramic
    Jan 25, 2014 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, 2014 at 0:17
  • The number of rows aren't going to change. It's the amount of time that should change. Jan 25, 2014 at 0:43
  • I have a website with 50k members, its been really slow recently, I Just ran the second query and my website is now 3x faster! thank you! I thought I had a server problem. Nov 8, 2016 at 14:37
-1

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.

5
  • 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, 2014 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, 2014 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, 2014 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, 2014 at 16:00
  • Old question, but "49 ms is not that slow" is just wrong. That slow log says the query took 49 seconds.
    – ceejayoz
    Feb 16, 2017 at 3:11
-1

you could change wp_options to type MEMORY. For this you need to convert any blob to large varchar and increase heapsize in RDS.

However it may also be more sensible to solve this by putting a caching server in front of WordPress to eliminate the requests.

3
  • Uh, changing wp_options to a MEMORY table would wipe its data out on a reboot...
    – ceejayoz
    Feb 16, 2017 at 3:10
  • Yes why is that a problem. Refilling can be easily handled by scripting
    – jdog
    Feb 16, 2017 at 4:33
  • WordPress stores important stuff in wp_options. You didn't mention any such scripting (nor this major downfall) in your answer, which would leave people who follow your advice pretty screwed with a non-functional WordPress install after their first database reboot or crash.
    – ceejayoz
    Feb 16, 2017 at 13:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .