X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E54B4D1D8AE for ; Sun, 15 Feb 2004 02:01:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84048-10 for ; Sat, 14 Feb 2004 22:01:00 -0400 (AST) Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34]) by svr1.postgresql.org (Postfix) with ESMTP id 5F0B1D1CA5F for ; Sat, 14 Feb 2004 22:00:57 -0400 (AST) Received: from localhost (morework [127.0.0.1]) by morework.geizhals.at (Postfix) with ESMTP id 6756B52413D; Sun, 15 Feb 2004 03:00:43 +0100 (CET) Received: from morework.geizhals.at ([127.0.0.1]) by localhost (morework [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01211-08; Sun, 15 Feb 2004 03:00:43 +0100 (CET) Received: from geizhals.at (chello080110242194.117.11.tuwien.teleweb.at [80.110.242.194]) by morework.geizhals.at (Postfix) with ESMTP id 13AA152413A; Sun, 15 Feb 2004 03:00:36 +0100 (CET) Message-ID: <402ED348.7000800@geizhals.at> Date: Sun, 15 Feb 2004 03:02:48 +0100 From: "Marinos J. Yannikos" User-Agent: Mozilla Thunderbird 0.5b (Windows/20040201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: josh@agliodbs.com Cc: pgsql-performance@postgresql.org Subject: Re: optimization ideas for frequent, large(ish) updates References: <402C213A.4030105@geizhals.at> <200402122228.41680.josh@agliodbs.com> In-Reply-To: <200402122228.41680.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p5 at geizhals.at X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/164 X-Sequence-Number: 5688 Josh Berkus wrote: > 800MB for sort mem? Are you sure you typed that correctly? You must be > counting on not having a lot of concurrent queries. It sure will speed up > index updating, though! 800MB is correct, yes... There are usually only 10-30 postgres processes active (imagine 5-10 people working on the web front-end while cron jobs access the db occasionally). Very few queries can use such large amounts of memory for sorting, but they do exist. > I think you might do well to experiment with using the checkpoint_delay and > checkpoint_sibilings settings in order to get more efficient batch processing > of updates while selects are going on. [commit_*?] I thought that could improve only concurrent transactions... > What have you set max_fsm_relations and max_fsm_pages to? The latter should > be very high for you, like 10,000,000 good guess ;-) the former is set to 10,000 (I'm not sure how useful this is for those temporary tables) > For that matter, what *version* of PostgreSQL are you running? 7.4.1 > Also, make sure that your tables get vaccuumed regularly. There is a noticeable difference between a properly vacuumed db (nightly "vacuum full") and a non-vacuumed one and people will start complaining immediately if something goes wrong there... > Well, a battery-backed RAID controller with a fast cache would certainly help. http://www.lsilogic.com/products/ultra320_scsi_megaraid_storage_adapters/320x4128t.html (RAID-5 with 9 15k rpm drives; at a hindsight, perhaps we should have tried a 0+1) > You'll also be glad to know that a *lot* of the improvements in the upcoming > PostgreSQL 7.5 are aimed at giving better peformance on large, high-activity > databases like yours. That's good to hear... Regards, Marinos