Re: Observation about db response time

От: george young
Тема: Re: Observation about db response time
Дата: ,
Msg-id: 20050830143618.2c10144d.gry@ll.mit.edu
(см: обсуждение, исходный текст)
Ответ на: Observation about db response time  (<>)
Список: pgsql-performance

 On Sat, 27 Aug 2005 21:28:57 +0530 (IST)
<> 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)


В списке pgsql-performance по дате сообщения:

От: Michael Stone
Дата:
Сообщение: Re: RAID Configuration Sugestion
От: Ron
Дата:
Сообщение: Re: RAID Configuration Sugestion