X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 98FB7D1B1D0 for ; Mon, 26 Jul 2004 13:24:41 -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 78421-05 for ; Mon, 26 Jul 2004 16:24:40 +0000 (GMT) Received: from mpls-qmqp-02.inet.qwest.net (mpls-qmqp-02.inet.qwest.net [63.231.195.113]) by svr1.postgresql.org (Postfix) with SMTP id 8BBBDD1B16A for ; Mon, 26 Jul 2004 13:24:38 -0300 (ADT) Received: (qmail 42231 invoked by uid 0); 26 Jul 2004 15:12:41 -0000 Received: from mpls-pop-10.inet.qwest.net (63.231.195.10) by mpls-qmqp-02.inet.qwest.net with QMQP; 26 Jul 2004 15:12:41 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-10.inet.qwest.net with SMTP; 26 Jul 2004 16:24:37 -0000 Date: Mon, 26 Jul 2004 10:25:38 -0600 Message-Id: <1090859137.22512.12.camel@localhost.localdomain> From: "Scott Marlowe" To: "Gaetano Mendola" Cc: "=?ISO-8859-1?Q?Herv=E9?= Piedvache" , pgsql-performance@postgresql.org Subject: Re: Insert are going slower ... In-Reply-To: <4105131F.9050005@bigfoot.com> References: <200407131850.52095.herve@elma.fr> <200407131010.30790.josh@agliodbs.com> <200407140142.11720.footcow@noos.fr> <4105131F.9050005@bigfoot.com> Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/178 X-Sequence-Number: 7569 On Mon, 2004-07-26 at 08:20, Gaetano Mendola wrote: > Hervé Piedvache wrote: SNIP > > sort_mem = 512000 > > This is too much, you are instructing Postgres to use 512MB > for each backend ( some time each backend can use this quantity > more then one ) agreed. If any one process needs this much sort mem, you can set it in that sessions with set sort_mem anyway, so to let every sort consume up to 512 meg is asking for trouble. > > effective_cache_size = 5000000 > > 5GB for 8 GB system is too much No, it's not. Assuming that postgresql with all it's shared buffers is using <2 gig, it's quite likely that the kernel is caching at least 5 gigs of disk data. Effective cache size doesn't set any cache size, it tells the planner about how much the kernel is caching. > > random_page_cost = 3 > > on your HW you can decrease it to 2 > and also decrease the other cpu costs On fast machines it often winds up needing to be set somewhere around 1.2 to 2.0