Seneca Cunningham wrote:
Steven Mooij wrote:
Some additional info. We repeated the test on different hardware with
unfortunately the same result. This is the output of the postgresql
logfile of that experiment:
[log messages about processes kill 9ed]
This time we tested with postgresql 8.0.3 on a 2.6.12 linux kernel.
(Previously i tested with postgresql 7.4.9 on a 2.6.15 kernel.)
Does dmesg or syslog mention the OOM killer? I tried running your test
case while watching memory usage and stopped the postmaster before it
finished as I didn't want to risk the kernel killing it (or some random
other process) the hard way.
Yes, the oom-killer kills the postmaster. But that's besides the
problem. In my opinion the bug is that PostgreSQL has an inefficient
implementation of this operation, that takes way too much memory. (Of
course, I say this with a lot of respect for all the good
PostgreSQL-code that has been written.)
Jan 18 11:29:40 laptop2 kernel: [4304395.479000] oom-killer: gfp_mask=0x1d2
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] DMA per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] cpu 0 hot: low 2, high 6,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] cpu 0 cold: low 0, high 2,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Normal per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] cpu 0 hot: low 62, high 186,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] cpu 0 cold: low 0, high 62,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] HighMem per-cpu: empty
Jan 18 11:29:41 laptop2 kernel: [4304395.479000]
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Free pages: 4964kB (0kB
HighMem)
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Active:52023 inactive:52455
dirty:0 writeback:0 unstable:0 free:1241 slab:3563 mapped:102467 pagetables:751
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] DMA free:2032kB min:88kB
low:108kB high:132kB active:5492kB inactive:5512kB present:16384kB
pages_scanned:16847 all_unreclaimable? yes
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] lowmem_reserve[]: 0 486 486
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Normal free:2932kB min:2776kB
low:3468kB high:4164kB active:202600kB inactive:204308kB present:498560kB
pages_scanned:379686 all_unreclaimable? no
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] HighMem free:0kB min:128kB
low:160kB high:192kB active:0kB inactive:0kB present:0kB pages_scanned:0
all_unreclaimable? no
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] DMA: 0*4kB 0*8kB 1*16kB 1*32kB
1*64kB 1*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 0*4096kB = 2032kB
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Normal: 63*4kB 1*8kB 7*16kB
6*32kB 3*64kB 1*128kB 0*256kB 0*512kB 0*1024kB 1*2048kB 0*4096kB = 2932kB
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] HighMem: empty
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Swap cache: add 267257, delete
267257, find 1913/4312, race 0+3
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Free swap = 0kB
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Total swap = 979956kB
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] Out of Memory: Killed process
8208 (postmaster).
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] oom-killer: gfp_mask=0x1d2
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] DMA per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] cpu 0 hot: low 2, high 6,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] cpu 0 cold: low 0, high 2,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Normal per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] cpu 0 hot: low 62, high 186,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] cpu 0 cold: low 0, high 62,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] HighMem per-cpu: empty
Jan 18 11:29:41 laptop2 kernel: [4304399.118000]
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Free pages: 4716kB (0kB
HighMem)
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Active:51957 inactive:52556
dirty:0 writeback:0 unstable:0 free:1179 slab:3547 mapped:102505 pagetables:745
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] DMA free:2032kB min:88kB
low:108kB high:132kB active:5496kB inactive:5528kB present:16384kB
pages_scanned:18629 all_unreclaimable? yes
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] lowmem_reserve[]: 0 486 486
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Normal free:2684kB min:2776kB
low:3468kB high:4164kB active:202332kB inactive:204696kB present:498560kB
pages_scanned:463523 all_unreclaimable? no
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] HighMem free:0kB min:128kB
low:160kB high:192kB active:0kB inactive:0kB present:0kB pages_scanned:0
all_unreclaimable? no
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] DMA: 0*4kB 0*8kB 1*16kB 1*32kB
1*64kB 1*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 0*4096kB = 2032kB
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Normal: 1*4kB 1*8kB 7*16kB
6*32kB 3*64kB 1*128kB 0*256kB 0*512kB 0*1024kB 1*2048kB 0*4096kB = 2684kB
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] HighMem: empty
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Swap cache: add 267302, delete
267302, find 1913/4316, race 0+3
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Free swap = 0kB
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Total swap = 979956kB
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] Out of Memory: Killed process
8209 (postmaster).
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] oom-killer: gfp_mask=0x1d2
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] DMA per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] cpu 0 hot: low 2, high 6,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] cpu 0 cold: low 0, high 2,
batch 1
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Normal per-cpu:
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] cpu 0 hot: low 62, high 186,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] cpu 0 cold: low 0, high 62,
batch 31
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] HighMem per-cpu: empty
Jan 18 11:29:41 laptop2 kernel: [4304399.141000]
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Free pages: 4716kB (0kB
HighMem)
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Active:51990 inactive:52547
dirty:0 writeback:26 unstable:0 free:1179 slab:3547 mapped:102473 pagetables:741
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] DMA free:2032kB min:88kB
low:108kB high:132kB active:5496kB inactive:5528kB present:16384kB
pages_scanned:18629 all_unreclaimable? yes
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] lowmem_reserve[]: 0 486 486
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Normal free:2684kB min:2776kB
low:3468kB high:4164kB active:202464kB inactive:204660kB present:498560kB
pages_scanned:491576 all_unreclaimable? yes
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] HighMem free:0kB min:128kB
low:160kB high:192kB active:0kB inactive:0kB present:0kB pages_scanned:0
all_unreclaimable? no
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] lowmem_reserve[]: 0 0 0
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] DMA: 0*4kB 0*8kB 1*16kB 1*32kB
1*64kB 1*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 0*4096kB = 2032kB
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Normal: 1*4kB 1*8kB 7*16kB
6*32kB 3*64kB 1*128kB 0*256kB 0*512kB 0*1024kB 1*2048kB 0*4096kB = 2684kB
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] HighMem: empty
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Swap cache: add 267329, delete
267303, find 1913/4317, race 0+3
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Free swap = 0kB
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Total swap = 979956kB
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] Out of Memory: Killed process
9294 (postmaster).
--
Steven Mooij
Koekamp 83
2623XP Delft
e [EMAIL PROTECTED]
t 015 256 8304
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
