Some days ago I encountered with a strange problem. MySQL and Apache resides both on the same server. In attempt to optimize MySQL someone increased the key buffer too much. Also the number of temp tables has been set too large.
At first it worked fine, but when the server load increased, Apache used some memory, while MySQL started to create temp tables on disk instead of memory. Queries took more time to complete, and new connections continued to arrive. In less than 20 minutes the whole server nearly stopped. There were about 20 running processes, all in the state “Copying to temp table”.
The question is: how to avoid such situation ? In, for example, MSSQL this scenario is hardly possible.