Re: vacuum, performance, and MVCC

On Thu, 2006-06-22 at 13:56 -0400, Mark Woodward wrote:
> >> > You mean systems that are designed so exactly, that they can't take
> >> 10%
> >> > performance change ?
> >>
> >> No, that's not really the point, performance degrades over time, in one
> >> minute it degraded 10%.
> >>
> >> The update to session ratio has a HUGE impact on PostgreSQL. If you have
> >> a
> >> thousand active sessions, it may take a minute to degrade 10% assuming
> >> some level of active vs operations per session per action.
> >
> > So don't do an update. Multiple updates to the same row block anyway
> > which is generally not something you want anyway.
>
> The example is a very active web site, the flow is this:
>
> query for session information
> process HTTP request
> update session information
>
> This happens for EVERY http request. Chances are that you won't have
> concurrent requests for the same row, but you may have well over 100 HTTP
> server processes/threads answering queries in your web server farm.
>
> >
> > If you INSERT into multiple partitions (by time -- say one per minute)
> > and TRUNCATE periodically (30 minute old partitions for 30 minute
> > expiry) it works much better. Expiring the session is quite fast as well
> > since they'll go away with the truncate.
> >
> > Index on sessionid and time and grab the row with the most recent time.
>
> I doubt that that approach (1) answers the problem or (2) would be more
> efficient.

Depends on what else you have going on in the background. If you can
vacuum constantly and have the garbage cleaned up, it may not be. If you
have a long running transaction (pg_dump, vacuum?, report query, ...) in
the background then it can make a pretty good difference last time I
measured.

If this is the only thing that the database has and vacuum still isn't
able to keep up, who knows you might find advantages to being able to
truncate periodically instead of a constant vacuum.

--