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 AA05A53605 for ; Fri, 22 Apr 2005 18:04:27 -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 67677-07 for ; Fri, 22 Apr 2005 21:04:18 +0000 (GMT) Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by svr1.postgresql.org (Postfix) with ESMTP id 88E875334A for ; Fri, 22 Apr 2005 18:04:17 -0300 (ADT) Received: from jfradkin ([63.162.100.127]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IFD00GPM976K3Q4@vms044.mailsrvcs.net> for pgsql-performance@postgresql.org; Fri, 22 Apr 2005 16:04:19 -0500 (CDT) Date: Fri, 22 Apr 2005 17:04:19 -0400 From: "Joel Fradkin" Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon In-reply-to: <20050422183054.GB14419@dcc.uchile.cl> To: "'Alvaro Herrera'" Cc: "'Andreas Pflug'" , "'Dave Page'" , , , "'Joshua D. Drake'" , , "'Steve Hatt'" Message-id: <000001c5477e$da38e3e0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/625 X-Sequence-Number: 12022 One further question is: is this really a meaningful test? I mean, in production are you going to query 300000 rows regularly?=20 It is a query snippet if you will as the view I posted for audit and = case where tables are joined are more likely to be ran. Josh and I worked over this until we got explain analyze on the linux = box to 1 sec. I was just using this as a test as I don't have my views set up = on MYSQL. So many of my reports pull huge data sets (comprised of normalized = joins). I am thinking I probably have to modify to using an non normalized = table, and Josh is sending me information on using cursors instead of selects. And is the system always going to be used by only one user? =20 No we have 400+ concurrent users I guess the question is if this big select is representative of the load = you expect in production. Yes we see many time on the two processor box running MSSQL large return sets using 100%cpu for 5-30 seconds. What happens if you execute the query more times? Do the times stay the same as the second run? I will definitely have to pressure testing prior to going live in production. I have not done concurrent tests as honestly single user = tests are failing, so multiple user testing is not something I need yet. Joel --=20 Alvaro Herrera () "Use it up, wear it out, make it do, or do without"