WordPress.org

Make WordPress Core

Opened 3 years ago

Last modified 5 weeks ago

#24044 reopened enhancement

Add index to wp_options to aid/improve performance

Reported by: DanBUK Owned by:
Milestone: Awaiting Review Priority: normal
Severity: major Version:
Component: Options, Meta APIs Keywords: has-patch
Focuses: performance Cc:

Description

I work for a hosting company and we manage many '000s of WP sites for people. In order to improve the performance of all of these instances I find myself adding an index onto wp_options autoload many times daily.

I'm aware this is a small gain, but any gain is good.

This index will be hit on every single page load.

Attachments (9)

autoload_key.patch (552 bytes) - added by DanBUK 3 years ago.
Patch to add index onto wp_options autoload
24044.diff (566 bytes) - added by cliffseal 3 years ago.
24044-test.php (6.2 KB) - added by pento 3 years ago.
24044-test-results.txt (5.7 KB) - added by pento 3 years ago.
dan_results.txt (1.7 KB) - added by DanBUK 3 years ago.
test results
dan_test.php (3.3 KB) - added by DanBUK 3 years ago.
dan_results_myisam.txt (1.7 KB) - added by DanBUK 3 years ago.
test results myisam
wp_options_test_index_options.php (5.0 KB) - added by jrchamp 5 weeks ago.
Updated version of dan_test.php with alternate index options
wp_options_test_index_options.results (4.1 KB) - added by jrchamp 5 weeks ago.
Test results for MyISAM and InnoDB. Single column index appears to be better.

Download all attachments as: .zip

Change History (74)

@DanBUK
3 years ago

Patch to add index onto wp_options autoload

#1 follow-up: @nacin
3 years ago

Could you show some statistics for how this helps performance on your sites? See ticket:14258#comment:3 for why we have not done this.

#2 @DanBUK
3 years ago

  • Summary changed from Add index to wp_options to aid improve performance to Add index to wp_options to aid/improve performance

This may be the case, but there are some godforsaken ugly plugins out there which mean that wp_options ends up '000s of rows. In these situations this will help with performance.

Also the maintenance of such an index is not placing a lot of burden on the DB. Overall this is reducing the work to deliver the page, that is what matters.

#3 @DanBUK
3 years ago

Also the index will still be used for statistics, rather than assuming a full table scan every time. The index has a high level of cardinality.

#4 follow-up: @DanBUK
3 years ago

And assuming that all wp_options tables will have > 30% autoload == yes isn't prudent, there are many ways in which people use the wp_options table.

#5 in reply to: ↑ 4 @nacin
3 years ago

Replying to DanBUK:

And assuming that all wp_options tables will have > 30% autoload == yes isn't prudent, there are many ways in which people use the wp_options table.

I disagree; it's a very fair assumption. "Ugly" plugins that add thousands of rows to a table are still very, very likely to autoload all of them, because that's the default. The alternative is each get_option() is an extra query, which you'd probably notice as a host.

The maintenance of an index is not placing a lot of burden, no. But if the index is just sitting there waiting to be ignored, then it doesn't do us much good. The larger issue is that I haven't seen any evidence — in terms of performance or otherwise — to actually show that an index here would reduce the work to deliver the page.

I'm not saying you've added an index to thousands of sites yet assuredly have no performance improvements to show for it. I'm saying, show us the measurable improvement so we can see it for ourselves.

#6 @toscho
3 years ago

  • Cc info@… added

#7 @pento
3 years ago

  • Cc gary@… added

#8 @DeanMarkTaylor
3 years ago

  • Cc DeanMarkTaylor added

#9 @krazybean
3 years ago

On a table with above 1k rows in wp_options

Before autoload index:
mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | wp_options | ALL | NULL | NULL | NULL | NULL | 1460 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

After adding index named i1

mysql> create index i1 on wp_options(autoload, option_name, option_value(50));

mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | wp_options | ref | i1 | i1 | 62 | const | 178 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+

After the index was created the total rows searched dropped from 1460 to 178.

Version 0, edited 3 years ago by krazybean (next)

#10 @cliffseal
3 years ago

  • Keywords dev-feedback added

On a WordPress 'app' in which lots of non-autoloaded transients are used, I got similar results, running the same queries as @krazybean.

Before:

mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    | NULL | 8196 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

After:

mysql> create index i1 on wp_options(autoload, option_name, option_value(50));
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_options | ref  | i1            | i1   | 62      | const |  155 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Understood this may not affect the general population, but I wonder how we could best help folks who make extensive use of transients in certain situations.

