Ubuntu 20.04

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-32050 should be released soon. Can you check if the situation would improve after upgrading?

With the table at 1.6GB size the history length was 2317

With the table at 50GB size after a single day

History list length 24897

The history list is measured in transactions, not rows. If the transactions are updating indexed columns, they could cause the secondary indexes to grow a lot.

Note that InnoDB never shrinks .ibd files, except when the table is being rebuilt due to an operation like OPTIMIZE TABLE, ALTER TABLE, or TRUNCATE TABLE. So, even if the purge of history caught up, the file size would likely remain at 50GB until it needs to be extended again.

I'm not sure how to respond to this as from a user's point of view, having the table increase to 500GB by deleted data or indexes which are oversized compared to the actuall necessary data appears unreasonble. Basically as a user when I delete the data I expect the data to be deleted and the space reclaimed if all went properly.

Does the table description help in deducing why the space is never reclaimed? If not how can I assist in troubleshooting this?

CREATE TABLE `wp_options` (

  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `option_name` varchar(191) NOT NULL DEFAULT '',

  `option_value` longtext NOT NULL,

  `autoload` varchar(20) NOT NULL DEFAULT 'yes',

  PRIMARY KEY (`option_id`),

  UNIQUE KEY `option_name` (`option_name`),

  KEY `autoload` (`autoload`)

) ENGINE=InnoDB AUTO_INCREMENT=12987461 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

To understand why this is as it is, you could watch these presentations:
https://mariadb.org/fest2022/how-innodb-multi-version-concurrency-control-mvcc-works/
https://mariadb.org/fest2023/innodb-undo-log-and-purge-history/

OK sure, but what is the end-user supposed to do if that's the case? 3 facts remain at the very lest

1. Obviously we need the many reads / writes / deletes otherwise the app wouldn't do it in the first place.
2. The database size increases by more than 50GB in a day in one site that's 17.8TB in a year. We cannot have this much space free especially in shared hosting environments where we have 50 customers with wordpress sites for example. That's 2.5TB increase in a day
3. We can't keep running optimize or alter table statements constantly as they are blocking operations and for the time they take to execute, the site drops temporarily

How is a user supposed to tackle this?

From the settings
MariaDB [(none)]> show variables like '%undo%';
----------------------------------+

----------------------------------+

----------------------------------+

By default it's turned to off, but does it mean that enabling innodb_undo_log_truncate would actually reclaim the space?

The setting innodb_undo_log_truncate is related to truncating dedicated innodb_undo_tablespaces. The space that is occupied by undo log records in the system tablespace can only be reclaimed in very recent versions that include MDEV-14795.

The end user is expected to upgrade to MariaDB 10.6.16 once it is released, and report if the problem was fixed by that.

OK I see, it sounds reasonable.

Until then is it possible to mitigate ?

Hi @Marko I'm now on Server version: 10.6.16-MariaDB-1:10.6.16+maria~ubu2004 mariadb.org binary distribution and the issue's impact is reduced but not eliminated

I still get a bloated filesystem within a few days. At least it's not 500G which is progress but still the increase is there for indeterminate time and resources are not being released.

While I understand the concept you shared in the videos, having ever-growing tables in the filesystem holding deleted data doesn't help. Optimizing the DB tables constantly doesn't help with performance or downtimes either.

Before optimize
rw-rw--- 1 mysql mysql 6.8G Nov 22 08:21 /var/lib/mysql/db/wp_options.ibd

After optimize
rw-rw--- 1 mysql mysql 140M Nov 22 08:24 /var/lib/mysql/db/wp_options.ibd

I'll provide the history list length when it increases again as after optimize it's

> History list length 25

The same table is now 1.2G and history list length is 160

ioweb.gr, thank you for the update. It seems to me that after the fix of MDEV-32050, the remaining problem is that the .ibd file gets fragmented, or pages are not being merged when the index trees are being shrunk when the history is being deleted.

The remaining growth could also be related to the way InnoDB allocates pages within the data file. That may not have been improved enough in MDEV-13013.

Can you try to get some page-level statistics from the data file? To force all data to be written back from the buffer pool to the data file, you might execute

FLUSH TABLES wp_options FOR EXPORT; UNLOCK TABLES;

(or alternatively tune innodb_max_dirty_pages_pct and wait some time as noted in MDEV-30000) and then execute the innochecksum utility on the wp_options.ibd file, to determine which indexes are consuming the most space within the data files. The INDEX_ID should correspond to INFORMATION_SCHEMA.INNODB_SYS_INDEXES.INDEX_ID.

There is an attribute COMMENT 'MERGE_THRESHOLD=50' that was introduced in MySQL 5.7 and MariaDB Server 10.2, for tables as well as individual indexes. You could try to experiment with it.

FLUSH TABLES wp_options FOR EXPORT; UNLOCK TABLES;

This didn't work. The size remains at over 1.3G

+--------------------------------+-----------+

| Variable_name                  | Value     |

+--------------------------------+-----------+

| innodb_max_dirty_pages_pct     | 90.000000 |

| innodb_max_dirty_pages_pct_lwm | 0.000000  |

What should I put for innodb_max_dirty_pages_pct?

ioweb.gr, the only purpose of the SQL statements was to ensure that innochecksum can access a reasonably recent version of the data in the .ibd file. We’d want to see what the data pages inside the .ibd file are being used for.

I see and please excuse me if I'm not being easy to assist but I'm not well versed into database administrative tasks.

I'm assuming I need to run

$ innochecksum -S wp_options.ibd

File::wp_options.ibd

================PAGE TYPE SUMMARY==============

#PAGE_COUNT     PAGE_TYPE

===============================================

     324        Index page

       0        Undo log page

       1        Inode page

       0        Insert buffer free list page

    1404        Freshly allocated page

       6        Insert buffer bitmap

       0        System page

       0        Transaction system page

       1        File Space Header

       5        Extent descriptor page

   81971        BLOB page

       0        Compressed BLOB page

       0        Page compressed page

       0        Page compressed encrypted page

       0        Other type of page

===============================================

Additional information:

Undo page type: 0

Undo page state: 0 active, 0 cached, 0 to_purge, 0 prepared, 0 other

index_id        #pages          #leaf_pages     #recs_per_page  #bytes_per_page

31538956                234             233             48              13039

31538957                60              59              189             11942

31538958                14              13              799             12985

index_id        page_data_bytes_histgram(empty,...,oversized)

31538956                0       2       2       6       6       12      8       17      19      49      113     0

31538957                0       0       0       1       0       10      16      5       1       1       26      0

31538958                0       1       0       1       0       1       0       0       0       1       10      0

And then check for example index_id 31538956 which seems to consume most size

Which yields

mysql -e 'SELECT * FROM information_schema.INNODB_SYS_INDEXES

> WHERE INDEX_ID = 31538956;'

+----------+---------+----------+------+----------+---------+----------+-----------------+

| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE    | MERGE_THRESHOLD |

+----------+---------+----------+------+----------+---------+----------+-----------------+

| 31538956 | PRIMARY | 18614158 |    3 |        1 |       3 | 14808244 |              50 |

+----------+---------+----------+------+----------+---------+----------+-----------------+

Thank you, this is better.

I don’t think that the innochecksum tool currently parses the allocation bitmap pages. That is, some of the pages that it identifies as index or BLOB pages could actually be marked as free in the tablespace. Can you post the results of the following:

SELECT COUNT(*), SUM(LENGTH(option_value)) FROM wp_options;

If you are using the default innodb_page_size=16k, you would seem to have 81971*16384 = 1,343,012,864 bytes in current or former BLOB data pages inside the file, which is almost 10 times the file size after OPTIMIZE TABLE.

Based on the information gathered so far, the bug would seem to be the way how BLOB pages are allocated in InnoDB. It would be great if you can write a script that would simulate your workload. Using such a script we should be able to reproduce and fix the anomaly.

SELECT COUNT(*), SUM(LENGTH(option_value)) FROM wp_options;

10450	21169372	

I see. Unfortunately I'm not able to provide info for the workload which is causing it. I understand something is writing and deleting data which is temporary in the wp_options table which is common to a lot of plugins in wordpress but also wordpress itself and in the wordpress world they're called transients as a form of object cache.

