THD may not cleaning up all memory between queries

THD may not cleaning up all memory between queries

THD stores allocated memory for a set of different purposes:

• Vio data data (used for reading data from a connection)
• Default 16K may be allocated in THD->net.read_buffer
• While testing this, it was not allocated

• Connection data
• Basically query string + some overhead
• Stored in THD->net.buff of size (THD->net.buff_end - THD->net.buff)
• A block of 16K is allocated by default
• Can grow up to max_allowed_size = 16777216. Will not decrease
• Slave connection size may grow up to slave_max_allowed_packet = 1073741824
• We should probably reallocate this at end of query if bigger than
  max_allowed_size.

• Memory used to process the current query
• Stored in THD->main_mem_root
• At startup, a permanent block of query_prealloc_size(24K) is
  allocated. If needed new blocks of query_alloc_size(16K) are
  allocated.
• This is reset to query_prealloc_size at end of each statement

• For prepared statements
  These are allocated in blocks of query_alloc_block_size (16K)

• Transactions information
• At startup, a permanent block of transaction_prealloc_size(4K) is
  allocated. If needed new blocks of transaction_alloc_block_size
  (8K) is allocated.

• Query digest
• Stored in THD->m_token_array
• Fixed size array of 'max_digest_length' (1024)

• Binary log, allocated if log is enabled.
• Stored in THD->cache_mngr
• A memory of binlog_stmt_cache_size (32K) + binlog_cache_size (32K)
  is allocated at first binlog write.

• User variables
• Stored in THD->user_vars
• stores name and data for each variable
• Data can be reset to 0 but name will be available until connection is closed

• User level locks
• Stored in THD->ull_hash
• Memory reused for new entries
• hash memory will not shrink

• Open handlers (HANDLER OPEN)
• Stored in THD->handler_tables_hash
• Memory reused for new entries
• hash memory will not shrink

• Sequences (to get previous value of a sequence)
• Stored in THD->sequences

• Session tracker. Active if session_track_schema = ON
• Variables stored in THD->session_tracker.sysvars in a hash
• One allocation per tracked variable (THD->session_track_system_variables)
• 8K for the hash to track variables; Could be reduced to 1K

Note that the 'basic' memory used for idle connection should be
sizeof(THD) = 27888 + query_prealloc_size (24) + connection data(16K)
+ some other small objects (see above).

On my current system memory_used memory_used is 75872 bytes for an
idle, just started connection without binary logging.

Normally when running queries the 'memory_used' from
SELECT id, command, memory_used, max_memory_used from information_schema.processlist
should not grow 'a lot' over time except in the following cases:

• There are active prepared statements
• This memory should go back to 'basic' when all statements are freed.
• Queries with a lot of text (which causes connection string to
  permanently increased).

While visiting a MariaDB user running 10.6.18 and checking the process
lists, I noticed a couple of connections that where using some 100M of
memory even while being 'Sleep'.

The task is to find out case if there is some common cases where the
'max_memory' grows a lot without coming back.

Known issues where this could happen (probably not related to this case)

• A lot of prepared statements that are never freed
• A lot of user variables that are never freed
• A lot of locks that are never freed
• Having really long query strings, up to 128M or more

Some variables from the user:
query_alloc_block_size 16384 (default)
query_prealloc_size 24576 (default)
max_allowed_packet 268,435,456
slave_max_allowed_packet 1,073,741,824 (default)
binlog_cache_size 1,048,576
binlog_stmt_cache_size 131,072
transaction_alloc_block_size 8192 (default)
transaction_prealloc_size 4096 (default)

Note that if one is using a server compiled with safemalloc, one can find all allocated memory for a connection with:

p sf_report_leaked_memory(connection_id)