I recently upgraded to postgresql 7.4 and I am having a problem with postmaster using lots of memory for a query (keeps growing even up to 400MB+ till I stop postgresql ). I don't recall this ever happening with 7.3 with the exact same query but on different data (just as much data tho, or even more). Happened on 7.4 so I upgraded to 7.4.1. Is HashAggregate something new in 7.4? If I have time and disk space I'll downgrade to 7.3.3 and see if it happens there. How do I configure a memory consumption limit on a 7.4 postgresql without killing it?
I believe postgresql.conf is 7.4.1 default. md5sum= 75ffabc3e90457bd9d6e4ce649e17b6e postgresql.conf
Problem query: select ip_saddr,count(*),sum(ip_totlen) from cust_ulog where true group by ip_saddr order by sum(ip_totlen) desc limit 10
Results from Explain:
Limit (cost=335158.05..335158.08 rows=10 width=13)
-> Sort (cost=335158.05..335161.65 rows=1440 width=13)
Sort Key: sum(ip_totlen)
-> HashAggregate (cost=335075.31..335082.51 rows=1440 width=13)
-> Seq Scan on cust_ulog (cost=0.00..264115.32 rows=9461332 width=13)
(5 rows)
select count(*) from cust_ulog ; count --------- 9461332 (1 row)
Time: 51922.612 ms
Table definition:
Table "public.cust_ulog"
Column | Type | Modifiers
--------------+-----------------------------+-----------
id | integer |
oob_time_sec | timestamp without time zone |
oob_prefix | text |
oob_in | text |
oob_out | text |
ip_saddr | inet |
ip_daddr | inet |
ip_totlen | smallint |
ip_ttl | smallint |
ip_id | integer |
ip_protocol | smallint |
ip_tos | smallint |
tcp_sport | integer |
tcp_dport | integer |
tcp_seq | bigint |
tcp_ack_seq | bigint |
tcp_ack | boolean |
tcp_rst | boolean |
tcp_psh | boolean |
tcp_syn | boolean |
tcp_fin | boolean |
tcp_window | integer |
tcp_urgp | integer |
udp_sport | integer |
udp_dport | integer |
udp_len | smallint |
icmp_type | smallint |
icmp_code | smallint |
icmp_echoid | bigint |
icmp_echoseq | bigint |psql -V psql (PostgreSQL) 7.4.1 contains support for command-line editing
select version();
version
-------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)Output from: top 3:13pm up 1:14, 5 users, load average: 0.90, 0.33, 0.36 81 processes: 79 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 18.5% user, 8.3% system, 0.0% nice, 73.0% idle Mem: 254572K av, 250124K used, 4448K free, 0K shrd, 288K buff Swap: 522072K av, 92756K used, 429316K free 21048K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 16084 postgres 18 0 258M 217M 23868 D 24.7 87.6 0:20 postmaster
Output from: vmstat 2
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 1 0 71720 4388 504 18172 328 154 1629 200 171 123 10 2 87
1 0 0 75560 4352 464 19492 2154 1416 10730 1416 442 401 21 11 67
1 0 2 77992 4444 456 20568 2062 1498 9170 1504 412 373 13 9 78
0 1 0 80552 4372 284 20944 1878 1412 11158 1426 430 485 17 8 75
0 1 0 81704 4456 284 21884 1946 956 9114 956 395 345 13 8 79
1 0 0 83496 4408 292 22420 2096 412 10864 418 423 406 18 8 74
1 0 0 85540 4384 304 21936 1664 1218 9828 1218 400 387 16 9 75
0 1 0 86140 4352 296 22688 1672 522 11016 528 422 370 18 7 75
1 0 0 87696 4376 288 21376 1998 852 11086 856 437 393 19 8 73
0 1 0 89592 4444 288 21900 1686 1132 12118 1132 434 416 21 8 71
1 0 0 92512 4360 296 21396 1792 1262 11456 1268 429 393 18 5 77
0 2 1 94316 4324 292 21640 2172 508 10644 508 407 402 16 11 72
0 1 0 97160 4436 300 23504 1752 1302 12548 1308 427 455 24 9 67
1 0 0 100096 4428 292 23660 1542 1994 10374 1994 409 373 14 8 78
1 0 0 102848 4400 292 23700 2022 1708 10280 1708 406 365 12 9 79
1 0 0 105620 4456 292 25524 2028 1434 12396 1434 431 435 22 8 69
0 1 0 108248 4404 292 26200 2162 1068 12402 1068 437 409 24 8 68
--
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
