Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question

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