I had an Out of Memory error while running this query in psql over a
170 million rows table:
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
group by data, usuario
FC2 PG 7.4.6 1GB mem
Linux s1 2.6.9-1.11_FC2 #1 Sun Jan 2 15:49:30 EST 2005 i686 athlon
i386 GNU/Linux
logfile:
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
...
LOG: recycled transaction log file "000001D70000009A"
LOG: removing transaction log file "000001D700000097"
...
ERROR: canceling query due to user request
LOG: could not send data to client: Broken pipe
LOG: unexpected EOF on client connection
postgresql.conf (everything else default):
shared_buffers = 3000
sort_mem = 131072
vacuum_mem = 131072
max_fsm_pages = 300000
fsync = false
wal_buffers = 256
checkpoint_segments = 32
extra_float_digits = 2
lc_messages = 'C'
lc_monetary = 'pt_BR.UTF-8'
lc_numeric = 'pt_BR.UTF-8'
lc_time = 'pt_BR.UTF-8'
var/log/messages:
Feb 7 15:29:32 s1 su(pam_unix)[24675]: session closed for user root
Feb 7 16:30:22 s1 kernel: oom-killer: gfp_mask=0x1d2
Feb 7 16:30:22 s1 kernel: Mem-info:
Feb 7 16:30:22 s1 kernel: DMA per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 2, high 6, batch 1
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 2, batch 1
Feb 7 16:30:22 s1 kernel: Normal per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 32, high 96, batch 16
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 32, batch 16
Feb 7 16:30:22 s1 kernel: HighMem per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 12, high 36, batch 6
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 12, batch 6
Feb 7 16:30:22 s1 kernel:
Feb 7 16:30:22 s1 kernel: Free pages: 1280kB (120kB HighMem)
Feb 7 16:30:23 s1 kernel: Active:132041 inactive:116634 dirty:0
writeback:0 unstable:0 free:320 slab:2226 mapped:246816
pagetables:1456
Feb 7 16:30:23 s1 kernel: DMA free:16kB min:16kB low:32kB high:48kB
active:6616kB inactive:6068kB present:16384kB
Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:23 s1 kernel: Normal free:1144kB min:936kB low:1872kB
high:2808kB active:445348kB inactive:426140kB present:901120kB
Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:24 s1 kernel: HighMem free:120kB min:128kB low:256kB
high:384kB active:76200kB inactive:34328kB present:114624kB
Feb 7 16:30:24 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:24 s1 kernel: DMA: 0*4kB 0*8kB 1*16kB 0*32kB 0*64kB
0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 16kB
Feb 7 16:30:24 s1 kernel: Normal: 32*4kB 9*8kB 3*16kB 2*32kB 1*64kB
0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 1144kB
Feb 7 16:30:24 s1 kernel: HighMem: 0*4kB 1*8kB 1*16kB 1*32kB 1*64kB
0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 120kB
Feb 7 16:30:25 s1 kernel: Swap cache: add 422343, delete 421792, find
12780/16340, race 0+0
Feb 7 16:30:25 s1 kernel: Free swap: 0kB
Feb 7 16:30:25 s1 kernel: 258032 pages of RAM
Feb 7 16:30:25 s1 kernel: 28656 pages of HIGHMEM
Feb 7 16:30:25 s1 kernel: 3138 reserved pages
Feb 7 16:30:26 s1 kernel: 14914 pages shared
Feb 7 16:30:26 s1 kernel: 551 pages swap cached
Feb 7 16:30:26 s1 kernel: Out of Memory: Killed process 30679 (psql).
Any advice on how to avoid it?
Regards, Clodoaldo Pinto
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