#11 follow-up: @nacin
3 years ago

What's the purpose behind adding option_value to that index? It looks like it would only serve to increase the length of the index.

What's the performance difference on these queries? Reducing rows examined from 1460 to 178 (or even 10,000 to 100) is still peanuts. DanBUK mentioned adding this index many times daily, but I am trying to figure out if that was simply cathartic or if it was actually making a difference.

#12 @cliffseal
3 years ago

I was just trying to replicate his results.

I wish I had tracked the difference when I added it to a much larger version of that same app—important queries went from several seconds in some cases to < 1 in all. I assure you it was directly related to adding an autoload index.

Again, I know this isn't the most common configuration, but it's allowing me to work with tons of transients at once with decent page load times.

If you can tell me what sort of feedback/results you need, I'll find a way to get it for you.

#13 @pento
3 years ago

  • Keywords reporter-feedback added; dev-feedback removed

There are a few things to consider here.

First up, there's unlikely to be any benefit to adding (option_name, option_value(50)) to the index. There are no queries in WordPress core that would use them.

So, if we were going to add an index here, it would just be on autoload, which would have a very low cardinality. For some sites, this would potentially avoid a table scan, but I'm inclined to think that most sites would have >30% of rows with autoload set to yes.

Anyway, to determine if this index would be helpful to you, and the degree of helpfulness, it would be most useful to see how long the query takes to run with and without the index, rather than looking at the number of rows read. (If you're running on a test machine, run the query at least twice, to ensure the cache is warmed up.

If you're not using InnoDB, I'd also be curious to see how switching to that, with a large innodb_buffer_pool_size, changes the query time.

Finally, if you're keeping stats on page generation time, I'd be interested to to see how this changes in production with an autoload index.

#14 @cliffseal
3 years ago

Agreed on adding that specific index—again, just trying to replicate results. Simply adding an autoload index is what made the monumental change in my case.

I'll run these tests and post the results here. Thanks!

Last edited 3 years ago by cliffseal (previous) (diff)

#15 @nacin
3 years ago

  • Component changed from Performance to Options and Meta
  • Focuses performance added

#16 @cliffseal
3 years ago

On an InnoDB database with about 615K rows in wp_options, here's what came across, running locally in MAMP. I started with an autoload index, removed it, and added it back.

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

628 rows in set (0.01 sec)

ALTER TABLE wp_options DROP INDEX autoload;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

628 rows in set (2.30 sec)

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

628 rows in set (0.00 sec)

Does that help?

#17 @cliffseal
3 years ago

Also, I went through these steps again and ran the SELECT query a few more times after removing the index as requested. Still got:

628 rows in set (2.24 sec)
628 rows in set (2.24 sec)
628 rows in set (2.23 sec)

Then:

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
628 rows in set (0.01 sec)
628 rows in set (0.01 sec)
628 rows in set (0.00 sec)

#18 @nacin
3 years ago

Why do you have 615K rows in your wp_options table?

#19 @cliffseal
3 years ago

Because the Transients API has been perfect for a specific project needing lots of individual external API calls cached.

Again, not arguing for wp_options size, just trying to show where the index can make a big difference.

#20 follow-up: @nacin
3 years ago

I figured it would be transients.

You should enable an external object cache, that way transients aren't stored in the database. If the lack of an index on this table forces hosting companies to consider the availability of an external object cache for their users, I'm not necessarily going to be bothered by that.

I find it hard to justify an index that benefits an infinitesimal number of sites, probably slows down many others, especially were the sites affected have better steps to take to improve performance.

#21 @cliffseal
3 years ago

Actually, in this specific case, object caching _was_ enabled and kept getting overloaded, so this was the only option after having to disable it.

Again, totally understood this is not a common case, but I also wanted to demonstrate that it could make a big difference for some.

Here's a patch just in case. :)

Last edited 3 years ago by cliffseal (previous) (diff)

@cliffseal
3 years ago

#22 in reply to: ↑ 20 ; follow-ups: @DanBUK
3 years ago

Sorry I never came back with statistics. I'll try and find some of the customer sites I've worked on and generate some data.

Replying to pento:

If you're not using InnoDB, I'd also be curious to see how switching to that, with a large innodb_buffer_pool_size, changes the query time.

In almost all cases everyone should be using InnoDB for a WP database, excluding the tables that have FULLTEXT indexes of course. (Then again, full text indexing should really be shifted to something like Solr, but that is another story...) The nature of the wp_comments especially cannot scale with MyISAM, the write level of a busy WP site means that you'll end up with table locks. And the number of plugins that utilise wp_meta or wp_options to store post views, or similar that get updated on every view... Again leads to locking that blocks the viewing of the site.

Just by having a large buffer pool that doesn't change how the MySQL query planner/statistic collator/table scanning works. It's not about if the data is in RAM or not, the wp_options table even in the cases I've seen with up to 10k rows, isn't actually a lot of data, and will either end up in the buffer pool or the OS file system caches. The issue is that the planning has to examine all the rows in the table, every time the page is loaded. That's dependent upon CPU time.

Heck you can put your buffer pool up to 5x your database size and you'll see 0 performance improvement over 1x. Ideally the buffer pool should be as big as the dataset that is accessed often. Ergo if you have a 10GB InnoDB dataset and 9GB of that is archive of logs/actions/very old posts/etc you only really need a 1GB buffer pool.

But that is the thing, we are moving into the realm of database optimisation, and to be honest from my view that isn't always seen by developers. Don't even get me started on Magento.

Replying to nacin:

You should enable an external object cache, that way transients aren't stored in the database. If the lack of an index on this table forces hosting companies to consider the availability of an external object cache for their users, I'm not necessarily going to be bothered by that.

Quite often the hosting company cannot control the customer fully. The customer may have limited budget and we work within their constraints.

Also there are lots of plugins out there that are already using transients, most users of WP that are not developers/sys admins/etc do not understand that maybe they have used the inappropriate method of storing data.

Replying to nacin:

I find it hard to justify an index that benefits an infinitesimal number of sites, probably slows down many others, especially were the sites affected have better steps to take to improve performance.

How do you think this will slow things down? It is an index of boolean cardinality, therefore the binary tree will have only two branches (YES|NO). The addition/updating of rows only require very small index changes. I really cannot understand how you think it will slow things down.

#23 in reply to: ↑ 22 @DanBUK
3 years ago

Replying to DanBUK:

But that is the thing, we are moving into the realm of database optimisation, and to be honest from my view that isn't always seen by developers. Don't even get me started on Magento.

My main basis for this is that developers don't see the application later in production on much busier sites that their test box that takes 10 requests per minute. Once you are dealing with a site that takes 100 requests per second minor changes to indexes in this suggestion make a huge difference. The Read/Write ratio is what counts.

#24 in reply to: ↑ 22 ; follow-up: @nacin
3 years ago

Replying to DanBUK:

But that is the thing, we are moving into the realm of database optimisation, and to be honest from my view that isn't always seen by developers.

Pardon me :) I'm just watching over this ticket, playing devil's advocate and working to turn assumptions into data. On the other hand, pento spent something like four years at MySQL AB and Oracle. So feel free to continue to geek out with him.

#25 in reply to: ↑ 24 @cliffseal
3 years ago

Replying to nacin:

I'm just watching over this ticket, playing devil's advocate and working to turn assumptions into data.

That's needed! I've been using this for a while now, but never saw a ton of good data on it either.

How can we test the impact of the index on a DB on the other extreme and test the assumption it might slow things down?

@pento
3 years ago

#26 @pento
3 years ago

Okay, here are some test results! All of this was on MySQL 5.5, I didn't test on other versions. The MyISAM results should be largely the same on other versions, the InnoDB results may be a little slower on older versions, but I don't expect there to be massive differences.

Anyway, here are the variables tested:

  • Engine: InnoDB or MyISAM
  • Rows: 100, 1000, 10000, 100000, 1000000
  • Percentage of rows selected: 5%, 20%, 50%, 80%, 95%

The full testing script and results are attached, but here's the summary:

  • Adding this index causes nearly all queries to be slower, except for InnoDB at 100k rows. I have no idea why this is the case.
  • Once MyISAM gets to 100K rows, adding the index makes it exponentially slower.
  • I killed the test script before it finished MyISAM 1M rows, because each query was taking > 2 minutes to run.

@DanBUK
3 years ago

test results

@DanBUK
3 years ago

#27 @DanBUK
3 years ago

  • Keywords reporter-feedback removed

I've performed some slightly different testing. With some restrictions.

As we are only issuing queries with autoload=yes the results for =no are not relevant.

With any site that has a high level of read/write concurrency MyISAM won't scale. (You would be surprised how many modules update wp_options on every page load.)

The results indicate that only in the case when 50% or more of the rows have autoload=yes things slow down. But this is by a negligible amount, even with 100k rows in wp_options the difference was only 0.02s (6.45%). Yet in the case of 100k rows and 20% of the rows have autoload=yes the gains in speed are 0.07s (35%).

Therefore I propose that the gains offered by adding this index outweigh the losses.

