From: Shaun Thomas <sthomas@peak6.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Marti Raudsepp <marti@juffo.org>
Cc: Andy Colson <andy@squeakycode.net>
Cc: Igor Chudov <ichudov@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres for a "data warehouse", 5-10 TB
Date: Mon, 12 Sep 2011 15:04:40 -0500
Message-ID: <4E6E65D8.4050403@peak6.com> (raw)
In-Reply-To: <CAOR=d=2LG26r0NH-+wn7FhTnsR1G1r8gveN9YWDfZW9Z+3dvsQ@mail.gmail.com>
References: <CAMhtkAbK7saRO_Tayb1nS3YLWN=Wns=Ci47qs-3YbX0T2+hkpA@mail.gmail.com>
<CAOR=d=0zmw-EGRJyYmwCTxR5g6osbFLS5i-3kzjG05j7S=1JaA@mail.gmail.com>
<CAMhtkAah2c4XfSec=OtgL1V51wpD=jygbZnBVBRYCV02MscebQ@mail.gmail.com>
<4E6CC458.4010206@squeakycode.net>
<CABRT9RB-etP-K-0u0y-Wv+BysSd9ToZm71BqcY7hFQZC2tmjYQ@mail.gmail.com>
<4E6E31DF.6020407@peak6.com>
<CAOR=d=2LG26r0NH-+wn7FhTnsR1G1r8gveN9YWDfZW9Z+3dvsQ@mail.gmail.com>
On 09/12/2011 02:48 PM, Scott Marlowe wrote:
> I put it to you that your hardware has problems if you have a pg db
> that's corrupting from having too much vacuum activity.
What? No. We optimized by basically forcing autovacuum to never run
during our active periods. We never actually encountered wrap-around
corruption. I was just saying that 600M is a relatively high setting for
autovacuum_freeze_max_age. :)
I was alluding to the fact that if a DBA had his system running for a
week at our transaction level, and PG didn't have forced auto vacuum,
and their maintenance lapsed even slightly, they could end up with a
corrupt database. Not too far-fetched for someone coming from MySQL, really.
Our problem is we run a financial site, and the front-end very
aggressively monitors network and database timeouts. The limit is
sufficiently low that a vacuum would cause enough IO to trigger
application timeouts, even with vacuum_cost_delay. And of course,
setting vacuum_cost_delay too high quickly triples or quadruples vacuum
times. Now that we're using FusionIO cards, I've been thinking about
turning autovacuum back on, but I want to run some tests first.
My point stands, though. Don't go crazy with transactions until you know
your config can stand up to it, and reduce if possible. We found some
tweak points that drastically reduced transaction count with no
detrimental effect on the app itself, so we jumped on them.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email
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: sthomas@peak6.com, scott.marlowe@gmail.com, marti@juffo.org, andy@squeakycode.net, ichudov@gmail.com
Subject: Re: Postgres for a "data warehouse", 5-10 TB
In-Reply-To: <4E6E65D8.4050403@peak6.com>
* 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