Hi,
The following SQL join command runs the PostgreSQL server out of memory. The server runs on a box with Red Hat Enterprise Linux Server release 6.3 (Santiago) and PostgreSQL 8.3.21.
select wm_nfsp from "5611_isarq".wm_nfsp
left join "5611_nfarq".nfe on
wm_nfsp.tpdoc = 7 where 1 = 1 and
wm_nfsp.codpre = 2866 and
wm_nfsp.compet = '10/2012';
Explain result:
Nested Loop Left Join (cost=7356.61..48466.46 rows=346312 width=32)
Join Filter: (wm_nfsp.tpdoc = 7)
-> Bitmap Heap Scan on wm_nfsp (cost=11.65..1162.37 rows=11 width=34)
Recheck Cond: (codpre = 2866)
Filter: ((compet)::text = '10/2012'::text)
-> Bitmap Index Scan on idx_wm_nfsp_codpre (cost=0.00..11.64 rows=714 width=0)
Index Cond: (codpre = 2866)
-> Materialize (cost=7344.96..8959.47 rows=161451 width=0)
-> Seq Scan on nfe (cost=0.00..7183.51 rows=161451 width=0)
(9 rows)
Once the query starts the top command starts showing an increase of memory use and minutes later vmstat shows the server performing a lot swapping and almost stops everything until the PID is killed.
top command output while join running on PID 29787:
top - 11:26:41 up 10 days, 6:30, 3 users, load average: 131.05, 74.55, 32.33
Tasks: 499 total, 3 running, 496 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.8%us, 12.7%sy, 0.0%ni, 0.0%id, 75.2%wa, 0.0%hi, 1.3%si, 0.0%st
Mem: 32876756k total, 32677176k used, 199580k free, 4804k buffers
Swap: 16777208k total, 6248980k used, 10528228k free, 485188k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29787 postgres 20 0 35.5g 29g 98m D 30.8 92.9 15:07.23 postgres: ipm Fisca
98 root 20 0 0 0 0 D 13.7 0.0 23:46.72 [kswapd0]
31496 postgres 20 0 546m 20m 18m D 4.9 0.1 0:00.29 postgres: ipm Fisca
29024 postgres 20 0 547m 52m 50m D 4.0 0.2 0:03.95 postgres: ipm Fisca
vmstat output showing the server is performing a lot of swapping:
Tue Nov 6 11:27:06 BRST 2012
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 143 6653476 199076 6368 476356 0 1 694 646 2 0 8 3 88 1 0
1 79 6700576 204104 6212 453808 64 9438 99833 9503 14213 9477 10 9 0 80 0
5 74 6813252 199144 5196 488872 7 22540 102592 22704 13770 8762 9 10 2 80 0
2 58 6855596 199332 4456 462592 70 8474 133870 8509 13527 9242 8 10 0 82 0
3 90 6907264 199096 5544 472112 102 10403 102617 11136 12764 8497 7 9 0 84 0
Running the same command on a PostgreSQL 9.0.0 server results in an OUT OF MEMORY message stopping the backend but preventing the entire server to stop.
The development team is going to change the SQL join command but my concern is with other similar SQL commands not yet identified.
Is there a way to make PostgreSQL 8.3.21 server stop memory bound backends as PostgreSQL 9.0.0 does?
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
