Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-8253

Implement a feature to allow truncating the intrinsic session temporary tablespaces

Details

    • New Feature
    • Status: Open
    • Medium
    • Resolution: Unresolved
    • 8.0.28-19 (Q1 2022), 8.0.30-22 (Q3 2022)
    • None
    • None

    Description

      Session temporary tablespaces store user-created temporary tables and internal temporary tables created by the optimizer when InnoDB is configured as the storage engine for on-disk internal temporary tables. Beginning with MySQL 8.0.16, the storage engine used for on-disk internal temporary tables is InnoDB.

      Session temporary tablespaces are allocated to a session from a pool of temporary tablespaces on the first request to create an on-disk temporary table. A maximum of two tablespaces is allocated to a session, one for user-created temporary tables and the other for internal temporary tables created by the optimizer. The temporary tablespaces allocated to a session are used for all on-disk temporary tables created by the session. When a session disconnects, its temporary tablespaces are truncated and released back to the pool.
      https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html

      While the implementation works completely fine when the session disconnects it's problematic for real-life use cases. If an application is using connection pooling or any proxy like ProxySQL to connect to the database, the connections rarely expire. Also, as that's the idea behind the connection pooling the connections are shared for a different part of the application or even different applications.

      As MySQL already have a mechanism to truncate the Session Temporary Tablespaces when the connection disappears it would be very helpful to have some kind of mechanism that would trigger the same behaviour if Session Temporary Tablespace is larger than some threshold and the transactions/query is completed.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              iwo.panowicz Iwo Panowicz
              Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Smart Checklist