Re: SLRUs in the main buffer pool, redux

On Thu, Jun 16, 2022 at 1:13 PM Konstantin Knizhnik <knizhnik(at)garret(dot)ru> wrote:
> I wonder which workload can cause CLOG to become a bottleneck?
> Usually Postgres uses hint bits to avoid clog access. So standard pgbench doesn't demonstrate any degrade of performance even in case of presence of long living transactions,
> which keeps XMIN horizon.

I haven't done research on this in a number of years and a bunch of
other improvements have been made since then, but I remember
discovering that CLOG could become a bottleneck on standard pgbench
tests especially when using unlogged tables. The higher you raised the
scale factor, the more of a bottleneck CLOG became. That makes sense:
no matter what the scale factor is, you're constantly updating rows
that have not previously been hinted, but as the scale factor gets
bigger, those rows are likely to be older (in terms of XID age) on
average, and so you need to cache more CLOG buffers to maintain
performance. But the SLRU system provides no such flexibility: it
doesn't scale to large numbers of buffers the way the code is written,
and it certainly can't vary the number of buffers devoted to this
purpose at runtime. So I think that the approach we're talking about
here has potential in that sense.

However, another problem with the SLRU code is that it's old and
crufty and hard to work with. It's hard to imagine anyone being able
to improve things very much as long as that's the basis. I don't know
that whatever code Thomas has written or will write is better, but if
it is, that would be good, because I don't see a lot of improvement in
this area being possible otherwise.

--
Robert Haas
EDB: http://www.enterprisedb.com