This is an additional information.
I wrote:
> If we want to resolve the probmen fundamentally, we might have to
> improve SubTrans using a better buffer management algorithm or so.
The above is maybe wrong. I checked each lwlock of pg_subtrans's buffers.
All lwlocks are uniformly acquired and I could not see any differences
among buffers. So the cause seems not to be a buffer management algorithm,
but just a lack of SLRU buffer pages.
NUM_SUBTRANS_BUFFERS is defined as 32 in HEAD. If we increase it,
we can avoid the old transaction problem for a certain time.
However, it doesn't help much on high-load -- for example, on a workload
with 2000 tps, we will use up 1000 pg_subtrans pages in 15 minites.
I suppose it is not enough for online and batch/maintenance mix.
Also, the simple scanning way in SLRU will likely cause another performance
issue when we highly increase the number of buffers. A sequential scanning
is used in SLRU, so it will not work well against many buffers.
I hope some cares in upper layer, snapshot, hitbits or something,
being discussed in the recent thread.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center