Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D0A202FC10A for ; Thu, 29 Mar 2012 16:27:26 -0300 (ADT) Received: from nm26-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.74]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SDL0E-0006hx-5U for pgsql-performance@postgresql.org; Thu, 29 Mar 2012 19:27:26 +0000 Received: from [98.139.215.141] by nm26.bullet.mail.bf1.yahoo.com with NNFMP; 29 Mar 2012 19:27:09 -0000 Received: from [98.139.212.200] by tm12.bullet.mail.bf1.yahoo.com with NNFMP; 29 Mar 2012 19:27:08 -0000 Received: from [127.0.0.1] by omp1009.mail.bf1.yahoo.com with NNFMP; 29 Mar 2012 19:27:08 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 860442.1627.bm@omp1009.mail.bf1.yahoo.com Received: (qmail 27893 invoked by uid 60001); 29 Mar 2012 19:27:08 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1333049228; bh=3HI6rEafeT1mJDxwVl843votYU+n2D22PpzjW+IHWSo=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=StUrmXcEnpUmscurRvnOvh7hOt3HkOyrNRP8NI4Qcip9ByIpIcSx7opPDAWQaDmWmdfkj1to90/kJY+kpJ4DUlH8zSo04FBwLOkQTx0PJWUX6PsRFzh2TryR92KcgKHbSYSN3mbpnrdX72XKlPnW5rED+WQcZEkNeI+mfR7d4ug= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=FxoCmDhPX/VzQyD31oaXYiYO6kKqWJ1g/WhSiuqMDzKlfef4VmEeAF7RYzCgWIYnMOVxe1Z7wWZc9Zo8vVqSnxWQSB/VL6VXrSJ9k+2KtyQjgjFnjakOz/KTShz1nDqWCU0x4tEnAcOLyXu3axEUgEn+mcyp8PK8MgJVMBSr50c=; X-YMail-OSG: 6BeXi.4VM1kapQxCJ0ylwO55znrIsDEqaO8zMC4_Wd9EE6o zqHuvWjrBPnORDCVdl.rXCJknekXY3iJyznmAiqqUdfaY6ltCAOcrkYz0VTY unCMm8U5yDEL8CgfVjLIvD9Vp2IsoA7IdzogoeEt4wE93XpisdrAws0W3YVD uVEJfuk9T6kXPvAX5vr_7O.yW5MrGdbVQSvWydckSgbgpuD3L9LGAOu0FyUy C4AyVx5XGEJsTgIG5M8tWLcYVmSgfCRCOOQIPuGeGIRFMpLAeyC99iL7BnY2 CSXtlz3wUuPyOZ3Gp_5d.6wIgsPTM.M1ibolv7aSGqDiJJCtz7Z4LHdQF3GZ B7_SirmZ4Fyibz.hpaY31jQaP6w4gfrBarGLKgIJlapTiHCaGU7VAwOMpK8N mn2U_PiyY4l8HoottmsRaEQa779ebKaDK8s3VuzGCtDnxuCqBeufdNIx4qhn JtkZKlr4A3BBENDvbDAJ1lD.ARMgF4fAp4ZE3G9dpOJgBgIZ1 Received: from [64.126.29.122] by web162801.mail.bf1.yahoo.com via HTTP; Thu, 29 Mar 2012 12:27:08 PDT X-Mailer: YahooMailWebService/0.8.117.340979 References: Message-ID: <1333049228.99470.YahooMailNeo@web162801.mail.bf1.yahoo.com> Date: Thu, 29 Mar 2012 12:27:08 -0700 (PDT) From: Bob Lunney Reply-To: Bob Lunney Subject: Re: database slowdown while a lot of inserts occur To: "Campbell, Lance" , "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="2065536479-1647390001-1333049228=:99470" X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201203/181 X-Sequence-Number: 46631 --2065536479-1647390001-1333049228=:99470 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Lance,=0A=0AMay small inserts cause frequent fsyncs. =A0Is there any way th= ose small inserts can be batched into some larger sets of inserts that use = copy to perform the load?=0A=0ABob Lunney=0A=0A=0A_________________________= _______=0A From: "Campbell, Lance" =0ATo: "Campbell, La= nce" ; "pgsql-performance@postgresql.org" =0ASent: Thursday, March 29, 2012 1:02 PM=0ASubject: = Re: [PERFORM] database slowdown while a lot of inserts occur=0A =0A=0A =0AI= forgot to mention that the slowdown in particular for other applications i= s when they are trying to insert or update tables unrelated to the applicat= ion mentioned in my prior application that does the massive small inserts.= =0A=A0=0A=A0=0AThanks,=0A=A0=0ALance Campbell=0ASoftware Architect=0AWeb Se= rvices at Public Affairs=0A217-333-0382=0A=A0=0AFrom:pgsql-performance-owne= r@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf = Of Campbell, Lance=0ASent: Thursday, March 29, 2012 12:59 PM=0ATo: pgsql-pe= rformance@postgresql.org=0ASubject: [PERFORM] database slowdown while a lot= of inserts occur=0A=A0=0APostgreSQL 9.0.x=0AWe have around ten different a= pplications that use the same database.=A0 When one particular application = is active it does an enormous number of inserts.=A0 Each insert is very sma= ll.=A0 During this time the database seems to slow down in general.=A0 The = application in question is inserting into a particular table that is not us= ed by the other applications.=0A=A0=0A1)=A0=A0=A0=A0=A0 What should I do to= confirm that the database is the issue and not the applications?=0A2)=A0= =A0=A0=A0=A0 How can I identify where the bottle neck is occurring if the i= ssue happens to be with the database?=0A=A0=0AI have been using PostgreSQL = for eight years.=A0 It is an amazing database.=0A=A0=0AThanks,=0A=A0=0ALanc= e Campbell=0ASoftware Architect=0AWeb Services at Public Affairs=0A217-333-= 0382 --2065536479-1647390001-1333049228=:99470 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Lance,

