InnoDB system tables cannot be optimized or defragmented

MDEV-14795 implemented a way to shrink the InnoDB system tablespace after undo tablespaces have been moved to separate files (MDEV-29986). But, we did not yet implement any logic to optimize or defragment any pages of InnoDB system tables so that the system tablespace could be shrunk further.

It could happen that at some time of the past when the system tablespace had been filled by undo log pages (using innodb_undo_tablespaces=0), some DDL operation was executed and some pages of data dictionary tables were allocated at a large page number. These pages would stay there and prevent the system tablespace from being shrunk, long after all the undo pages had been freed.

To allow the tablespace to be shrunk further, we must implement some logic to move the pages of the InnoDB system tables towards the start of the system tablespace. Luckily, there are no BLOB columns among the system tables; we will not have to implement any logic for relocating them.

In one data file that we analyzed, 57 of the last 64 pages (1 MiB extent) of the InnoDB system tablepace belonged to the SYS_INDEXES table. The rest were 7 undo log pages, which had been marked as freed.