Thread

1. Observation about db response time

   akshay@airtightnetworks.net — 2005-08-27T15:58:57Z

   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
   
   
   
   
   

2. Re: Observation about db response time

   george young <gry@ll.mit.edu> — 2005-08-30T18:36:18Z

    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)