May small inserts cause frequent fsync= s.  Is there any way those small inserts can be batched into some larg= er sets of inserts that use copy to perform the load?

Bob Lunney

From: "Campbell, L= ance" <lance@illinois.edu>
= To: "Campbell, Lance" <lance@illinois.edu>; "pgsql-perform= ance@postgresql.org" <pgsql-performance@postgresql.org>
Sent: Thursday, March 29, 2012 1:0= 2 PM
Subject: Re: [PER= FORM] database slowdown while a lot of inserts occur
=0A
=0A=0A =0A =0A=0A=0A
=0A
=0A
I forgot to mention that the slowdown in particular for other applications= is when they are trying to insert or update tables unrelated to the applic= ation mentioned in my prior application that does the massive=0A small inse= rts.
=0A
 
=0A
=0A
 
=0A
Thanks, =0A
=  
=0A
Lance Campbell
=0A
Software Architect
= =0A
Web = Services at Public Affairs
=0A
217-333-0382
=0A
=0A<= div class=3D"yiv672164638MsoNormal">  <= /span>
=0A
=0A
=0A
<= b>From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performanc= e-owner@postgresql.org]=0AOn Behalf Of Campbell, Lance
=0ASent= : Thursday, March 29, 2012 12:59 PM
=0ATo: pgsql-performance@= postgresql.org
=0ASubject: [PERFORM] database slowdown while a lo= t of inserts occur
=0A
=0A
=0A
 
=0A
Postgr= eSQL 9.0.x
=0A
We have around ten= different applications that use the same database.  When one particul= ar application is active it does an enormous number of inserts.  Each = insert is very small.  During this time the database seems to slow dow= n in general. =0A The application in question is inserting into a part= icular table that is not used by the other applications.
=0A
 
=0A
1)&nb= sp;    =0AWhat should I do to confirm tha= t the database is the issue and not the applications?
=0A
2)     =0AHow can I i= dentify where the bottle neck is occurring if the issue happens to be with = the database?
=0A
 
= =0A
I have been using PostgreSQL for ei= ght years.  It is an amazing database.
=0A
 
=0A
Thanks= ,
=0A
 
=0A
Lance Campbell
=0A
Software Architect
=0A
Web Services at Public Affairs
=0A
217-333-0382
=0A
  =0A
=0A
=0A=0A


--2065536479-1647390001-1333049228=:99470--