It's certain that it's some type of transient that's repeating too often the write/delete process

However to be able to reproduce this, I need to see what actual data is stored in the deleted space otherwise I can't even guess what type of data is being created or deleted constantly to find the pattern

It feels like chasing my tail. In order to find the reason I need to see the data to start tracking which query writes them and when. In order to find the bug you need to see the workload that causes it.

Can you give any hints about the workload? Would option_id ever be changed in an UPDATE? For InnoDB BLOBs, that is handled as a special case: the newly inserted record with the updated PRIMARY KEY will inherit the BLOBs from the old record that will be delete-marked, and the old record will ‘disown’ the BLOBs. When BLOBs themselves are updated, a new copy is always allocated and written, and the old copy should be freed as part of purging the undo log records. There could be some bug in this area.

Can you provide copies of the allocation bitmap pages for the file? That is, if you are using the default innodb_page_size=16384, we would need copies of the files produced by the following:

dd bs=16384 skip=0 count=1 if=wp_options.ibd of=page0.bin

dd bs=16384 skip=16384 count=1 if=wp_options.ibd of=page16384.bin

dd bs=16384 skip=32768 count=1 if=wp_options.ibd of=page32768.bin

dd bs=16384 skip=49152 count=1 if=wp_options.ibd of=page49152.bin

dd bs=16384 skip=65536 count=1 if=wp_options.ibd of=page65536.bin

These pages contain bitmaps that identify which pages inside the file are allocated and not garbage. Please also post the innochecksum output for the file at the same point of time.

If most of the BLOB pages are actually marked as allocated, then this would be a bug in the purge of BLOBs. If they are marked as free, then the failure to reuse the freed pages would be a bug of the page allocator.

As far as I know option_id is never changed. I've done a grep in code and I don't see this key anywhere in SQL update / insert commands.

Regarding the files request, do they contain sensitive information? If so is there a secure way to share them where the link won't be publicly accessible?

For BLOBs and PRIMARY KEY updates, we have the open bug MDEV-31441. But, it should not be applicable here.

The allocation bitmap pages do not contain any sensitive information or any user data. Because your data is mostly ASCII text (except for the option_id which would be stored as a big endian 64-bit integer), you could run the strings utility on the files to ensure that there is no legible text stored there. Index pages should have the strings infimum and supremum on them.

Page 0 contains the size of the file in pages, as well as information on which page numbers between 0 and innodb_page_size-1 are allocated. There also are some linked lists of allocated file segments or extents which span all the allocation bitmap pages.

OK I've added the required files in blob.zip

Hi,

Anything else needed on my end to help reproduce this issue?

For the record we still get extreme filesystem sizes daily

Hi, sorry, I was occupied with other tasks. In the file innochecksum_result.txt inside blob.zip (as well as in some earlier comments by you) we can see the following:

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

31538956		192		191		58		13399

31538957		75		74		178		11534

31538958		27		26		649		10536

The first one corresponds to the PRIMARY KEY index, and the next two should correspond to the UNIQUE KEY and the non-unique KEY. BLOB pages will be allocated from the same "file segment" as primary key leaf pages (BTR_SEG_LEAF). According to the #leaf_pages output above, there only are 191 primary key leaf pages or BLOB pages in total. There are at least 2 index leaf pages, because we see that there is one non-leaf page (the total number of pages is one larger).

Inside the file we also have statistics saying that there are 480,609 BLOB pages. It looks like at most 189 of them will actually be allocated for BLOBs.

The allocation bitmap page format is a little cumbersome to parse manually from a hexdump, because the bitmap bytes are interleaved with something else. There are two bits per page. One is always set, so 0xaa would refer to 4 allocated pages and 0xff would refer to 4 free pages. In page65536.bin I see 0xfaffff…. The first byte 0xfa means that the first 2 pages (page 65536 and 65537) are allocated by the system (for the allocation bitmap itself, and for a change buffer bitmap). The files page32768.bin and page49152.bin look similar. In the file page16384.bin the bitmap starts with 0xfaffff… but there are some scattered allocations: 0xabfffffbfffffbfaffbfaaaaaaaaaa. Based on this quick look, it seems that only some of the first 20,000 pages (or 320 MiB) of the file may actually be used, while the file is much larger. In total, only 294 pages (4.6 MiB) inside the file are used by actual payload, according to innochecksum. Actually, there should be 30 allocation bitmap pages in total.

