Re: BUG #2168: 45.000.000 records too much? - Mailing list pgsql-bugs

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:3563mapped:102467 pagetables:751 
Jan 18 11:29:41 laptop2 kernel: [4304395.479000] DMA free:2032kB min:88kB low:108kB high:132kB active:5492kB
inactive:5512kBpresent: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:204308kBpresent: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:0kBpresent: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*2048kB0*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*1024kB1*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:3547mapped:102505 pagetables:745 
Jan 18 11:29:41 laptop2 kernel: [4304399.118000] DMA free:2032kB min:88kB low:108kB high:132kB active:5496kB
inactive:5528kBpresent: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:204696kBpresent: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:0kBpresent: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*2048kB0*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*1024kB1*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:3547mapped:102473 pagetables:741 
Jan 18 11:29:41 laptop2 kernel: [4304399.141000] DMA free:2032kB min:88kB low:108kB high:132kB active:5496kB
inactive:5528kBpresent: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:204660kBpresent: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:0kBpresent: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*2048kB0*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*1024kB1*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   steven@mooij.name
t   015 256 8304