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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
