Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 00A0737BABB for ; Fri, 3 Oct 2008 07:09:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27966-10 for ; Fri, 3 Oct 2008 07:09:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web52909.mail.re2.yahoo.com (web52909.mail.re2.yahoo.com [206.190.49.19]) by postgresql.org (Postfix) with SMTP id 1172E37BA2F for ; Fri, 3 Oct 2008 07:09:41 -0300 (ADT) Received: (qmail 39610 invoked by uid 60001); 3 Oct 2008 10:09:37 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Message-ID; b=eA9kwZ6QBo+RA/rBq8IKUiEgULRXaj4e41JQ1K+JY8CBDBU88CHOD9RXrl/Mv4UGXB9dG7KCwKxp9mF718+1PtlSdYmqZCDuo4I5KzA2P8/04p3GTaJVwsKZIQ65u2u3mpPvTsLJmAuqOsS7FD8X8ok2Qeok/Vm25SoAc6VPejc=; X-YMail-OSG: t6iezZkVM1mWdF6otjNJPC8kg_KNwuSFzW1ZgGojBMQdy0Qn5S4qLeK2I4zF9uEhbLkliGrJupS9KkudKwUb4q2raWq8chK21FSTfs6W5_UBvvQJLLktF8tLkL1aSvXP.wM.S9qA4Sn.BImr.GiDI5Bc2FBU4gGBpphhRa2KbyXmsC2r8A-- Received: from [57.79.10.142] by web52909.mail.re2.yahoo.com via HTTP; Fri, 03 Oct 2008 03:09:37 PDT X-Mailer: YahooMailWebService/0.7.218.2 Date: Fri, 3 Oct 2008 03:09:37 -0700 (PDT) From: Iulian Dragan Reply-To: iulian_dragan@yahoo.com Subject: Re: Slow Inserts on large tables To: Peter Childs Cc: Postgresql Performance In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-414534045-1223028577=:39549" Message-ID: <552000.39549.qm@web52909.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/23 X-Sequence-Number: 31604 --0-414534045-1223028577=:39549 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Peter, (please take this with a pinch of salt as I am no expert) Here is=A0 a possible scenario: Each of your checkpoints takes 90 seconds or more (you told it=A0 so with t= he checkpoint_completion_target).=20 If your insert fills 3 checkpoint segments (48 megs ) in less than 90 secon= ds then a new checkpoint request is issued. And maybe a third one, and so o= n. I imagine that this can flood the disk cache with write requests at some= point although I can't explain how. Have a look at the log, see the interval between the checkpoint requests an= d try to make this (a lot) larger than the checkpoint duration. Start by increasing your checkpoint_segments (to, say, 16). If this doesn't= work, maybe the timeout is too short, or the 90 seconds target to generous= . Regards, Iulian --- On Fri, 10/3/08, Peter Childs wrote: From: Peter Childs Subject: Re: [PERFORM] Slow Inserts on large tables To:=20 Cc: "Postgresql Performance" Date: Friday, October 3, 2008, 9:47 AM 2008/10/3 Peter Eisentraut : > Peter Childs wrote: >> >> I have a problem where by an insert on a "large" table will sometimes >> take longer than usual. > >> I think the problem might have something to do with checkpoints, > > Then show us your checkpointing-related parameters. Or try to set them t= o a > lot higher values so checkpoints happen more rarely and see if that makes a > difference. > > More often or less often? I've currently got them set to checkpoint_segments =3D 3 checkpoint_timeout =3D 180s checkpoint_completion_target =3D 0.5 after reading that doing more smaller checkpoints might make each checkpoint work quicker and hence less of a performance hit when they actually happen. Regards Peter --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance =0A=0A=0A --0-414534045-1223028577=:39549 Content-Type: text/html; charset=us-ascii
Peter,

(please take this with a pinch of salt as I am no expert)

Here is  a possible scenario:
Each of your checkpoints takes 90 seconds or more (you told it  so with the checkpoint_completion_target).
If your insert fills 3 checkpoint segments (48 megs ) in less than 90 seconds then a new checkpoint request is issued. And maybe a third one, and so on. I imagine that this can flood the disk cache with write requests at some point although I can't explain how.
Have a look at the log, see the interval between the checkpoint requests and try to make this (a lot) larger than the checkpoint duration.
Start by increasing your checkpoint_segments (to, say, 16). If this doesn't work, maybe the timeout is too short, or the 90 seconds target to generous.

Regards,

Iulian

--- On Fri, 10/3/08, Peter Childs <peterachilds@gmail.com> wrote:
From: Peter Childs <peterachilds@gmail.com>
Subject: Re: [PERFORM] Slow Inserts on large tables
To:
Cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
Date: Friday, October 3, 2008, 9:47 AM

2008/10/3 Peter Eisentraut <peter_e@gmx.net>:
> Peter Childs wrote:
>>
>> I have a problem where by an insert on a "large" table will
sometimes
>> take longer than usual.
>
>> I think the problem might have something to do with checkpoints,
>
> Then show us your checkpointing-related parameters.  Or try to set them to
a
> lot higher values so checkpoints happen more rarely and see if that makes
a
> difference.
>
>

More often or less often?

I've currently got
 them set to

checkpoint_segments = 3
checkpoint_timeout = 180s
checkpoint_completion_target = 0.5

after reading that doing more smaller checkpoints might make each
checkpoint work quicker and hence less of a performance hit when they
actually happen.

Regards

Peter

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

--0-414534045-1223028577=:39549--