I've been attempting to tweak performance on a FreeBSD 6 server I'm
running that has both Postgresql 8.1 and Mysql 4.1 running
simultaneously.

To attempt to gauge performance I was directed to the super-smack
(http://vegan.net/tony/supersmack/) benchmark.

Testing gave results that showed mysql far outperforming postgresql
in select and update tests, in factors of roughly 6-7x in terms of
queries per second (running stock configurations--I was able to
improve mysql performance a good amount with playing with the config
file settings, though I couldn't make any change in postgresql
performance. I DID increase shmmin shmmax and semmap during the
course of testing).

So, my question is, before I do any further digging, is super-smack
flawed? It's very possibile I'm doing something stupid with the
execution of the benchmark too, but as I said, I just wanted to see
if anyone else had used super-smack, or had comments? I'm glad to
post system specs, configs, etc, if anyone is interested.

Alternatively, would anyone recommend any other benchmarks to run?

thanks much,
Scott

I also wanted to make clear I didn't want to turn this into a mysql
vs postgresql discussion, as I much prefer postgresql.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, May 01, 2006 at 03:05:54AM -0500, Scott Sipe wrote:
> So, my question is, before I do any further digging, is super-smack
> flawed?

It's sort of hard to say without looking at the source -- it certainly isn't
a benchmark I've heard of before, and it's also sort of hard to believe a
benchmark whose focus seems to be so thoroughly on one database (MySQL). The
site claims (about PostgreSQL) support that "it looks like it works";
certainly not a good start for fair benchmarking :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 01/05/06, Scott Sipe <cscotts@mindspring.com> wrote:
> So, my question is, before I do any further digging, is super-smack
> flawed?

Hmm, selects and updates of a 90k row table using the primary key, but
no sign of a VACUUM ANALYZE...

Cheers, Steve Woodcock

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Steve Woodcock" <steve.woodcock@gmail.com> writes:
> On 01/05/06, Scott Sipe <cscotts@mindspring.com> wrote:
>> So, my question is, before I do any further digging, is super-smack
>> flawed?

> Hmm, selects and updates of a 90k row table using the primary key, but
> no sign of a VACUUM ANALYZE...

Reasonably recent versions of PG should be able to do "the right thing"
in the presence of a simple primary key, even without any prior ANALYZE;
the planner will see the unique index and make the right conclusions
about statistics.

If the test is doing a huge number of UPDATEs and no VACUUM anywhere,
then accumulation of dead rows would eventually hurt, but it's not clear
from Scott's report if that's the issue.

I'm inclined to think there's some more subtle bias in the testbed.
I haven't dug into the code to look at how they are managing multiple
connections, but I wonder if say there's something causing the client to
not notice responses from the server right away when it's going through
libpq.

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 ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

On Mon, May 01, 2006 at 01:54:49PM +0200, Steinar H. Gunderson wrote:
> On Mon, May 01, 2006 at 03:05:54AM -0500, Scott Sipe wrote:
> > So, my question is, before I do any further digging, is super-smack
> > flawed?
>
> It's sort of hard to say without looking at the source -- it certainly isn't
> a benchmark I've heard of before, and it's also sort of hard to believe a
> benchmark whose focus seems to be so thoroughly on one database (MySQL). The
> site claims (about PostgreSQL) support that "it looks like it works";
> certainly not a good start for fair benchmarking :-)

If you want a more realistic test, try dbt2:
http://sourceforge.net/projects/osdldbt
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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. +

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq