postgresql won't restart. Cannot allocate memory. Must reboot. Any alternative short of reboot?

Поиск
Список
Период
Сортировка
От Mitchell Laks
Тема postgresql won't restart. Cannot allocate memory. Must reboot. Any alternative short of reboot?
Дата
Msg-id 20090706161133.GA20160@earthlink.net
обсуждение исходный текст
Список pgsql-admin
Hi,

I am a very happy user of Postgresql! Thank you all for this marvelous work!

I have an older linux server running debian etch 4.0 using the old postgresql-7.4.

There is essentially a single application running on that machine which serves up data from a single postgresql
database.
There is no new data added to the database. It is simply serving up legacy information. The server does nothing else.

I suspect I am working at the limits of the memory capabilities of this machine. It has 1G of ram. My postgresql
databasehas a few very small control tables 
and has a single very large table LTA_IDB (the pg_dump of this main table is 1.9G in size ) and
du -sh /var/lib/postgresql/7.4/
5.7G    /var/lib/postgresql/7.4/

These are my settings in
postgresql.conf:

shared_buffers = 48000
max_connections = 512
 sort_mem = 4096
effective_cache_size = 4000
wal_buffers = 8

------

while in the file

/etc/sysctl.conf
kernel.shmmax = 635000000

----------------------------------

The server works fine most of the time, though occasionally has trouble and my application fails

When I log into the machine I see that Postgresql has closed and  the database wont restart.

Thus when I log in to the machine and try to restart postgresql I get this message:


A2006:/home/wustl# /etc/init.d/postgresql-7.4 start
Starting PostgreSQL 7.4 database server: main* The PostgreSQL server failed to start. Please check the log output:
2009-07-06 09:37:56 [1251] FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=5432001, size=407429120, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap
space.To reduce the request size (currently 407429120 bytes). 
        The PostgreSQL documentation contains more information about shared memory configuration.
 failed!

Thus interactive restarting the database seems not to work at that point.

However I can easily restart the postgresql database system by rebooting the server itself. Then postgresql will start
easilyand all is fine. 

Question 1:

Is there something I can do besides rebooting the whole server to reset the memory so that postgreql will start up
again?What does rebooting do? 


Question 2:

I would prefer to do the minimal changes to preserve the working of this system, and avoid this recurrent problem.
Might changing the paramaters help? I would prefer not to have to
add memory to this older system, as I might have trouble matching the memory and the server is far from where I am etc.
If I do need to add memory, what should I set the parameters to?



Question 3:
I run the vacuum program 4 times a day from a cron job.

30 1 * * * vacuumdb --analyze LTA_IDB
30 12 * * * vacuumdb --analyze LTA_IDB
30 17 * * * vacuumdb --analyze LTA_IDB
30 20 * * * vacuumdb --analyze LTA_IDB

where LTA_IDB is the large database table mentioned above.

Is this neccessary for a system that is no longer is storing new information? What does it accomplish? Does it tune it
morefor the queries?  

Thank you so much!

Mitchell

В списке pgsql-admin по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Make the primary key a multilingual value
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Questions on setup and usage