0

I'm in a situation with a wordpress site where a table wp_options keeps increasing in filesystem size really fast even though it only has 10000 records and we have to constantly optimize it to reduce the usage.

It's my understanding that when records are constantly deleted / inserted they are not actually deleted from the ibd file but they're kind of soft-deleted.

Thus the filesystem size on the file actually increases constantly reaching even 500G within 2 days.

In order to fix the issue I need to actually see what is being written / deleted from the table constantly to find possible plugins that might be affecting it.

I can safely assume it's wordpress transients however I can't say with certainty which type of transient is causing this bloat without examining the data.

So in order to find this out I'm looking for ways to read the soft-deleted data from the bloated ibd file.

I've tried a couple of tools like undrop-for-innodb but it's not yielding any results.

Any ideas how I can read the mass of deleted data that's occupying the filesystem space?

Server version: 10.6.15-MariaDB-1:10.6.15+maria~ubu2004 mariadb.org binary distribution
Improve this question
5
  • I think this was a bug that has been fixed. Are you on the latest 10.6 release? (another release out very soon (days)).
    – danblack
    Commented Nov 1, 2023 at 8:41
  • Hi, I'm on 10.6.15
    – gabtzi
    Commented Nov 1, 2023 at 9:14
  • no fixes seem relevant in 10.6.16. I'd suggest ALTER TABLE wp_options FORCE to get the size down, should take as much time as writing those existing 10k records. Look at SHOW ENGINE INNODB STATUS at the and "History list length" information_schema.INNODB_TRX on transactions open on the table. If it continues create a bug report.
    – danblack
    Commented Nov 1, 2023 at 23:23
  • History list length is 2317 currently but not sure what to check with INNODB_TRX . Isn't there a way to limit how much extra space the tables will use for history compared to the actual data they need to maintain??
    – gabtzi
    Commented Nov 2, 2023 at 10:23
  • I've monitored a lot more of wordpress databases and they all have quite a chunk of deleted data occupying space in the wp_options table. i've opened a bug report as I think it's a general issue not site specific jira.mariadb.org/browse/MDEV-32663
    – gabtzi
    Commented Nov 2, 2023 at 14:08

1 Answer 1

Reset to default
0

Some plugins are quite abusive with wp_options. There is no way to do what you are asking about, but I would try

SELECT * FROM wp_options
    ORDER BY RAND()  LIMIT 50;

It may be evident that many of the rows are being used by one plugin.

If not obvious in the output, repeat the query a time or two to get some more random stuff and/or catch the plugin doing its naughtiness.

This plugin may help the table be more efficient, but won't help with abuse or size: WP Index Improvements

Note: By making queries to that table faster, it may decrease (dramatically?) the "history list length".

Improve this answer
3
  • Unfortunately upon further observation it's not just wordpress. I've just noticed it on magento session tables and in cron_schedule history tables as well. I even manually deleted 3.5m rows and it didn't make a dent in the filesystem size after days. Eventually the deleted data should have been released
    – gabtzi
    Commented Feb 9 at 8:14
  • For big deletes: mysql.rjweb.org/doc.php/deletebig
    – Rick James
    Commented Feb 9 at 17:32
  • The db table is also increasing in filesize on the filesystem (not actual size) even with normal processing of regular transactions where expired sessions are deleted. I just happened to notice it after deleting a big chunk due to a bug, but the main issue is still there
    – gabtzi
    Commented Feb 9 at 21:35

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.