Skip site navigation (1) Skip section navigation (2)

Re: BUG #2168: 45.000.000 records too much?

From: Steven Mooij <steven(at)mooij(dot)name>
To: Seneca Cunningham <scunning(at)ca(dot)afilias(dot)info>
Cc: pgsql-bugs(at)postgresql(dot)org, Rudolph Froger <r(dot)froger(at)estrate(dot)nl>
Subject: Re: BUG #2168: 45.000.000 records too much?
Date: 2006-01-20 10:17:51
Message-ID: 43D0B8CF.4070007@mooij.name (view raw or flat)
Thread:
Lists: 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: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   steven(at)mooij(dot)name
t   015 256 8304


In response to

pgsql-bugs by date

Next:From: Stephen FrostDate: 2006-01-20 14:49:57
Subject: pg_restore error checking
Previous:From: Kris SendenDate: 2006-01-20 08:37:53
Subject: BUG #2189: Prepared Statement is not parsed correctly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group