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 1E0B55E3F15 for ; Mon, 16 Aug 2004 12:22:36 -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 28511-10 for ; Mon, 16 Aug 2004 15:22:39 +0000 (GMT) Received: from ucsns.ucs.co.za (ucs.co.za [196.23.43.2]) by svr1.postgresql.org (Postfix) with ESMTP id AD7AF5E37CE for ; Mon, 16 Aug 2004 12:22:20 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ucsns.ucs.co.za (Postfix) with ESMTP id 200B32BD4C for ; Mon, 16 Aug 2004 17:12:09 +0200 (SAST) Received: from ucsns.ucs.co.za ([127.0.0.1]) by localhost (ucsns.ucs.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27719-06 for ; Mon, 16 Aug 2004 17:11:59 +0200 (SAST) Received: from ucspost.ucs.co.za (mailgw1.ucs.co.za [196.23.43.253]) by ucsns.ucs.co.za (Postfix) with ESMTP id 6B0FF2BD5F for ; Mon, 16 Aug 2004 17:11:59 +0200 (SAST) Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.3]) by ucspost.ucs.co.za (Postfix) with ESMTP id 999C5DA300 for ; Mon, 16 Aug 2004 17:11:59 +0200 (SAST) Received: from svb.ucs.co.za (svb.ucs.co.za [172.31.1.148]) by jhb.ucs.co.za (Postfix) with SMTP id D9EDA975A0 for ; Mon, 16 Aug 2004 17:11:58 +0200 (SAST) Date: Mon, 16 Aug 2004 17:12:34 +0200 From: Stef To: pgsql-performance@postgresql.org Subject: Strange problems with more memory. Message-Id: <20040816171234.5a9bac8f@svb.ucs.co.za> X-Mailer: Sylpheed-Claws 0.9.12 (GTK+ 1.2.10; i386-pc-linux-gnu) User-Agent: sillypheed-claws (anti-aliased) X-Face: GFoC95e6r)_TTG>n~=uFLojP=O~4W@Ms]>:.DMm/')(z3\Mwj^XP@? Q:3";lD.OM1"^mDu}2NJ@US:)dO:U*iY5EM50&Tx. X-Operating-System: sid X-X-X: _-^-_ Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at ucs.co.za X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/246 X-Sequence-Number: 7882 Hi all, I'm running postgres 7.3.4 on a quad Xeon 2.8 GHz with Mem: 1057824768 309108736 748716032 0 12242944 256413696 Swap: 518053888 8630272 509423616 on Linux version 2.4.26-custom Data directory is mounted with noatime. Nothing else but one 11GB database is running on this machine. When the database was created, I changed the following defaults : shared_buffers = 24415 sort_mem = 5120 vacuum_mem = 10240 commit_delay = 5000 commit_siblings = 100 These settings worked fine, but were not optimal, I thought, and processing stuff on this database was a bit slow. The machine is not nearly used to it's capacity, and I realized that disk IO is what's slowing me down. So I decided to give postgres more shared memory and much more sort memory, as it does a lot of "group by'"s and "order by"'s during the nightly processing. These were the new settings I tried : shared_buffers = 61035 sort_mem = 97657 I thought because it's only one process that runs queries exclusively at night, I should be able to set the sort_mem this high without worrying about running out of memory. It seems I was mistaking, as I started getting these kind of errors in dmesg : VM: killing process postmaster __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process postmaster and I kept on getting these postgres errors : ERROR: Index ???? is not a btree I systematically reduced the shared buffers back down to 24415, and this kept on happening. As soon as I reduced sort_mem back to under 10000,the problem stopped. But the database is just as slow as before. (By slow I mean not as fast as it should be on such a powerful machine compared to much worse machines running the same processes) What can I do to make this database run faster on this machine. Can anyone suggest how I would go about speeding up this database. I need to prepare a database three times the size of this one, running the same processes, and I don't know what improvements I can do on hardware to make this possible. On the current machine I can easily get another 1GB or 2GB of memory, but will that help at all? Without going into the details of exactly the queries that run on this machine, what would be needed to make postgres run very fast on this machine? Please help. Kind Regards Stefan