X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 782CAD6876 for ; Sun, 6 Nov 2005 09:31:05 -0400 (AST) 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 05349-05 for ; Sun, 6 Nov 2005 13:30:59 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from obelix.askesis.nl (laudanum.demon.nl [82.161.125.16]) by svr1.postgresql.org (Postfix) with ESMTP id E9B34D6862 for ; Sun, 6 Nov 2005 09:30:59 -0400 (AST) Received: obelix.askesis.nl 172.31.0.1 from 172.31.1.8 172.31.1.8 via HTTP with MS-WebStorage 6.0.6249 Received: from Panoramix by obelix.askesis.nl; 06 Nov 2005 14:31:02 +0100 Subject: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? From: Joost Kraaijeveld To: Pgsql-Performance Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sun, 06 Nov 2005 14:30:54 +0100 Message-Id: <1131283854.15471.4.camel@Panoramix> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.06 required=5 tests=[AWL=0.059, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.06 X-Spam-Level: X-Archive-Number: 200511/98 X-Sequence-Number: 15355 Hi, I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better. The query below is running for more than 1.5 hours (5500 seconds) now, while the rest of the system does nothing (I don't even type or move a mouse...). - Is that to be expected? - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given the fact that fsync is off? (Note: with bonnie++ I get write performance > 50 MB/sec and read performace > 70 MB/sec with > 2000 read/write ops /sec? - Does anyone else have any experience with the 3Ware RAID controller (which is my suspect)? - Any good idea how to determine the real botleneck if this is not the performance I can expect? My hard- and software: - PostgreSQL 8.0.3 - Debian 3.1 (Sarge) AMD64 - Dual Opteron - 4GB RAM - 3ware Raid5 with 5 disks Pieces of my postgresql.conf (All other is default): shared_buffers = 7500 work_mem = 260096 fsync=false effective_cache_size = 32768 The query with explain (amount and orderbedrag_valuta are float8, ordernummer and ordernumber int4): explain update prototype.orders set amount = odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber = odbc.orders.ordernummer; QUERY PLAN ----------------------------------------------------------------------------- Hash Join (cost=50994.74..230038.17 rows=1104379 width=466) Hash Cond: ("outer".ordernumber = "inner".ordernummer) -> Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455) -> Hash (cost=48233.79..48233.79 rows=1104379 width=15) -> Seq Scan on orders (cost=0.00..48233.79 rows=1104379 width=15) Sample output from iostat during query (about avarage): Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.13 23.76 8764.36 24 8852 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl