pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhow can a couple of expensive queries drag my system down?
7+ messages / 6 participants
[nested] [flat]
* how can a couple of expensive queries drag my system down?
@ 2008-03-26 19:48 p prince <pprince127@gmail.com>
2008-03-26 20:19 ` Re: how can a couple of expensive queries drag my system down? Alan Hodgson <ahodgson@simkin.ca>
2008-03-26 20:31 ` Re: how can a couple of expensive queries drag my system down? Scott Marlowe <scott.marlowe@gmail.com>
2008-03-26 21:58 ` Re: how can a couple of expensive queries drag my system down? PFC <lists@peufeu.com>
0 siblings, 3 replies; 7+ messages in thread
From: p prince @ 2008-03-26 19:48 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
@ 2008-03-26 20:19 ` Alan Hodgson <ahodgson@simkin.ca>
2 siblings, 0 replies; 7+ messages in thread
From: Alan Hodgson @ 2008-03-26 20:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
@ 2008-03-26 20:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-27 04:09 ` Re: how can a couple of expensive queries drag my system down? Shane Ambler <pgsql@Sheeky.Biz>
2008-03-27 15:13 ` Re: how can a couple of expensive queries drag my system down? Erik Jones <erik@myemma.com>
2 siblings, 2 replies; 7+ messages in thread
From: Scott Marlowe @ 2008-03-26 20:31 UTC (permalink / raw)
To: p prince <pprince127@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
2008-03-26 20:31 ` Re: how can a couple of expensive queries drag my system down? Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-03-27 04:09 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-03-27 04:57 ` Re: how can a couple of expensive queries drag my system down? Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Shane Ambler @ 2008-03-27 04:09 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: p prince <pprince127@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
2008-03-26 20:31 ` Re: how can a couple of expensive queries drag my system down? Scott Marlowe <scott.marlowe@gmail.com>
2008-03-27 04:09 ` Re: how can a couple of expensive queries drag my system down? Shane Ambler <pgsql@Sheeky.Biz>
@ 2008-03-27 04:57 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2008-03-27 04:57 UTC (permalink / raw)
To: Shane Ambler <pgsql@sheeky.biz>; +Cc: p prince <pprince127@gmail.com>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
2008-03-26 20:31 ` Re: how can a couple of expensive queries drag my system down? Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-03-27 15:13 ` Erik Jones <erik@myemma.com>
1 sibling, 0 replies; 7+ messages in thread
From: Erik Jones @ 2008-03-27 15:13 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: p prince <pprince127@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how can a couple of expensive queries drag my system down?
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
@ 2008-03-26 21:58 ` PFC <lists@peufeu.com>
2 siblings, 0 replies; 7+ messages in thread
From: PFC @ 2008-03-26 21:58 UTC (permalink / raw)
To: p prince <pprince127@gmail.com>; pgsql-performance
> 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)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-03-27 15:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-26 19:48 how can a couple of expensive queries drag my system down? p prince <pprince127@gmail.com>
2008-03-26 20:19 ` Alan Hodgson <ahodgson@simkin.ca>
2008-03-26 20:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-27 04:09 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-03-27 04:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-27 15:13 ` Erik Jones <erik@myemma.com>
2008-03-26 21:58 ` PFC <lists@peufeu.com>
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