MySQL – Slow query – wp_options table. Website unable to handle traffic

amazon ec2amazon-rdsdatabaseMySQL

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.

Best Answer

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.

Related Solutions

MySQL query very slow on Amazon RDS but really fast on the laptop

Try experimenting with the Provision IOPS that RDS offers. Regardless of instance size, the IO is limited, and you can use the Provision IOPS settings, at an additional cost, to improve IO performance.

You can provision up to 3TB storage and 30,000 IOPS per database instance. For a workload with 50% writes and 50% reads running on an m2.4xlarge instance, you can realize up to 25,000 IOPS for Oracle and 12,500 IOPS for MySQL. However, by provisioning up to 30,000 IOPS, you may be able to achieve lower latency and higher throughput. Your actual realized IOPS may vary from the amount you provisioned based on your database workload, instance type, and database engine choice. Refer to the Factors That Affect Realized IOPS section of the Amazon RDS User Guide to learn more.

You can convert from standard storage to Provisioned IOPS storage and get consistent throughput and low I/O latencies. You will encounter a short availability impact when doing so. You can independently scale IOPS (in increments of 1000) and storage on-the-fly with zero downtime. The ratio of IOPS provisioned to the storage requested (in GB) should be between 3 and 10. For example, for a database instance with 1000 GB of storage, you can provision from 3,000 to 10,000 IOPS. You can scale the IOPS up or down depending on factors such as seasonal variability of traffic to your applications.

AWS RDS Provision IOPS

Mysql – AWS RDS MySQL / Slow Query Log

Did you know there is slow log table? It is called mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.01 sec)

mysql>

There are two variables you must activate to start recording slow queries in mysql.slow_log:

Here is the situation

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

mysql>

log_output is already defaulted to TABLE. You will need to enable slow_query_log. Here is what you must do:

If your RDS Instance has no DB Parameter Group...

Spin up an AmazonRDS MySQL Instance that uses a DB Parameter Group.
Edit the DB Parameter Group by setting the slow_query_log to 1
Restart the DB Instance

If your RDS Instance has DB Parameter Group...

Edit the DB Parameter Group by setting the slow_query_log to 1
Restart the DB Instance

Once the Instance has been restarted, login to mysql and run this query

mysql> SELECT SLEEP(15);

Since the default for long_query_time is 10 seconds, the query SELECT SLEEP(15); should show up in mysql.slow_log. Read the RDS Documentation to see if there are any limits on the table.

CAVEAT

Here is a fair warning. Notice mysql.slow_log is a CSV file. The table has no key. While you can query the table, it will do full table scans. It would be great if you can do the following:

ALTER TABLE mysql.slow_log ENGINE=MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Then, you can query very fast for date and time ranges. I tried to run this and I get this:

mysql> ALTER TABLE mysql.slow_log ENGINE=MyISAM;
ERROR 1044 (42000): Access denied for user 'lwdba'@'%' to database 'mysql'
mysql>

Check the RDS Documentation to see if Amazon can do this for you.

Give it a Try !!!