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 65C0252BA7 for ; Tue, 16 Aug 2005 13:43:12 -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 34733-03 for ; Tue, 16 Aug 2005 16:43:06 +0000 (GMT) Received: from gghcwest.com (adsl-71-128-90-172.dsl.pltn13.pacbell.net [71.128.90.172]) by svr1.postgresql.org (Postfix) with ESMTP id 3B41152B75 for ; Tue, 16 Aug 2005 13:43:04 -0300 (ADT) Received: from toonses.gghcwest.com (toonses.gghcwest.com [192.168.168.115]) by gghcwest.com (8.12.10/8.12.9) with ESMTP id j7GGh0md027531; Tue, 16 Aug 2005 09:43:02 -0700 Received: from jwb by toonses.gghcwest.com with local (Exim 4.50) id 1E54Wj-00049f-TV; Tue, 16 Aug 2005 09:43:01 -0700 Subject: Re: Need for speed From: "Jeffrey W. Baker" To: Ulrich Wisser Cc: pgsql-performance@postgresql.org In-Reply-To: <430208AE.7080100@relevanttraffic.se> References: <430208AE.7080100@relevanttraffic.se> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Tue, 16 Aug 2005 09:43:01 -0700 Message-Id: <1124210581.15880.10.camel@toonses.gghcwest.com> Mime-Version: 1.0 X-Mailer: Evolution 2.3.6.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-1.019 required=5 tests=[AWL=-1.069, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/176 X-Sequence-Number: 13923 On Tue, 2005-08-16 at 17:39 +0200, Ulrich Wisser wrote: > Hello, > > one of our services is click counting for on line advertising. We do > this by importing Apache log files every five minutes. This results in a > lot of insert and delete statements. At the same time our customers > shall be able to do on line reporting. > > We have a box with > Linux Fedora Core 3, Postgres 7.4.2 > Intel(R) Pentium(R) 4 CPU 2.40GHz This is not a good CPU for this workload. Try an Opteron or Xeon. Also of major importance is the amount of memory. If possible, you would like to have memory larger than the size of your database. > 2 scsi 76GB disks (15.000RPM, 2ms) If you decide your application is I/O bound, here's an obvious place for improvement. More disks == faster. > I did put pg_xlog on another file system on other discs. Did that have a beneficial effect? > Still when several users are on line the reporting gets very slow. > Queries can take more then 2 min. Is this all the time or only during the insert? > I need some ideas how to improve performance in some orders of > magnitude. I already thought of a box with the whole database on a ram > disc. So really any idea is welcome. You don't need a RAM disk, just a lot of RAM. Your operating system will cache disk contents in memory if possible. You have a very small configuration, so more CPU, more memory, and especially more disks will probably all yield improvements.