Re: Super-smack?

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