MariaDB Galera 3 Node DR

The system tablespace should grow either due to the change buffer or due to undo logs. ALTER TABLE operations should not write excessive amounts of undo log in the reported version, because the MDEV-11415 fix (affecting ALGORITHM=COPY) is already present. Native ALTER TABLE should not exercise the change buffer, but ALGORITHM=COPY could do that.

It seems to me that your question should best be answered by our support staff.

I have enabled innodb_file_per_table =1 in my.cnf file , for each table *.ibd file is generated .

We are loading Data into MariaDB Galera DR using Apache Kakfa and sometime by Load ifile option

You might want to consider acquiring a support contract.

During the development of MDEV-15528, I mentioned to thiru that the mechanism of shrinking undo tablespaces that was implemented in MDEV-13564 could be applied to any tablespace.

Undo tablespace truncation is a special case: the entire tablespace will be rewritten. But, we could use a similar mechanism for shrinking any tablespace. If the last pages of the tablespace are freed, we could shrink the file.

However, it is not trivial to shrink an arbitrary InnoDB tablespace. Even if the last pages in a tablespace are unallocated, we would want some hysteresis so that files would not be repeatedly extended (MDEV-13013) and shrunk.

Ultimately, I would like to remove the system tablespace at some point (MDEV-11633). It stores the following:

• the doublewrite buffer (128 data pages at fixed location; MDEV-11659)
• the TRX_SYS page that points to undo log headers and the doublewrite buffer
• the DICT_HDR page that contains some global information
• data dictionary tables that store the metadata of persistent InnoDB tables
• the change buffer (not used if innodb_change_buffering=none)
• undo logs (unless innodb_undo_tablespaces are being used)
• InnoDB tables created with innodb_file_per_table=0

I think that it is easiest to configure the server so that nothing will unnecessarily be created in the system tablespace:

• Disable the change buffer.
• Make sure that undo logs will be stored in separate tablespaces starting with undo001.
• Make sure that nothing is changing innodb_file_per_table from its default value ON.

This should prevent most fragmentation of the system tablespace. Theoretically, the data dictionary tables could still cause trouble, but I would be surprised if it were the case.

Would it be possible to defragment or pack the system tablespace?

• The 128 doublewrite buffer pages as well as a few other pages are at a fixed location and must be left alone.
• Undo log pages are virtually impossible to relocate, due to DB_ROLL_PTR pointing to them. But, we do support truncation of dedicated undo tablespaces.
• BLOB pages are virtually impossible to relocate, because undo log records may point to them. Also, there may be multiple clustered index records pointing to the same BLOB, in case the PRIMARY KEY of a record that contains BLOBs was updated. The entire singly-linked list of BLOB pages would have to be moved in an atomic mini-transaction. Obeying the latching order while keeping all pointers to the BLOB pages synchronized could be challenging. If the BLOB is larger than the buffer pool, it could be outright impossible without changing the file format in some way.

I would like to know if this issue remains after implementing the mitigation measures: disable the change buffer, use dedicated undo tablespaces, and never use innodb_file_per_table=0. Any remaining growth should only be caused by the data dictionary tables. Those tables should not contain any BLOBs, so theoretically it should be possible to relocate pages of dictionary tables closer to the start, so that the system tablespace could be shrunk.

Note: It could be that the defragmentation feature (MDEV-5834, MDEV-18385) is only doing something about B-tree fill factor, and possibly not even attempting to move the pages to the start of the tablespace. And that feature definitely does not operate on any dictionary tables.

Once MDEV-26445 has been fixed, I think that it should be safe to enable InnoDB undo tablespace truncation (and multiple undo tablespaces). If you additionally disable the change buffer and ensure that no tables are being created in the system tablespace, then the system tablespace should not keep growing.

MDEV-27734 disabled the InnoDB change buffer due to known but irreproducible data corruption issues.

Fixing MDEV-14795 will allow the InnoDB system tablespace to be shrunk in place. Until then, the only way to shrink it is to start from the scratch (dump and reload all InnoDB tables).

This ticket was closed because it is possible to avoid the growth of the InnoDB system tablespace by changing some configuration parameters.

A contributing factor to the growth of the system tablespace would be the “caching” (delayed freeing) of undo log pages. The fix of MDEV-29593 should reduce the excessive allocation of undo log pages.

That said, I would still recommend setting multiple innodb_undo_tablespaces. MDEV-29986 changed the default to 3.