MySQL Performance: MySQL => Strange MySQL behavior

Members

Help

Home

Home » Performance » MySQL » Strange MySQL behavior

Show: Today's Messages :: Show Polls :: Message Navigator

Strange MySQL behavior [message #2067]

Tue, 16 October 2007 03:42

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

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.

Report message to a moderator

Re: Strange MySQL behavior [message #2068 is a reply to message #2067 ]

Tue, 16 October 2007 09:21

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

AlexN wrote on Tue, 16 October 2007 09:42

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.

Report message to a moderator

Re: Strange MySQL behavior [message #2070 is a reply to message #2068 ]

Tue, 16 October 2007 10:23

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

sterin wrote on Tue, 16 October 2007 09:21

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 ?

Report message to a moderator

Re: Strange MySQL behavior [message #2073 is a reply to message #2070 ]

Tue, 16 October 2007 12:24

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Quote:

Exactly. But where is the safe side ?
...

That is for the administrator/developer to decide (I guess that you are one of them Wink

).

The problem is that you can't give a generic answer since it depends on the application.

As for a DOS attack, that is actually attacking the TCP stack of the OS so the outcome of that depends on how the OS handles it.

[Updated on: Tue, 16 October 2007 12:24]

Report message to a moderator

Re: Strange MySQL behavior [message #2076 is a reply to message #2073 ]

Wed, 17 October 2007 03:35

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

sterin wrote on Tue, 16 October 2007 12:24

That is for the administrator/developer to decide (I guess that you are one of them Wink

That's right, but I can not decide without necessary information. That's what I am looking for. The system seems to be tuned correctly, but then it goes down in minutes. It is like an avalanche.

sterin wrote on Tue, 16 October 2007 12:24

As for a DOS attack, that is actually attacking the TCP stack of the OS so the outcome of that depends on how the OS handles it.

Not necessary. The term "Denial Of Service" is more general, as there are many sorts of DOS attacks. Various components of a system could be DOSed. Including MySQL server, of course.

Report message to a moderator

Re: Strange MySQL behavior [message #2091 is a reply to message #2076 ]

Wed, 17 October 2007 19:55

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

AlexN wrote on Wed, 17 October 2007 09:35

That's right, but I can not decide without necessary information. That's what I am looking for. The system seems to be tuned correctly, but then it goes down in minutes. It is like an avalanche.

Correct, and the information here is that you don't have enough headroom to properly handle it. Ergo, reduce the settings to give more headroom to handle those cases. And avalanche is the correct name for it because it usually happens very fast when you reach the limit.

It's like driving a car.
You can go faster and faster by narrowing the margins. And in the end you go really fast and then suddenly you crash and you are lying upside down in a ditch and wonder why you didn't go slower with more margins.

AlexN wrote on Wed, 17 October 2007 09:35

Not necessary. The term "Denial Of Service" is more general, as there are many sorts of DOS attacks. Various components of a system could be DOSed. Including MySQL server, of course.

Granted that the expression can be used in a broader perspective.
But how are any malicious DOS'ers going to reach MySQL?
If you have designed and setup your system properly then you shouldn't expose the DB to the public and that means that all DB access has to go through your application and then it is up to the application to handle it.

Report message to a moderator

Re: Strange MySQL behavior [message #2092 is a reply to message #2091 ]

Thu, 18 October 2007 03:06

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

sterin wrote on Wed, 17 October 2007 19:55

But how are any malicious DOS'ers going to reach MySQL?

Easily. Just browse the site and find some slow queries that
you beleive are handled by MySQL. Usually it could be search,
price calculation, statistics, etc. Run those queries from
many connections at once, and you're done. The problem is,
that to bring down MySQL with badly designed database you
need sufficiently less connections than to bring down TCP/IP
stack. Also there exist software that prevents DOS attacks
on TCP/IP stack, but there is no software to detect attacks
on MySQL...

Report message to a moderator

Re: Strange MySQL behavior [message #2095 is a reply to message #2067 ]

Thu, 18 October 2007 05:03

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Re: Strange MySQL behavior [message #2097 is a reply to message #2095 ]

Thu, 18 October 2007 06:26

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

sterin wrote on Thu, 18 October 2007 05:03

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.

Report message to a moderator

Re: Strange MySQL behavior [message #2098 is a reply to message #2097 ]

Thu, 18 October 2007 08:26

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

AlexN wrote on Thu, 18 October 2007 12:26

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

As long as it is CPU bound yes, but the webserver with the PHP interpreter can also allocate a lot of memory and then you will experience the exact same thing.

Since the whole reason for the avalanche effect is that you have run out of RAM on the machine and it starts to swap.

And when that has happened it starts spending almost all CPU time (worst case) to just swap in and out all active processes to/from RAM trying to execute them and basically nothing gets done.

[Updated on: Thu, 18 October 2007 08:27]

Report message to a moderator

Previous Topic:	IS NOT NULL condition - MySQL doesn't use index
Next Topic:	Query tuning - Performance Issue

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 21:15:27 EDT 2009

Total time taken to generate the page: 0.05223 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.