pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres function use makes machine crash.
3+ messages / 2 participants
[nested] [flat]

* Postgres function use makes machine crash.
@ 2004-06-07 11:34  Stef <svb@ucs.co.za>
  0 siblings, 1 reply; 3+ messages in thread

From: Stef @ 2004-06-07 11:34 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

I've been dealing with a problem for the past two days
where a certain sql statement works 2 out of 5 times, and
the other 3 times, it causes the machine (quad Xeon 2.8GHz
+ 792543232 bytes mem, linux kernel  2.4.26-custom, pg ver 7.3.4)
to slow down, and finally grind to a halt. It looks like postgres
gets itself into an insane loop, because no matter how much
shared memory I give it, it uses it all, and then
the kernel starts swapping. 

I'm pretty sure it's not the kernel, because I've tried four different 
2.4.2* stable kernels, and the same happens.

I've attached the query, and the functions used inside the query,
as well as the table structure and an explain. (I haven't been
able to get explain analyze)

It seems that when I replace the functions used in the query,
with the actual values returned by them (one date in each case),
the query runs in 10 seconds.

I did vacuum analyze, and reindex seemed to  work at one
stage, but now it doesn't anymore. 

Is there some limitation in using functions that I do not know 
about, or is it a bug? 

(It seems to be hanging on the max_fpp('''')
function call from inside the fpp_max_ms() function.)

Please help.

Kind Regards
Stefan

Attachments:

  query.sql (application/octet-stream, 1.3K) download

explain.txt (text/plain, 2.4K) download =# EXPLAIN SELECT group_code::text AS group_code, -# sku::text AS sku, -# stktype_code::varchar(2) AS stktype_code, -# brn_code::text AS brn_code, -# SUM(overdue)::int4 AS overdue, -# SUM(current)::int4 AS current, -# SUM(future)::int4 AS future -# FROM ( (# SELECT group_code, (# sku, (# stktype_code, (# brn_code, (# CASE WHEN to_date <= max_fpp_ms() THEN (# SUM(out_qty) (# ELSE 0 (# END AS overdue, (# CASE WHEN to_date > max_fpp_ms() (# AND to_date <= max_fpp_me() THEN (# SUM(out_qty) (# ELSE 0 (# END AS current, (# CASE WHEN to_date > max_fpp_me() THEN (# SUM(out_qty) (# ELSE 0 (# END AS future (# FROM gir_outstanding (# GROUP BY group_code, (# sku, (# stktype_code, (# brn_code, (# to_date (# ) AS sub -# GROUP BY group_code, -# sku, -# stktype_code, -# brn_code -# ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Aggregate (cost=15880.41..16055.62 rows=876 width=44) -> Group (cost=15880.41..15989.92 rows=8761 width=44) -> Sort (cost=15880.41..15902.31 rows=8761 width=44) Sort Key: group_code, sku, stktype_code, brn_code -> Subquery Scan sub (cost=13335.57..15306.72 rows=8761 width=44) -> Aggregate (cost=13335.57..15306.72 rows=8761 width=44) -> Group (cost=13335.57..14649.67 rows=87607 width=44) -> Sort (cost=13335.57..13554.58 rows=87607 width=44) Sort Key: group_code, sku, stktype_code, brn_code, to_date -> Seq Scan on gir_outstanding (cost=0.00..4687.07 rows=87607 width=44) (10 rows)
functions.sql (application/octet-stream, 546B) download
structures.txt (text/plain, 2.7K) download =# \d gir_outstanding Table "public.gir_outstanding" Column | Type | Modifiers ----------------+-----------------------------+----------- supp_code | text | supp_name | text | supp_brn | text | ord_no | text | due_date | timestamp without time zone | to_date | timestamp without time zone | group_code | text | brn_code | text | desc_short | text | cluster_brn | text | country_code | text | req_doc_no | integer | ops_code | text | sku | text | std_descr | text | acde_code | text | req_qty | double precision | grv_qty | double precision | skul_qty | double precision | pref_date | timestamp without time zone | skul_grv_qty | double precision | out_qty | double precision | skul_ord_cost | numeric(16,2) | out_cost | numeric | stktype_code | character varying(2) | gir_type_code | text | gir_type_descr | text | Indexes: gir_oustanding_idx1 btree (cluster_brn, sku, stktype_code) =# select count(1) from gir_outstanding; count ------- 87607 (1 row) =# \d fpp Table "public.fpp" Column | Type | Modifiers ---------+-----------------------------+----------- code | text | not null descr | text | me_dte | timestamp without time zone | cal_dte | timestamp without time zone | gl_per | integer | Indexes: fpp_pkey primary key btree (code), fpp_uidx1 unique btree (code) =# select count(1) from fpp; count ------- 92 =# \d br_prof Table "public.br_prof" Column | Type | Modifiers --------------------+-----------------------------+----------- br_cde | text | not null bprof_opened | timestamp without time zone | fpp_cde | text | bprof_avg_del_time | integer | bprof_auto_err_log | boolean | Indexes: br_prof_pkey primary key btree (br_cde) =# select count(1) from br_prof; count ------- 1 (1 row)
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Postgres function use makes machine crash.
@ 2004-06-07 15:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Stef <svb@ucs.co.za>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2004-06-07 15:11 UTC (permalink / raw)
  To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance

Stef <svb@ucs.co.za> writes:
> I've been dealing with a problem for the past two days
> where a certain sql statement works 2 out of 5 times, and
> the other 3 times, it causes the machine (quad Xeon 2.8GHz
> + 792543232 bytes mem, linux kernel  2.4.26-custom, pg ver 7.3.4)
> to slow down, and finally grind to a halt.

IIRC, PG prior to 7.4 had some problems with memory leaks in repeated
execution of SQL-language functions ... and your query sure looks like
it's going to be doing a lot of repeated execution of those functions.

Please try it on 7.4.2 and see if you still have a problem.

It seems somewhat interesting that you see the problem only sometimes
and not every time, but there's not much point in investigating further
if it turns out the problem is already fixed.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Postgres function use makes machine crash.
@ 2004-06-07 15:34  Stef <svb@ucs.co.za>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Stef @ 2004-06-07 15:34 UTC (permalink / raw)
  To: pgsql-performance

Tom Lane mentioned :
=> Please try it on 7.4.2 and see if you still have a problem.

Will do, and I'll post the results

Thanks!



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-06-07 15:34 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-07 11:34 Postgres function use makes machine crash. Stef <svb@ucs.co.za>
2004-06-07 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 15:34   ` 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