DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=H+ewT2i0s0JSkoYplTdjM5t2f3t0/cUfPwbnRaSGA5VDNqJRGRM6YG0M0/qho83D03nG5s2r/tcCumI5ZOXxYOi7crBMo9xxm0492n2lKEpPaAWG/jTvKCIfZGBuUlKJIGWrAo5loAsW6dPu0aKYrexb2gorbajNUPmfEoqLt6Y=
Message-ID: <dcc563d10803262157i2cd27bd2pe997a567997e2dad@mail.gmail.com>
Date: Wed, 26 Mar 2008 22:57:11 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Shane Ambler" <pgsql@sheeky.biz>
Subject: Re: how can a couple of expensive queries drag my system down?
Cc: "p prince" <pprince127@gmail.com>, pgsql-performance@postgresql.org
In-Reply-To: <47EB1DE9.5010507@Sheeky.Biz>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
	<dcc563d10803261331g6c35f964se6bce1720ebd8209@mail.gmail.com>
	<47EB1DE9.5010507@Sheeky.Biz>

On Wed, Mar 26, 2008 at 10:09 PM, Shane Ambler <pgsql@sheeky.biz> wrote:
> Scott Marlowe wrote:
>  > On Wed, Mar 26, 2008 at 1:48 PM, p prince <pprince127@gmail.com> wrote:
>  >> is this 'normal'? (loaded question I know)
>  >> Should I be looking to offload expensive reporting queries to read-only
>  >> replicants of my database?
>  >
>  > Yes, definitely look into setting up something like a slony slave
>  > that's used for reporting queries.  The nice thing about this setup is
>  > you only need to replicate the tables you run reports against.
>  >
>
>  I would look at fixing the slow queries so that they aren't a problem first.

I'm not sure you're reading the same thread as me.  Or something.
I've had reporting queries that took the better part of an hour to
run, and this was completely normal.  When you're running millions of
rows against each other for reporting queries it's not unusual to blow
out the cache.

Maybe the queries are inefficient, and maybe they're not.   But one
should not be running reporting queries on a live transactional
database.


>
>  I'm sure if you send in your queries and table defs you can get some
>  useful feedback here.
>
>  If there is no way of improving them then look at a reporting slave.
>
>
>  --
>
>  Shane Ambler
>  pgSQL (at) Sheeky (dot) Biz
>
>  Get Sheeky @ http://Sheeky.Biz
>