@DanBUK
3 years ago

test results myisam

#28 @DanBUK
3 years ago

And just for completeness, similar results as I mentioned for innodb are the same for MyISAM.

#29 follow-up: @pento
3 years ago

With attachment:dan_test.php, I still had the same result of MyISAM getting really slow for large tables:

myisam_VARCHAR20_Size_100000_Percent_50 'YES' 0.97143130302429
myisam_VARCHAR20_INDEXED_Size_100000_Percent_50 'YES' 11.403997087479
>> Slower

myisam_VARCHAR20_Size_100000_Percent_20 'YES' 0.96122975349426
myisam_VARCHAR20_INDEXED_Size_100000_Percent_20 'YES' 5.91277384758
>> Slower

myisam_VARCHAR20_Size_100000_Percent_5 'YES' 0.94139242172241
myisam_VARCHAR20_INDEXED_Size_100000_Percent_5 'YES' 1.2993580341339
>> Slower

As much as I'd love to ignore MyISAM, the vast majority of WordPress sites use it, including WordPress.com. While we're clearly getting different results here, we can't take the risk that other sites will suffer the same slowness I'm seeing.

#30 @cliffseal
3 years ago

So, the utility of this index is (roughly) inversely proportional to the percentage of rows where autoload = 'yes'? That'd make sense, considering, in my case, 0.001% of rows are such.

To me, this now goes back to:

Replying to nacin:

"Ugly" plugins that add thousands of rows to a table are still very, very likely to autoload all of them, because that's the default.

If that's the case, is there any way we can 'police' this behavior better with plugins, or define and encourage a best practice for it?

#31 in reply to: ↑ 29 ; follow-up: @DanBUK
3 years ago

Replying to pento:

