how to limit the creation on disk temp tables

Hello List,

so far I could not find any answer on how to abort queries that exceed
certain size of internal temporary tables.

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.

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


Sincerely,
Alexandr N

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: how to limit the creation on disk temp tables

----- Original Message -----
> From: "Alexandr Normuradov"
>
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org