From: george young <gry@ll.mit.edu>
To: pgsql-performance@postgresql.org
Subject: Re: Observation about db response time
Date: Tue, 30 Aug 2005 14:36:18 -0400
Message-ID: <20050830143618.2c10144d.gry@ll.mit.edu> (raw)
In-Reply-To: <2527.192.168.1.175.1125158337.squirrel@mailserv>
References: <2527.192.168.1.175.1125158337.squirrel@mailserv>
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)
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: gry@ll.mit.edu
Subject: Re: Observation about db response time
In-Reply-To: <20050830143618.2c10144d.gry@ll.mit.edu>
* 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