How do you have your MySQL instance configured? (I'm really surprised your getting such slow results.)

I have the following:-

thread_cache_size = 8
innodb_buffer_pool_size = 1G
key_buffer_size = 128M
table_cache = 1024

(Running on a 2CPU / 2GB KVM Centos 6 VM, gah I just noticed it's only MySQL 5.1, will install 5.5 and re-test.)
I added my MyISAM results as an attachment earlier.

#32 in reply to: ↑ 31 @DanBUK
3 years ago

Replying to DanBUK:

(Running on a 2CPU / 2GB KVM Centos 6 VM, gah I just noticed it's only MySQL 5.1, will install 5.5 and re-test.)

I'm getting similar results on 5.5 MyISAM:-

myisam_VARCHAR20_Size_100000_Percent_50 'YES' 0.23201403617859
myisam_VARCHAR20_INDEXED_Size_100000_Percent_50 'YES' 0.29787020683289
>> Slower

myisam_VARCHAR20_Size_100000_Percent_20 'YES' 0.13488883972168
myisam_VARCHAR20_INDEXED_Size_100000_Percent_20 'YES' 0.12055621147156
>> Faster

myisam_VARCHAR20_Size_100000_Percent_5 'YES' 0.086687040328979
myisam_VARCHAR20_INDEXED_Size_100000_Percent_5 'YES' 0.030073165893555
>> Faster

#33 @pento
3 years ago

I've had a bit more of a play around with my MySQL settings, but I haven't managed to make performance suck less on MyISAM.

I'm using a standard VVV install for testing. You're welcome to play around with it and see if you can get better performance out of it, but I'm strongly inclined to not add this index. VVV is a pretty popular base for WordPress dev shops to create sites with these days, so if I'm running into performance problems, there's a good chance a bunch of other sites will, too.

#34 @jeichorn
2 years ago

At Pagely on a semi-regular basis we end up with sites with hundreds of thousands of rows in wp_options. The cause of this is normally storing sessions in wp_options. In a case like this having the index on autoload is huge win.

Before index

mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    | NULL | 807663 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+

After index:

mysql> explain select option_name, option_value from wp_options where autoload = 'yes';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | wp_options | ref  | autoload      | autoload | 62      | const |  265 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

We are talking about reducing query time by 1 second in this instance. As the average size of wp_options grows and grows its a nice low cost index to add that protects people from plugins that use wp_options as a temp data store.

Last edited 2 years ago by DrewAPicture (previous) (diff)

#35 @SergeyBiryukov
2 years ago

#29637 was marked as a duplicate.

#36 @brokentone
2 years ago

Even without thousands of rows, we found benefits in adding this index ourselves. I'd advocate for it to be added in core.

Last edited 2 years ago by brokentone (previous) (diff)

#37 @JanR
2 years ago

I hope you don't mind me barging in here... A referrer from ticket #29637 to my site brought me to this ticket, and apparently it has been submitted more than once (#14258). So there is some demand for this.

In a live-situation, adding an index on the autoload column resulted in a drop of examined rows, from 33927 to 1508. I call that significant. The real performance gain depends on a lot of factors, I know, but I just wanted to share these numbers.

PS: before one starts to install and test with MySQL 5.5 (or 5.6 for that matter), notice the innodb_buffer_pool_instances *and* innodb_buffer_pool_size settings. One can even increase InnoDB performance by adjusting innodb_write_io_threads and innodb_read_io_threads. As long as you have the RAM. Don't use MyISAM (IMO).

#38 @jorbin
23 months ago

  • Keywords close added

I think we need to pull the trigger and make a decision here rather than letting this ticket waste away.

All the testing shows that adding it reduces performance for MyISAM which if we take a "first, do no harm" approach, means that this comes accross as a non starter. I think we should close as maybelater and if evidence of it not harming performance for MyISAM comes up, we can revisit.

This of course doesn't prevent individual site owners from adding the index themselves.

#39 @pento
23 months ago

  • Keywords close removed
  • Milestone Awaiting Review deleted
  • Resolution set to maybelater
  • Status changed from new to closed

Agreed, I'm open to revisiting this when we have more InnoDB users. As InnoDB use is steadily increasing, we'll get there eventually. :-)

In the mean time, for folks who happen upon this ticket, you have two options:

  • Try adding this index yourself, and see if it improves performance for you
  • Use an external object cache to avoid hitting the DB

#40 in reply to: ↑ 1 @fightthecurrent
17 months ago

Replying to nacin:

Could you show some statistics for how this helps performance on your sites? See ticket:14258#comment:3 for why we have not done this.

Here are some real world stats. Here you can see when the index was added to the options table:
http://i.imgur.com/gDt8eaR.png

Here you can see average transaction times decrease when the index was added:
http://i.imgur.com/dFGBbPW.png

And here you can see the load on the database decrease dramatically when the index was added:
http://i.imgur.com/EGhDD1R.png

I think adding the index is highly valuable. This comes from a site running WooCommerce where a large percentage of the records in the options table are transients, so there's no reason for them all to be loaded on every page load.

Edit: all tables are using InnoDB as well.

Last edited 17 months ago by fightthecurrent (previous) (diff)

#41 in reply to: ↑ 11 ; follow-up: @fightthecurrent
17 months ago

Replying to nacin:

What's the purpose behind adding option_value to that index? It looks like it would only serve to increase the length of the index.

What's the performance difference on these queries? Reducing rows examined from 1460 to 178 (or even 10,000 to 100) is still peanuts. DanBUK mentioned adding this index many times daily, but I am trying to figure out if that was simply cathartic or if it was actually making a difference.

How about reducing 553,749 to 711?

#42 in reply to: ↑ 41 @JanR
17 months ago

Replying to fightthecurrent:

Replying to nacin:

What's the purpose behind adding option_value to that index? It looks like it would only serve to increase the length of the index.

What's the performance difference on these queries? Reducing rows examined from 1460 to 178 (or even 10,000 to 100) is still peanuts. DanBUK mentioned adding this index many times daily, but I am trying to figure out if that was simply cathartic or if it was actually making a difference.

How about reducing 553,749 to 711?

Nice graphs @fightthecurrent.

We all know the performance gain - on InnoDB - with having an index on the autoload column of the wp_options table.

The following is somewhat based on assumptions, and needs some investigation (it's early Sunday morning here...): nowadays at least most hosting providers are on MySQL version 5.5+, which has InnoDB as the default storage engine. The WordPress installer (wp-admin/install.php) already checks the MySQL version being used, and there is no engine definition in wp-admin/includes/schema.php. Thus making an extra check on MySQL storage engine quite possible:

ITTT: If MySQL version >= 5.5 && storage engine = InnoDB, add an index on the autoload column.

There is more than one way to lookup the storage engine: http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table. For plugins that require FULLTEXT indexes on table columns: MySQL >= 5.5.4 supports FULLTEXT indexes in InnoDB (http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html).

#43 @dandruczyk
13 months ago

  • Resolution maybelater deleted
  • Status changed from closed to reopened
  • Version 3.6 deleted

I was having unusual (and climbing) CPU usages and frequent showings in the slow query log of this query, so I added the index for each Wordpress blog on my SQL server (118 of them), CPU usage for MySQL alone dropped by a factor of 3+ (from about 30-35% to 10%). NO OTHER changes were made..

The databases are a mix of MyISAM (older blogs) and InnoDB (newer ones), ALL blogs except 1 are current Wordpress 4.3. MySQL is 5.5.44 running on Ubuntu 14.04 64 bit on a VMware ESXi host backed by enterprise grade SAN storage. The MySQL server is quad (virtual) core, 8GB ram.

The change to all blogs was made at 13:20 (see picture linked below). The remaining blogs had the index added over a 10 minute period starting around 13:30
[http://imgur.com/qOce6KT
]
This is a fairly busy MySQL server handing about 2100 queries per second pushing about 100Mbits/sec of traffic continuously.

I encourage that this index be added as the drop in resource consumption is significant nad it has essentially no downside to having it there.

Last edited 13 months ago by dandruczyk (previous) (diff)

#44 follow-up: @francogilio
13 months ago

  • Severity changed from normal to major

Here are some results from a test some months old:

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
628 rows in set (2.24 sec)
628 rows in set (2.24 sec)
628 rows in set (2.23 sec)

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
628 rows in set (0.01 sec)
628 rows in set (0.01 sec)
628 rows in set (0.00 sec)

As you can see, the difference is huge. I may roll out a local copy of this site to do some test, if it helps.

#45 follow-up: @KalenJohnson
13 months ago

From my understanding, MySQL 5.5 by default uses InnoDB. Looks like statistics have most likely gone up since this ticket was created. About 70% are on MySQL 5.5+, and 2% are on MariaDB :joy:

So unless there are hosts that are switching from InnoDB to MyISAM by default, seems like the vast majority are now on InnoDB.

And judging from the results people have been posting in the past 4 months, seems like there are huge advantages to adding the index. Time to revisit?

#46 in reply to: ↑ 45 @JanR
12 months ago

Replying to KalenJohnson:

[...]
So unless there are hosts that are switching from InnoDB to MyISAM by default, seems like the vast majority are now on InnoDB.

Unfortunately, upgrading your MySQL version to 5.5+ doesn't convert your existing MyISAM tables automagically... You'll need to do that yourself, either through MySQL/phpMyAdmin or using a plugin.

And judging from the results people have been posting in the past 4 months, seems like there are huge advantages to adding the index. Time to revisit?

Agreed! :) There is a number of plugins to convert MyISAM tables to InnoDB, and it's up to "us" (I work for a hosting company) to inform clients and customers about such important changes, and to recommend them to convert their existing database tables.

#47 @DanBUK
12 months ago

So if we go around in circles... As I have with my view of how it's been received.

The original resistance to adding this index was due to MyISAM usage; even by WordPress.org. Which at any time is a bad idea. We are now at a time when InnoDB or other newer storage engines exist and are used by default.

  1. Every lookup of a page on a wordpress site checks the wp_options for how the plugins should operate.
  2. They depend upon autoload being true
  3. It's cardinality of index is very high, its either true or false.

Just add the index...

#48 @SergeyBiryukov
12 months ago

  • Milestone set to Awaiting Review

#49 in reply to: ↑ 44 @galbaras
11 months ago

I have a wp_options table with over 600,000 records, mostly WooCommerce sessions and transients, which used to load the initial "autoload" query in 2.5 seconds, just like @francogilio. After creating a secondary index on "autoload", it's loading in 0.01 seconds and the site is MUCH faster in general, possibly because this query prevented table updates while it was being scanned without the index.

To me, such a small index is not something that should be so strictly opposed, considering it affects a core query executed by every page. It's just likely to speed many sites up, including some sites whose owners don't know phpMyAdmin from a bar of soap.

Last edited 11 months ago by galbaras (previous) (diff)

#50 @SIRavecavec
8 months ago

This is a must!

#51 @ReneHermi
8 months ago

Lets create an conditional alter statement which adds an index only when innodb is used! We have the year 2016, innodb is used as the default db in all serious hosting companies today.

Make a cut and do not stuck in the past. This small change will save energy, costs and natural resources worldwide. Save a tree, add an index to wp_options!

#52 @robinwilson16
7 months ago

I am just looking into performance issues on someone's WordPress site and why after 3 years has nothing been done?
There is no logic to making autoload of type VARCHAR(20) as it wastes space and is inefficient as shown above and an index would drastically improve performance for all the many installs out there.

I would have thought a type of CHAR(1) would be best with Y and N but obviously this would take development time whereas adding the index would not.

#53 @markouver
7 months ago

Robin has a good point. Would MyISAM still suffer a performance hit at all if the column was an indexed CHAR(1) instead of an indexed VARCHAR(20)?

#54 @jkhongusc
5 weeks ago

+1 to changing autoload field to another type like TINYINT or ENUM('no','yes') if you dont add an index to the autoload field

University of Southern California's (USC) WP site was hit by this issue luckily during move-in week. We use WP as an Enterprise portal. All USC members (up to 100k users) access our WP portal as a gateway for services. Every users's portal page is unique = no page caching.

USC has:
mysql InnoDB
WP multisite
lots of users which indirectly has lots of wp_options rows
uses transients to store complex queries/data
does not use an object cache (work in progress)
huge database server sized for our usage + growth (8 vCPUs, 16GB RAM)

One day our database CPU went to 100%. I noticed lots of slow queries doing full table scans. When I ran 'show processlist' I saw hundreds of these -
SELECT option_name, option_value FROM mywp_options WHERE autoload = 'yes';
I immediately dropped an index on it and the cpu usage went from 100% to under 5%:
create index uscautoload on wp_options(autoload, option_name);

Crisis averted and I get to keep my job. I was the one who pushed to use WP as our portal

Last edited 5 weeks ago by jkhongusc (previous) (diff)

#55 @nigro.simone
5 weeks ago

On MariaDB 10.1.9

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0156 seconds.)

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0000 seconds.)

