pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@qwest.net>
To: C. Bensend <benny@bennyvision.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
Date: Thu, 17 Jun 2004 17:50:48 -0600
Message-ID: <1087516248.27839.28.camel@localhost.localdomain> (raw)
In-Reply-To: <1854.134.244.169.17.1087487894.squirrel@webmail.stinkweasel.net>
References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net>
	<15549.1087351669@sss.pgh.pa.us>
	<50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net>
	<15996.1087354312@sss.pgh.pa.us>
	<58063.63.227.74.41.1087355391.squirrel@webmail.stinkweasel.net>
	<1087363871.1582.20.camel@localhost.localdomain>
	<1185.134.244.169.17.1087396700.squirrel@webmail.stinkweasel.net>
	<opr9qaj9yp94ope3@olivier.amaze.ulb.ac.be>
	<1087486656.27839.2.camel@localhost.localdomain>
	<1854.134.244.169.17.1087487894.squirrel@webmail.stinkweasel.net>

On Thu, 2004-06-17 at 09:57, C. Bensend wrote:
> > The problem here isn't pg_autovacuum, but too small of settings for
> > fsm.  I've run multi-day tests where autovacuum kept the size of the
> > database pretty much the same with 200+ updates a second going on.
> 
> Hi Scott,
> 
>    Could you explain the fsm a little more?  I have done _no_ tuning on
> my database simply because I don't know how, but I would be interested
> to hear your recommendations for the fsm settings.

OK, Here's the basics of what fsm does.  As you probably already know,
PostgreSQL uses an in store multiple versioning system, where the first
version of a tuple points to next newest version, and so on, until we
get to the last version.  So, if you update the same row four times,
you'll have five copies of it in the data storage files, the original,
plus four copies.

Each transaction that needs to access said tuple will grab the first
one, check the date stamp on it, and go on the the next one, until it
finds the last valid one for it, based on the rules of visibility.  We
won't get into that right now, because what's important is that we have
5 versions of that tuple in memory.  

Now, after all the currently processing transactions finish, all the new
transactions are going to be seeing the fifth version of the tuple from
then on.  But they'll all start by looking at the first one and working
forward one at a time.  

Along comes vacuum and vacuum full.  Vacuum full marks all these dead
tuples, then actually compresses the data file back down to save the
actual lost space.  Plain (or lazy) vacuums simply mark the rows as
free, without actually reclaiming them.  The free space map is what
vacuum uses to mark those tuples as free, and what the storage manager
checks to find free space to place new tuples in the tables.

So, the free space map needs to be big enough to hold a reference to
every single freed row from vacuuming, or the vacuumed tuple space will
not get reused, and the storage manager will simply append new tuples
onto the end of the data file.

You can find a bit more on this subject at:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html






view thread (18+ messages)  latest in thread

Message-ID: <1087516248.27839.28.camel@localhost.localdomain>
Permalink:  ../1087516248.27839.28.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1087516248.27839.28.camel@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smarlowe@qwest.net, benny@bennyvision.com
  Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
  In-Reply-To: <1087516248.27839.28.camel@localhost.localdomain>
  References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net> <15549.1087351669@sss.pgh.pa.us> <50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net> <15996.1087354312@sss.pgh.pa.us> <58063.63.227.74.41.1087355391.squirrel@webmail.stinkweasel.net> <1087363871.1582.20.camel@localhost.localdomain> <1185.134.244.169.17.1087396700.squirrel@webmail.stinkweasel.net> <opr9qaj9yp94ope3@olivier.amaze.ulb.ac.be> <1087486656.27839.2.camel@localhost.localdomain> <1854.134.244.169.17.1087487894.squirrel@webmail.stinkweasel.net> <1087516248.27839.28.camel@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox