List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:September 28 2011 5:48am
Subject:Re: how to limit the creation on disk temp tables
View as plain text  
----- Original Message -----
> From: "Alexandr Normuradov" <normalex@stripped>
> 
> so far I could not find any answer on how to abort queries that
> exceed certain size of internal temporary tables.

I'm not sure there is.

> On certain quite often scenarios these internal tables are being
> converted to Myisam on disk tables. And that creates a high IO
> depending on situation.
> Putting tmpdir in tmpfs solves half of the problem.

Well, yes, although it's more efficient to increase your max_temporary_table variable (or
whatever it's called; lazy) so you don't waste cycles on the conversion. 

> Have anyone came up with a better approach on how to restrict them?

Well, the whole idea of a database is that you ask it a question and it gives you an
answer - there's not really any mechanism for asking a question and then saying "don't
answer that".

I suppose you could point your tmpspace to an unwriteable location, although I'm not sure
what the effect would be on the rest of the database's operations.

If you do an explain of each query beforehand, you'll get an idea of what it's going to do
and you could prevent actually executing it based off that, but it'd not be an exact
science either.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
how to limit the creation on disk temp tablesAlexandr Normuradov28 Sep
  • Re: how to limit the creation on disk temp tablesJohan De Meersman28 Sep