Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBKUn-0007De-4u for pgsql-admin@arkaria.postgresql.org; Mon, 19 Aug 2013 08:07:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VBKUl-0003C8-VJ for pgsql-admin@arkaria.postgresql.org; Mon, 19 Aug 2013 08:07:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBKUk-0003C1-Q4 for pgsql-admin@postgresql.org; Mon, 19 Aug 2013 08:07:22 +0000 Received: from mail-qe0-x230.google.com ([2607:f8b0:400d:c02::230]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBKUh-00009L-JG for pgsql-admin@postgresql.org; Mon, 19 Aug 2013 08:07:22 +0000 Received: by mail-qe0-f48.google.com with SMTP id 3so758807qea.21 for ; Mon, 19 Aug 2013 01:07:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=user-agent:date:subject:from:to:message-id:thread-topic :mime-version:content-type; bh=DViontjZJrTTQB6ShxybBjflO0oeO3irE8EyyC9qRXo=; b=f+m2dGfogM/UHzjEhcE9HOzP0SwswgSxrVqpCzluXdNPBNmBBqqo/RTcrnNcAzMmS4 YEaPx1D4zmxDm4efk+UXvqzMbudUT/EHVlcMDTqvyVdX+kFtsWa0xVNHiqgW96QlzM42 4fGi5ic9TVa9SHj80E+O9wguPs+qI6J8k5nFGC9pgcqiJB6LVhHpTL1Htty36Hn3stXF wg47gK03g/RgqCpAAPC0fzw2PgPqHfIgqjHxr4n1onhbY+nxiJmlRmIhpclWLIxgOTYk ngm6LNDYt+CfCDJ3ZhODm8atjjUOEa40AwUx6g/j7s78i2ktSRzrIagrFRCZE8gszfAI T5CA== X-Received: by 10.49.25.167 with SMTP id d7mr13591935qeg.35.1376899638458; Mon, 19 Aug 2013 01:07:18 -0700 (PDT) Received: from [10.8.1.62] (40.166.255.173.client.static.strong-in91.as13926.net. [173.255.166.40]) by mx.google.com with ESMTPSA id w8sm14945843qej.3.1969.12.31.16.00.00 (version=TLSv1 cipher=RC4-SHA bits=128/128); Mon, 19 Aug 2013 01:07:18 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.2.3.120616 Date: Mon, 19 Aug 2013 11:07:12 +0300 Subject: postgres crashes on insert in 40 different threads From: Dzmitry To: Message-ID: Thread-Topic: postgres crashes on insert in 40 different threads Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3459755240_18112897" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3459755240_18112897 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Hey folks,=20 I have postgres server running on ubuntu 12, Intel Xeon 8 CPUs 29 GB RAM. With following settings: max_connections =3D 550 shared_buffers =3D 12GB temp_buffers =3D 8MB max_prepared_transactions =3D 0 work_mem =3D 50MB maintenance_work_mem =3D 1GB fsync =3D on wal_buffers =3D 16MB commit_delay =3D 50 =20 commit_siblings =3D 7 checkpoint_segments =3D 32 checkpoint_completion_target =3D 0.9 effective_cache_size =3D 22GB autovacuum =3D on autovacuum_vacuum_threshold =3D 1800 autovacuum_analyze_threshold =3D 900 I am doing a lot of writes to DB in 40 different threads =AD so every thread check if record exists =AD if not =3D> insert record, if exists =3D> update record.=20 During this update, my disk IO almost always =AD 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 o= f 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 o= f 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 o= f 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 =AD 169GB. Anyone know how can I get rid of DB crash ? Thanks, Dzmitry --B_3459755240_18112897 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable
Hey folks, 
 I have postgres server running on ubuntu 12, Intel Xeon 8 CPUs 2= 9 GB RAM. 
With following setti= ngs:
max_connections =3D 550=
shared_buffers =3D 12GB
temp_buffers =3D 8MB
ma= x_prepared_transactions =3D 0
work_mem =3D 5= 0MB
maintenance_work_mem =3D 1GB
fsync =3D on
wa= l_buffers =3D 16MB
commit_delay =3D 50  = ;
commit_siblings =3D 7
checkpoint_segments =3D 32
checkpoint_completion_target =3D 0.9
ef= fective_cache_size =3D 22GB
autovacuum =3D o= n
autovacuum_vacuum_threshold =3D 1800
autovacuum_analyze_threshold =3D 900

I am do= ing a lot of writes to DB in 40 different threads – so every thread ch= eck if record exists – if not =3D> insert record, if exists =3D> upd= ate record. 
During this update, my= disk IO almost always – 100% and sometimes it crash my DB with follow= ing message: 

2013-08-19 03:18:00 UTC LOG:  checkpointer pro= cess (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 cr= ash of another server process
2013-08-19 03:18:00 UTC DETAIL: &nbs= p;The postmaster has commanded this server process to roll back the current = transaction and exit, because another server process exited abnormally and p= ossibly corrupted shared memory.
2013-08-19 03:18:00 UTC HINT: &nb= sp;In a moment you should be able to reconnect to the database and repeat yo= ur command.
2013-08-19 03:18:00 UTC WARNING:  terminating con= nection because of crash of another server process
2013-08-19 03:1= 8:00 UTC DETAIL:  The postmaster has commanded this server process to r= oll back the current transaction and exit, because another server process ex= ited abnormally and possibly corrupted shared memory.
2013-08-19 0= 3:18:00 UTC HINT:  In a moment you should be able to reconnect to the d= atabase 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 thi= s server process to roll back the current transaction and exit, because anot= her server process exited abnormally and possibly corrupted shared memory.

My DB size is not very big – 169GB.&nb= sp;

Anyone know how can I get rid of DB crash  = ;? 


Thanks,
  Dzmitry

--B_3459755240_18112897--