Re: postgres crashes on insert in 40 different threads

>> Dzmitry wrote:
>>> On 8/19/13 11:36 AM, "Stéphane Schildknecht"
>>><stephane.schildknecht@postgresql.fr> wrote:
>>>> Le 19/08/2013 10:07, Dzmitry a écrit :
>>>>>  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
>>[...]
>>>>>
>>>>> My DB size is not very big ­ 169GB.
>>>>>
>>>>> Anyone know how can I get rid of DB crash  ?
>>
>>>> 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.
>>
>>> I don't think it's the case. I am using newrelic for monitoring my DB
>>> servers(I have one master and 2 slaves - all use the same
>>>configuration) -
>>> memory is not going above 12.5GB, so I have a good reserve, also I don't
>>> see any swapping there :(
>>
>> You can check by examining /var/log/messages to see if the OOM
>> killer is at fault, which is highly likely.
>>
>> The OOM killer uses heuristics, so it does the wrong thing occasionally.
>>
>> The documentation is helpful:
>> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

> Do you mean postgres log file(in postgres.conf)
> 
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_min_messages = warning
> 
> Or /var/log/messages ? Because I haven't this file :(

I meant the latter.
/var/log/messages is just where syslog output is directed on some
Linux distributions.  I don't know Ubuntu, so sorry if I got
it wrong.  Maybe it is /var/log/syslog on Ubuntu.
In case of doubt check your syslog configuration.

Yours,
Laurenz Albe