Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.15, 10.6.16, 10.6.17
-
None
-
Ubuntu 20.04
Description
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?
I also think that there should be a limit to the expansion of the table with deleted data. Like, why would a table of 20MBs in size need 500GB of deleted data in the filesystem?
Attachments
Issue Links
- is blocked by
-
MDEV-20094 InnoDB blob allocation allocates extra extents
-
- Closed
-
-
MDEV-29823 Secondary index records may never be purged after rollback
-
- Confirmed
-
- relates to
-
MDEV-28499 wordpress wp_options tables cannot be optimized
-
- Open
-
-
MDEV-28038 Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)
-
- Open
-
-
MDEV-34097 Extremely slow performance on specific to Wordpress/Woocommerce joins
-
- Open
-
- links to
I've managed to get a good look at the queries running in the DB by getting a huge log of queries in wp_options table.
or queries like this
It seems the bulk is queries like in this file sample.zip
Which are very large.
rw-rr- 1 root root 128G Oct 29 20:58 wp_options_query.logrw-rr- 1 root root 23G Feb 29 2024 wp_options_query_real.logrw-rr- 1 root root 14M Oct 29 20:56 wp_options_query_real_notransient.logOut of the full option log of 128G of queries,
23G were actually writing on the wp_options table
Out of which 14M only where not of the same type as sample.zip
Out of the 23G file I found these types of queries
18 fornext
6 forupdate
5 itemsUPDATE
1 permissionsUPDATE
60 tablesUPDATE
4 tonet
3295 UPDATE`wp_options`
So I can deduce that consecutively running queries like this reproduces the growth issue