Huge wp_options table

Question

Huge wp_options table

50l3r

2022年5月5日 14:03

I have a problem on a WP site. Website crashes because there is no more available disk space.

Searching, i detect that wp_options table size is 12GB, but only have 1100 rows aprox:

Any ideas? Thanks in advance

[UPDATE 1] If i export wp_options table, drop and import, sizes reduces at 9,7mb:

I had no opportunity to optimize the table with OPTIMIZE TABLE wp_options but I will try it if it happens again

[UPDATE 2] Problem still here. I try to OPTIMIZE TABLE wp_options;without results:

Topic mysql options database Wordpress

Category Web

Pranu Pranav · Accepted Answer · 2021年8月2日 08:05

One of the blogs I manage experienced this problem. wp_option table loaded with GiB of non-required data. This happens few of the plugins insert metadata, notification data, etc...to wp_option, but won't delete those data. Some of the plugins are Woo Commerce, Monster Insights, Astra theme...(Sorry, if this bug is being resolved.)

To solve the problem:

First access phpmyadmin panel.

Go to SQL and run the following SQL Command:

SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
UNION
SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
UNION
(SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)

Look for the row which is consuming GiB of data. (Look for value row which has 6 digit value.)
Delete the row.

This solved the problem.

References: https://kinsta.com/knowledgebase/wp-options-autoloaded-data/

ras · Accepted Answer · 2021年2月13日 19:59

Have a look on following

Plugin's usage

plugins marking the status/change on wp_options table (security scan, crons ,API etc.)
wp_options inspection

"Autoload" column

Autoloaded data is data that is loaded on every page of your WordPress site.

and "Transients" named fields in "option_name" column

standardized way of storing cached data in the database.

Refer following for more details

https://kinsta.com/knowledgebase/wp-options-autoloaded-data/ https://wordpress.stackexchange.com/questions/6602/are-transients-garbage-collected https://developer.wordpress.org/apis/handbook/transients/

mozboz · Accepted Answer · 2020年7月7日 13:16

I'd be interested to validate what you're looking at there to make sure it really is that table. MySQL shouldn't behave like this unless you actually have 12Gb in a row somewhere. But if you can run SQL on this database, maybe you want to try MySQL OPTIMIZE TABLE command which asks MySQL to see if it can optimize the storage for a table. So you could try running the query:

OPTIMIZE TABLE wp_options;

If that doesn't work, please post full screenshot of why you think it's this table, and say if you're able to run e.g. mysqlbackup as dropping and restoring the table could be an option.

EDIT: Did a bit more searching and this may be a known issue depending on some InnoDB setup in MySQL. More information and potential setting to change here: https://www.ibm.com/support/pages/mysql-tables-grow-very-large-even-though-purger-enabled

Huge wp_options table

About