Re: Vacuum and Transactions

On T, 2005-10-04 at 11:10 -0400, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > The catch is that there are some other very active structures (like
> > pg_listener for Slony) which after a couple of hours without vacuuming
> > will quickly have the DB at an unreasonably high load (low tens) which
> > seems to all but halt the vacuum on the large structure.
> 
> Yeah.  We desperately need to reimplement listen/notify :-( ... that
> code was never designed to handle high event rates.

Sure. But it handles amazingly well event rates up to a few hundred
events per second - given that pg_listener is cleaned up often enough.
Above a few hundred eps it starts geting stuck on locks.

It also seems that Slony can be modified to not use LISTEN/NOTIFY in
high load situations (akin to high performance network cards, which
switch from interrupt driven mode to polling mode if number of packets
per second reaches certain thresolds).

Unfortunately Slony and Listen/Notify is not the only place where high-
update rate tables start to suffer from vacuums inability to clean out
dead tuples when working in parallel with other slower vacuums. In real
life there are other database tasks which also need some tables to stay
small, while others must be huge in order to work effectively. Putting
small and big tables in different databases and using dblink-like
functionality when accessing them is one solution for such cases, but it
is rather ugly :(

-- 
Hannu Krosing <hannu@skype.net>