On Wed, Dec 07, 2005 at 14:14:31 +0200, Assaf Yaari <assafy@xxxxxxxxxxxx> wrote: > Hi Jan, > > As I'm novice with PostgreSQL, can you elaborate the term FSM and > settings recommendations? http://developer.postgresql.org/docs/postgres/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM > BTW: I'm issuing VACUUM ANALYZE every 15 minutes (using cron) and also > changes the setting of fsync to false in postgresql.conf but still time > seems to be growing. You generally don't want fsync set to false. > Also no other transactions are open. Have you given us explain analyse samples yet? > > Thanks, > Assaf. > > > -----Original Message----- > > From: Jan Wieck [mailto:JanWieck@xxxxxxxxx] > > Sent: Tuesday, December 06, 2005 2:35 PM > > To: Assaf Yaari > > Cc: Bruno Wolff III; pgsql-performance@xxxxxxxxxxxxxx > > Subject: Re: [PERFORM] Performance degradation after > > successive UPDATE's > > > > On 12/6/2005 4:08 AM, Assaf Yaari wrote: > > > Thanks Bruno, > > > > > > Issuing VACUUM FULL seems not to have influence on the time. > > > I've added to my script VACUUM ANALYZE every 100 UPDATE's > > and run the > > > test again (on different record) and the time still increase. > > > > I think he meant > > > > - run VACUUM FULL once, > > - adjust FSM settings to database size and turnover ratio > > - run VACUUM ANALYZE more frequent from there on. > > > > > > Jan > > > > > > > > Any other ideas? > > > > > > Thanks, > > > Assaf. > > > > > >> -----Original Message----- > > >> From: Bruno Wolff III [mailto:bruno@xxxxxxxx] > > >> Sent: Monday, December 05, 2005 10:36 PM > > >> To: Assaf Yaari > > >> Cc: pgsql-performance@xxxxxxxxxxxxxx > > >> Subject: Re: Performance degradation after successive UPDATE's > > >> > > >> On Mon, Dec 05, 2005 at 19:05:01 +0200, > > >> Assaf Yaari <assafy@xxxxxxxxxxxx> wrote: > > >> > Hi, > > >> > > > >> > I'm using PostgreSQL 8.0.3 on Linux RedHat WS 3.0. > > >> > > > >> > My application updates counters in DB. I left a test > > over the night > > >> > that increased counter of specific record. After night running > > >> > (several hundreds of thousands updates), I found out > > that the time > > >> > spent on UPDATE increased to be more than 1.5 second (at > > >> the beginning > > >> > it was less than 10ms)! Issuing VACUUM ANALYZE and even > > >> reboot didn't > > >> > seemed to solve the problem. > > >> > > >> You need to be running vacuum more often to get rid of the deleted > > >> rows (update is essentially insert + delete). Once you get > > too many, > > >> plain vacuum won't be able to clean them up without > > raising the value > > >> you use for FSM. By now the table is really bloated and > > you probably > > >> want to use vacuum full on it. > > >> > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > > choose an index scan if your joining column's > > datatypes do not > > > match > > > > > > -- > > #============================================================= > > =========# > > # It's easier to get forgiveness for being wrong than for > > being right. # > > # Let's break this rule - forgive me. > > # > > #================================================== > > JanWieck@xxxxxxxxx # > >
