To: "p prince" <pprince127@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: how can a couple of expensive queries drag my system down?
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
Content-Transfer-Encoding: 7bit
Date: Wed, 26 Mar 2008 22:58:16 +0100
Message-ID: <op.t8m8neczcigqcu@apollo13.peufeu.com>
In-Reply-To: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
User-Agent: Opera Mail/9.24 (Linux)


> is this 'normal'? (loaded question I know)

	Depends. If you are on the edge, disk-wise, yes a big fat query can push  
it over and make it fall.

> Should I be looking to offload expensive reporting queries to read-only
> replicants of my database?

	You could do this, especially if the heavy queries involve reading  
gigabytes of data from disk (as reporting queries like to do). In that  
case, you can even use a cheap machine with cheap disks for the slave  
(even striped RAID) since data is duplicated anyway and all that matters  
is megabytes/second, not IOs/second.

> Is this a symptom of slow disk?

	vmstat will tell you this.
	If iowait time goes through the roof, yes it's disk bound.
	If cpu use goes 100%, then it's cpu bound.

> imporoperly tuned postgres settings? bad

	Also possible, you can try EXPLAIN of the problematic queries.

> choice of OS, hardware, storage?

	Depends on how your SAN handles load. No idea about that.

> Is this a sign of disk contention?

	Most probable.

> How does CPU load come into play?

	With 8 CPUs, less likely.
	(Your problem query can swamp at most 1 CPU, so if the machine grinds  
with still 7 other cores available for the usual, it probably isn't  
cpu-bound)