Horton

Re: Postgres DB crashing

Richard Huxton <dev@archonet.com>

View thread View on postgr.es
From: Richard Huxton <dev@archonet.com>
To: bhanu udaya <udayabhanu1984@hotmail.com>
Cc: Kevin Grittner <kgrittn@mail.com>, Adrian Klaver <adrian.klaver@gmail.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>, "pgadmin-support@postgresql.org" <pgadmin-support@postgresql.org>, Albe Laurenz <laurenz.albe@wien.gv.at>, Chris Travers <chris.travers@gmail.com>, Magnus Hagander <magnus@hagander.net>
Date: 2013-06-20T10:17:17Z
Lists: pgsql-general 
On 18/06/13 18:31, bhanu udaya wrote:
> Hello,
> Greetings.
>
> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
> postgressql is crashing when simulatenously 800 to 1000 threads are run
> on a 10 million records schema. Not sure, if we have to tweak some more
> parameters of postgres. Currently, the postgressql is configured as
> below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
> limitation to support only 800 threads or any other configuration
> required. Please look at the log as below with errors. Please reply
>
>
> max_connections	5000
> shared_buffers	2024 MB
> synchronous_commit 	off
> wal_buffers 	100 MB
> wal_writer_delays 	1000ms
> checkpoint_segments 	512
> checkpoint_timeout 	5 min
> checkpoint_completion_target 	0.5
> checkpoint_warning 	30s
> work_memory 	1G
> effective_cache_size 	5 GB

Just to point out, your memory settings are set to allow *at least*

  shared-buffers 2GB + (5000 * 1GB) = 5TB+

You don't have that much memory. You probably don't have that much disk. 
This is never going to work.

As has been said, there's no way you can do useful work simultaneously 
with 1000 threads if you only have 4 cores - use a connection pooler. 
You'll also need to reduce work_mem to 1MB or so.

-- 
   Richard Huxton
   Archonet Ltd