X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 30E3AD8421 for ; Tue, 13 Sep 2005 00:44:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20729-02 for ; Tue, 13 Sep 2005 03:44:37 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id 98419D8403 for ; Tue, 13 Sep 2005 00:44:35 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 36so2356435wra for ; Mon, 12 Sep 2005 20:44:35 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:references; b=l+gKbYxCLhf+rjN2PV7dhiPUT8Q7aFjHChrp/Mx+cbYY1eyz0wfZOKiOhP3CRr4v74xVqex8zpBpnfp3pG5fN8HcU6LJkVyagYHRDlmOjOgf48Wxa8CcBBXozOX7y5qMv/ZmkIAOSEhb61ZrDYy2fcmBq9OQ+B5UGkBLQoVuqZQ= Received: by 10.54.57.23 with SMTP id f23mr177462wra; Mon, 12 Sep 2005 20:44:35 -0700 (PDT) Received: by 10.54.158.11 with HTTP; Mon, 12 Sep 2005 20:44:35 -0700 (PDT) Message-ID: <84621a605091220442775249b@mail.gmail.com> Date: Mon, 12 Sep 2005 22:44:35 -0500 From: Brandon Black Reply-To: blblack@gmail.com To: Greg Stark , pgsql-performance@postgresql.org Subject: Re: Performance considerations for very heavy INSERT traffic In-Reply-To: <87slw91x8a.fsf@stark.xeocode.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12365_28318204.1126583075426" References: <84621a6050912140464c380c3@mail.gmail.com> <87slw91x8a.fsf@stark.xeocode.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.229 required=5 tests=[AWL=-0.091, HTML_10_20=0.295, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/154 X-Sequence-Number: 14462 ------=_Part_12365_28318204.1126583075426 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12 Sep 2005 23:07:49 -0400, Greg Stark wrote: >=20 > The WAL parameters like commit_delay and commit_siblings are a bit of a > mystery. Nobody has done any extensive testing of them. It would be quite > helpful if you find anything conclusive and post it. It would also be > surprising if they had a very large effect. They almost got chopped=20 > recently > because they weren't believed to be useful. >=20 > You might also ponder whether you need to by issuing a commit for every= =20 > datum. > If you only need to commit periodically you can get much better=20 > throughput. I > suppose that's the same as commit_siblings. It would be interesting to=20 > know if > you can get those parameters to perform as well as batching up records > yourself. Ideally I'd like to commit the data seperately, as the data could contain= =20 errors which abort the transaction, but it may come down to batching it and= =20 coding things such that I can catch and discard the offending row and retry= =20 the transaction if it fails (which should be fairly rare I would hope). I= =20 was hoping that the commit_delay/commit_siblings stuff would allow me to=20 maintain simplistic transaction failure isolation while giving some of the= =20 benefits of batching things up, as you've said. I have seen performance=20 gains with it set at 100ms and a 3-6 siblings with 8 backends running, but = I=20 haven't been able to extensively tune these values, they were mostly random= =20 guesses that seemed to work. My cycles of performance testing take a while,= =20 at least a day or two per change being tested, and the differences can even= =20 then be hard to see due to variability in the testing load (as it's not=20 really a static test load, but a window on reality). On top of that, with= =20 the time it takes, I've succumbed more than once to the temptation of=20 tweaking more than one thing per performance run, which really muddies the= =20 results. > Increasing shared_buffers seems to always help, even out to half of the= =20 > dev > > box's ram (2G). >=20 > Half should be a pessimal setting. It means virtually everything is=20 > buffered > twice. Once in the kernel and once in Postgres. Effectively halving your > memory. If that's really helping try raising it even further, to somethin= g > like 90% of your memory. But the conventional dogma is that shared buffer= s > should never be more than about 10k no matter how much memory you have.= =20 > Let > the kernel do the bulk of the buffering. >=20 > That said it's even more mysterious in your situation. Why would a large > shared buffers help insert-only performance much at all? My guess is that= =20 > it's > letting the vacuums complete quicker. Perhaps try raising work_mem? I find it odd as well. After reading the standard advice on shared_buffers,= =20 I had only intended on raising it slightly. But seeing ever-increasing=20 performance gains, I just kept tuning it upwards all the way to the 2G=20 limit, and saw noticeable gains every time. During at least some of the tes= t=20 cycles, there was no deleting or vacuuming going on, just insert traffic. I= =20 guessed that shared_buffers' caching strategy must have been much more=20 effective than the OS cache at something or other, but I don't know what=20 exactly. The only important read traffic that comes to mind is the index=20 which is both being constantly updated and constantly checked for primary= =20 key uniqueness violations. All of these replies here on the list (and a private one or two) have given= =20 me a long list of things to try, and I feel confident that at least some of= =20 them will gain me enough performance to comfortably deploy this application= =20 in the end on somewhat reasonable hardware. Thanks to everyone here on the= =20 list for all the suggestions, it has been very helpful in giving me=20 directions to go with this that I hadn't thought of before. When I finally get all of this sorted out and working reasonably optimally,= =20 I'll be sure to come back and report what techniques/settings did and didn'= t=20 work for this workload. -- Brandon ------=_Part_12365_28318204.1126583075426 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 12 Sep 2005 23:07:49 -0400, Greg Stark <gsstark@mit.edu> wrote:

The WAL param= eters like commit_delay and commit_siblings are a bit of a
mystery. Nobo= dy has done any extensive testing of them. It would be quite
helpful if you find anything conclusive and post it. It would also besurprising if they had a very large effect. They almost got chopped recen= tly
because they weren't believed to be useful.

You might also po= nder whether you need to by issuing a commit for every datum.
If you only need to commit periodically you can get much better through= put. I
suppose that's the same as commit_siblings. It would be interesti= ng to know if
you can get those parameters to perform as well as batchin= g up records
yourself.

Ideally I'd like to commit the data seperately, as the data could contain errors which abort the transaction, but it may come down to batching it and coding things such that I can catch and discard the offending row and retry the transaction if it fails (which should be fairly rare I would hope).  I was hoping that the commit_delay/commit_siblings stuff would allow me to maintain simplistic transaction failure isolation while giving some of the benefits of batching things up, as you've said.  I have seen performance gains with it set at 100ms and a 3-6 siblings with 8 backends running, but I haven't been able to extensively tune these values, they were mostly random guesses that seemed to work.  My cycles of performance testing take a while, at least a day or two per change being tested, and the differences can even then be hard to see due to variability in the testing load (as it's not really a static test load, but a window on reality).  On top of that, with the time it takes, I've succumbed more than once to the temptation of tweaking more than one thing per performance run, which really muddies the results.

> Increasing s= hared_buffers seems to always help, even out to half of the dev
> box= 's ram (2G).

Half should be a pessimal setting. It means virtually everything is= buffered
twice. Once in the kernel and once in Postgres. Effectively ha= lving your
memory. If that's really helping try raising it even further,= to something
like 90% of your memory. But the conventional dogma is that shared buff= ers
should never be more than about 10k no matter how much memory you ha= ve. Let
the kernel do the bulk of the buffering.

That said it's e= ven more mysterious in your situation. Why would a large
shared buffers help insert-only performance much at all? My guess is th= at it's
letting the vacuums complete quicker. Perhaps try raising work_m= em?

I find it odd as well.  After reading the standard advice on shared_buffers, I had only intended on raising it slightly.  But seeing ever-increasing performance gains, I just kept tuning it upwards all the way to the 2G limit, and saw noticeable gains every time.  During at least some of the test cycles, there was no deleting or vacuuming going on, just insert traffic.  I guessed that shared_buffers' caching strategy must have been much more effective than the OS cache at something or other, but I don't know what exactly.  The only important read traffic that comes to mind is the index which is both being constantly updated and constantly checked for primary key uniqueness violations.

All of these replies here on the list (and a private one or two) have given me a long list of things to try, and I feel confident that at least some of them will gain me enough performance to comfortably deploy this application in the end on somewhat reasonable hardware.  Thanks to everyone here on the list for all the suggestions, it has been very helpful in giving me directions to go with this that I hadn't thought of before.

When I finally get all of this sorted out and working reasonably optimally, I'll be sure to come back and report what techniques/settings did and didn't work for this workload.

-- Brandon
------=_Part_12365_28318204.1126583075426--