Re: Postgres DB crashing

On Thu, Jun 20, 2013 at 5:17 AM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> 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.

aside: if you have particular query that needs extra work_mem, you can
always temporarily raise it at run time (unlike shared buffers).

OP needs to explore use of connection pooler, in particular pgbouncer.
Anyways none of this explains why the server is actually crashing.

merlin