Query killed with Out of memory
Hello,
With a heavy query, when line number results raise over 600k query hangs with out of memory.
Here is the explain analyze:
CTE Scan on lista (cost=25066.66..47721.23 rows=3678 width=260)
CTE lista
-> Unique (cost=24956.32..25066.66 rows=3678 width=512)
-> Sort (cost=24956.32..24965.52 rows=3678 width=512)
Sort Key: "*SELECT* 1".id, "*SELECT* 1".data_log, "*SELECT* 1".type_log, "*SELECT* 1".ip, "*SELECT* 1".log_id, "*SELECT* 1".url_dominio, "*SELECT* 1".porta, "*SELECT* 1".action_bind, "*SELECT* 1".action, ('DNS_DENIED/403'
::text), "*SELECT* 1".array_dominio
-> Append (cost=905.76..24738.50 rows=3678 width=512)
-> Subquery Scan "*SELECT* 1" (cost=905.76..12423.64 rows=3652 width=512)
-> Hash Anti Join (cost=905.76..12387.12 rows=3652 width=512)
Hash Cond: (((public.webtraffic_bind.ip)::text = (wb.ip)::text) AND ((public.webtraffic_bind.log_id)::text = (wb.log_id)::text) AND (public.webtraffic_bind.url_dominio = wb.url_dominio) AND ((public.webt
raffic_bind.porta)::text = (wb.porta)::text))
Join Filter: ((wb.data_log >= public.webtraffic_bind.data_log) AND (wb.data_log < (public.webtraffic_bind.data_log + '00:02:00'::interval)))
-> Bitmap Heap Scan on webtraffic_bind (cost=269.23..11638.68 rows=3678 width=512)
Recheck Cond: ((type_log)::text = 'queries'::text)
-> Bitmap Index Scan on type_log_wbidx (cost=0.00..268.31 rows=3678 width=0)
Index Cond: ((type_log)::text = 'queries'::text)
-> Hash (cost=636.17..636.17 rows=18 width=274)
-> Bitmap Heap Scan on webtraffic_bind wb (cost=564.94..636.17 rows=18 width=274)
Recheck Cond: (((type_log)::text = 'security'::text) AND ((action_bind)::text = 'approved'::text))
-> BitmapAnd (cost=564.94..564.94 rows=18 width=0)
-> Bitmap Index Scan on type_log_wbidx (cost=0.00..268.31 rows=3678 width=0)
Index Cond: ((type_log)::text = 'security'::text)
-> Bitmap Index Scan on action_bind_wbidx (cost=0.00..296.37 rows=3678 width=0)
Index Cond: ((action_bind)::text = 'approved'::text)
-> Subquery Scan "*SELECT* 2" (cost=905.76..12314.86 rows=26 width=512)
-> Hash Semi Join (cost=905.76..12314.60 rows=26 width=512)
Hash Cond: (((public.webtraffic_bind.ip)::text = (wb.ip)::text) AND ((public.webtraffic_bind.log_id)::text = (wb.log_id)::text) AND (public.webtraffic_bind.url_dominio = wb.url_dominio) AND ((public.webt
raffic_bind.porta)::text = (wb.porta)::text))
Join Filter: ((wb.data_log >= public.webtraffic_bind.data_log) AND (wb.data_log < (public.webtraffic_bind.data_log + '00:02:00'::interval)))
-> Bitmap Heap Scan on webtraffic_bind (cost=269.23..11638.68 rows=3678 width=512)
Recheck Cond: ((type_log)::text = 'queries'::text)
-> Bitmap Index Scan on type_log_wbidx (cost=0.00..268.31 rows=3678 width=0)
Index Cond: ((type_log)::text = 'queries'::text)
-> Hash (cost=636.17..636.17 rows=18 width=274)
-> Bitmap Heap Scan on webtraffic_bind wb (cost=564.94..636.17 rows=18 width=274)
Recheck Cond: (((type_log)::text = 'security'::text) AND ((action_bind)::text = 'approved'::text))
-> BitmapAnd (cost=564.94..564.94 rows=18 width=0)
-> Bitmap Index Scan on type_log_wbidx (cost=0.00..268.31 rows=3678 width=0)
Index Cond: ((type_log)::text = 'security'::text)
-> Bitmap Index Scan on action_bind_wbidx (cost=0.00..296.37 rows=3678 width=0)
Index Cond: ((action_bind)::text = 'approved'::text)
SubPlan 2
-> Index Scan using stpestensioni_domini_idx on stpestensioni_domini (cost=0.01..12.18 rows=2 width=0)
Index Cond: ((estensione)::text = ((($1)[(array_length($1, 1) - 1)] || '.'::text) || ($1)[array_length($1, 1)]))
SubPla
--------
Work_mem is.512mb, shared buffers 3084mb and system Ram 10Gb. Postgres version is 8.4.8 and for some months i cannot upgrade.
Is there a way to solve the problem?
Thank you, very best regards.
Francesco
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
With a heavy query, when line number results raise over 600k query hangs with out of memory.
Here is the explain analyze:
[...]
Work_mem is.512mb, shared buffers 3084mb and system Ram 10Gb. Postgres version is 8.4.8 and for some months i cannot upgrade.Is there a way to solve the problem?
Hi,
a few ideas:
- what goes out of memory? The client? if you query from a programming language you should set the fetch/batch size
to some value so that it won't fetch the whole 600k rows into memory... for the psql client you can do
\set FETCH_COUNT 1000
- work_mem 512 MB is high-ish unless you have a small value for max_connection...
- 8.4.8 was released in 2011, the latest 8.4 release is 8.4.22, you'r missing lots of patches (and 8.4 was EOLed more
than two years ago)
Bye,
Chris.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general