#56 @robinwilson16
5 weeks ago

Is this ever likely to happen?
It seems it was actually requested here 6 years ago and closed as won't fix: #14258

There are two issues here:

  • There is a missing index (probably resulting in a table scan each time)
  • An unsuitable data type has been used of VARCHAR(20) (CHAR(1) would be much better with Y/N or better still BIT with 0/1)

The two issues compound each other and I feel these are fairly basic relational database design principles.
The first would require no product changes and would be low risk to implement
The second would require some development work (table could be altered to VARCHAR(3) for a smaller improvement)

Still after 6 years of asking, nothing has yet been done.
Robin

Last edited 5 weeks ago by SergeyBiryukov (previous) (diff)

#57 @jorbin
5 weeks ago

The test results showed that while the speed improvement is common for innodb table, but is the exact opposite for myisam tables. Therefore, it's not a change with a low risk as it has the potential to make many sites slower.

In the last few years, with mysql 5.5+ becoming much more popular, it's likely that new sites are using innodb, but I don't know if we have any stats on innodb vs myisam. @pento - Any idea on that?

An issue with only implementing the change on sites that are using innodb for the options table is that people move DBs around without knowing the versions. This was seen a lot during the switch from utf8 to utf8mb4

For the sites that benefit from this index, I do encourage them to add the index themselves (with a prefix). Core DB migrations don't remove individually added indexes.

