Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 652FF16C5E91 for ; Sat, 31 Mar 2012 23:20:21 -0300 (ADT) Received: from nm30.bullet.mail.bf1.yahoo.com ([98.139.212.189]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SEAOv-00044L-Gr for pgsql-performance@postgresql.org; Sun, 01 Apr 2012 02:20:20 +0000 Received: from [98.139.215.142] by nm30.bullet.mail.bf1.yahoo.com with NNFMP; 01 Apr 2012 02:20:03 -0000 Received: from [98.139.212.249] by tm13.bullet.mail.bf1.yahoo.com with NNFMP; 01 Apr 2012 02:20:03 -0000 Received: from [127.0.0.1] by omp1058.mail.bf1.yahoo.com with NNFMP; 01 Apr 2012 02:20:03 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 925440.81224.bm@omp1058.mail.bf1.yahoo.com Received: (qmail 1847 invoked by uid 60001); 1 Apr 2012 02:20:03 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1333246803; bh=t/uXHCCyoRzVJkfiJFenPQoBykBXDaRRBCTrLYoUbpA=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=TddetiXBk2juhCiQnp01XcmFvboiPW54vjSd0MA0V69qj6TIkkxTVqd37mQ4yqtGFqnNiaMKkP6aW70nsshpJzYUjk9IxPvq87EoZZXbfYubexTFLJOps/b4XEpkDhdOzdvQwIy3vCpWitpWArM3r7ykf6SHd8hzdJMF8eeifV0= 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=iuh6xaDtNI7Bvzw71gz2FXBv5B/Z4TKQA71ErwVCAibxx/i0/b8/syCvVEce6ExCa571T0ieMelKuYqXfDey43j2TAoiabqYCzZRFZSrRJ3ymoCy/6OeRaz2UlKQ+2AuFLHS+mCr8H3qvUibLgB4saBEohTFbE2ubBgjYjklhU0=; X-YMail-OSG: LGWr2fEVM1m8WRhL6UhGHYwIeGBY7k8ecEaKG2we7Q_qVbv fwxSqMco3kqASF5ZuDBmMXFh.BAvuTh5E8HyDpUI8KtTDY3yNAFOj0pEIRIs GoBvVbM_aJO1kACr8wtGraVMb_nfi3qJpED0GP2d2_2Y0Y9Kqs23vjmdxOUQ uaVYIhLBNxGjrpNf3nLkeP2e63iryKEKyuQa4fOO7nLZ_c_0XBoifMRkd4AR S3obb5CkadUODyYMRoOArzuXF7o34r2zWDmbG2rlet_X.d3gnAgFOpc5c58o ZyfC0R7.Hqo8fjx4ZzWcy4ICJX7wFznKvIXDA_gGQUCNjAoUyRQ5snPES84L 01REIHvO2Oq9Tzy31cUgL4uAsymqcA8e1ItRECo4YeK29fpmYMD5bbOaDiHQ QvrGTIFg5ksgKp2bY7zfm4xM2IX8MkyDYGG4Rp877ElRU67Qjy_HclIAe7.o 6pK1ZabUxaw_0tRL_nx1AFi.MNrnWTemzU0bNOsFc_YCQMaIw Received: from [64.126.29.122] by web162804.mail.bf1.yahoo.com via HTTP; Sat, 31 Mar 2012 19:20:03 PDT X-Mailer: YahooMailWebService/0.8.117.340979 References: <1333049228.99470.YahooMailNeo@web162801.mail.bf1.yahoo.com> <4F7659AC.1000507@fuzzy.cz> Message-ID: <1333246803.96113.YahooMailNeo@web162804.mail.bf1.yahoo.com> Date: Sat, 31 Mar 2012 19:20:03 -0700 (PDT) From: Bob Lunney Reply-To: Bob Lunney Subject: Re: database slowdown while a lot of inserts occur To: Tomas Vondra , "pgsql-performance@postgresql.org" In-Reply-To: <4F7659AC.1000507@fuzzy.cz> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-905771916-1813837693-1333246803=:96113" X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201203/196 X-Sequence-Number: 46646 ---905771916-1813837693-1333246803=:96113 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Tomas,=0A=0AYou are correct. =A0I was assuming that each insert was issued = as an implicit transaction, without the benefit of an explicit BEGIN/COMMIT= batching many of them together, as I've seen countless times in tight loop= s trying to pose as a batch insert.=0A=0ABob=0A=0A=0A=0A___________________= _____________=0A From: Tomas Vondra =0ATo: pgsql-performance@p= ostgresql.org =0ASent: Friday, March 30, 2012 8:11 PM=0ASubject: Re: [PERFO= RM] database slowdown while a lot of inserts occur=0A =0AOn 29.3.2012 21:27= , Bob Lunney wrote:=0A> Lance,=0A> =0A> May small inserts cause frequent fs= yncs.=A0 Is there any way those small=0A> inserts can be batched into some = larger sets of inserts that use copy to=0A> perform the load?=0A=0ANot nece= ssarily - fsync happens at COMMIT time, not when the INSERT is=0Aperformed = (unless each INSERT stands on it's own).=0A=0ATomas=0A=0A-- =0ASent via pgs= ql-performance mailing list (pgsql-performance@postgresql.org)=0ATo make ch= anges to your subscription:=0Ahttp://www.postgresql.org/mailpref/pgsql-perf= ormance ---905771916-1813837693-1333246803=:96113 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Tomas,

You are correct.  I wa= s assuming that each insert was issued as an implicit transaction, without = the benefit of an explicit BEGIN/COMMIT batching many of them together, as = I've seen countless times in tight loops trying to pose as a batch insert.<= /span>

Bob


= From: Tomas Vondra <tv@f= uzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] database slowdown while = a lot of inserts occur

=0AOn 29.3.2012 21:27, Bob Lu= nney wrote:
> Lance,
>
> May small inserts cause frequen= t fsyncs.  Is there any way those small
> inserts can be batched= into some larger sets of inserts that use copy to
> perform the load= ?

Not necessarily - fsync happens at COMMIT time, not when the INSER= T is
performed (unless each INSERT stands on it's own).

Tomas
=
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subs= cription:
http://www.postgresql.org/mailpref/pgsql-performance

---905771916-1813837693-1333246803=:96113--