Strange MySQL behavior

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.

[B]AlexN wrote on Tue, 16 October 2007 09:42[/B]

The question is: how to avoid such situation ? In, for example, MSSQL this scenario is hardly possible.

Not true, if you set the memory usage limits for MSSQL to use more memory than you have RAM in the machine it will also bring the system to it's knees. It's a OS problem not a DBMS problem.

The problem is that you want to give the DBMS as much RAM memory as possible to use as cache to avoid disk IO.

BUT if you set this value too high then the OS will start to swap and that will degrade performance a_lot.

So it is better to stay on the safe side with the memory settings, especially since you are running Apache/PHP on the same server which also will consume unregular amounts of RAM.

[B]sterin wrote on Tue, 16 October 2007 09:21[/B]

So it is better to stay on the safe side with the memory settings, especially since you are running Apache/PHP on the same server which also will consume unregular amounts of RAM.

Exactly. But where is the safe side ? As you correctly pointed, there are other programs on server that use unregular amounts of RAM. So when the load increases, the memory load can pass some threshold above which the system will go down.
Is it a possible DOS-attack scenario ?

But that is not a DOS attack targeted against MySQL per se.

That is an attack targeted against the slow and weak parts of your application and it’s design.
The fact that your application is using a database in the background is another matter.
Because if you have bad PHP code (or whatever language you are using) you can target it the same way.

The attacker only knows that the page is slow and that is what he is attacking.

[B]sterin wrote on Thu, 18 October 2007 05:03[/B]

Because if you have bad PHP code (or whatever language you are using) you can target it the same way.

The attacker only knows that the page is slow and that is what he is attacking.

Not really. Just PHP code will not cause that ‘avalanche’ scenario. The response time will increase more or less linear with increasing load.