Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL clustering VS MySQL clustering

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>,Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, marty(at)outputservices(dot)com,herve(at)elma(dot)fr, pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL clustering VS MySQL clustering
Date: 2005-01-25 03:11:04
Message-ID: 20050125031104.GW67721@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)decibel(dot)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?"

In response to

Responses

pgsql-performance by date

Next:From: John Arbash MeinelDate: 2005-01-25 03:15:28
Subject: Re: poor performance of db?
Previous:From: SpaceBallOneDate: 2005-01-25 02:31:10
Subject: Re: poor performance of db?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group