pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedObservation about db response time
6+ messages / 6 participants
[nested] [flat]
* Observation about db response time
@ 2005-08-27 15:58 akshay@airtightnetworks.net
0 siblings, 1 reply; 6+ messages in thread
From: akshay@airtightnetworks.net @ 2005-08-27 15:58 UTC (permalink / raw)
To: pgsql-performance
Hello Friends,
We were having a database in pgsql7.4. The database was responding very
slowly even after full vacuum (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 fasteen the database?
Regards,
akshay
^ permalink raw reply [nested|flat] 6+ messages in thread
* Observation about db response time
@ 2005-08-30 13:05 Akshay Mathur <akshay.mathur@airtightnetworks.net>
0 siblings, 2 replies; 6+ messages in thread
From: Akshay Mathur @ 2005-08-30 13:05 UTC (permalink / raw)
To: pgsql-performance
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. ( <http://www.airtightnetworks.net/;
www.airtightnetworks.net)
O: +91 20 2588 1555 ext 205
F: +91 20 2588 1445
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Observation about db response time
@ 2005-08-30 13:13 Frank Wiles <frank@wiles.org>
parent: Akshay Mathur <akshay.mathur@airtightnetworks.net>
1 sibling, 1 reply; 6+ messages in thread
From: Frank Wiles @ 2005-08-30 13:13 UTC (permalink / raw)
To: Akshay Mathur <akshay.mathur@airtightnetworks.net>; +Cc: pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Observation about db response time
@ 2005-08-30 14:53 Vivek Khera <vivek@khera.org>
parent: Akshay Mathur <akshay.mathur@airtightnetworks.net>
1 sibling, 0 replies; 6+ messages in thread
From: Vivek Khera @ 2005-08-30 14:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Observation about db response time
@ 2005-08-30 16:39 Jeffrey W. Baker <jwb@gghcwest.com>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 0 replies; 6+ messages in thread
From: Jeffrey W. Baker @ 2005-08-30 16:39 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: Akshay Mathur <akshay.mathur@airtightnetworks.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Observation about db response time
@ 2005-08-30 18:36 george young <gry@ll.mit.edu>
parent: akshay@airtightnetworks.net
0 siblings, 0 replies; 6+ messages in thread
From: george young @ 2005-08-30 18:36 UTC (permalink / raw)
To: pgsql-performance
On Sat, 27 Aug 2005 21:28:57 +0530 (IST)
<akshay@airtightnetworks.net> threw this fish to the penguins:
> Hello Friends,
> We were having a database in pgsql7.4. The database was responding very
> slowly even after full vacuum (select
> count(*) from some_table_having_18000_records was taking 18 Sec).
One comment here: "select count(*)" may seem like a good benchmark, but
it's not generally. If your application really depends on this number, fine.
Otherwise, you should measure performance with a real query from your
application. The "select count(*)" can be very slow because it does
not use indexes.
> 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).
This sounds like some index is getting gooped up. If you do a lot of
deleting from tables, your indexes can collect dead space that vacuum
can not reclaim. Try in sql "reindex table my_slow_table" for a
suspect table. In the contrib directory of the postgresql
distribution there is a script called "reindexdb". You can run this
to reindex your whole database.
I also wonder about file system slowdowns. What hardware/OS/filesystem
are you using?
-- George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-30 18:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-27 15:58 Observation about db response time akshay@airtightnetworks.net
2005-08-30 18:36 ` george young <gry@ll.mit.edu>
2005-08-30 13:05 Observation about db response time Akshay Mathur <akshay.mathur@airtightnetworks.net>
2005-08-30 13:13 ` Frank Wiles <frank@wiles.org>
2005-08-30 16:39 ` Jeffrey W. Baker <jwb@gghcwest.com>
2005-08-30 14:53 ` Vivek Khera <vivek@khera.org>
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