MySQL Bugs: #48112: Add limits for (total and per user) temporary tables usage

Bug #48112	Add limits for (total and per user) temporary tables usage
Submitted:	16 Oct 2009 11:28
Reporter:	Andrii Nikitin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any

Description:
It will be useful if DBA can limit:

- max size single temporary table can occupy
- max size of total size of all temporary tables
- max number of simultaneous temporary tables created total in server
- max number of simultaneous temporary tables created per connection
- max number of rows temporary table can hold

These limits may be set globaly and per user.
They should not affect important system operations (like recovery).

How to repeat:
feature request

Suggested fix:
feature request

Currently max_tmp_tables does nothing, implementing it will be good start and will cover most important part of this request

Andrii,

and what should happen when the server is about to exceed the limit? I guess for "individual" limits like 
- max size single temporary table can occupy
- max number of rows temporary table can hold
- max number of simultaneous temporary tables created per connection
aborting the query is the only possible choice?

What about the "collective" limits, like these:
- max size of total size of all temporary tables
- max number of simultaneous temporary tables created total in server

Should the queries start to fail when the limits are exhausted? One can also think of making queries to wait until other queries have freed the resources, but what if this is a single session that has consumed all the limits (and hence waiting will actually hang the server)?

Hello Sergey, thanks for looking into it.

Currently some users are limiting "max file size" in tmp directory, so mysql gets OS error trying to create too big temporary table.

First problem that this increases coupling between mysqld and OS.
Second, bigger, problem is that this may affect critical operations, like crash recovery, etc.

So, it is OK if query is aborted if some mentioned limit is exceeded (error is generated the same way as with other resources, like OOM).

I was the original instigator of this ticket.

I guess there are 2 options when different limits are exceeded:
1. generate an error and abort the query/transaction
2. wait (hoping that some space will become available)

Option (2) sounds attractive but can cause other problems as processes get "locked up" waiting for space, causing a connection pile up.

So perhaps initially option (1) is the only sensible option, unless this could be toggled as under certain workloads one option may be better than the other.

We had an issue on a large server with temporary tables occupying over 30GB of disk space and filling up the tmpdir partition. It would have been nice to avoid this problem and let the application get an error and try again, as the cause was due to several different batch jobs running at once.

> What about the "collective" limits, like these:
> - max size of total size of all temporary tables
> - max number of simultaneous temporary tables created total in server

This is like a typical deadlock situation. Choose the worse offender and "kill it", or give it the "out of temporary table space" error. Usually it's best to kill the process occupying the smallest amount of tmpdir as any rollbacks caused by killing a process will likely take longer if the tmpdir usage is bigger. Again this may lead a long running "process hog" as the only remaining process. So again ideally an "oldest" tmpdir user, or "largest" tmpdir user, or "latest" tmpdir options would be ideal. If only one option is available at the beginning then that's fine.

> One can also think of
> making queries to wait until other queries have freed the resources, but what if this is
> a single session that has consumed all the limits (and hence waiting will > actually hang the server)?

Well if you get to the stage that there is only one thread running and no "tmpdir" resources it's clear what you have to do.... 
1. Get the dba to dynamically increase the sizes (assuming the thread is in some sort of "hold" state, or
2. just kill the thread.

So my initial suggestions would be to:
1. kill threads when the limits are exceeded, according to some sort of "worse offender" algorithm.
2. envisage that other algorithms may be possible in the future and include configuration variables so that they only need be changed in future versions of MySQL to change the new behaviour.

As a second thought IIRC Sybase and Oracle have the concept of a "temp db/schema" and this has a predetermined size.

It's been a while since I've used either of these so I'll check their behaviour but according to 

http://www.dbspecialists.com/files/presentations/temp_space.html
oracle will give an ORA-1652: unable to extend temp segment error. That matches the simplest suggestion I mentioned.

I've forgotten what Sybase does (and will check) but IIRC sybase will just halt all threads until there is space.

So my initial reaction would be to try and do things as simple as possible first, be as consistent as possible with other RDBMS vendors, and if necessary have extra flags to adjust behaviour if needed.

For now I'd just kill all threads using too much space if the limit is local and if the limit is a global one attempt to MINIMISE damage by killing the thread that will generate less rollbacks.

http://bugs.mysql.com/bug.php?id=70857 is a duplicate of this.