Re: Postgresql Caching

On Sun, Oct 15, 2006 at 02:39:36PM -0700, Josh Berkus wrote:
> > Using memcache, I've had problems with consistency brought right to
> > the front. Both of these have failed me:
> > 1) When updating a PostgreSQL record, I invalidate the memcache record.
> > If another process comes along in parallel before I commit, notices
> > that the memcache record is invalidated, it queries the data from
> > SQL, and updates the memcache record back to the old value. :-(
> >
> > 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.
> The answer is that cached values are not expected to be consistent. If they
> were, then they'd have to have all of the transaction overhead which
> PostgreSQL has, and lose any gain in efficiency.

Agree.

> Generally, memcached is best used for values that don't get kept in the
> database at all. Example (how I use it)
>
> Using pgmemcache for user session information:
>
> 1) User logs in. Their session information is stored in the permanent
> user_session table and the pgmemcache pseudotable, user_session_cache.
> 2) User browses the website. This requires updating their session every time
> a page is loaded with their last activity timestamp, their page path (for
> backtracking) and their ip information (for hijack prevention). This
> informaiton is recorded in user_session_cache *only*, with the presumption
> that it will be lost if the server goes down.
> 3) User logs out (or is auto-logged-out). Keys are deleted from
> user_session_cache and their exit information is written to the permanent
> table user_session.

Is there a benefit here to doing this with pgmemcache over the application
calling in to memcache directly?

Are you able to effectively and efficiently include memcache derived
information within select queries that include information you want
to pull out of the database?

I like the logout commits information part, and it's something I've been
meaning to do, but haven't gotten around to.

> The advantage of this is that it allows lowering the amount of write activity
> to the user_session table by 95% with no loss of information we care about.
> Of course, if you are using a Java or .NET application server, it probably
> provides the above functionality itself.

Agree.

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/