You know that long running query on your server, the one with a group by and a large join? It was probably creating a very large temp table on disk. How large was it? It is hard to tell. If the temp table has spilled to disk and you can login to the server, then you can check the size of the temp table file.
I prefer to monitor this and have mysqld enforce a limit. Who wants to run out of disk space because of a 100G temp table? But MySQL provides no limits for this. You can use a separate file system for temp tables to limit their size. If you have not done that then I think you are stuck. I want to add an option to MySQL so that the max size of an on-disk temp table can be enforced. Options already exist for the max size of an in-memory temp table.
Posts
DBJ: Five More Dials To Turn
2 hours ago
2 comments:
Mark,
Really even mem table restrictions are broken in MySQL or at least part broken. If you are looking for safety for most cases such limit is enough but it is trivial to write query using many large temporary tables and so hogging resources.
It would be great if one could set some per session and per server limits to avoid one session affecting others too much and server from running out of space etc.
@Peter - can you elaborate on what else is broken? I have some things in mind, but you work in more varied environments.
Post a Comment