Kernel kills postgres process - help need

On Wed, 2008-01-09 at 22:57 +0100, Hervé Piedvache wrote:

Hi,

I have a big trouble with a PostgreSQL server ... regulary since I have added
8 Gb of memory, on a server having already 8Gb of memory, I have troubles.
Nothing else have changed ... I'm on a Dell server, and all the memory
diagnostics from Dell seems to be good ...
When I have a lot of connexions (persistante connexions from 6 web apache/php
serveurs using PDO, about 110 process on each web servers) on the server, or
long request, it's difficult for me to know when it's appening, the kernel
seems to kill my postgresql process then the server become completly
instable, and most of the time need a reboot ...

I'm on Linux kernel 2.6.15 with a version 8.1.10 of PostgreSQL.
My database is a size of 56G
RAM = 16 Gb

[snip]

Jan 9 20:30:47 db2 kernel: oom-killer: gfp_mask=0x84d0, order=0

It looks like the Out Of Memory Killer was invoked, and you need to find
out why it was invoked.

I posted to LKML here:

http://kerneltrap.org/mailarchive/linux-kernel/2007/2/12/54202

because linux has a behavior -- which in my opinion is a bug -- that
causes the OOM killer to almost always kill PostgreSQL first, regardless
of whether it was truly the offending process or not.

So, find out which process truly caused the memory pressure that lead to
the OOM being invoked, and fix that problem.

You may also consider some other linux configuration options that make
invocation of OOM killer less likely.

Regards,
Jeff Davis

=?utf-8?q?Herv=C3=A9_Piedvache?= <bill.footcow@gmail.com> writes:

When I have a lot of connexions (persistante connexions from 6 web apache/php
serveurs using PDO, about 110 process on each web servers) on the server, or
long request, it's difficult for me to know when it's appening, the kernel
seems to kill my postgresql process then the server become completly
instable, and most of the time need a reboot ...

Turn off memory overcommit.

max_connections = 2048

Have you considered using a connection pooler in front of a smaller
number of backends?

If you really need that many backends, it'd likely be a good idea to
reduce max_files_per_process to perhaps 100 or so. If you manage
to run the kernel out of filetable slots, all sorts of userland stuff
is going to get very unhappy.

regards, tom lane

Tom,

Le mercredi 09 janvier 2008, Tom Lane a écrit :

=?utf-8?q?Herv=C3=A9_Piedvache?= <bill.footcow@gmail.com> writes:

When I have a lot of connexions (persistante connexions from 6 web
apache/php serveurs using PDO, about 110 process on each web servers) on
the server, or long request, it's difficult for me to know when it's
appening, the kernel seems to kill my postgresql process then the server
become completly instable, and most of the time need a reboot ...

Turn off memory overcommit.

My sysctl.conf file looks like this :
kernel.shmmax= 941604096
kernel.sem = 250 32000 100 400
fs.file-max=655360
vm.overcommit_memory=2
vm.overcommit_ratio=30

max_connections = 2048

Have you considered using a connection pooler in front of a smaller
number of backends?

Which system do you recommand for this ?

If you really need that many backends, it'd likely be a good idea to
reduce max_files_per_process to perhaps 100 or so. If you manage
to run the kernel out of filetable slots, all sorts of userland stuff
is going to get very unhappy.

I'll try this ...

regards,
--
Hervé

Le mercredi 09 janvier 2008, Jeff Davis a écrit :

On Wed, 2008-01-09 at 22:57 +0100, Hervé Piedvache wrote:

Hi,

I have a big trouble with a PostgreSQL server ... regulary since I have
added 8 Gb of memory, on a server having already 8Gb of memory, I have
troubles. Nothing else have changed ... I'm on a Dell server, and all the
memory diagnostics from Dell seems to be good ...
When I have a lot of connexions (persistante connexions from 6 web
apache/php serveurs using PDO, about 110 process on each web servers) on
the server, or long request, it's difficult for me to know when it's
appening, the kernel seems to kill my postgresql process then the server
become completly instable, and most of the time need a reboot ...

I'm on Linux kernel 2.6.15 with a version 8.1.10 of PostgreSQL.
My database is a size of 56G
RAM = 16 Gb

[snip]

Jan 9 20:30:47 db2 kernel: oom-killer: gfp_mask=0x84d0, order=0

It looks like the Out Of Memory Killer was invoked, and you need to find
out why it was invoked.

I posted to LKML here:

http://kerneltrap.org/mailarchive/linux-kernel/2007/2/12/54202

because linux has a behavior -- which in my opinion is a bug -- that
causes the OOM killer to almost always kill PostgreSQL first, regardless
of whether it was truly the offending process or not.

So, find out which process truly caused the memory pressure that lead to
the OOM being invoked, and fix that problem.

How can I process to find this ? It's a production server for a web service,
and I have no idea how to find wich process was the cause of this ... !?

You may also consider some other linux configuration options that make
invocation of OOM killer less likely.

On this server there is only Postgresql, slony, and sshd running the rest is
only Linux basic process (cron, atd, getty etc.)

regards,
--
Hervé Piedvache

On Jan 9, 2008 3:57 PM, Hervé Piedvache <bill.footcow@gmail.com> wrote:

SNIP

0+0
Jan 9 20:30:48 db2 kernel: Free swap = 15623168kB
Jan 9 20:30:48 db2 kernel: Total swap = 15623172kB
Jan 9 20:30:48 db2 kernel: Free swap: 15623168kB
Jan 9 20:30:48 db2 kernel: oom-killer: gfp_mask=0x84d0, order=0
Jan 9 20:30:48 db2 kernel: Mem-info:
Jan 9 20:30:48 db2 kernel: DMA per-cpu:
Jan 9 20:30:48 db2 postgres[7634]: [2-1] LOG: background writer process (PID
7639) was terminated by signal 9

This makes no sense to me. The OS is showing that there's
16G free swap. Why is it killing things? I'm betting there's some
bug with too large of a swap resulting in some kind of wrap around or
something.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 09 Jan 2008 14:17:14 -0800
Jeff Davis <pgsql@j-davis.com> wrote:

I posted to LKML here:

http://kerneltrap.org/mailarchive/linux-kernel/2007/2/12/54202

because linux has a behavior -- which in my opinion is a bug -- that
causes the OOM killer to almost always kill PostgreSQL first,
regardless of whether it was truly the offending process or not.

If that isn't an argument for FreeBSD I don't know what is...

/linuxpoet

So, find out which process truly caused the memory pressure that lead
to the OOM being invoked, and fix that problem.

You may also consider some other linux configuration options that make
invocation of OOM killer less likely.

Regards,
Jeff Davis

---------------------------(end of
broadcast)--------------------------- TIP 2: Don't 'kill -9' the
postmaster

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHhVcVATb/zqfZUUQRAp0YAJ4ooisf5xRDvXegEl2f/r3TTTB4jACfSuFn
O1MUlow1sg++4zdoh6TGu6Y=
=JMWG
-----END PGP SIGNATURE-----

On Wednesday 09 January 2008 18:21, Joshua D. Drake wrote:

On Wed, 09 Jan 2008 14:17:14 -0800

Jeff Davis <pgsql@j-davis.com> wrote:

I posted to LKML here:

http://kerneltrap.org/mailarchive/linux-kernel/2007/2/12/54202

because linux has a behavior -- which in my opinion is a bug -- that
causes the OOM killer to almost always kill PostgreSQL first,
regardless of whether it was truly the offending process or not.

If that isn't an argument for FreeBSD I don't know what is...

Funny, it looked like an argument for Solaris to me. ;-)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Wed, Jan 09, 2008 at 04:59:45PM -0600, Scott Marlowe wrote:

On Jan 9, 2008 3:57 PM, Hervé Piedvache <bill.footcow@gmail.com> wrote:

Jan 9 20:30:48 db2 kernel: Free swap = 15623168kB
Jan 9 20:30:48 db2 kernel: Total swap = 15623172kB
Jan 9 20:30:48 db2 kernel: Free swap: 15623168kB
Jan 9 20:30:48 db2 kernel: oom-killer: gfp_mask=0x84d0, order=0
Jan 9 20:30:48 db2 kernel: Mem-info:
Jan 9 20:30:48 db2 kernel: DMA per-cpu:
Jan 9 20:30:48 db2 postgres[7634]: [2-1] LOG: background writer process (PID
7639) was terminated by signal 9

This makes no sense to me. The OS is showing that there's
16G free swap. Why is it killing things? I'm betting there's some
bug with too large of a swap resulting in some kind of wrap around or
something.

At a guess it's this:

Jan 9 20:30:48 db2 kernel: DMA32 free:0kB min:0kB low:0kB high:0kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no

