Thread: Big trouble with memory !!
Hi, We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we have many troubles with PostgreSQL and most of them seems to be memory troubles. As far as we can see, kernel kills the postmaster process when it begins to use swap. You can see the output from dmesg at the bottom of the message. The first thing I am not sure to understand is that the kernel should kill processes to reallocate memory only when physical memory and swap memory are exhausted, shouldn't it ? Second thing: it seems to be related to our kernel switch as it did not happen before that. This can occur when queries / a vacuum require too much memory to run. I have configured my kernel with these options: # shared mem kernel.shmmax= 641604096 # semaphore kernel.sem = 250 32000 100 400 fs.file-max=65536 # overcommit vm.overcommit_memory=2 Does anyone can explain me why I have this problem and how to resolve it ? This server is a dedicated PostgreSQL server with 4Gb of RAM. PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Linux kernel 2.6.11.6 #1 SMP Last Vacuum : VACUUM full VERBOSE ANALYZE prefs; INFO: vacuuming "public.prefs" INFO: "prefs": found 32 removable, 1010549 nonremovable row versions in 30847 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 165 to 505 bytes long. There were 102930 unused item pointers. Total free space (including removable row versions) is 39264528 bytes. 17 pages are or will become empty, including 0 at the end of the table. 23600 pages containing 38830876 free bytes are potential move destinations. CPU 0.60s/0.32u sec elapsed 16.80 sec. INFO: index "ix_anon_prefs" now contains 1010549 row versions in 7596 pages DETAIL: 32 index row versions were removed. 123 index pages have been deleted, 123 are currently reusable. CPU 0.04s/1.27u sec elapsed 1.32 sec. INFO: index "ix_prefs_fromsite" now contains 1010549 row versions in 10472 pages DETAIL: 32 index row versions were removed. 859 index pages have been deleted, 859 are currently reusable. CPU 0.07s/0.86u sec elapsed 0.95 sec. INFO: index "ix_datecrea_prefs" now contains 1010549 row versions in 5956 pages DETAIL: 32 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/1.15u sec elapsed 1.23 sec. INFO: index "prefs_pkey" now contains 1010549 row versions in 8360 pages DETAIL: 32 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.15s/1.23u sec elapsed 1.67 sec. INFO: "prefs": moved 52064 row versions, truncated 30847 to 26221 pages DETAIL: CPU 2.60s/22.43u sec elapsed 136.48 sec. INFO: index "ix_anon_prefs" now contains 1010549 row versions in 7639 pages DETAIL: 52064 index row versions were removed. 123 index pages have been deleted, 123 are currently reusable. CPU 0.01s/0.33u sec elapsed 0.39 sec. INFO: index "ix_prefs_fromsite" now contains 1010549 row versions in 10472 pages DETAIL: 52064 index row versions were removed. 834 index pages have been deleted, 834 are currently reusable. CPU 0.13s/0.29u sec elapsed 1.94 sec. INFO: index "ix_datecrea_prefs" now contains 1010549 row versions in 5956 pages DETAIL: 52064 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.28u sec elapsed 0.28 sec. INFO: index "prefs_pkey" now contains 1010549 row versions in 8360 pages DETAIL: 52064 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.04s/0.34u sec elapsed 0.56 sec. INFO: vacuuming "pg_toast.pg_toast_17230" INFO: "pg_toast_17230": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_17230_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.35 sec. INFO: analyzing "public.prefs" INFO: "prefs": 26221 pages, 3000 rows sampled, 1015468 estimated total rows ERROR: out of memory DETAIL: Failed on request of size 24000. dmesg : ======= Free pages: 11996kB (4672kB HighMem) Active:620816 inactive:114574 dirty:76 writeback:0 unstable:0 free:3031 slab:14426 mapped:592875 pagetables:202522 DMA free:3588kB min:68kB low:84kB high:100kB active:64kB inactive:0kB present:16384kB pages_scanned:103 all_unreclaimable? yes lowmem_reserve[]: 0 880 3759 Normal free:3736kB min:3756kB low:4692kB high:5632kB active:312kB inactive:0kB present:901120kB pages_scanned:517 all_unreclaimable? yes lowmem_reserve[]: 0 0 23039 HighMem free:5312kB min:512kB low:640kB high:768kB active:2482232kB inactive:458296kB present:2948992kB pages_scanned:0 all_unreclaimable? no lowmem_reserve[]: 0 0 0 DMA: 1*4kB 0*8kB 0*16kB 0*32kB 0*64kB 0*128kB 0*256kB 1*512kB 1*1024kB 1*2048kB 0*4096kB = 3588kB Normal: 0*4kB 1*8kB 1*16kB 0*32kB 0*64kB 1*128kB 0*256kB 1*512kB 1*1024kB 1*2048kB 0*4096kB = 3736kB HighMem: 1384*4kB 6*8kB 3*16kB 2*32kB 0*64kB 0*128kB 0*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 6208kB Swap cache: add 107282, delete 91308, find 40542/48162, race 0+0 Free swap = 2020244kB Total swap = 2097136kB Out of Memory: Killed process 14286 (postmaster). postgresql.conf : ============ # - Memory - shared_buffers = 60000 # min 16, at least max_connections*2, 8KB each sort_mem = 32768 # min 64, size in KB vacuum_mem = 32768 # min 1024, size in KB # - Free Space Map - max_fsm_pages = 600000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 5000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' Regards, -- Hervé Piedvache NOUVELLE ADRESSE - NEW ADDRESS : Elma Ingénierie Informatique 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747
Hervé Piedvache wrote: > Hi, > > We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we > have many troubles with PostgreSQL and most of them seems to be memory > troubles. > > As far as we can see, kernel kills the postmaster process when it begins to > use swap. You can see the output from dmesg at the bottom of the message. > The first thing I am not sure to understand is that the kernel should kill > processes to reallocate memory only when physical memory and swap memory are > exhausted, shouldn't it ? > Second thing: it seems to be related to our kernel switch as it did not happen > before that. > > This can occur when queries / a vacuum require too much memory to run. > I have configured my kernel with these options: > # shared mem > kernel.shmmax= 641604096 > # semaphore > kernel.sem = 250 32000 100 400 > fs.file-max=65536 > # overcommit > vm.overcommit_memory=2 > > Does anyone can explain me why I have this problem and how to resolve it ? > > This server is a dedicated PostgreSQL server with 4Gb of RAM. You might want to try vm.overcommit_memory=1. You don't appear to be the only one suffering from an over-zealous oom-killer. http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1295.html http://www.linuxquestions.org/questions/history/291119 -- Richard Huxton Archonet Ltd
On Wed, Apr 06, 2005 at 04:35:43PM +0200, Hervé Piedvache wrote: > Hi, > > We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we > have many troubles with PostgreSQL and most of them seems to be memory > troubles. > > As far as we can see, kernel kills the postmaster process when it begins to > use swap. You can see the output from dmesg at the bottom of the message. > The first thing I am not sure to understand is that the kernel should kill > processes to reallocate memory only when physical memory and swap memory are > exhausted, shouldn't it ? > Second thing: it seems to be related to our kernel switch as it did not happen > before that. My guess is that your problem stems from this line: > vm.overcommit_memory=2 The code was changed so this *really* would not let anything exceed the available memory. Instead of all shared pages for each library being counted in common, each copy gets counted individually because each process could possibly demand their own copy by writing to it. This link seems to imply it was done in the 2.5 series. http://kerneltrap.org/node/326 What I don't understand is that with true strict overcommit, the kernel should never need to kill your process since there is always in principle enough room. That worked, see: > ERROR: out of memory > DETAIL: Failed on request of size 24000. Except the kernel killed it anyway, very odd. Which means someone isn't counting properly. Your shared_buffers are on the high side, but that may be the appropriate setting for your system... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Wednesday 06 April 2005 17:03, Richard Huxton wrote: > Hervé Piedvache wrote: > > Hi, > > > > We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this > > date we have many troubles with PostgreSQL and most of them seems to be > > memory troubles. > > > > As far as we can see, kernel kills the postmaster process when it begins > > to use swap. You can see the output from dmesg at the bottom of the > > message. The first thing I am not sure to understand is that the kernel > > should kill processes to reallocate memory only when physical memory and > > swap memory are exhausted, shouldn't it ? > > Second thing: it seems to be related to our kernel switch as it did not > > happen before that. > > > > This can occur when queries / a vacuum require too much memory to run. > > I have configured my kernel with these options: > > # shared mem > > kernel.shmmax= 641604096 > > # semaphore > > kernel.sem = 250 32000 100 400 > > fs.file-max=65536 > > # overcommit > > vm.overcommit_memory=2 > > > > Does anyone can explain me why I have this problem and how to resolve it > > ? > > > > This server is a dedicated PostgreSQL server with 4Gb of RAM. > > You might want to try vm.overcommit_memory=1. You don't appear to be the > only one suffering from an over-zealous oom-killer. > But if you look at the documentation of PostgreSQL : ------------------------------------------------------------------------------------------------- 16.5.3. Linux Memory Overcommit In Linux 2.4 and later, the default virtual memory behavior is not optimal for PostgreSQL. Because of the way that the kernel implements memory overcommit, the kernel may terminate the PostgreSQL server (the postmaster process) if the memory demands of another process cause the system to run out of virtual memory. If this happens, you will see a kernel message that looks like this (consult your system documentation and configuration on where to look for such a message): Out of Memory: Killed process 12345 (postmaster). This indicates that the postmaster process has been terminated due to memory pressure. Although existing database connections will continue to function normally, no new connections will be accepted. To recover, PostgreSQL will need to be restarted. One way to avoid this problem is to run PostgreSQL on a machine where you can be sure that other processes will not run the machine out of memory. On Linux 2.6 and later, a better solution is to modify the kernel's behavior so that it will not "overcommit" memory. This is done by selecting strict overcommit mode via sysctl: sysctl -w vm.overcommit_memory=2 or placing an equivalent entry in /etc/sysctl.conf. You may also wish to modify the related setting vm.overcommit_ratio. For details see the kernel documentation file Documentation/vm/overcommit-accounting. Some vendors' Linux 2.4 kernels are reported to have early versions of the 2.6 overcommit sysctl. However, setting vm.overcommit_memory to 2 on a kernel that does not have the relevant code will make things worse not better. It is recommended that you inspect the actual kernel source code (see the function vm_enough_memory in the file mm/mmap.c) to verify what is supported in your copy before you try this in a 2.4 installation. The presence of the overcommit-accounting documentation file should not be taken as evidence that the feature is there. If in any doubt, consult a kernel expert or your kernel vendor. ------------------------------------------------------------------------------------------------- Where is the good solution ? -- Hervé Piedvache NOUVELLE ADRESSE - NEW ADDRESS : Elma Ingénierie Informatique 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747
Hervé Piedvache wrote: > On Wednesday 06 April 2005 17:03, Richard Huxton wrote: > >>Hervé Piedvache wrote: >> >>>Hi, >>> >>>We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this >>>date we have many troubles with PostgreSQL and most of them seems to be >>>memory troubles. >>> >>>As far as we can see, kernel kills the postmaster process when it begins >>>to use swap. You can see the output from dmesg at the bottom of the >>>message. The first thing I am not sure to understand is that the kernel >>>should kill processes to reallocate memory only when physical memory and >>>swap memory are exhausted, shouldn't it ? >>>Second thing: it seems to be related to our kernel switch as it did not >>>happen before that. >>> >>>This can occur when queries / a vacuum require too much memory to run. >>>I have configured my kernel with these options: >>># shared mem >>>kernel.shmmax= 641604096 >>># semaphore >>>kernel.sem = 250 32000 100 400 >>>fs.file-max=65536 >>># overcommit >>>vm.overcommit_memory=2 >>> >>>Does anyone can explain me why I have this problem and how to resolve it >>>? >>> >>>This server is a dedicated PostgreSQL server with 4Gb of RAM. >> >>You might want to try vm.overcommit_memory=1. You don't appear to be the >>only one suffering from an over-zealous oom-killer. >> > > > But if you look at the documentation of PostgreSQL : > ------------------------------------------------------------------------------------------------- > 16.5.3. Linux Memory Overcommit > > In Linux 2.4 and later, the default virtual memory behavior is not optimal for > PostgreSQL. Because of the way that the kernel implements memory overcommit, > the kernel may terminate the PostgreSQL server (the postmaster process) if > the memory demands of another process cause the system to run out of virtual > memory. > > If this happens, you will see a kernel message that looks like this (consult > your system documentation and configuration on where to look for such a > message): > > Out of Memory: Killed process 12345 (postmaster). > > This indicates that the postmaster process has been terminated due to memory > pressure. Although existing database connections will continue to function > normally, no new connections will be accepted. To recover, PostgreSQL will > need to be restarted. > > One way to avoid this problem is to run PostgreSQL on a machine where you can > be sure that other processes will not run the machine out of memory. > > On Linux 2.6 and later, a better solution is to modify the kernel's behavior > so that it will not "overcommit" memory. This is done by selecting strict > overcommit mode via sysctl: > > sysctl -w vm.overcommit_memory=2 > > or placing an equivalent entry in /etc/sysctl.conf. You may also wish to > modify the related setting vm.overcommit_ratio. For details see the kernel > documentation file Documentation/vm/overcommit-accounting. > > Some vendors' Linux 2.4 kernels are reported to have early versions of the 2.6 > overcommit sysctl. However, setting vm.overcommit_memory to 2 on a kernel > that does not have the relevant code will make things worse not better. It is > recommended that you inspect the actual kernel source code (see the function > vm_enough_memory in the file mm/mmap.c) to verify what is supported in your > copy before you try this in a 2.4 installation. The presence of the > overcommit-accounting documentation file should not be taken as evidence that > the feature is there. If in any doubt, consult a kernel expert or your kernel > vendor. > ------------------------------------------------------------------------------------------------- Ah, but I believe there have been some issues with the Linux kernel recently: http://www.kerneltraffic.org/kernel-traffic/kt20050212_296.html#6 If the oom-killer is running riot on your system that would suggest the bug is still there in some form. -- Richard Huxton Archonet Ltd
Martijn van Oosterhout <kleptog@svana.org> writes:
> What I don't understand is that with true strict overcommit, the kernel
> should never need to kill your process since there is always in
> principle enough room.
Indeed. Are you *sure* you have overcommit turned off? That should
disable the OOM killer altogether. You should probably go read the
kernel documentation rather than assume Postgres' documentation knows
what it's talking about ;-)
regards, tom lane
Richard Huxton <dev@archonet.com> writes: > You might want to try vm.overcommit_memory=1. You don't appear to be the > only one suffering from an over-zealous oom-killer. > http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1295.html Hmm, in particular Andrea Arcangeli implies here http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1358.html that there are some pretty serious bugs in this area in 2.6.10. So the answer is you need a different kernel version. regards, tom lane
On Wed, Apr 06, 2005 at 12:52:55PM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > What I don't understand is that with true strict overcommit, the kernel > > should never need to kill your process since there is always in > > principle enough room. > > Indeed. Are you *sure* you have overcommit turned off? That should > disable the OOM killer altogether. You should probably go read the > kernel documentation rather than assume Postgres' documentation knows > what it's talking about ;-) What I don't understand is the problem with overcommitting. Not overcommitting is rather wasteful of memory and you'll start seeing failed allocations long before "free" indicates there's a problem. See the original poster of this thread whose memory allocation for 24KB was being rejected while there is still megabytes of "free" memory (not to mention swap). The memory which is "overcommitted" is being used for disk cache and buffers in the meantime. Note the kernel message says "postmaster" was killed by the kernel, yet that was most probably a child process. Both the postmaster, the stats collector and any child process will be referred to as "postmaster". AFAIK no exec() is being done to change the name (see /proc/<pid>/status). BTW, according to this page in the archive: http://archives.postgresql.org/pgsql-patches/2003-11/msg00194.php 2 - (NEW) strict overcommit. The total address space commit for the system is not permitted to exceed swap + a configurable percentage (default is 50) of physical RAM. Depending on the percentage you use, in most situations this means a process will not be killed while accessing pages but will receive errors on memory allocation as appropriate. So the origial poster had 4GB of RAM and 2GB of swap, so by that rule he's only allowed to allocate a total of (swap)2GB+ 50%(memory)4GB = 4GB. In other words, he'd never use swap at all. Maybe fiddling that percentage would be a better idea. But overcommit still seems useless to me. PS, I only know about Linux here. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> What I don't understand is the problem with overcommitting.
The problem with Linux overcommit is that when the kernel does run out
of memory, the process it chooses to kill isn't necessarily one that was
using an unreasonable amount of memory. The earlier versions were quite
willing to kill "init" ;-) ... I think they hacked it to prevent that
disaster, but it's still entirely capable of deciding to take out the
(real) postmaster, your mail daemon, or other processes you'd prefer not
to lose. As such, the feature is really too dangerous to enable on
machines being used for production purposes.
regards, tom lane
On Wed, Apr 06, 2005 at 04:37:59PM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > What I don't understand is the problem with overcommitting. > > The problem with Linux overcommit is that when the kernel does run out > of memory, the process it chooses to kill isn't necessarily one that was > using an unreasonable amount of memory. The earlier versions were quite > willing to kill "init" ;-) ... I think they hacked it to prevent that > disaster, but it's still entirely capable of deciding to take out the > (real) postmaster, your mail daemon, or other processes you'd prefer not > to lose. As such, the feature is really too dangerous to enable on > machines being used for production purposes. Ok, I think the point I'm trying to make is that with "strict autocommit" in its current state isn't really that strict and just causes the problem to happen elsewhere. The guy had heaps of memory available and the system is dying on him. Better turn autocommit off and let him use up the 2GB of swap before having processes killed. Or rather, if you can't stop the killer anyway, take advantage of the extra memory. That formula they use is bizarre. I tend to allocate max 250MB swap even on machines with a gig of memory. If I'm using a lot of swap I'm doing something wrong. By the formula they use strict autocommit would start failing memory allocations with my memory only half used... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> Ok, I think the point I'm trying to make is that with "strict
> autocommit" in its current state isn't really that strict and just
> causes the problem to happen elsewhere.
Right, but that is surely just a kernel bug, and one that's not been
around very long. Presumably it'll be fixed soon.
> That formula they use is bizarre.
I presume that the point of the formula is that you want some fraction
of physical RAM to be reserved for disk buffers ... 50% might be
unreasonably high, or not ...
regards, tom lane
On Wednesday 06 April 2005 18:57, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > You might want to try vm.overcommit_memory=1. You don't appear to be the > > only one suffering from an over-zealous oom-killer. > > > > http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1295.html > > Hmm, in particular Andrea Arcangeli implies here > http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1358.html > that there are some pretty serious bugs in this area in 2.6.10. OK I downgrade to 2.6.9 ... Let's see ! Thanks ! -- Hervé Piedvache NOUVELLE ADRESSE - NEW ADDRESS : Elma Ingénierie Informatique 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747
On Wednesday 06 April 2005 18:52, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > What I don't understand is that with true strict overcommit, the kernel > > should never need to kill your process since there is always in > > principle enough room. > > Indeed. Are you *sure* you have overcommit turned off? How to know this point ? I mean is there any sure action to do to know this point ... ? > That should disable the OOM killer altogether. You should probably go read > the kernel documentation rather than assume Postgres' documentation knows > what it's talking about ;-) hugh ? I can't read that ... PostgreSQL documentation is my bible ... the only good explanation are always inside the PostgreSQL documentation ... :o) May be a correction for the next release ? -- Hervé Piedvache NOUVELLE ADRESSE - NEW ADDRESS : Elma Ingénierie Informatique 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747