Re: cheaper snapshots redux

On Sat, Aug 27, 2011 at 1:38 AM, Gokulakannan Somasundaram
<gokul007(at)gmail(dot)com> wrote:
> First i respectfully disagree with you on the point of 80MB. I would say
> that its very rare that a small system( with <1 GB RAM ) might have a long
> running transaction sitting idle, while 10 million transactions are sitting
> idle. Should an optimization be left, for the sake of a very small system to
> achieve high enterprise workloads?

With the design where you track commit-visbility sequence numbers
instead of snapshots, you wouldn't need 10 million transactions that
were all still running. You would just need a snapshot that had been
sitting around while 10 million transactions completed meanwhile.

That having been said, I don't necessarily think that design is
doomed. I just think it's going to be trickier to get working than
the design I'm now hacking on, and a bigger change from what we do
now. If this doesn't pan out, I might try that one, or something
else.

> Second, if we make use of the memory mapped files, why should we think, that
> all the 80MB of data will always reside in memory? Won't they get paged out
> by the  operating system, when it is in need of memory? Or do you have some
> specific OS in mind?

No, I don't think it will all be in memory - but that's part of the
performance calculation. If you need to check on the status of an XID
and find that you need to read a page of data in from disk, that's
going to be many orders of magnitude slower than anything we do with s
snapshot now. Now, if you gain enough elsewhere, it could still be a
win, but I'm not going to just assume that.

As I play with this, I'm coming around to the conclusion that, in
point of fact, the thing that's hard about snapshots has a great deal
more to do with memory than it does with CPU time. Sure, using the
snapshot has to be cheap. But it already IS cheap. We don't need to
fix that problem; we just need to not break it. What's not cheap is
constructing the snapshot - principally because of ProcArrayLock, and
secondarily because we're grovelling through fairly large amounts of
shared memory to get all the XIDs we need.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company