| Bug #102313 | Tablespace may still larger than limit after truncation | ||
|---|---|---|---|
| Submitted: | 20 Jan 2021 12:24 | Modified: | 21 Jan 2021 13:16 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Analyzing | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jan 2021 12:26]
zhai weixiang
sorry, possiblely it should be : n_pages = srv_max_undo_tablespace_size /(4 * UNIV_PAGE_SIZE)
[20 Jan 2021 19:09]
Sunny Bains
This is by design. In our tests, where the undo tablespace is being used very aggressively, the only way to reduce the impact of truncate and reduce stalls is to keep the tabelspace larger and a percentage of its max size. The assumption is that it will grow very quickly again. This is actually an interim fix until we roll out the change that allows the user to set the initial size and increment size explicitly via ALTER TABLESPACE ...; stay tuned.
[21 Jan 2021 13:16]
MySQL Verification Team
Just setting the correct status.
Description: I checked the commit of 60fd370cae5af4d92038e308da2ebec8fe1843cc for MySQL 8.0.23. It says: 3) When an undo tablespace is truncated, it is normally re-created at 16 MB. But if the current extend size is larger than 16 MB and the previous file extension happened within the last second, indicating a busy system with at least some recent aggressive growth, this patch creates the new tablespace at 1/4th of --innodb-max-undo-log-size. The document at https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html also says: Prior to MySQL 8.0.23, the initial size of an undo tablespace depends on the innodb_page_size value. For the default 16KB page size, the initial undo tablespace file size is 10MiB. For 4KB, 8KB, 32KB, and 64KB page sizes, the initial undo tablespace files sizes are 7MiB, 8MiB, 20MiB, and 40MiB, respectively. As of MySQL 8.0.23, the initial undo tablespace size is normally 16MiB. The initial size may differ when a new undo tablespace is created by a truncate operation. In this case, if the file extension size is larger than 16MB, and the previous file extension occurred within the last second, the new undo tablespace is created at a quarter of the size defined by the innodb_max_undo_log_size variable but actually it may create a new undo tablespace with 1/4th of old tablespace. ref code in function trx_undo_truncate_tablespace: ``` if (space->m_undo_extend > INITIAL_UNDO_SPACE_SIZE_IN_PAGES && space->m_last_extended.elapsed() < 1000) { /* UNDO is beeing extended aggressively, dont' reduce size to default. */ n_pages = fil_space_get_size(old_space_id) / 4; } `` If size of old tablespace is extremely large, the 1/4th of it may be larger than innodb_max_undo_log_size How to repeat: read the code Suggested fix: change: n_pages = fil_space_get_size(old_space_id) / 4; to: n_pages = srv_max_undo_tablespace_size / 4;