From: PFC <lists@peufeu.com>
To: p prince <pprince127@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: how can a couple of expensive queries drag my system down?
Date: Wed, 26 Mar 2008 22:58:16 +0100
Message-ID: <op.t8m8neczcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
References: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
> 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)
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: lists@peufeu.com, pprince127@gmail.com
Subject: Re: how can a couple of expensive queries drag my system down?
In-Reply-To: <op.t8m8neczcigqcu@apollo13.peufeu.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