Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <

Hi,

On 2016-04-16 18:27:06 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2016-04-16 17:52:44 -0400, Tom Lane wrote:
> >> That's more than a 5X penalty, which seems like it would make the
> >> feature unusable; unless there is an argument that that's an extreme
> >> case that wouldn't be representative of most real-world usage.
> >> Which there may well be; I've not been following this thread carefully.
>
> > The 4 % was with the feature disabled (in comparison to before it's
> > introduction), we're not sure where that's coming from. But the 5x - and
> > that was just on a mid-sized box - is with the feature enabled.
>
> 128 processors is a mid-sized box?

It has fewer.

> Or if you didn't have 128 processors, why are you testing "-c 128 -j
> 128" cases?

I tried 128, because it's a random number I picket out of my hat. I've
posted various different client numbers elsewhere in the thread. The
machine (a VM, this isn't the best test!), has 20 cores / 40 hw threads
afaik.

But 128 connections on 40 "cpus" isn't unrealistic. Many workloads have
a lot more connections and concurrent queries than cores - besides often
being operationally easier, it's also sensible from a hardware
utilization perspective. Due to latency effects individual connections
frequently are idle; even if the client were issuing queries as fast as
possible.

> More seriously, the complaints here seem to center on performance in a
> read-only workload; but I don't actually see why you'd want to turn on
> this feature in a read-only, or even read-mostly, workload.

In a purely read-only workload it's surely pointless. But I don't see
why the results would be any benefit in a 75 read/25 write mix; which is
probably already more write heavy than a lot of the actual workloads out
there.

> It exists for
> the benefit of people who are trying to keep their pg_xlog/ directories
> reasonably sized, no? That doesn't sound very read-only-ish to me.

pg_xlog size? By my understanding it's there to cope with the bloat
introduced by longrunning readonly transactions? Isn't the idea that
"old snapshots" basically don't enforce their xmin anymore, allowing
vacuum/hot pruning? Such old snapshots continue to work as long as
they're not used to make visibility decisions about pages which have
been modified "recently". The whole feature only is interesting if such
old snapshots are likely to only access data that's not frequently
modified.

- Andres