From: Jim C. Nasby <decibel@decibel.org>
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 21:11:04 -0600
Message-ID: <20050125031104.GW67721@decibel.org> (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>
On Sun, Jan 23, 2005 at 03:40:03PM -0500, Tom Lane wrote:
> 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.
To pull out the oft-used "show me the numbers" card... has anyone done a
study to see if keeping this stuff out of the 'critical path' actually
helps overall system performance? While the current scheme initially
speeds up transactions, eventually you have to run vacuum, which puts a
big load on the system. If you can put off vacuuming until off-hours
(assuming your system has off-hours), then this doesn't matter, but more
and more we're seeing systems where vacuum is a big performance issue
(hence recent work with the delay in vacuum so as not to swamp the IO
system).
If you vacuum as part of the transaction it's going to be more efficient
of resources, because you have more of what you need right there (ie:
odds are that you're on the same page as the old tuple). In cases like
that it very likely makes a lot of sense to take a small hit in your
transaction time up-front, instead of a larger hit doing a vacuum down
the road.
Of course, without numbers this is a bunch of hand-waving, but I don't
think it's valid to assume that minimizing the amount of work you do in
a transaction means better throughput without considering what it will
cost to do the work you're putting off until later.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
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: decibel@decibel.org, 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: <20050125031104.GW67721@decibel.org>
* 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