From: Tom Lane <tgl@sss.pgh.pa.us>
To: David B <davidb999@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question
Date: Thu, 17 Mar 2005 15:33:30 -0500
Message-ID: <27132.1111091610@sss.pgh.pa.us> (raw)
In-Reply-To: <c76ea21c05031712092663f1e8@mail.gmail.com>
References: <c76ea21c05031712092663f1e8@mail.gmail.com>
David B <davidb999@gmail.com> writes:
> I'm looking at PG vs MySql for a high volume site.
> Few 10's of millions inserts per day.
> Also some deletes (1% - say 250,000 per day).
> Also updates (perhaps 10% say 2.5m per day)
> Lots of indexes on master table.
> When I test vacuum it seems very slow.
> I understand I need to do vacuums on a regular basis.
> I'm thinking with that much activity I should do it nightly
At least. But you are missing the point of vacuum being "slow". It's a
background activity and you needn't care how long it takes, as long as
it doesn't put too much load on the system. In that light, you probably
want to play around with the vacuum cost delay settings to find
something that doesn't hurt response time too much while it's running.
See
http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-RESOURCE
section 16.4.3.4.
> How does vacuum work in a replication environment.
You still have to vacuum on every machine.
> 15minute lock is a long time.
There's no lock, unless you are using VACUUM FULL which you shouldn't.
> Does Mysql have a similar issue. Does it use something like 'vacuum'.
AFAIK they don't have vacuum, but mysql doesn't have a great reputation
for being fast in concurrent-write scenarios, so you're probably not
going to find it to be the winner for your problem.
> Slony appears very active group. Which can be a good thing or mean its
> still a work in progress!!
Slony-I is production grade now, Slony-II is just starting ...
regards, tom lane
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: tgl@sss.pgh.pa.us, davidb999@gmail.com
Subject: Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question
In-Reply-To: <27132.1111091610@sss.pgh.pa.us>
References: <c76ea21c05031712092663f1e8@mail.gmail.com> <27132.1111091610@sss.pgh.pa.us>
* 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