[MDEV-28699] Shrink temporary tablespaces without restart Created: 2022-05-30  Updated: 2024-01-05  Resolved: 2023-10-27

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - InnoDB
Fix Version/s: 11.3.1

Type: Task Priority: Blocker
Reporter: William Edwards Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 2
Labels: Preview_11.3

Issue Links:
Blocks
is blocked by MDEV-14795 InnoDB system tablespace cannot be sh... Closed
is blocked by MDEV-32028 InnoDB scrubbing doesn't write zero w... Closed
is blocked by MDEV-32145 Disable read-ahead for temporary tabl... Closed
is blocked by MDEV-32151 innodb_immediate_scrub_data_uncompres... Closed
PartOf
Problem/Incident
causes MDEV-33101 Server crashes when starting the serv... Closed
Relates
relates to MDEV-26782 InnoDB temporary tablespace: reclaimi... Closed
relates to MDEV-12227 Defer writes to the InnoDB temporary ... Closed
relates to MDEV-13013 InnoDB unnecessarily extends data files Closed
relates to MDEV-14795 InnoDB system tablespace cannot be sh... Closed
relates to MDEV-15528 Avoid writing freed InnoDB pages Closed
relates to MDEV-32163 Crash recovery fails after DROP TABLE... Closed

 Description   

The documentation at https://mariadb.com/kb/en/innodb-temporary-tablespaces/ says:

Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

This means temporary tablespaces will keep growing until MariaDB is restarted. Having to restart MariaDB periodically is not optimal. It would be welcome if MariaDB shrinks temporary tablespaces on disk without restarting.

To achieve this, we would introduce new syntax:

SET GLOBAL innodb_truncate_temporary_tablespace_now=1;



 Comments   
Comment by Marko Mäkelä [ 2022-05-31 ]

Before we can fix this, I think that MDEV-13013 needs to be addressed. If any space is available in the data file, we should try to allocate that space, instead of extending the file.

When it comes to shrinking InnoDB data files, the groundwork is laid out in MDEV-15528. Theoretically, we could implement shrinking of any InnoDB data files. Currently we only do that for innodb_undo_log_truncate=ON when rebuilding an entire undo tablespace.

For the temporary tablespace, we do not even have to think about recovery or logging. Possibly we could extend the idea of MDEV-12227 and implement a new file format in which pages in the temporary tablespace are assigned a file offset when the pages are actually spilled from the buffer pool to the file system.

Comment by William Edwards [ 2022-07-24 ]

Thank you for your substantive reply, Marko. I think that this issue, together with MDEV-12459, shows that temporary tablespaces need some work...

Comment by Marko Mäkelä [ 2023-01-10 ]

I think that this will be easier to implement, once MDEV-14795 has been implemented.

Comment by Marko Mäkelä [ 2023-09-07 ]

Now that tests by mleich seem to be reasonably stable, I reviewed the current development version. The logic looks mostly OK. The only problem that I found is a lock order inversion (or latching order violation) when we are evicting page allocation bitmap pages that would be in the to-be-freed page range. We must never wait for a page latch while holding buf_pool.mutex. If a "trylock" fails, we need a fallback path that will involve releasing and reacquiring buf_pool.mutex. See buf_page_create_low() for an example.

Comment by Marko Mäkelä [ 2023-09-12 ]

This rebase is OK to push to the preview release. Thank you for separating the bug fixes into separate commits. I did not notice any correctness problems, but it would be nice to address my minor review comments.

Comment by Matthias Leich [ 2023-10-10 ] 

origin/preview-11.3-preview, origin/bb-11.3-mdev-32254, preview-11.3-preview 76e20f00772148fa928c6c6e42401f38ca89abf0 2023-09-27T13:08:34+10:00

performed quite good in RQG testing (with innodb_truncate_temporary_tablespace_now etc.). No new InnoDB related problems related to the modifications of MDEV-28699.

Comment by Marko Mäkelä [ 2023-10-25 ]

This could not be pushed to 11.3 before MDEV-32028, MDEV-32145, and MDEV-32151 had been merged.

Generated at Thu Feb 08 10:02:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.