Re: Postgres for a "data warehouse", 5-10 TB

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