From: Harry Jackson <harryjackson@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Crashing DB or Server?
Date: Fri, 16 Dec 2005 13:38:24 +0000
Message-ID: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com> (raw)
In-Reply-To: <c244500b0512160515p23eafee2u@mail.gmail.com>
References: <c244500b0512160515p23eafee2u@mail.gmail.com>
On 12/16/05, Moritz Bayer <moritz.bayer@googlemail.com> wrote:
> This is really weird, just a few hours ago the machine run very smooth
> serving the data for a big portal.
Can you log the statements that are taking a long time and post them
to the list with the table structures and indexes for the tables being
used.
To do this turn on logging for statements taking a long time, edit
postgresql.conf file and change the following two parameters.
log_min_duration_statement = 2000 # 2 seconds
Your log should now be catching the statements that are slow. Then use
the statements to get the explain plan ie
dbnamr=# explain [sql thats taking a long time]
We would also need to see the table structures.
dbname=# \d [table name of each table in above explain plan]
> Has anybody an idea what might have happened here?
> I need a quick solution, since I'm talking about an live server that should
> be running 24 hours a day.
It may be that the planner has started to pick a bad plan. This can
happen if the database is regularly changing and the stats are not up
to date. I believe it can happen even if the stats are up to date but
is much less likely to do so.
It might also be an idea to vacuum the database.
dbname=# VACUUM ANALYZE;
This will load the server up for a while though.
--
http://www.hjackson.orghttp://www.uklug.co.uk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: harryjackson@gmail.com
Subject: Re: Crashing DB or Server?
In-Reply-To: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox