X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4CEA29FA4F2 for ; Fri, 5 May 2006 06:50:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 02471-01 for ; Fri, 5 May 2006 06:50:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from candle.pha.pa.us (candle.pha.pa.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id F24E99FA294 for ; Fri, 5 May 2006 06:50:13 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id k459oER10293; Fri, 5 May 2006 05:50:14 -0400 (EDT) From: Bruce Momjian Message-Id: <200605050950.k459oER10293@candle.pha.pa.us> Subject: Re: Super-smack? In-Reply-To: <6879.1146505396@sss.pgh.pa.us> To: Tom Lane Date: Fri, 5 May 2006 05:50:14 -0400 (EDT) CC: Steve Woodcock , Scott Sipe , pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/103 X-Sequence-Number: 18890 Isn't Super Smack a breakfast cereal? :-) --------------------------------------------------------------------------- Tom Lane wrote: > I wrote: > > FWIW, my own experiments with tests like this suggest that PG is at > > worst about 2x slower than mysql for trivial queries. If you'd reported > > a result in that ballpark I'd have accepted it as probably real. 6x I > > don't believe though ... > > Just for amusement's sake, I tried compiling up super-smack on my own > machine, and got results roughly in line with what I would've expected. > > Machine: dual Xeon EM64T, forget the clock rate at the moment, running > Fedora Core 4 (kernel 2.6.15-1.1831_FC4smp); hyperthreading enabled > > Postgres: fairly recent CVS tip, no special build options except > --enable-debug, no changes to default runtime configuration options > > MySQL: 5.0.18, current Red Hat RPMs, no changes to default configuration > > The "select" test, with 1 and 10 clients: > > $ super-smack -d pg select-key.smack 1 10000 > Query Barrel Report for client smacker1 > connect: max=0ms min=-1ms avg= 3ms from 1 clients > Query_type num_queries max_time min_time q_per_s > select_index 20000 0 0 3655.24 > $ super-smack -d pg select-key.smack 10 10000 > Query Barrel Report for client smacker1 > connect: max=54ms min=4ms avg= 12ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 200000 0 0 7431.20 > > $ super-smack -d mysql select-key.smack 1 10000 > Query Barrel Report for client smacker1 > connect: max=0ms min=-1ms avg= 0ms from 1 clients > Query_type num_queries max_time min_time q_per_s > select_index 20000 0 0 6894.03 > $ super-smack -d mysql select-key.smack 10 10000 > Query Barrel Report for client smacker1 > connect: max=14ms min=0ms avg= 5ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 200000 0 0 16798.05 > > The "update" test, with 1 and 10 clients: > > $ super-smack -d pg update-select.smack 1 10000 > Query Barrel Report for client smacker > connect: max=0ms min=-1ms avg= 4ms from 1 clients > Query_type num_queries max_time min_time q_per_s > select_index 10000 0 0 1027.49 > update_index 10000 0 0 1027.49 > $ super-smack -d pg update-select.smack 10 10000 > Query Barrel Report for client smacker > connect: max=13ms min=5ms avg= 8ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 100000 1 0 1020.96 > update_index 100000 28 0 1020.96 > > The above is with fsync on (though I think this machine's disk lies > about write complete so I'd not trust it as production). With fsync off, > > $ super-smack -d pg update-select.smack 1 10000 > Query Barrel Report for client smacker > connect: max=0ms min=-1ms avg= 3ms from 1 clients > Query_type num_queries max_time min_time q_per_s > select_index 10000 0 0 1478.25 > update_index 10000 0 0 1478.25 > $ super-smack -d pg update-select.smack 10 10000 > Query Barrel Report for client smacker > connect: max=35ms min=5ms avg= 21ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 100000 1 0 3067.68 > update_index 100000 1 0 3067.68 > > versus mysql > > $ super-smack -d mysql update-select.smack 1 10000 > Query Barrel Report for client smacker > connect: max=0ms min=-1ms avg= 0ms from 1 clients > Query_type num_queries max_time min_time q_per_s > select_index 10000 0 0 4101.43 > update_index 10000 0 0 4101.43 > $ super-smack -d mysql update-select.smack 10 10000 > Query Barrel Report for client smacker > connect: max=3ms min=0ms avg= 0ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 100000 1 0 5388.31 > update_index 100000 6 0 5388.31 > > Since mysql is using myisam tables (ie not transaction safe), I think > the fairest comparison is to the fsync-off numbers. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +