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
Activity
Field | Original Value | New Value |
---|---|---|
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'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? |
Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
Remote Link | This issue links to "dba link (Web Link)" [ 36237 ] |
Attachment | blob.zip [ 72600 ] |
Component/s | Storage Engine - InnoDB [ 10129 ] | |
Component/s | N/A [ 14411 ] |
Assignee | Marko Mäkelä [ marko ] |
Link | This issue relates to MDEV-28499 [ MDEV-28499 ] |
Affects Version/s | 10.6.17 [ 29518 ] | |
Affects Version/s | 10.6.16 [ 29014 ] |
Attachment | historylenth.zip [ 73186 ] |
Attachment | historylenth-1.zip [ 73201 ] |
Assignee | Marko Mäkelä [ marko ] | Thirunarayanan Balathandayuthapani [ thiru ] |
Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
Link | This issue relates to MDEV-28038 [ MDEV-28038 ] |
Link |
This issue is blocked by |
Link | This issue is blocked by MDEV-29823 [ MDEV-29823 ] |
Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
Attachment | history-list-length.zip [ 73985 ] |
Fix Version/s | 10.6 [ 24028 ] |
Attachment | sample.zip [ 74185 ] |
Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Marko Mäkelä [ marko ] |
Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
Link | This issue relates to MDEV-34097 [ MDEV-34097 ] |
Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
Assignee | Marko Mäkelä [ marko ] | Steve Shaw [ JIRAUSER56063 ] |
Assignee | Steve Shaw [ JIRAUSER56063 ] | Axel Schwenke [ axel ] |
I think that this could be a duplicate of
MDEV-32050. Can you please check the InnoDB history list length from the output of SHOW ENGINE INNODB STATUS?Furthermore, MariaDB Server 10.6.16 with a fix of
MDEV-32050should be released soon. Can you check if the situation would improve after upgrading?