From: Stéphane Schildknecht <stephane.schildknecht@postgresql.fr>
To: pgsql-admin@postgresql.org
Subject: Re: postgres crashes on insert in 40 different threads
Date: Mon, 19 Aug 2013 10:36:39 +0200
Message-ID: <5211D917.5060805@postgresql.fr> (raw)
In-Reply-To: <CE37ACE0.116CA%dzmitry.nikitsin@gmail.com>
References: <CE37ACE0.116CA%dzmitry.nikitsin@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Le 19/08/2013 10:07, Dzmitry a écrit :
> Hey folks,
> I have postgres server running on ubuntu 12,Intel Xeon 8 CPUs 29 GB RAM.
> With following settings:
> max_connections = 550
> shared_buffers = 12GB
> temp_buffers = 8MB
> max_prepared_transactions = 0
> work_mem = 50MB
> maintenance_work_mem = 1GB
> fsync = on
> wal_buffers = 16MB
> commit_delay = 50
> commit_siblings = 7
> checkpoint_segments = 32
> checkpoint_completion_target = 0.9
> effective_cache_size = 22GB
> autovacuum = on
> autovacuum_vacuum_threshold = 1800
> autovacuum_analyze_threshold = 900
>
> I am doing a lot of writes to DB in 40 different threads – so every thread
> check if record exists – if not => insert record, if exists => update record.
> During this update, my disk IO almost always – 100% and sometimes it crash my
> DB with following message:
>
> 2013-08-19 03:18:00 UTC LOG: checkpointer process (PID 28354) was terminated
> by signal 9: Killed
> 2013-08-19 03:18:00 UTC LOG: terminating any other active server processes
> 2013-08-19 03:18:00 UTC WARNING: terminating connection because of crash of
> another server process
> 2013-08-19 03:18:00 UTC DETAIL: The postmaster has commanded this server
> process to roll back the current transaction and exit, because another server
> process exited abnormally and possibly corrupted shared memory.
> 2013-08-19 03:18:00 UTC HINT: In a moment you should be able to reconnect to
> the database and repeat your command.
> 2013-08-19 03:18:00 UTC WARNING: terminating connection because of crash of
> another server process
> 2013-08-19 03:18:00 UTC DETAIL: The postmaster has commanded this server
> process to roll back the current transaction and exit, because another server
> process exited abnormally and possibly corrupted shared memory.
> 2013-08-19 03:18:00 UTC HINT: In a moment you should be able to reconnect to
> the database and repeat your command.
> 2013-08-19 03:18:00 UTC WARNING: terminating connection because of crash of
> another server process
> 2013-08-19 03:18:00 UTC DETAIL: The postmaster has commanded this server
> process to roll back the current transaction and exit, because another server
> process exited abnormally and possibly corrupted shared memory.
>
> My DB size is not very big – 169GB.
>
> Anyone know how can I get rid of DB crash ?
>
>
> Thanks,
> Dzmitry
>
The fact that the checkpointer was killed -9 let me think the OOMKiller has
detected you were out of memory.
Could that be the case?
12GB of shared_buffers on a 29Gb box is too high. You should try to lower that
value to 6GB, for instance.
550*50MB, that is 27GB of RAM that PostgreSQL could try to adress.
I can imagine your system is swapping a lot, and you exhaust swap memory before
crash.
Regards,
--
Stéphane Schildknecht
Loxodata - Conseil, expertise et formations
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: stephane.schildknecht@postgresql.fr
Subject: Re: postgres crashes on insert in 40 different threads
In-Reply-To: <5211D917.5060805@postgresql.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox