pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Guy Thornley <guy@esphion.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Tatsuo Ishii <t-ishii@sra.co.jp>
Cc: marty@outputservices.com
Cc: herve@elma.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL clustering VS MySQL clustering
Date: Mon, 24 Jan 2005 13:21:50 +1300
Message-ID: <20050124002150.GA19730@conker.esphion.com> (raw)
In-Reply-To: <6346.1106512803@sss.pgh.pa.us>
References: <41F144FE.30902@outputservices.com>
	<41F14758.4030602@outputservices.com>
	<20050122.121300.41631131.t-ishii@sra.co.jp>
	<25335.1106428223@sss.pgh.pa.us>
	<1106511352.31592.112.camel@localhost.localdomain>
	<6346.1106512803@sss.pgh.pa.us>

> The real issue with any such scheme is that you are putting maintenance
> costs into the critical paths of foreground processes that are executing
> user queries.  I think that one of the primary advantages of the
> Postgres storage design is that we keep that work outside the critical
> path and delegate it to maintenance processes that can run in the
> background.  We shouldn't lightly toss away that advantage.

As a rather naive user, I'd consider modifying the FSM so that it has pages
with 'possibly freeable' space on them, as well as those with free space.

This way when the pages of actually free space is depleted, the list of
'possibly freeable' pages could be vacuumed (as a batch for that relation)
then placed on the actually-free list like vacuum currently does

Since there is concern about critical path performance, there could be an
extra backend process that would wake up perodically (or on a signal) and
vacuum the pages, so theyre not processed inline with some transaction. Then
grabbing a page with free space is the same as it is currently.

Actually I was hoping to find some time to investigate this myself, but my
employer is keeping me busy with other tasks ;/. Our particular data
management problems could be mitigated much better with a data partitioning
approach, anyway.

On another note, is anybody investigating backing up the FSM with disk files
so when the FSM size exceeds memory allocated, the appropriate data is
swapped to disk? At least since 7.4 you no longer need a VACUUM when
postgres starts, to learn about free space ;)

- Guy Thornley



view thread (131+ messages)  latest in thread

Message-ID: <20050124002150.GA19730@conker.esphion.com>
Permalink:  ../20050124002150.GA19730@conker.esphion.com/
Also on:    postgresql.org/message-id/20050124002150.GA19730@conker.esphion.com

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: guy@esphion.com, tgl@sss.pgh.pa.us, simon@2ndquadrant.com, t-ishii@sra.co.jp, marty@outputservices.com, herve@elma.fr
  Subject: Re: PostgreSQL clustering VS MySQL clustering
  In-Reply-To: <20050124002150.GA19730@conker.esphion.com>

* 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