1

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:

enter image description here

Any ideas? Thanks in advance

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

enter image description here

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:

enter image description here

3
  • 1
    It's not possible to tell with just what you've got in the question, you'll need to look through the table to see if any of those optioons have really big values, then see what the option name is, the prefix/name will give clues as to the plugin/theme responsible
    – Tom J Nowell
    Jun 23 '20 at 8:41
  • Out of interest what did you use to see the size of the tables? It doesn't make sense that 1100 rows would be 12Gb, unless somehow something put 12Gb in one row, if that's even possible. You could backup, drop and reload the table perhaps to try to clear this issue. There's also MySQL OPTIMIZE TABLE command, which may clear up any weird issues to do with free space not being reclaimed
    – mozboz
    Jun 23 '20 at 8:43
  • If i export table, drop and import backup, size reduces drastically to 9,7mb. i.gyazo.com/c86676a4e1a507f4389c3ff31081bf73.png
    – 50l3r
    Jun 23 '20 at 11:46
0

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

2
  • I have fixed the problem recreating the table but it will possibly happen again. As soon as the size starts to increase I will try to perform the optimization
    – 50l3r
    Jun 23 '20 at 11:51
  • I still have the problem. i.gyazo.com/a382414592d840cdfdb05aa8619ff3ff.png I try to optimize table without results
    – 50l3r
    Jun 28 '20 at 8:45
0

Have a look on following

0

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:

  1. First access phpmyadmin panel.
  2. 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)
    
  3. Look for the row which is consuming GiB of data. (Look for value row which has 6 digit value.)
  4. Delete the row.

This solved the problem.

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

2
  • You could also just turn the autoload flag off. And report the problem to the plugin that's creating the data, else it may well just come straight back.
    – Rup
    Aug 2 at 8:06
  • I tried turning off autoload flag. But the permission denied. Then tried this. Aug 3 at 3:18

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.