If someone wants to work up some tests on BIT, with an index, without an index, on multiple versions of MySQL using both innodb and myisam than we can explore that as an option. A change like that would need to be committed early in a dev cycle (like we are right now) but can't be made without data showing it would be beneficial for some (though ideally, it would be beneficial for everyone) and have no little to no harm for everyone.

#58 @galbaras
5 weeks ago

It should be easy enough to write code that checks for the storage engine and does the best for what it finds.

It should be equally as easy to decide that InnoDB is the way to go for wp_options on MySQL (5.5+), given the the update frequency and granularity, an simply ensure that that's the case, possibly during install and core updates.

In fact, the code can even run some test queries with and without the index, and leave the better one.

It's a wonderful world of WordPress out there :)

Personally, I've switched to InnoDB and created the autoload index on wp_options and all of my sites smile at me as they zoom by.

Last edited 5 weeks ago by galbaras (previous) (diff)

#59 @robinwilson16
5 weeks ago

@jorbin, thanks for the reply.
I'm just wondering but could an upgrade script not possibly move WordPress (or at least that table) to InnoDB maybe with a prompt during the upgrade stating the benefits of InnoDB as otherwise people may just carry on using MyISAM without realising there is a choice and the issue will never be able to be solved with possible further fragmentation in future.

I helped a client solve an issue where they were using MyISM and Dutch as the codepage because the previous hosting provider had these set as default (the website was not Dutch).

Also the Ticket URLs in emails never work either and display this error:

No handler matched request to /ticket/24044#comment:58

This is because the # is converted into %23

Last edited 5 weeks ago by robinwilson16 (previous) (diff)

@jrchamp
5 weeks ago

Updated version of dan_test.php with alternate index options

@jrchamp
5 weeks ago

Test results for MyISAM and InnoDB. Single column index appears to be better.

#60 @jeichorn
5 weeks ago

So i just finished a set of tests using ~750 real wp_options tables on mysql 5.6.

