Обсуждение: hot restart of posgtresql
hi all by unknown reason our database server rebooted couple hours ago. now i cannot restart posgresql 8.1 heres the error code i get: ===================================================== root@eddie:/var/run# /etc/init.d/postgresql-8.1 start Starting PostgreSQL 8.1 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2007-07-03 19:20:42 EDT 2928 FATAL: could not create shared memory segment: Invalid argument 2007-07-03 19:20:42 EDT 2928 DETAIL: Failed system call was shmget(key=5432001, size=3276259328, 03600). 2007-07-03 19:20:42 EDT 2928 HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 3276259328 bytes), reduce PostgreSQL's shared_buffers parameter (currently 393216) and/or its max_connections parameter (currently 250). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. failed! ======================================================= heres my sysctl: kernel.shmmax=4294967296 kernel.shmall=536870912 Any help will be really appreciated Thanks
ngaleyev@wavelengthmail.com writes: > 2007-07-03 19:20:42 EDT 2928 FATAL: could not create shared memory > segment: Invalid argument > 2007-07-03 19:20:42 EDT 2928 DETAIL: Failed system call was > shmget(key=5432001, size=3276259328, 03600). > 2007-07-03 19:20:42 EDT 2928 HINT: This error usually means that > PostgreSQL's request for a shared memory segment exceeded your kernel's > SHMMAX parameter. You can either reduce the request size or reconfigure > the kernel with larger SHMMAX. To reduce the request size (currently > 3276259328 bytes), reduce PostgreSQL's shared_buffers parameter (currently > 393216) and/or its max_connections parameter (currently 250). > heres my sysctl: > kernel.shmmax=4294967296 > kernel.shmall=536870912 Why have you got SHMALL set to half a GB when you have Postgres configured to ask for over 3 GB? regards, tom lane
Tom Lane wrote: > ngaleyev@wavelengthmail.com writes: > > 2007-07-03 19:20:42 EDT 2928 FATAL: could not create shared memory > > segment: Invalid argument > > 2007-07-03 19:20:42 EDT 2928 DETAIL: Failed system call was > > shmget(key=5432001, size=3276259328, 03600). > > 2007-07-03 19:20:42 EDT 2928 HINT: This error usually means that > > PostgreSQL's request for a shared memory segment exceeded your kernel's > > SHMMAX parameter. You can either reduce the request size or reconfigure > > the kernel with larger SHMMAX. To reduce the request size (currently > > 3276259328 bytes), reduce PostgreSQL's shared_buffers parameter (currently > > 393216) and/or its max_connections parameter (currently 250). > > > heres my sysctl: > > kernel.shmmax=4294967296 > > kernel.shmall=536870912 > > Why have you got SHMALL set to half a GB when you have Postgres > configured to ask for over 3 GB? Hmm, isn't shmall measured in pages? I think the problem may be that he changed the settings in sysctl.conf but he hasn't rebooted since, so the kernel is running with other settings. Trying with sysctl -w to change the actual values might prove helpful. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes: > Tom Lane wrote: >> ngaleyev@wavelengthmail.com writes: >>> heres my sysctl: >>> kernel.shmmax=4294967296 >>> kernel.shmall=536870912 >> >> Why have you got SHMALL set to half a GB when you have Postgres >> configured to ask for over 3 GB? > Hmm, isn't shmall measured in pages? Depends on platform, which he didn't say ... but if it is a platform that measures in pages, I wonder whether that value is provoking an integer overflow somewhere. I don't think Linux allows more than about 2 million, for instance. > I think the problem may be that he changed the settings in sysctl.conf > but he hasn't rebooted since, so the kernel is running with other > settings. Trying with sysctl -w to change the actual values might prove > helpful. But his DB *was* running before, so somehow the active sysctl settings are different now than they were. I was betting on there having been a manual tweak to sysctl that never got made in the configuration file, and thus got lost on reboot. regards, tom lane
thanks for replies everyone platform is Debian 2.6 btw Heres how i got temp fix (i dont have much background in postgresql, so dont kick me hard): few months ago old admin upgraded to 6GB of ram and increased shared_buffers in posgresql.conf from 16384 to 393216 (3GB) He also modifed sysctl to look like this: ----- kernel.shmmax=4294967296 kernel.shmall=536870912 ----- I looked in top command, and dmesg, both indicate 6GB of ram. So I started gradually increase "sysctl -w kernel.shmmax=xxx" during that i was checking out /proc/sys/kernel/shmmax it was keeping up with corresponding values until i reached that number - 4294967296, it was showing "0" instead. so i lowered it to 4200000000, about highest number that would not null that /proc value. Still wouldnt start :( but with different error now, didnt save it After that i decreased shared_buffers back to 16384, and it worked. Didnt mess with it anymore for right now, don't want to experiment on live server. Old admin is not around anymore, so I sort of inherited responsibility for the server, but I don't know much about postgresql yet. So, whats the optimum values for this amount of memory as far as SHMALL, MAX and shared_buffers? Database size is about 115GB, daily traffic is about 800MB. Thanks alot -Nik