5

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?

Dan Loewenherz
  • 913
  • 3
  • 10
  • 19
  • restart the server, that should free any used shared memory, than start the postgres – Troydm Apr 17 '11 at 09: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. – Dan Loewenherz Apr 17 '11 at 17:23

3 Answers3

4

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.

janneb
  • 3,841
  • 19
  • 22
1

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.

Phil Hollenback
  • 14,947
  • 4
  • 35
  • 52
  • 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?) – Dan Loewenherz Apr 17 '11 at 17:22
1

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.

Grzegorz Szpetkowski
  • 1,108
  • 7
  • 10
  • I only have one Postgres server running, it is not a cluster. – Dan Loewenherz Apr 25 '11 at 23:58
  • http://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 00: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. – Dan Loewenherz Apr 26 '11 at 00:14