Description:
From docs (https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html) :
"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."
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.
Anything sane here, as a connection to recycling, is also a solution. The problem is it requires an effort than could be easily handled by the database, just to make everyone's life easier.
How to repeat:
-
Suggested fix:
-
