Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm running PostgreSQL 8.4.5 on Ubuntu 10.04. I run an EC2 instance with a few sites running on nginx. Most of these sites run on Django and connect to this Postgres instance.

For some reason, at 8:45pm tonight, Postgres went down. I logged into the machine, and I see this error message:

 * Starting PostgreSQL 8.4 database server
 * The PostgreSQL server failed to start. Please check the log output:
2011-04-17 04:46:49 UTC FATAL:  could not create shared memory segment: Cannot allocate memory
2011-04-17 04:46:49 UTC DETAIL:  Failed system call was shmget(key=5432001, size=16211968, 03600).
2011-04-17 04:46:49 UTC 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 16211968 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1792) and/or its max_connections parameter (currently 53).
        The PostgreSQL documentation contains more information about shared memory configuration.
   ...fail!

The first thing I did was change the Linux shared memory allocation.

sysctl -w kernel.shmmax=367108864>
sysctl -p /etc/sysctl.conf

That didn't do it. So then I edited /etc/postgresql/8.4/main/postgresql.conf and lowered the max_connections value. That worked...for about 10 minutes.

Now I'm getting the same error, no matter how low max_connections is. I need to set it to at least 9 (since that's the number of Django projects that need access to this Postgres server).

Any thoughts on how I might go about fixing this?

share|improve this question
    
restart the server, that should free any used shared memory, than start the postgres –  Troydm Apr 17 '11 at 9:11
    
@Troydm the server already went down, so ideally any shared memory should already have been freed. Per @janneb's suggestion I'm going to try to manually delete the SHM segments. –  dlo Apr 17 '11 at 17:23
add comment

3 Answers 3

up vote 4 down vote accepted

You can use the 'ipcs' command to list all the SHM segments. If a program crashes without deleting them, they may be lying around consuming memory; you can remove these manually with the 'ipcrm' command.

share|improve this answer
    
Thanks! I'll try this out and will get back to you. –  dlo Apr 17 '11 at 17:22
add comment

Some googling indicates that you should reduce the postgres shared_buffers and max_connections numbers. have you done anything with shared_buffers yet?

Have you reviewed the postgresql shared memory documentation? Lots of details in there about how to tune the shared memory parameters on a linux machine.

share|improve this answer
    
Yep, I also reduced shared_buffers (although, I'm unsure how much to lower them to). I stopped when it seemed the lowering had gone too low--what's a good benchmark for when to start? I don't want to be running on 0 shared_buffers (may hurt performance?) –  dlo Apr 17 '11 at 17:22
add comment

Can you show more verbose cluster log ? Is postmaster crashing arbitrarily ? Which code it returns to OS ? How many PostgreSQL clusters you have, only 8.4/main ?

What about your kernel.shmall and kernel.shmmni ? Try to use ipcs -ml, ipcs -m and check your memory (free, top, System Monitor) usage . Try to calm down OOM killer:

vm.overcommit_memory = 2
vm.overcommit_ratio = 50

AFAIK sysctl -w does not change params permanently (only until next OS reboot) and you need to add kernel.shmmax=367108864 to /etc/sysctl.conf.

If possible upgrade your PostgreSQL to 8.4.8 as suggested in Versioning policy:

We always recommend that all users run the latest available minor release for whatever major version is in use.

I think this is probably OOM killer issue, because it kills postmaster with SIGKILL signal and without releasing shared memory. Look at documentation:

Important: It is best not to use SIGKILL to shut down the server. Doing so will prevent the server from releasing shared memory and semaphores, which might then have to be done manually before a new server can be started. Furthermore, SIGKILL kills the postgres process without letting it relay the signal to its subprocesses, so it will be necessary to kill the individual subprocesses by hand as well.

and here:

n 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 might terminate the PostgreSQL server (the master server process) if the memory demands of another process cause the system to run out of virtual memory.

BTW max_connections are "cheap". Rather reduce shared_buffers.

share|improve this answer
    
I only have one Postgres server running, it is not a cluster. –  dlo Apr 25 '11 at 23:58
    
people.debian.org/~mpitt/architecture.html One server is a so called "database cluster" (or catalog cluster in SQL standard) created by initdb. –  Grzegorz Szpetkowski Apr 26 '11 at 0:03
    
Interesting...well then, I suppose I am running a cluster. I haven't had any downtime with my above fix, and I am reluctant to change the configuration lest it go down again. I'm trying to see if I can replicate the issue on a non-production database. –  dlo Apr 26 '11 at 0:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.