Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6582F2E2D63 for ; Wed, 14 Nov 2007 15:08:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 92364-05 for ; Wed, 14 Nov 2007 15:07:51 -0400 (AST) Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 803532E2D39 for ; Wed, 14 Nov 2007 15:07:52 -0400 (AST) Received: from perhan.alvh.no-ip.org (190-95-16-8.bk17-dsl.surnet.cl [190.95.16.8]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id lAEJ7mx1021061 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Wed, 14 Nov 2007 11:07:50 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id D3E4547C0D; Wed, 14 Nov 2007 16:08:14 -0300 (CLST) Date: Wed, 14 Nov 2007 16:08:14 -0300 From: Alvaro Herrera To: Tena Sakai Cc: pgsql-admin@postgresql.org Subject: Re: postgres bogged down beyond tolerance Message-ID: <20071114190814.GU19014@alvh.no-ip.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-11) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Wed, 14 Nov 2007 11:07:51 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/121 X-Sequence-Number: 27364 Tena Sakai wrote: > I checked kernel parameter > shmmax and it was set as 33554432. I "fixed" it as > suggested by the manual: > http://www.postgresql.org/docs/8.2/static/kernel-resources.html Note that just by changing shmmax you're not doing anything to Postgres itself. If you want Postgres to use more memory, you need to increase shared_buffers in postgresql.conf. Changing shmmax is only required so that the kernel allows Postgres to allocate all those shared_buffers. > Namely, I shutdown the database, issued two commands: > /sbin/sysctl -w kernel.shmmax=134217728 > /sbin/sysctl -w kernel.shmall=2097152 > and rebooted the computer. > > After it came up, I checked the shmmax and it is set > as 33554432. Which surprised me. Since I used -w > flag, I thought it should've written to /etc/sysctl.conf, > but there is no such entry at all and the data of this > file is from 2006. No, the -w actually means "write to the kernel", which is state that doesn't persist by itself. It's your own responsability to write it to sysctl.conf. Anyhow, I don't think any of this has anything to do with a performance problem. If you haven't ever vacuumed your database, do so now and try again. (Actually, have a read of the "maintenance" chapter in the manual). -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender)