Re: limiting hint bit I/O

On Fri, Jan 14, 2011 at 05:24:31PM -0800, Josh Berkus wrote:
> On 1/14/11 11:51 AM, Tom Lane wrote:
> > The people whose tables are mostly insert-only complain about it, but
> > that's not the majority of our userbase IMO. We just happen to have a
> > couple of particularly vocal ones, like Berkus.
>
> It might or might not be the majority, but it's an extremely common case
> affecting a lot of users. Many, if not most, software applications have
> a "log" table (or two, or three) which just accumulates rows, and when
> that log table gets a vacuum freeze it pretty much halts the database in
> its tracks. Between my client practice and IRC, I run across complaints
> about this issue around 3 times a month.

If the problem is that all the freezing happens at once, then ISTM the
solution is to add a random factor. Say, when a tuple just passes the
lower threshold it has a 1% chance of being frozen. The chance grows
until it is 100% as it reaches the upper threshold.

This should reduce the freezing traffic to a constant (hopefully
manageable) stream, since as the chance of freezing increases the
amount of data to be frozen goes down, so they should cancel somewhat.

To avoid rewriting pages multiple times, if one tuple can be frozen on
a page, we should freeze as many as possible, but the logic may do that
already.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle