Thread: Observation about db response time

Observation about db response time

From
"Akshay Mathur"
Date:

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?

 

Regards,

 

akshay

 

 

---------------------------------------

Akshay Mathur

SMTS, Product Verification

AirTight Networks, Inc. (www.airtightnetworks.net)

O: +91 20 2588 1555 ext 205

F: +91 20 2588 1445

 

Re: Observation about db response time

From
Frank Wiles
Date:
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.

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: Observation about db response time

From
Vivek Khera
Date:

On Aug 30, 2005, at 9:05 AM, Akshay Mathur wrote:

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).

On a 7.4.2 db, there should probably be no index bloat, but there could be.  Does REINDEX on your tables help?  If not, then VACUUM FULL followed by REINDEX may help.  The latter should result in nearly the same as your dump+restore.  And you need to run vacuum often enough to keep your tables from bloating.  How often that is depends on your update/delete rate.

Also, updating to 8.0 may help.

Vivek Khera, Ph.D.

+1-301-869-4449 x806



Re: Observation about db response time

From
"Jeffrey W. Baker"
Date:
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