2

How can I find what options or plugins are taking up all that size? It's not transients because after deleting all it's still 13GB.

SELECT option_name, option_value FROM hihgv_options WHERE autoload = 'yes' Returns 833 rows in 0.141 sec

SELECT SUM(LENGTH(option_value)) as autoload_size FROM hihgv_options WHERE autoload='yes'; returns 670,540


At one point it was 13GB and went down to MB when I move everything from the My Custom Functions plugin to the child theme's functions.php file.

0

2 Answers 2

1

After executing these:

[root@shop ~]# mysqlcheck -p -c wordpress hihgv_options
wordpress.hihgv_options                            OK
[root@shop ~]# mysqlcheck -p -o wordpress hihgv_options
wordpress.hihgv_options
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
[root@shop ~]# mysqlcheck -p -a wordpress hihgv_options
wordpress.hihgv_options                            OK

Size is down to 1.6MB, not sure if it was optimize because I've done that before from HeidiSQL with no change at all.

Edit: Also the SQL queries from OP still return the same numbers.

Edit 2: Just did it again, after executing:

[root@shop wordpress]# mysqlcheck -p -o wordpress wp_options note : Table does not support optimize, doing recreate + analyze instead status : OK

It went from 3.9GB to 10MB. If anyone knows exactly why I'd appreciate an explanation.

3
  • Does it slowly grow back up? Do you do a lot of writing to the table? I don't know MySQL storage engine well enough: it might be that there's a large transaction log that needs flushing at some point, or it overallocates storage as you do lots of writes, or something along those lines: it might be related to write load on the table.
    – Rup
    Sep 13, 2019 at 8:51
  • @Rup Not sure, it's a WooCommerce e-shop, not sure if it's the transients or what. I'm going to dump when it's +100 MB then optimize and dump again to compare.
    – Daviid
    Sep 13, 2019 at 21:13
  • after a lot of searching and frustration, this solved it for me! dropped the table size from 670GB to 13.5MB???
    – TimmyG
    Aug 5, 2020 at 16:36
0

For a start, you can delete all session entries with

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

(this can take some hours, if you have millions of entries in your table)

Then you should find out, why the sessions are not deleted when they are expired. There seems to be a buggy plugin, or the whole data-overhead is a relict of an old buggy WP version

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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