I’ve got a production database with a wp_options table reportedly totalling around 951,679,500,288 (900GB+) in total data length. However, when I export the database and examine it locally it only reports a small number of MB (usually 3-7MB).
There are about 2,000-10,000 rows of data in this table. The reason for this fluctuation is there is a great number of transient cache data being stored in this table and the cron is scheduled to remove them routinely. That’s why there is a discrepancy in the number of rows in the 2 screenshots. Otherwise, I have checked numerous times and the non-transient data is all exactly the same and represented in both environments.
It’s like there’s almost a TB of garbage data hiding in this table that I can’t access or see and it’s only present on production. Staging and local environments with the same database operate just fine without the missing ~TB of data.
summary of table on production:
summary of table from same db on local:
summary of both db sizes in comparison:
What could be causing the export of a SQL file to dis-regard 900GB of data? I’ve exported SQL and CSV via Adminer as well as using the ‘wp db export’ command.
And how could there be 900GB of data on production that I cannot see or account for other than when it calculates the total data length of the table?
2
Answers
It seems like deleted rows have not been purged. You can try OPTIMIZE TABLE.
Some WP plugins create "options" but fail to clean up after themselves. Suggest you glance through that huge table to see what patterns you find in the names. (Yeah, that will be challenging.) Then locate the plugin, and bury it more than 6 feet under.
OPTIMIZE TABLEmight clean it up. But you probably don’t know what the setting ofinnodb_file_per_tablewas when the table was created. So, I can’t predict whether it will help a lot, take a long time, not help at all, or even crash.