Re: Postgresql Caching

On Sun, Oct 15, 2006 at 06:33:36PM -0700, Jeremy Drake wrote:
> > 2) When updating a PostgreSQL record, I updated the memcache record
> > to the new value. If another process comes along in parallel before
> > I commit, that is still looking at an older view, cross-referencing
> > may not work as expected.
> Shouldn't you be able to use 2-stage commit for this? Prepare to commit,
> update the memcache record, then commit? Or am I thinking of something
> else?

Two stage commits makes the window of error smaller, it can't eliminate it.

I believe the window would be reduced to:

1) The transactions that are currently looking at older data, and:

2) Any memcache query that happens between the time of the PostgreSQL
commit and the memcache commit.

It's a pretty small window. The question for my use case, would be
very hundreds of people clicking on web links per second, might happen
to hit the window. By setting the memcache store to 5 seconds instead
of the regular 60+, I seem to have eliminated all reported cases of the
problems. If the stored data is invalid, it only stays invalid for a
short time. I'm compromising accuracy for efficiency.

The thing about all of this is, if what memcache is doing could be
done with consistency? PostgreSQL would probably already be doing it
right now...

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/