[MDEV-18257] Make memory usage safer for temp tables Created: 2019-01-15  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Manjot Singh (Inactive) Assignee: Ralf Gebhardt
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9954 Prevent MEMORY tables from consuming ... Confirmed

 Description   

Through conversations with a customer, adding variables to make temp tables in memory and memory table size more measurable. These variables should be a maximum of a global (or session) total. ie memory for tmp or heap tables in total can never exceed this value.

2 variables should be added:
max_total_tmp_table_size
max_total_heap_table_size

If set, queries wanting to insert to or create in memory tables would wait on memory availability or they could just go to disk as on disk temp tables. This could be controlled with another variable, such as max_total_heap_table_wait=0|1

For example, if max_total_tmp_table_size = 1G, and 9 connections do 1 query each which creates 9 100M tables. Query 10 comes through and creates 101M of temp table, it cannot complete until one of the previous 9 queries deallocates memory, ie deletes rows or drops tables.

While this makes more sense as a global variable, it could also be per session, in that this session cannot exceed the session or global total at all (whichever is lower).

Defaults should be 0 (unlimited).

 Comments   
Comment by Oleksandr Byelkin [ 2019-05-16 ]

Have author of this read about max_heap_table_size?

Actually what the difference between temporary and heap total? I suppose one is for query execution other for ususal tables.

Automatic internal query:
1) temporary in query could not use more then 3 or 4 (depends on version) tables i.e. total limit already present.
2) temporary taböes are not the only in memory structures for query execution and (surprise surprise) they are also limited (just recently was fixed bug in group_concat)
So maybe just documentation should be clear about what and how limit automatic temporary tables?

Usual in memory tables:
how it supposed to work at all with examples and so on, taking into account that tables can be shared between connections/users?

Comment by Manjot Singh (Inactive) [ 2019-05-16 ]

@Oleksandr @ralf what I see commonly with enterprise customers is overuse of temp tables at concurrency.

1) What this ticket is, is a GLOBAL limit on all, not individual queries, not connections, the entire mariadb server.

2) this is about in memory temp tables just like tmp_table_size. max_tmp_tables is an unused variable so this is not helpful and would probably apply to in memory and on disk tables.

What we need is a way to limit overall server memory use and have queries queue just like other enterprise RDBMS products.

Generated at Thu Feb 08 08:42:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.