In MySQL Server 8.0.23 an improvement was made to InnoDB that allows truncation and deletion of all tablespaces to be faster. In fact, making it almost instantaneous as long as AHI is disabled. This is done by allowing pages in the buffer pool to be freed lazily instead of immediately when a tablespace is deleted. This change generalizes the technique that was introduced in 8.0.21 that made UNDO tablespace truncation instant, described here. Similar to the UNDO-specific fix, this work makes any tablespace deletion or truncation process much faster and, what is more important, removes one of the main causes for MySQL server stalls during a drop/truncate of a table.

There are three parts to the DROP TABLE/TABLESPACE process. The first is the InnoDB buffer pool adjustment, second is the cleanup of the AHI indexes and the third has to do with the deletion of the file on the file system. Former two are cause for stalls and are usually the bigger overhead especially for very large buffer pools or where temporary tablespaces are used very aggressively for query processing. This important change fixes the first part.

Users have asked us for faster dropping of tables/tablespaces. This work makes the first and biggest step toward delivering the performance of these operations. The MySQL Server 8.0.23 Release Notes also attribute this work an improvement in AHI handling of dropped tables, which in the end was not delivered in this version. However, improving AHI handling is subject of current work and will be fixed soon too.

Operations affected

In addition to operations specified in the post mentioned above , the tablespace truncation and deletion is executed on explicit operations like:

• Tablespace deletion with DROP TABLESPACE
• Table deletion or truncation for any table that resides in a file-per-table tablespace with DROP TABLE and TRUNCATE TABLE
• Implicit temporary tablespace truncation. In this case, it may surprise users that these are truncated not on DROP TEMPORARY TABLE but on session disconnect. For example the internal temporary tablespaces might have been used by the optimizer while executing some more complicated queries.

Kind of problems seen in previous MySQL Server versions

During tablespace deletion and truncation one of the steps was to purge the buffer pool of the pages used by the deleted/truncated tablespace. The bigger the buffer pool the more pages it had to traverse and hold internal latches for longer, which would impact the foreground/query threads, this is what caused the stalls. Any parallel DML transaction needed to wait for this internal structure update to finish, before continuing its own work. This effectively was causing the Server to stall for multiple seconds or more, which was very painful on huge Buffer Pools that had multiple hundreds of GB of memory assigned.
What is important, the length of the stall does not relate to size of the deleted tablespace. Deleting large or small, even empty tablespace would stall the server for the same time because the entire buffer pool had to be traversed to check for stale pages.

Improvements in 8.0.23

With this change there is no need to do any expensive traversal of the buffer pool data structures to evict the deleted pages. The dropped tablespace pages are handled lazily and additionally can be reused as free pages when scanning the LRU lists looking for pages to evict and reuse by other query threads. This change results in the operation to be almost instantaneous: the work done in InnoDB is very short and the speed of the operation depends on how quickly the OS can delete the actual data file. Because of this, as long as AHI is not involved, the operation does not cause any stalls, in all cases, even if the OS takes some time to delete the data file.

Performance example

The size of the LRU list and Flush list that needed to be updated in previous versions can be checked with the following query:

For tests I’ve used a MySQL Server instance with default configuration and Buffer Pool sizes of 32, 64, 128, 256 and 450GB. Test plan follows:

• Create a big table and fill it with data about the size of the Buffer Pool.
• Create and delete 100 empty tables, concurrently.

When the above test was conducted on a previous version of MySQL Server it could be seen clearly that the DROP TABLE statements that were meant to be concurrent, were executed sequentially. The server was stalled during this time, unable to serve DML queries.

The same plan executed on the new version finished significantly faster, independent of the size of the Buffer Pool, and the queries seemed to be executed concurrently.

A chart with data collected is presented below:

It is a big improvement! So a chart with logarithmic Y-axis presents the data in a more readable way:

MySQL Server when used on production will have a much bigger number of modified pages than this synthetic test and in consequence any tablespace deletions in pre 8.0.23 versions will result in much longer and more severe stalls. The improvement seen in the testing above is up to 251x!

Thank you for using MySQL!