Search Postgresql Archives

Out of memory error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux