pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery kills machine.
7+ messages / 5 participants
[nested] [flat]
* Query kills machine.
@ 2004-08-24 08:32 Stef <svb@ucs.co.za>
0 siblings, 1 reply; 7+ messages in thread
From: Stef @ 2004-08-24 08:32 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I've attached all the query in query.sql
I'm using postgres 7.3.4 on Linux version 2.4.26-custom
( /proc/sys/vm/overcommit_memory = 0 this time )
free :
total used free shared buffers cached
Mem: 1810212 1767384 42828 0 5604 1663908
-/+ buffers/cache: 97872 1712340
Swap: 505912 131304 374608
After I rebuilt the database, the query was fast (28255.12 msec).
After one night's insertion into the tables that the query select from,
the query all of a sudden uses up all resources , and the kernel
starts swapping, and I haven't seen the query actually finish when
this happens. I did vacuum analyze AND reindex, but that doesn't
help.
I attached the explain analyze of the query before this happens, and
the explain plan from when it actually happens that the query doesn't finish.
The one noticeable difference, was that before, it used merge joins, and
after, it used hash joins.
When the query was slow, I tried to : set enable_hashjoin to off
for this query, and the query finished relatively fast again (316245.16 msec)
I attached the output of that explain analyze as well, as well as the postgres
settings.
Can anyone shed some light on what's happening here. I can't figure it out.
Kind Regards
Stefan
Attachments:
query.sql (application/octet-stream, 2.5K) download
explain_analyze_before.txt (text/plain, 4.8K) download
Aggregate (cost=87597.84..89421.82 rows=2702 width=484) (actual time=22727.88..28164.74 rows=12040 loops=1)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) - sum(qty_out_goods)) < 0::numeric)
-> Group (cost=87597.84..88003.17 rows=27022 width=484) (actual time=22727.45..23242.01 rows=42705 loops=1)
-> Sort (cost=87597.84..87665.40 rows=27022 width=484) (actual time=22727.43..22756.74 rows=42705 loops=1)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code, br.cluster_descr
-> Merge Join (cost=84388.96..85608.78 rows=27022 width=484) (actual time=20303.41..21814.25 rows=42705 loops=1)
Merge Cond: (("outer".group_code = "inner".group_code) AND ("outer".sku = "inner".sku))
-> Sort (cost=64472.34..64489.67 rows=6930 width=388) (actual time=16503.56..16530.23 rows=42705 loops=1)
Sort Key: s.group_code, os.sku
-> Merge Join (cost=63006.13..64030.25 rows=6930 width=388) (actual time=14394.48..15794.71 rows=42705 loops=1)
Merge Cond: (("outer".cluster_brn = "inner".cluster_code) AND ("outer".sku = "inner".sku))
-> Index Scan using old_sku_uidx1 on old_sku os (cost=0.00..797.79 rows=17799 width=64) (actual time=0.02..47.66 rows=17799 loops=1)
-> Sort (cost=63006.13..63045.07 rows=15574 width=324) (actual time=14393.77..14556.50 rows=132703 loops=1)
Sort Key: br.cluster_code, s.sku
-> Merge Join (cost=61645.75..61921.64 rows=15574 width=324) (actual time=4862.56..6078.94 rows=132703 loops=1)
Merge Cond: ("outer".brn_code = "inner".brn_code)
-> Sort (cost=61587.79..61626.73 rows=15574 width=228) (actual time=4859.23..5043.43 rows=132703 loops=1)
Sort Key: s.brn_code
-> Index Scan using stmst_sku_idx4 on stmst_sku s (cost=0.00..60503.30 rows=15574 width=228) (actual time=0.07..1078.30 rows=132703 loops=1)
Index Cond: (fpp_code = '200408'::text)
-> Sort (cost=57.96..59.62 rows=667 width=96) (actual time=3.26..91.93 rows=133005 loops=1)
Sort Key: br.brn_code
-> Seq Scan on master_branch_descr br (cost=0.00..26.67 rows=667 width=96) (actual time=0.02..1.13 rows=667 loops=1)
-> Sort (cost=19916.61..20306.53 rows=155968 width=96) (actual time=3797.71..3914.26 rows=184223 loops=1)
Sort Key: i.group_code, i.sku
-> Seq Scan on master_sku_descr i (cost=0.00..6463.68 rows=155968 width=96) (actual time=0.01..293.74 rows=155968 loops=1)
SubPlan
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1 loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=14456)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1 loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=14456)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=12031)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=12031)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=11.96..11.96 rows=1 width=82) (actual time=0.16..0.16 rows=1 loops=14456)
-> Nested Loop (cost=0.00..11.95 rows=1 width=82) (actual time=0.08..0.15 rows=3 loops=14456)
-> Index Scan using stmst_sku_idx3 on stmst_sku k (cost=0.00..6.02 rows=1 width=50) (actual time=0.06..0.08 rows=3 loops=14456)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1) AND (stktype_code = $2))
-> Index Scan using master_branch_descr_pkey on master_branch_descr b (cost=0.00..5.92 rows=1 width=32) (actual time=0.02..0.02 rows=1 loops=42705)
Index Cond: ("outer".brn_code = b.brn_code)
Filter: ((brn_code <> cluster_code) AND (cluster_code = $0))
Total runtime: 28255.12 msec
(44 rows)
explain_after.txt (text/plain, 3.5K) download
Aggregate (cost=316107.28..316114.91 rows=11 width=322)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) - sum(qty_out_goods)) < 0::numeric)
-> Group (cost=316107.28..316108.98 rows=113 width=322)
-> Sort (cost=316107.28..316107.57 rows=113 width=322)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code, br.cluster_descr
-> Hash Join (cost=308622.49..316103.43 rows=113 width=322)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: ("inner".group_code = "outer".group_code)
-> Seq Scan on master_sku_descr i (cost=0.00..6467.08 rows=156008 width=96)
-> Hash (cost=308618.04..308618.04 rows=1781 width=226)
-> Hash Join (cost=1698.78..308618.04 rows=1781 width=226)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: ("outer".brn_code = "inner".brn_code)
-> Seq Scan on stmst_sku s (cost=0.00..284677.69 rows=356150 width=106)
Filter: (fpp_code = '200408'::text)
-> Hash (cost=1550.41..1550.41 rows=59347 width=120)
-> Merge Join (cost=57.89..1550.41 rows=59347 width=120)
Merge Cond: ("outer".cluster_brn = "inner".cluster_code)
-> Index Scan using old_sku_uidx1 on old_sku os (cost=0.00..702.79 rows=17822 width=24)
-> Sort (cost=57.89..59.56 rows=666 width=96)
Sort Key: br.cluster_code
-> Seq Scan on master_branch_descr br (cost=0.00..26.66 rows=666 width=96)
SubPlan
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=32.16..32.16 rows=1 width=50)
-> Merge Join (cost=32.11..32.15 rows=1 width=50)
Merge Cond: ("outer".brn_code = "inner".brn_code)
-> Sort (cost=14.38..14.39 rows=3 width=32)
Sort Key: b.brn_code
-> Index Scan using master_branch_descr_idx6 on master_branch_descr b (cost=0.00..14.35 rows=3 width=32)
Index Cond: (cluster_code = $0)
Filter: (brn_code <> cluster_code)
-> Sort (cost=17.73..17.74 rows=4 width=18)
Sort Key: k.brn_code
-> Index Scan using stmst_sku_idx3 on stmst_sku k (cost=0.00..17.69 rows=4 width=18)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1) AND (stktype_code = $2))
(44 rows)
explain_analyze_after_hashjoin_off.txt (text/plain, 5.0K) download
Aggregate (cost=354420.02..354427.65 rows=11 width=322) (actual time=246825.06..316125.53 rows=12050 loops=1)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) - sum(qty_out_goods)) < 0::numeric)
-> Group (cost=354420.02..354421.72 rows=113 width=322) (actual time=246701.96..247283.96 rows=42930 loops=1)
-> Sort (cost=354420.02..354420.30 rows=113 width=322) (actual time=246701.94..246736.97 rows=42930 loops=1)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code, br.cluster_descr
-> Merge Join (cost=354298.16..354416.17 rows=113 width=322) (actual time=245205.55..245634.53 rows=42930 loops=1)
Merge Cond: (("outer".brn_code = "inner".brn_code) AND ("outer".cluster_brn = "inner".cluster_code))
-> Sort (cost=354240.27..354296.76 rows=22595 width=226) (actual time=245179.07..245205.75 rows=42930 loops=1)
Sort Key: s.brn_code, os.cluster_brn
-> Merge Join (cost=348924.92..352606.20 rows=22595 width=226) (actual time=241551.96..244357.21 rows=42930 loops=1)
Merge Cond: ("outer".sku = "inner".sku)
-> Merge Join (cost=348924.92..351824.65 rows=22595 width=202) (actual time=241500.21..243397.42 rows=132883 loops=1)
Merge Cond: (("outer".sku = "inner".sku) AND ("outer".group_code = "inner".group_code))
-> Sort (cost=329001.17..329891.54 rows=356150 width=106) (actual time=238575.25..238742.16 rows=132883 loops=1)
Sort Key: s.sku, s.group_code
-> Seq Scan on stmst_sku s (cost=0.00..284677.69 rows=356150 width=106) (actual time=219190.28..235315.66 rows=132883 loops=1)
Filter: (fpp_code = '200408'::text)
-> Sort (cost=19923.75..20313.77 rows=156008 width=96) (actual time=2924.57..3048.34 rows=185259 loops=1)
Sort Key: i.sku, i.group_code
-> Seq Scan on master_sku_descr i (cost=0.00..6467.08 rows=156008 width=96) (actual time=1.62..976.63 rows=156008 loops=1)
-> Index Scan using old_sku_idx1 on old_sku os (cost=0.00..398.04 rows=17822 width=24) (actual time=49.45..209.45 rows=46294 loops=1)
-> Sort (cost=57.89..59.56 rows=666 width=96) (actual time=26.41..54.70 rows=43232 loops=1)
Sort Key: br.brn_code, br.cluster_code
-> Seq Scan on master_branch_descr br (cost=0.00..26.66 rows=666 width=96) (actual time=8.04..24.21 rows=666 loops=1)
SubPlan
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.06..0.06 rows=1 loops=14481)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8) (actual time=0.04..0.05 rows=0 loops=14481)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.04..0.04 rows=1 loops=14481)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8) (actual time=0.03..0.03 rows=0 loops=14481)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=12042)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding (cost=0.00..23.70 rows=6 width=8) (actual time=0.03..0.03 rows=0 loops=12042)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code = $2))
-> Aggregate (cost=32.16..32.16 rows=1 width=50) (actual time=4.53..4.53 rows=1 loops=14481)
-> Merge Join (cost=32.11..32.15 rows=1 width=50) (actual time=4.45..4.52 rows=3 loops=14481)
Merge Cond: ("outer".brn_code = "inner".brn_code)
-> Sort (cost=14.38..14.39 rows=3 width=32) (actual time=3.84..4.01 rows=271 loops=14481)
Sort Key: b.brn_code
-> Index Scan using master_branch_descr_idx6 on master_branch_descr b (cost=0.00..14.35 rows=3 width=32) (actual time=0.02..2.14 rows=564 loops=14481)
Index Cond: (cluster_code = $0)
Filter: (brn_code <> cluster_code)
-> Sort (cost=17.73..17.74 rows=4 width=18) (actual time=0.14..0.14 rows=3 loops=14481)
Sort Key: k.brn_code
-> Index Scan using stmst_sku_idx3 on stmst_sku k (cost=0.00..17.69 rows=4 width=18) (actual time=0.08..0.11 rows=3 loops=14481)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1) AND (stktype_code = $2))
Total runtime: 316245.16 msec
(47 rows)
settings.txt (text/plain, 174B) download
shared_buffers = 110592
wal_buffers = 400
sort_mem = 30720
vacuum_mem = 10240
checkpoint_segments = 30
commit_delay = 5000
commit_siblings = 100
effective_cache_size = 201413
stmst_sku.sql (application/octet-stream, 4.0K) download
old_sku.sql (application/octet-stream, 263B) download
master_sku_descr.sql (application/octet-stream, 1.4K) download
master_branch_descr.sql (application/octet-stream, 2.1K) download
gir_outstanding.sql (application/octet-stream, 1.5K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-24 09:08 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Stef <svb@ucs.co.za>
0 siblings, 1 reply; 7+ messages in thread
From: Christopher Kings-Lynne @ 2004-08-24 09:08 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
For starters,
> ------------------------------------------------------------------------
>
> shared_buffers = 110592
> wal_buffers = 400
> sort_mem = 30720
> vacuum_mem = 10240
> checkpoint_segments = 30
> commit_delay = 5000
> commit_siblings = 100
> effective_cache_size = 201413
Try more like this:
shared_buffers = 30000
wal_buffers = <default>
sort_mem = 4096
vacuum_mem = 10240
checkpoint_segments = 30
commit_delay = <default>
commit_siblings = <default>
effective_cache_size = 100000
Chris
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-24 13:30 Stef <svb@ucs.co.za>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 7+ messages in thread
From: Stef @ 2004-08-24 13:30 UTC (permalink / raw)
To: pgsql-performance
Christopher Kings-Lynne mentioned :
=> sort_mem = 4096
Reducing sort_mem to 4096 seems to make it run in a reasonable time
again. Any idea why? The database does a whole lot of huge sorts
every day, so I thought upping this parameter would help.
A couple of queries do seem to run slower now that I reduced
the sort_mem.
The shared buffers still makes a significant difference when I increase it.
Kind Regards
Stefan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-24 14:08 Magnus Naeslund(t) <mag@fbab.net>
parent: Stef <svb@ucs.co.za>
2 siblings, 0 replies; 7+ messages in thread
From: Magnus Naeslund(t) @ 2004-08-24 14:08 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef wrote:
> Christopher Kings-Lynne mentioned :
> => sort_mem = 4096
>
> Reducing sort_mem to 4096 seems to make it run in a reasonable time
> again. Any idea why? The database does a whole lot of huge sorts
> every day, so I thought upping this parameter would help.
>
> A couple of queries do seem to run slower now that I reduced
> the sort_mem.
>
> The shared buffers still makes a significant difference when I increase it.
>
Well you have to take in account that sort_mem is not the total memory
allocated for sorting but per connection and in complex expressions
serveral times that too.
So if you sort a lot it can push your operating system off the cliff and
it might start reaping things that shouldn't be reaped and start swapping.
If that happens _everything_ on that box will get slow...
Shared buffers on the other hand is only allocated once.
Regards,
Magnus
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-24 14:15 Magnus Naeslund(pg) <mag-pg@fbab.net>
parent: Stef <svb@ucs.co.za>
2 siblings, 0 replies; 7+ messages in thread
From: Magnus Naeslund(pg) @ 2004-08-24 14:15 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef wrote:
> Christopher Kings-Lynne mentioned :
> => sort_mem = 4096
>
> Reducing sort_mem to 4096 seems to make it run in a reasonable time
> again. Any idea why? The database does a whole lot of huge sorts
> every day, so I thought upping this parameter would help.
>
> A couple of queries do seem to run slower now that I reduced
> the sort_mem.
> The shared buffers still makes a significant difference when I
increase it.
>
Well you have to take in account that sort_mem is not the total memory
allocated for sorting but per connection and in complex expressions
serveral times that too.
So if you sort a lot it can push your operating system off the cliff and
it might start reaping things that shouldn't be reaped and start swapping.
If that happens _everything_ on that box will get slow...
Shared buffers on the other hand is only allocated once.
Regards,
Magnus
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-24 15:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stef <svb@ucs.co.za>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2004-08-24 15:23 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef <svb@ucs.co.za> writes:
> Reducing sort_mem to 4096 seems to make it run in a reasonable time
> again. Any idea why? The database does a whole lot of huge sorts
> every day, so I thought upping this parameter would help.
Not if you haven't got the RAM to support it :-(
Another thing you might look at is ANALYZEing the tables again after
you've loaded all the new data. The row-count estimates seem way off
in these plans. You might need to increase the statistics target,
too, to get better plans.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kills machine.
@ 2004-08-25 14:30 Stef <svb@ucs.co.za>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Stef @ 2004-08-25 14:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane mentioned :
=> Not if you haven't got the RAM to support it :-(
=>
=> Another thing you might look at is ANALYZEing the tables again after
=> you've loaded all the new data. The row-count estimates seem way off
=> in these plans. You might need to increase the statistics target,
=> too, to get better plans.
Thanks Tom, Christopher and Magnus!
I tested this, and found the correct sort_mem setting for my situation.
I'm testing a new default_statistics_target setting.
This is something I never considered.
Kind Regards
Stefan
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-08-25 14:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-24 08:32 Query kills machine. Stef <svb@ucs.co.za>
2004-08-24 09:08 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-08-24 13:30 ` Stef <svb@ucs.co.za>
2004-08-24 14:08 ` Magnus Naeslund(t) <mag@fbab.net>
2004-08-24 14:15 ` Magnus Naeslund(pg) <mag-pg@fbab.net>
2004-08-24 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-25 14:30 ` Stef <svb@ucs.co.za>
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