Thread: Help me stop postgres from crashing.

Help me stop postgres from crashing.

From
Sam
Date:
Hi,

I am a web developer, I've been using postgesql for a few years but
administratively I am a novice.

A particular web application I am working has a staging version
running one a vps, and a production version running on another vps.
They both get about the same usage, but the production version keeps
crashing and has to be re-started daily for the last couple days.  The
log file at the time of crash looks like this:

LOG:  could not accept new connection: Cannot allocate memory
LOG:  select() failed in postmaster: Cannot allocate memory
FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
LOG:  logger shutting down
LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT

It ran out of memory.

I am looking for a way to track down what is actually causing the
memory shortage and how to prevent it or increase the memory
available.

The vps in question is a media temple DV running CentOS and postgres
8.1.18

Thanks.

Re: Help me stop postgres from crashing.

From
Thom Brown
Date:
On 24 April 2010 18:48, Sam <sam@palo-verde.us> wrote:
Hi,

I am a web developer, I've been using postgesql for a few years but
administratively I am a novice.

A particular web application I am working has a staging version
running one a vps, and a production version running on another vps.
They both get about the same usage, but the production version keeps
crashing and has to be re-started daily for the last couple days.  The
log file at the time of crash looks like this:

LOG:  could not accept new connection: Cannot allocate memory
LOG:  select() failed in postmaster: Cannot allocate memory
FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
LOG:  logger shutting down
LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT

It ran out of memory.

I am looking for a way to track down what is actually causing the
memory shortage and how to prevent it or increase the memory
available.

The vps in question is a media temple DV running CentOS and postgres
8.1.18


Could you provide some more information?  What do you get if you run "sysctl -a | grep kernel.shm" and "sysctl -a | grep sem"? And what are you developing in which connects to the database?  Are you using persistent connections?  And how many connections to you estimate are in use?  What have you got max_connections and shared_buffers in your postgresql.conf file?  And how much memory does your VPS have?

Thom

Re: Help me stop postgres from crashing.

From
Tom Lane
Date:
Sam <sam@palo-verde.us> writes:
> A particular web application I am working has a staging version
> running one a vps, and a production version running on another vps.
> They both get about the same usage, but the production version keeps
> crashing and has to be re-started daily for the last couple days.  The
> log file at the time of crash looks like this:

> LOG:  could not accept new connection: Cannot allocate memory
> LOG:  select() failed in postmaster: Cannot allocate memory

This looks like a system-level memory shortage.  You might find useful
information in the kernel log.  I'd suggest enabling timestamps in the
PG log (see log_line_prefix) so that you can correlate events in the
two log files.

            regards, tom lane

Re: Help me stop postgres from crashing.

From
Samuel
Date:
On Apr 24, 4:13 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Sam <s...@palo-verde.us> writes:
> > A particular web application I am working has a staging version
> > running one a vps, and a production version running on another vps.
> > They both get about the same usage, but the production version keeps
> > crashing and has to be re-started daily for the last couple days.  The
> > log file at the time of crash looks like this:
> > LOG:  could not accept new connection: Cannot allocate memory
> > LOG:  select() failed in postmaster: Cannot allocate memory
>
> This looks like a system-level memory shortage.  You might find useful
> information in the kernel log.  I'd suggest enabling timestamps in the
> PG log (see log_line_prefix) so that you can correlate events in the
> two log files.
>
>                         regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Thanks, for the responses.

I've enabled the timestamps on the log lines.


Re: Help me stop postgres from crashing.

From
Samuel
Date:
On Apr 24, 3:53 pm, thombr...@gmail.com (Thom Brown) wrote:
> On 24 April 2010 18:48, Sam <s...@palo-verde.us> wrote:
>
>
>
>
>
> > Hi,
>
> > I am a web developer, I've been using postgesql for a few years but
> > administratively I am a novice.
>
> > A particular web application I am working has a staging version
> > running one a vps, and a production version running on another vps.
> > They both get about the same usage, but the production version keeps
> > crashing and has to be re-started daily for the last couple days.  The
> > log file at the time of crash looks like this:
>
> > LOG:  could not accept new connection: Cannot allocate memory
> > LOG:  select() failed in postmaster: Cannot allocate memory
> > FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
> > LOG:  logger shutting down
> > LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT
>
> > It ran out of memory.
>
> > I am looking for a way to track down what is actually causing the
> > memory shortage and how to prevent it or increase the memory
> > available.
>
> > The vps in question is a media temple DV running CentOS and postgres
> > 8.1.18
>
> > Could you provide some more information?  What do you get if you run
>
> "sysctl -a | grep kernel.shm" and "sysctl -a | grep sem"? And what are you
> developing in which connects to the database?  Are you using persistent
> connections?  And how many connections to you estimate are in use?  What
> have you got max_connections and shared_buffers in your postgresql.conf
> file?  And how much memory does your VPS have?
>
> Thom

This application is php5/Zend Framework and using Doctrine ORM which
manages the database connections, but they aren't persistent.

max_connections is 100 and shared_buffers is 1000

What is the best way to profile the exact number of connections (for
future reference)?  Right now, there is almost no site usage, the site
is not launched yet.  The staging version of the site has been running
for months without issues.  There is a cron that runs every three
minutes and checks a users account on another web service and tracks
that activity.

Re: Help me stop postgres from crashing.

From
Samuel
Date:
On Apr 24, 3:53 pm, thombr...@gmail.com (Thom Brown) wrote:
> On 24 April 2010 18:48, Sam <s...@palo-verde.us> wrote:
>
>
>
>
>
> > Hi,
>
> > I am a web developer, I've been using postgesql for a few years but
> > administratively I am a novice.
>
> > A particular web application I am working has a staging version
> > running one a vps, and a production version running on another vps.
> > They both get about the same usage, but the production version keeps
> > crashing and has to be re-started daily for the last couple days.  The
> > log file at the time of crash looks like this:
>
> > LOG:  could not accept new connection: Cannot allocate memory
> > LOG:  select() failed in postmaster: Cannot allocate memory
> > FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
> > LOG:  logger shutting down
> > LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT
>
> > It ran out of memory.
>
> > I am looking for a way to track down what is actually causing the
> > memory shortage and how to prevent it or increase the memory
> > available.
>
> > The vps in question is a media temple DV running CentOS and postgres
> > 8.1.18
>
> > Could you provide some more information?  What do you get if you run
>
> "sysctl -a | grep kernel.shm" and "sysctl -a | grep sem"? And what are you
> developing in which connects to the database?  Are you using persistent
> connections?  And how many connections to you estimate are in use?  What
> have you got max_connections and shared_buffers in your postgresql.conf
> file?  And how much memory does your VPS have?
>
> Thom

sysctl -a | grep kernel.shm
error: "Operation not permitted" reading key "kernel.cap-bound"
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 33554432

sysctl -a | grep sem
error: "Operation not permitted" reading key "kernel.cap-bound"
kernel.sem = 250    32000    32    128