On Thu, Jun 08, 2006 at 01:30:42AM +0300, Hannu Krosing wrote: > ??hel kenal p??eval, T, 2006-06-06 kell 13:53, kirjutas Christopher > Browne: > > >> We have triggers that fire is something interesting is found on insert. > > >> We want this thing to run for a log time. > > >> From the numbers, you can see the PostgreSQL database is VERY loaded. > > >> Running VACUUM may not always be possible without losing data. > > > > > > why ? just run it with very friendly delay settings. > > > > "Friendly delay settings" can have adverse effects; it is likely to > > make vacuum run on the order of 3x as long, which means that if you > > have a very large table that takes 12h to VACUUM, "vacuum delay" will > > increase that to 36h, which means you'll have a transaction open for > > 36h. > > > > That'll be very evil, to be sure... > > Not always. I know that it is evil in slony1 context, but often it *is* > possible to design your system in a way where a superlong transaction is > almost unnoticable. > > Long transactions are evil in case they cause some fast-changing table > to grow its storage size several orders of magnitude, but if that is not > the case then they just run there in backgroun with no ill effects, > especially do-nothing transactions like vacuum.
Plus, if the only issue here is in fact the long-running transaction for vacuum, there's other ways to address that which would be a lot less intrusive than doing something like going to 64 bit XIDs. IIRC, in 8.2 vacuum will start a new transaction every time it fills up maintenance_work_mem, so just setting that low could solve the problem (at the expense of a heck of a lot of extra IO). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
