16

I have been tracking the slow queries log of the WP based site (with the default value of the a long_query_time set to 10), and I have noticed that the following query is often getting logged -

# User@Host: root[root] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 394  Rows_examined: 458
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

I do not understand how such a small table can take so much time to execute. Is this just a symptom of some other problem? (Currently running Moodle, phpbb and WP on a dedicated VM).

16

Update: The reason the query is being logged is it doesn't use an index. The query time is 0, i.e. it actually executes fast. You can unset the "log-queries-not-using-indexes" option if you don't want these to be logged.

The wp_options table has no index on autoload, so the query ends up doing a full table scan. In general that table shouldn't get too large, so it's not a problem, but I'm guessing that's somehow happened in your case.

Adding an index might solve the problem, but as TheDeadMedic pointed out in the comments, it might not if the values of autoload are either majority yes, or evenly distributed between yes and no:

First, do this query to see what the distribution looks like:

SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;

if a large majority of them are set to 'no', you can solve the problem for now by adding an index on autoload.

ALTER TABLE wp_options ADD INDEX (`autoload`);

However, you might want to get to the bottom of why that table has gotten too large. Possibly some badly written plugin doing something fishy.

  • 2
    I doubt an index in this case would offer any gain - check out this article on cardinality. – TheDeadMedic Nov 6 '12 at 14:50
  • Depends on whether most options are set to autoload or not. I would think not, but in the table shouldn't ever get so big anyway so something fishy is going on. – Vinay Pai Nov 6 '12 at 15:05
  • 1
    I updated by answer to add a bit about checking the distribution of values. – Vinay Pai Nov 6 '12 at 15:14
  • 1
    I just noticed the comment, and realized my answer is completely wrong. The query isn't actually slow... it's just being logged in the slow query log because it doesn't use an index. – Vinay Pai Nov 6 '12 at 17:00
  • 1
    Thanks to this question and answer I discovered I had 90k entries in my wp_options table, 88.5k of which were set to autoload false. The rest were all "transient" entries added by plugins (presumably for caching?). Adding an index to the autoload column dropped my mySql load from an average of 89% to 2.5% instantly. Monitoring agents show my site's response time has dropped from 1900ms to 500ms. This was a gamechanger for me. – Mordred Mar 25 '17 at 3:41
5

I stumbled across the query mentioned in mytop running on my server a few days ago - and it actually took quite some time (about 10 seconds) for each query! So there are real-world situations where wp_options might grow to problematic size. In my case I suspect the caching plugin Cachify to be responsible for bloating wp_options.

Data of this particular wp_options:

5,309 rows
130MB of data

As a solution, I added the index similar to the solution posted by Vinay Pai, which solved the problem flawlessly.

1

My wp_options table only had about 235 rows of data. I tried indexing the table, but it didn't help.

Turns out that about 150 transient options had been inserted into the table, but hadn't been automatically deleted.

I don't know if it is related or not, but I'd been looking through my /var/log/apache2/access.log files and noticed that multiple (presumably compromised) Amazon Web Services servers (IP addresses beginning with 54.X.X.X and 32.X.X.X) had been attempting to exploit /~web-root-dir/xmlrpc.php.

After some troubleshooting, I queried the wp_options table for option names that contained "transient"

select * from wp_options where option_name like '%transient%';

One of the fields returned from this query is 'option_value' which has a datatype of LONGTEXT. According to the mySQL docs, a LONGTEXT field (for each row) can hold up to 4-Gigabytes of data.

When I executed the query, some of the rows (remember were working with those containing "transient") had massive amounts of data in the option_value field. Looking through the results, I also saw what looked like attempts to inject commands into the wp-cron process with the hopes they would be executed during the cron cycle(s).

My solution was to delete all of the "transient" rows. This will not hurt the server since "transient" rows will automatically repopulate (if they are supposed to be there).

After doing this, the server was once again responsive.

Query to delete these rows:

DELETE from wp_options where option_name like '%transient%';

I've added the AWS IP address /8 superblocks to my firewall, too (-:

  • Yep. I was suffering from "40 seconds load time" too until I discovered I had 20,000 wp_option records with massive data in them being loaded with every single page. Removing those considerably sped up the site. – JasonGenX Feb 12 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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