MySQL Bugs: #99136: TempTable wastes 1MB for each connection in thread cache

Bug #99136	TempTable wastes 1MB for each connection in thread cache
Submitted:	31 Mar 2020 17:15	Modified:	16 Jun 2020 17:44
Reporter:	Nikolai Ikhalainen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
MySQL allocates 1MB for each thread even inside thread cache (after closing connection) due to TempTable.

This is causing excessive memory usage, compare to mysql 5.7:
With high enough thread_cache_size mysql could handle simple requests issued by 6-10k connections. In case of rarely used temporary tables mysql wastes several GB of RAM for mostly unused temporary tables.

https://github.com/mysql/mysql-server/blob/mysql-8.0.19/storage/temptable/src/handler.cc#L...

/** A container for the list of the tables. Don't allocate memory for it from
 * the Allocator because the Allocator keeps one block for reuse and it is
 * only marked for reuse after all elements from it have been removed. This
 * container, being a global variable may allocate some memory and never free
 * it before its destructor is called at thread termination time. */
using Tables = std::unordered_map<std::string, Table>;

/** A list of the tables that currently exist for this OS thread. */
static thread_local Tables tls_tables;

How to repeat:
1. docker pull mysql/mysql-server:8.0 && docker run -d --name m80 -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql/mysql-server:8.0 --performance-schema-instrument='%=COUNTED' --thread-cache-size=100

2. Run multiple queries with temporary tables all at once:
 for i in $(seq 80) ; do mysql -e 'select sleep(5),sum(l) from (select length(c) l from test.t order by c LIMIT 100000) x' & done ; wait

3. Check memory usage caused by TempTable with:
select * from memory_summary_global_by_event_name where event_name='memory/temptable/physical_ram'\G

Suggested fix:
Make tls_tables cleanup after client disconnect configurable
or allow to reduce block size with configuration option.

There is a small simplification to make the problem easier to reproduce.
Instead of running heavy table it's enough to execute show variables like '...':

for i in $(seq 80) ; do mysql -e "show variables like '%tmp%';select sleep(10);" &>/dev/null & done ; wait

mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name='memory/temptable/physical_ram'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 80
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 83886080
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 80
             HIGH_COUNT_USED: 3
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 83886080
   HIGH_NUMBER_OF_BYTES_USED: 83886080
1 row in set (0.01 sec)

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  |  136 | Waiting on empty queue | NULL             |
|  9 | root            | 127.0.0.1:34342 | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+

As expected mysqladmin flush-threads clears CURRENT_NUMBER_OF_BYTES_USED

Hello Nikolai,

Thank you for the report and feedback.
Verified as described with 8.0.19 build.

Thanks,
Umesh

Test results - 8.0.19(current and reported version), 8.0.11(lowest GA version in 8.0.x)

Attachment: 99136_8.0.11_19.results (application/octet-stream, text), 17.85 KiB.

Posted by developer:
 
Fixed as of the upcoming 8.0.22 release, and here's the proposed changelog entry from the documentation team:

A segmentation fault occurred in the TempTable storage engine while using
the thread pool plugin. TempTable thread-local variables were not
compatible with the use of different threads for statements issued by a
single client connection. Use of thread local variables also lead to
excessive memory consumption due to the memory used by thread-local
variables remaining allocated for the life of the thread. To address these
issues, thread-local variables were replaced by a caching mechanism.

Posted by developer:
 
Changelog entry moved to 8.0.21 release notes.