This looks like a possible bug in the page allocation algorithm. I say "possible", because this would be an expected situation if there had been a huge purge lag at some point, and those BLOB pages were only recently freed. InnoDB never shrinks .ibd files. Possibly the secondary indexes will contribute to the purge lag; this would eventually be fixed by a file format change (MDEV-17598).

Can you monitor the history list length over time (once per second or minute), to see if there still are some bursts?

Would the .ibd file keep growing further from its current size, or would the former BLOB pages be eventually reused when the next burst of workload arrives?

Can you try to write a script that simulates the updates to this table? I am afraid that without such a script, it is hard to do anything.

My real problem is determining what updates are running.

Any ideas how to log all queries for a specific database and a specific table ? Perhaps if we parse a log file logging everything we can track the query that's mostly executing and try to simulate it.

I can write a script that monitors the history list length of course, it will run every minute (cron limitation) and log it . Do you also need me to output the filesize of the table as well in the same log?

It would output something like

<list length> - <size>
PS: no worries I totally understand

I’m not familiar with any query filtering, but maybe danblack can help with that.

Yes, the history size and the wp_options.ibd file size would be useful to sample together.

The collection of not so great options include:

• audit plugin, server_audit_events=QUERY, no per table option
• system version tables:

  ALTER TABLE wp_options (

  ADD start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,

  ADD end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,

  ADD PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),

  ADD SYSTEM VERSIONING;

  (remove with

  set statement system_versioning_alter_history=1 for alter table wp_options DROP COLUMN start_trxid, DROP end_trxid, DROP SYSTEM VERSIONING;

  ).

The better option is probably:

• log_bin=1, binlog_do_db=wp (no table option)
• mariadb-dump --dump-master=1 --single-transaction wp wp_options
• , possibly even with replicate_do_table or mariadb-binlog has --table option for filtering.
• If, you are willing to upload these - https://mariadb.com/kb/en/meta/mariadb-ftp-server/

There also is a MyISAM bug MDEV-28499 that mentions the table wp_options. As far as I understand, the .MYD heap file would keep growing until TRUNCATE or OPTIMIZE.

ioweb.gr, do you have any results that you could share? If the majority of read-write transactions is modifying something else than the wp_options table, then the history list length (which is measured in transactions, not rows) would not necessarily correlate with the growth of the file.

Hi,

sorry for the delay, I had to contact my customer about these options to get permission as they could potentially impede the normal functioning of the website
Would this option be good enough to test this ?

ALTER TABLE wp_options (

   ADD start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,

   ADD end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,

   ADD PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),

   ADD SYSTEM VERSIONING;

What would you need me to provide after adding this to the table?

I would not recommend enabling system versioning on the table on a production server. With system versioning, any UPDATE statements will be converted to INSERT and any DELETE to UPDATE. That is, the table will preserve the entire history from the time when system versioning was enabled.

It would be better to set up replication on this table only, and to enable the system versioning on the replica. It could be very helpful to have the history that corresponds to a time frame where the file is growing on the primary server. This of course together with the monitoring of the history list length on the primary server. On the replica, we would only be interested in the history of this table, nothing else.

Hi,
I've asked our server admin to see if it's doable, however he says that it's not in his scope of work or field of expertise. Unfortunately I don't have a person that can perform this type of setup to do replication on only this table to help out

We'll have to try and think of easier potential workarounds that are feasible to perform on my end without breaking our production server.

Any other ideas on how to approach this?

The reason for the table growth is system versioning. This means that nothing is never deleted from the table and any change will just grow it indefinitely until versioning data is truncated.
See
https://mariadb.com/kb/en/system-versioned-tables/#removing-old-history

Hi, in my case it's not because simply

ALTER TABLE wp_options DROP SYSTEM VERSIONING;
ERROR 4124 (HY000): Table `wp_options` is not system-versioned