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 ED4AC53479 for ; Wed, 27 Apr 2005 15:47:51 -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 79497-09 for ; Wed, 27 Apr 2005 18:47:42 +0000 (GMT) Received: from mta10.nationwireless.net (mta10.nationwireless.net [63.163.128.10]) by svr1.postgresql.org (Postfix) with ESMTP id 648F752967 for ; Wed, 27 Apr 2005 15:47:41 -0300 (ADT) Received: from jfradkin [63.162.100.127] by mta10.nationwireless.net with ESMTP (SMTPD32-8.15) id AE4D45D700E6; Wed, 27 Apr 2005 14:47:41 -0400 From: "Joel Fradkin" To: "'John A Meinel'" Cc: "'PostgreSQL Perform'" Subject: Re: Final decision Date: Wed, 27 Apr 2005 14:47:50 -0400 Message-ID: <000401c54b59$9d916050$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: <426FC9E5.60905@arbash-meinel.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/742 X-Sequence-Number: 12139 Just realize, you probably *don't* want to set that in postgresql.conf. You just want to issue an "SET enable_seqscan TO off" before issuing one of the queries that are mis-planned. I believe all the tested queries (90 some odd views) saw an improvement. I will however take the time to verify this and take your suggestion as = I can certainly put the appropriate settings in each as opposed to using = the config option, Thanks for the good advice (I believe Josh from Commandprompt.com also suggested this approach and I in my lazy self = some how blurred the concept.) Also, I second the notion of getting a confidentiality contract. There have been several times where someone had a pathological case, and by sending the data to someone (Tom Lane), they were able to track down and fix the problem. Excellent point, Our data is confidential, but I should write something = to allow me to ship concept without confidential, so in the future I can = just send a backup and not have it break our agreements, but allow minds = greater then my own to see, and feel my issues. What do you mean by "blew up"?=20 IIS testing was being done with an old 2300 and a optiplex both machines reached 100%CPU utilization and the test suite (ASP code written in = house by one of programmers) was not returning memory correctly, so it ran out of memory and died. Prior to death I did see cpu utilization on the 4proc = linux box running postgres fluctuate and at times hit the 100% level, but the server seemed very stable. I did fix the memory usage of the suite and = was able to see 50 concurrent users with fairly high RPS especially on = select testing, the insert and update seemed to fall apart (many 404 errors = etc) I assume you have IIS on a different machine than the database. Are you saying that the database slowed down dramatically, or that the machine crashed, or just that the web interface became unresponsive? Just the web interface. It probably depends on what queries are being done, and what kind of times you need. Usually the update machine needs the stronger hardware, so that it can do the writing. But it depends if you can wait longer to update data than to query data, obviously the opposite is true. It all depends on load, and that is pretty much application defined. I am guessing our app is like 75% data entry and 25% reporting, but the reporting is taking the toll SQL wise. This was from my insert test with 15 users. Test type: Dynamic=20 Simultaneous browser connections: 15=20 Warm up time (secs): 0=20 Test duration: 00:00:03:13=20 Test iterations: 200=20 Detailed test results generated: Yes Response Codes=20 =20 Response Code: 403 - The server understood the request, but is refusing = to fulfill it.=20 Count: 15=20 Percent (%): 0.29=20 =20 =20 Response Code: 302 - The requested resource resides temporarily under a different URI (Uniform Resource Identifier).=20 Count: 200=20 Percent (%): 3.85=20 =20 =20 Response Code: 200 - The request completed successfully.=20 Count: 4,980=20 Percent (%): 95.86=20 =20 My select test with 25 users had this Properties=20 =20 Test type: Dynamic=20 Simultaneous browser connections: 25=20 Warm up time (secs): 0=20 Test duration: 00:00:06:05=20 Test iterations: 200=20 Detailed test results generated: Yes=20 =20 Summary=20 =20 Total number of requests: 187=20 Total number of connections: 200=20 =20 Average requests per second: 0.51=20 Average time to first byte (msecs): 30,707.42=20 Average time to last byte (msecs): 30,707.42=20 Average time to last byte per iteration (msecs): 28,711.44=20 =20 Number of unique requests made in test: 1=20 Number of unique response codes: 1=20 =20 Errors Counts=20 =20 HTTP: 0=20 DNS: 0=20 Socket: 26=20 =20 Additional Network Statistics=20 =20 Average bandwidth (bytes/sec): 392.08=20 =20 Number of bytes sent (bytes): 64,328=20 Number of bytes received (bytes): 78,780=20 =20 Average rate of sent bytes (bytes/sec): 176.24=20 Average rate of received bytes (bytes/sec): 215.84=20 =20 Number of connection errors: 0=20 Number of send errors: 13=20 Number of receive errors: 13=20 Number of timeout errors: 0=20 =20 Response Codes=20 =20 Response Code: 200 - The request completed successfully.=20 Count: 187=20 Percent (%): 100.00=20 =20 Joel