Обсуждение: how can a couple of expensive queries drag my system down?

I'm not a DBA....but I play one at my office.
I also have a hand in system administration, development and stairwell sweeping.
Small shop...many hats per person.

We have a postgresql (v8.2.X) database with about 75 gigabytes of data.....almost half of it is represented by audit tables (changes made to the other tables).
It's running on a 8-cpu Sun box with 32 gig of ram (no other processes actively run on the database server)
The database itself resides on a Pillar SAN (Axiom) and is ZFS mounted to the database box.
We have upwards of 3000 active users hitting the system (via web/app servers) to the tune of (at peak times) of about 75-100 database transactions per second (many inserts/updates but just as many reads)
We have a couple of un-tuned queries that can be kicked off that can take multiple minutes to run.....(specifically ones that rummage through that audit data)

The other day, somebody kicked off 4 of these bad boys and other non-related transactions started taking much longer....inserts, updates, selects...all much longer than normal......(there was no table/row locking issue that we could locate).  propagated to the point where the system was nearly useless...the load average jumped up to almost 2.0 (normally hovers around .5) and all these queries were just taking too long...users started timing out...calls started....etc....

Today...a single expensive query brought the load average up to nearly 2  and started slowing down other transactions........

is this 'normal'? (loaded question I know)
Should I be looking to offload expensive reporting queries to read-only replicants of my database?
Is this a symptom of slow disk? imporoperly tuned postgres settings? bad choice of OS, hardware, storage?
Is this a sign of disk contention?
How does CPU load come into play?

Any thoughts would be helpful.....

Prince

On Wednesday 26 March 2008, "p prince" <pprince127@gmail.com> wrote:
> Is this a sign of disk contention?

Yes.

> How does CPU load come into play?

Processes waiting for disk I/O generally show up as load.

--
Alan

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.

> 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)

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 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

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
>

On Mar 26, 2008, at 3:31 PM, 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.

For simple two-node (i.e. no cascaded replication) I'd suggest looking
into Londiste.  It's loads easier to wrap your head around and it's
extremely easy to add/remove tables from replication as it doesn't
deal with "table sets" like Slony does.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com