database - WP_Options ID high

admin管理员组

文章数量:1122846

I have a site where the options_id field in the wp_options table has grown very, very large. A month and a half after the installation, the auto_increment value is now at over 10 million.

While there are a number of plugins installed (such as ACF and W3 Total Cache) they do not seem to be the cause. They have been installed on others sites together with the same theme framework without any issues.

I have a local dev environment of the affected site where all the plugins and theme updates are first tested. Its auto_increment value is only about 15,000 which I would expect given all the theme development on that install.

The site has also been very slow to load, at about 3 seconds per page if it wasn't cached by W3 Total Cache (with page caching enabled the load times are great). Given the slow load times and the high auto_increment value, there would seem to be some kind of connection between the two.

The options table itself only contains about a 1000 rows, which seems normal, so it is able to clean up after itself.

The site is part of a multisite network and each site in the network has at least an auto_increment value of 1 million or more.

Any other tables in the database haven't been affected, each of them shows a normal number of rows given the number of pages on each site.

Anyone have any ideas?

I have a site where the options_id field in the wp_options table has grown very, very large. A month and a half after the installation, the auto_increment value is now at over 10 million.

While there are a number of plugins installed (such as ACF and W3 Total Cache) they do not seem to be the cause. They have been installed on others sites together with the same theme framework without any issues.

I have a local dev environment of the affected site where all the plugins and theme updates are first tested. Its auto_increment value is only about 15,000 which I would expect given all the theme development on that install.

The site has also been very slow to load, at about 3 seconds per page if it wasn't cached by W3 Total Cache (with page caching enabled the load times are great). Given the slow load times and the high auto_increment value, there would seem to be some kind of connection between the two.

The options table itself only contains about a 1000 rows, which seems normal, so it is able to clean up after itself.

The site is part of a multisite network and each site in the network has at least an auto_increment value of 1 million or more.

Any other tables in the database haven't been affected, each of them shows a normal number of rows given the number of pages on each site.

Anyone have any ideas?

• database
• performance
• options

Share Improve this question edited Dec 22, 2017 at 9:47 Sid 77644 silver badges1818 bronze badges asked Sep 3, 2014 at 7:54 D AD A 14133 bronze badges 9

• Looks like transient cache values are been added and removed very frequently because of short cache interval. That could be the reason your options table only have 10000 rows but still have higher increment value. – Robert hue Commented Sep 3, 2014 at 7:58
• The transient cache seem to be a possible culprit, but what on earth would cause it to go into the millions? The site isn't live yet and has only seen a few dozen unique visitors. – D A Commented Sep 3, 2014 at 8:05
• Why don't you list all the plugins you are using. Because the only culprit I see is a plugin. – Robert hue Commented Sep 3, 2014 at 8:08
• I think it should be SearchWP because it needs to index keywords frequently and should reset cache on post publish, edit or update. You should reset your auto_increment value and disable this plugin for couple of days to test. – Robert hue Commented Sep 3, 2014 at 8:15
• 1 auto_increment value does not directly effect website speed. And yes I am confident that it's W3 Total Cache. Because I have also checked couple of more client website databases, and all websites have auto_increment value in millions. But all these website on development server does not have higher auto_increment values. The only plugin I do not install on my development server is W3 Total Cache but keep other plugin. – Robert hue Commented Sep 3, 2014 at 8:40

 |  Show 4 more comments

2 Answers 2

Sorted by: Reset to default 0

I had a similar issue on another site recently, memory usage was through the roof. When I checked the db, wp_options was about 80megabytes. Running this SQL reduced the entire db to 15mb:

DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient_%'

Obviously, replace wp_ with your table prefix.

the auto_increment value by itself is not important, but the fact that you got there indicates that you do a lot of option insertion either directly or more likely as a transients.

As said above, by itself that value is meaningless and even having 1k options should not impact the performance that much if they are auto loading. I would guess that your performance issue is unrelated to the options table size and might actually be cause by poor caching implementation that used transients, but might as well come from other areas of your code.

本文标签： databaseWPOptions ID high