How DBA solved overload problem ?

[Jan Grant]

[cc list cut]

On Thu, 18 Sep 2003, Supote Leelasupphakorn wrote:

> To all,
>
>    As a newly DBA, I really don't know how I deal with
> this problem. My problem is not so long ago, my database
> server seem to overloaded. It take me a time to find
> the cause of problem. I realize that some program don't
> queried wiht inappropriated SQL statement. I mean they're
> not efficient one.
>
> AS DBA how do you solved this problem?
>
> Thanks in advance,

It depends on your database to some extent; but basically, you need to
gather information. Assuming privacy and monitoring concerns have been
dealt with (hey, it happens), you ideally need to find a way to crank up
your DB's logging levels so that it records query statistics. You're
looking for particularly expensive queries. The usual crop of tools
(explain) can help you to tune your datyabase (eg, via index creation,
materialised views, etc.) or track down users to impart clue. From that
you should be able to identify the applications that are the source of
your problems.

You're probably better off directing specific questions at support
mailing lists for the DB in question.

jan

PS. If the DB in question is postgresql, you may find that complex query
performance is highly erratic due to the genetic planner kicking in.
Either turn up the number of generations it uses by an order of
magnitude or so, or turn it off.

-- 
jan grant, ILRT, University of Bristol. http://www.ilrt.bris.ac.uk/
Tel +44(0)117 9287088 Fax +44 (0)117 9287112 http://ioctl.org/jan/
Just because I have nothing to hide doesn't mean I have nothing to fear.