X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 82CA59FA0EF for ; Wed, 13 Sep 2006 18:10:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 00301-09 for ; Wed, 13 Sep 2006 21:10:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id CD0E69FA103 for ; Wed, 13 Sep 2006 18:10:32 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 13 Sep 2006 21:10:27 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 13 Sep 2006 16:10:25 -0500 Subject: Re: sql-bench From: Scott Marlowe To: Merlin Moncure Cc: Tom Lane , yoav x , pgsql-performance@postgresql.org In-Reply-To: References: <20060913122414.16995.qmail@web35811.mail.mud.yahoo.com> <12097.1158161568@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1158181825.24726.4.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 13 Sep 2006 16:10:25 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200609/136 X-Sequence-Number: 20822 On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote: > On 9/13/06, Tom Lane wrote: > > IIRC, with these settings PG 8.0 seemed to be about half the speed of > > mysql 5.0 w/myisam, which is probably somewhere in the ballpark of the > > truth for tests of this nature, ie, single query stream of fairly simple > > queries. If you try concurrent-update scenarios or something that > > stresses planning ability you may arrive at different results though. > > I have not retested with more recent versions. > > if postgresql uses prepared statements for such queries, it will > roughly tie mysql/myisam in raw query output on this type of load > which also happens to be very easy to prepare...afaik mysql gets zero > performance benefit from preparing statements This is extremely > trivial to test&confirm even on a shell script. [aside: will this > still be the case if peter e's planner changes become reality?] > > another cheater trick benchmarkers do to disparage postgresql is to > not run analyze intentionally. Basically all production postgresql > systems of any size will run analyze on cron. > > another small aside, I caught the sqlite people actually *detuning* > postgresql for performance by turning stats_command_string=on in > postgresql.conf. The way it was portrayed it almost looked like > cheating. I busted them on it (go to > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison and look for the > remarks right below the results) They're running autovacuum, which requires that, doesn't it? I'd rather them be running autovacuum than not vacuuming / analyzing at all. And autovacuum is a pretty realistic setting for most databases (I use it on my production machines.)