Re: Limiting setting of hint bits by read-only queries; vacuum_delay

Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>> we need testing against real world workloads, or at least a much
>> better synthetic one than pgbench, which per recent discussions
>> is probably the top objective of the project (a performance
>> farm, etc.).
>
> Self-tuning the background workloads needs lots of testing.
> Limiting foreground work needs very little, or none.

This is absolutely a real-world problem, but I disagree that the
solution you propose is risk-free.  It would be trivial to
construct a test which would show massive performance degradation.
Consider a single largish table which fits in cache and is subject
to frequent seqscans, and a workload which burns through
transaction IDs fast enough to cause clog thrashing as these xids
get old and still lack hint bits.

I think there are ways to deal with that problem, with the
foreground select telling the bgwriter or autovacuum to pay
attention to the problem tables (or pages), but now is not the time
to start designing that.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company