Here are the takeaways.

For 90% of my sample set didn't matter either way. Those sites has 2-500 options, and autoloaded 90%+ of them. With either Myisam or innodb index or not, the performance difference is in noise land.

The queries normally took around 0.001 seconds and the speed difference was in the 0.0002 range.
In general MyISAM is slightly slower in these cases, and Innodb is slightly faster, but I wouldn't consider either difference meaningful.

The final 10% are mostly 2 cases.

Sites with huge #s of options all autoloaded, this is slower in both myisam and innodb, but the absolute performance already sucked, and the php performance of autoloading 100k options is going to be a couple orders of magnitude worse then the db side so I think we can just ignore that outlier.

The remaining case seems to be 20% or less of the options autoloaded.

So my overall take is this change would be mostly a wash for the majority of site, with big performance wins for innodb in the case of small autoload, large # of rows (myisam is also helped in this situation). With the biggest downside being for innodb as well, large rows all autoloaded.

I'm not seeing the same huge performance losses for myisam on a large # of rows, though I would imagine that depending on the database configuration and amount of memory that could be an issue.

The one thing none of this testing takes into account though is the worst case situation. If you are scanning 100k rows in wp_options to return 500, and do that query at volume you can easily run your database out of cpu, and then performance of that query will fall apart.

Summary from my run at.
https://gist.github.com/jeichorn/d33dfcb6ba687dd84fb65945f59a4f5a

#61 @galbaras
5 weeks ago

Going back to a point that was mentioned a while back, transients likely account for the vast majority of options that are not autoloaded, so perhaps the solution should be applied only when a site has enough of them to make a difference.

Personally, I've seen loads of them created by WooCommerce, and some by Yoast SEO. Perhaps this is not a core issue, but one that should be addressed according to the plugins' use of transients. Problem is I can't think of a way to enforce this as a core edict, only as an appeal to the respective plugin developers.

Maybe there can be an upgrade or periodic task to look at the ratio of autoloaded options to the total rows and if it exceeds, say, 0.5, the table can be altered. Alternatively, the number of transients can be counted and if greater than, say, 500, the table can be altered.

#62 @jkhongusc
5 weeks ago

I am doing some post-op investigation after our outage. It looks like there are multiple triggers to our failure and not due just to the lack of the autoload index. I am trying to figure out two things:
1) when is this query triggered - select option_name, option_value from wp_options where autoload = 'yes';
2) why is the query being run multiple times? During our outage, there were hundreds of the query running.

I think a high traffic site exacerbates this problem. Info on our install:

  • WP multisite
  • five 8 CPU 16GB RAM WP instances
  • one 8CPU 16GB database MySQL InnoDB
  • 100 WP requests per second during peak traffic. These are dynamic requests, not static/assets
  • Our wp_options row count fluctuates in the 50-100k range, autoload='yes' count is in the 100s

We are planning to add a caching layer, either Redis or memcached, which will increase our use of transients. Will we run into a problem even with the autoload index when we have thousands of options autoloaded? I need to find an answer to my two questions above.

Running the query below on our test system without an index, runs really fast even though it is doing a full table scan (.01 seconds); but the row count on wp_options is 3k. This is why I suspect the lack of index alone is not an issue at least for our case.
select option_name, option_value from wp_options where autoload = 'yes';

Last edited 5 weeks ago by jkhongusc (previous) (diff)

#63 @jeichorn
5 weeks ago

@jkhongusc yeah so thats what i mentioned at the end of my notes. Table scan queries even when fast are heavy cpu users on the database. If you do your benchmarks at high concurrency you should see a larger effect from the index.

#64 @jkhongusc
5 weeks ago

@jeichorn -
I was implying that there may be a flaw in the logic when all options are loaded; essentially when calling wp_load_alloptions() . wp_options is site-wide data. IMO autoloading data should optimally occur once per server instance, across all instances pseudo-simultaneously.

I submit that in a high traffic environment, like ours having 100 (dynamic) requests per second, could result in a similar database CPU lockup even with the autoload index. When the 'alloption' cache is deleted, WP will attempt to refresh the cache for every single request until that cache is added. If there are lots of transients (thousands) the query could take too long to fulfill before the next set of requests come in. Then a cascading issue occurs and the database CPU locked at 100%. Sounds like a design flaw that will only hit high traffic sites.

#65 @galbaras
5 weeks ago

It looks like the function wp_load_alloptions() is called many times, but it caches its results using wp_cache_add().

Alas, it doesn't cache options when is_multisite() is true.

Note: See TracTickets for help on using tickets.