wp_options is GB in size

Question

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.

Answer 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.

Answer 2

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