Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7EC6D2E2D36 for ; Sat, 15 Dec 2007 11:47:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 60291-03 for ; Sat, 15 Dec 2007 11:47:06 -0400 (AST) Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 328CE2E2D8A for ; Sat, 15 Dec 2007 11:47:06 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id lBFFl3DL002663; Sat, 15 Dec 2007 10:47:03 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id lBFFl3c7002659; Sat, 15 Dec 2007 10:47:03 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Sat, 15 Dec 2007 10:47:02 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: okparanoid@free.fr cc: pgsql-performance@postgresql.org Subject: Re: update 600000 rows In-Reply-To: <1197677482.47631baa4052a@imp.free.fr> Message-ID: References: <1197677482.47631baa4052a@imp.free.fr> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/237 X-Sequence-Number: 28053 On Sat, 15 Dec 2007, okparanoid@free.fr wrote: > First when i run htop i see that the memory used is never more than 150 MB. > I don't understand in this case why setting shmall and shmmax kernel's > parameters to 16 GB of memory (the server has 32 GB) increase the rapidity of > the transaction a lot compared to a shmall and shmax in (only) 2 GB ?! The kernel parameters provide an upper limit for how much memory PostgreSQL can allocate, but by themselves they don't actually request more memory. There is a configuration parameters called shared_buffers that is the main thing to adjust. Since you say you're new to this, see http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for the first set of things you should be adjusting. If you're doing lots of updates, you'll need to increase checkpoint_segments as well. Once you get the memory allocated and checkpoint parameters in the right range, at that point you'll be prepared to look into transaction grouping and application issues in that area. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD