X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F61656448 for ; Thu, 17 Mar 2005 20:33:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60634-07 for ; Thu, 17 Mar 2005 20:33:33 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id BA4C9565A9 for ; Thu, 17 Mar 2005 20:33:32 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2HKXUDD027134; Thu, 17 Mar 2005 15:33:30 -0500 (EST) To: David B Cc: pgsql-admin@postgresql.org Subject: Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question In-reply-to: References: Comments: In-reply-to David B message dated "Thu, 17 Mar 2005 12:09:02 -0800" Date: Thu, 17 Mar 2005 15:33:30 -0500 Message-ID: <27132.1111091610@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/262 X-Sequence-Number: 16760 David B 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