From: Jeffrey W. Baker <jwb@gghcwest.com>
To: Frank Wiles <frank@wiles.org>
Cc: Akshay Mathur <akshay.mathur@airtightnetworks.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Observation about db response time
Date: Tue, 30 Aug 2005 09:39:17 -0700
Message-ID: <1125419957.2658.0.camel@kane> (raw)
In-Reply-To: <20050830081322.20a13d4f.frank@wiles.org>
References: <003401c5ad63$8420d940$9701a8c0@pune.wibhu.com>
<20050830081322.20a13d4f.frank@wiles.org>
On Tue, 2005-08-30 at 08:13 -0500, Frank Wiles wrote:
> On Tue, 30 Aug 2005 18:35:30 +0530
> "Akshay Mathur" <akshay.mathur@airtightnetworks.net> wrote:
>
> > Hello Friends,
> >
> > We were having a database in pgsql7.4.2 The database was responding
> > very slowly even after full vacuum analyze (select count(*) from
> > some_table_having_18000_records was taking 18 Sec).
> >
> > We took a backup of that db and restored it back. Now the same db on
> > same PC is responding fast (same query is taking 18 ms).
> >
> > But we can't do the same as a solution of slow response. Do anybody
> > has faced similar problem? Is this due to any internal problem of
> > pgsql? Is there any clue to fasten the database?
>
> This could be because you don't have max_fsm_pages and
> max_fsm_relations setup correctly or are not doing full vacuums
> often enough.
>
> If your database deletes a ton of data as a matter of course then
> sometimes a full vacuum will not clear up as much space as it could.
>
> Try increasing those configuration values and doing vacuums more
> often.
>
> If you should also explore upgrading to the latest 8.0 as you will
> no doubt see noticeable speed improvements.
This can also be caused by index bloat. VACUUM does not clear out the
index. You must use REINDEX for that.
-jwb
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: jwb@gghcwest.com, frank@wiles.org, akshay.mathur@airtightnetworks.net
Subject: Re: Observation about db response time
In-Reply-To: <1125419957.2658.0.camel@kane>
* 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