Which is why the bgwriter got whacked, it couldn't allocate any memory
for the disk transfer (though why the OOM killer gets invoked here I
don't know). Disabling overcommit won't help you either.

Perhaps a 64-bit architecture? Or a RAID controller that can access
high memory (is this possible?).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Tom,

Le mercredi 9 janvier 2008, Tom Lane a ï¿œcrit :

=?utf-8?q?Herv=C3=A9_Piedvache?= <bill.footcow@gmail.com> writes:

When I have a lot of connexions (persistante connexions from 6 web
apache/php serveurs using PDO, about 110 process on each web servers) on
the server, or long request, it's difficult for me to know when it's
appening, the kernel seems to kill my postgresql process then the server
become completly instable, and most of the time need a reboot ...

Turn off memory overcommit.

max_connections = 2048

Have you considered using a connection pooler in front of a smaller
number of backends?

You never answered me to this point ... we use persistants connections so I
don't understand the interest of using a pooler ...

Otherwise, what pooler do you recommand, and what will be the improvement for
us ?

Thanks,
--
Hervￜ Piedvache

Elma Ingï¿œnierie Informatique
Groupe Maximiles S.A.
3 rue d'Uzï¿œs
F-75002 - Paris - France
Pho. 33-144949901
Fax. 33-144882747

Herv� Piedvache escribi�:

Tom,

Le mercredi 9 janvier 2008, Tom Lane a �crit :

=?utf-8?q?Herv=C3=A9_Piedvache?= <bill.footcow@gmail.com> writes:

When I have a lot of connexions (persistante connexions from 6 web
apache/php serveurs using PDO, about 110 process on each web servers) on
the server, or long request, it's difficult for me to know when it's
appening, the kernel seems to kill my postgresql process then the server
become completly instable, and most of the time need a reboot ...

Have you considered using a connection pooler in front of a smaller
number of backends?

You never answered me to this point ... we use persistants connections so I
don't understand the interest of using a pooler ...

The problem with persistent connections is that they are, well,
persistent -- so they keep resources allocated, which the server cannot
then use for other things. The PHP model of persistent connections is
silly and useless, because each PHP process keeps an open connection (or
more than one, if it connects to different databases), which is then
idle most of the time.

A pooler also keeps the connections open, but they are given in turns to
different PHP processes as they need them. The total number of open
connections to the database server is lower, which leads to resource
wastage being lower.

Otherwise, what pooler do you recommand, and what will be the improvement for
us ?

The two most recommended ones I've seen around here are pgbouncer and
pgpool. I think pgbouncer is supposed to perform better, at the cost of
not having certain bells and whistles (which you may not need anyway).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

The PHP model of persistent connections is silly and useless, because each
PHP process keeps an open connection (or more than one, if it connects to
different databases), which is then idle most of the time.

Some might say that keeping PHP processes around which are idle most of the
time would be silly in itself.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Thanks Alvaro for your answer really clear.

Another, may be stupid question, but when you have several web nodes like
me ... with several physical database (I'm not talking about replication,
it's just that the web node can contact 3 or 4 differents database for
differents applications), what is the best way to process with a pooler ...
install one on each node or one on each database ?

Regards,

Le jeudi 7 fï¿œvrier 2008, Alvaro Herrera a ï¿œcrit :

Hervￜ Piedvache escribiￜ:

Tom,

Le mercredi 9 janvier 2008, Tom Lane a ï¿œcrit :

=?utf-8?q?Herv=C3=A9_Piedvache?= <bill.footcow@gmail.com> writes:

When I have a lot of connexions (persistante connexions from 6 web
apache/php serveurs using PDO, about 110 process on each web servers)
on the server, or long request, it's difficult for me to know when
it's appening, the kernel seems to kill my postgresql process then
the server become completly instable, and most of the time need a
reboot ...

Have you considered using a connection pooler in front of a smaller
number of backends?

You never answered me to this point ... we use persistants connections so
I don't understand the interest of using a pooler ...

The problem with persistent connections is that they are, well,
persistent -- so they keep resources allocated, which the server cannot
then use for other things. The PHP model of persistent connections is
silly and useless, because each PHP process keeps an open connection (or
more than one, if it connects to different databases), which is then
idle most of the time.

A pooler also keeps the connections open, but they are given in turns to
different PHP processes as they need them. The total number of open
connections to the database server is lower, which leads to resource
wastage being lower.

Otherwise, what pooler do you recommand, and what will be the improvement
for us ?

The two most recommended ones I've seen around here are pgbouncer and
pgpool. I think pgbouncer is supposed to perform better, at the cost of
not having certain bells and whistles (which you may not need anyway).

--
Hervￜ Piedvache

Elma Ingï¿œnierie Informatique
Groupe Maximiles S.A.
3 rue d'Uzï¿œs
F-75002 - Paris - France
Pho. 33-144949901
Fax. 33-144882747

Herv� Piedvache escribi�:

Another, may be stupid question, but when you have several web nodes like
me ... with several physical database (I'm not talking about replication,
it's just that the web node can contact 3 or 4 differents database for
differents applications), what is the best way to process with a pooler ...
install one on each node or one on each database ?

I don't really know the answer to this, but if you have one per database
server, then all the web nodes are going to share the connections to
that database server.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support