Re: Postgres DB crashing

From: Richard Huxton <dev(at)archonet(dot)com>
To: bhanu udaya <udayabhanu1984(at)hotmail(dot)com>
Cc: Kevin Grittner <kgrittn(at)mail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, Chris Travers <chris(dot)travers(at)gmail(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>
Subject: Re: Postgres DB crashing
Date: 2013-06-20 10:17:17
Message-ID: 51C2D6AD.2060400@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support 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

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Laurent Blume 2013-06-20 11:59:34 _T() macro won't build on Solaris
Previous Message Dionis Argiri 2013-06-20 06:55:05 Crash on MacOS X 10.8.2. PgAdmin v. 1.16.1

Browse pgsql-general by date

  From Date Subject
Next Message Amit Langote 2013-06-20 11:32:48 Re: Archiving and recovering pg_stat_tmp
Previous Message Sameer Thakur 2013-06-20 09:05:19 Re: Archiving and recovering pg_stat_tmp