Re: Postgres DB crashing

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