MySQL 5.0.22 , FreeBSD 6.1-STABLE: Benchmark

[Hugo Silva]

Today I decided to benchmark MySQL 5 performance on FreeBSD 6.1-STABLE.
This server is a Dual Xeon 2.8GHz, 4GB of RAM and 2x73GB SCSI disks that 
do 320MB/s

For all the tests, I restarted mysqld prior to starting the test,  
waited for about 1 minute for it to settle down, and ran super smack. 
For the consecutive runs, I executed super-smack right after the 
previous run ended.

Switching from HTT to no HTT was achieved by 
machdep.hyperthreading_allowed, and switching from/to libpthread/libthr 
was done via libmap.conf.

System:

FreeBSD ?? 6.1-STABLE FreeBSD 6.1-STABLE #3: Mon Jul  3 03:10:35 UTC 
2006     ??@??:/usr/obj/usr/src/sys/DATABASE  i386

Here are the results:


MySQL 5.0.22, built with BUILD_OPTIMIZED=yes and WITH_PROC_SCOPE_PTH=yes


=== 4BSD + libthr + HTT on ===

Run #1
connect: max=4ms  min=1ms avg= 3ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           20405.86

Run #2
connect: max=3ms  min=1ms avg= 2ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           20253.53

Run #3
connect: max=4ms  min=2ms avg= 2ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           20270.33




=== 4BSD + libthr + HTT off ===

Run #1
connect: max=5ms  min=2ms avg= 3ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           18253.60

Run #2
connect: max=6ms  min=1ms avg= 3ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           18350.27

Run #3
connect: max=4ms  min=1ms avg= 2ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           18529.71


=== 4BSD + libpthread + HTT on ===

Run #1:
connect: max=17ms  min=2ms avg= 7ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      5           0           3935.94


Run #2:
connect: max=18ms  min=1ms avg= 8ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      2           0           3919.89

Run #3:
connect: max=22ms  min=1ms avg= 13ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      2           0           3911.66


=== 4BSD + libpthread + HTT off ===
connect: max=12ms  min=1ms avg= 5ms from 10 clients

Run #1:
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           11193.40

Run #2:
connect: max=6ms  min=4ms avg= 5ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           11428.30

Run #3:
connect: max=7ms  min=4ms avg= 5ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      1           0              13714.02











=== ULE + libthr + HTT on ===
Run #1:
connect: max=2ms  min=0ms avg= 0ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      1           0           16179.09

Run #2:
connect: max=14ms  min=0ms avg= 7ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           17451.31

Run #3:
connect: max=5ms  min=1ms avg= 3ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      1           0              15787.02


=== ULE + libthr + HTT off ===

Run #1:
connect: max=6ms  min=6ms avg= 6ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0              11588.19

Run #2:
connect: max=220ms  min=2ms avg= 46ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           10651.16

Run #3:
connect: max=10ms  min=0ms avg= 5ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           10158.63



=== ULE + libpthread + HTT on ===

Run #1:
connect: max=9ms  min=1ms avg= 7ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      2           0           5869.52

Run #2:
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      2              0        5839.95

Run #3:
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      2           0           5680.97



=== ULE + libpthread + HTT off ===
Run #1:
connect: max=10ms  min=1ms avg= 8ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0              0        6111.21

Run #2:
connect: max=1597ms  min=1ms avg= 177ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      0           0           7225.26

Run #3:
connect: max=9ms  min=1ms avg= 4ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000      1           0           8187.13





Conclusions: 4BSD performed much better than ULE.
             libthr performs a lot better than libpthread. I'd risk 
saying libpthread has issues!
             Hyperthreading is sometimes benefitial. On the winning 
combination (4BSD+libthr), it is benefitial. On some other combinations 
(4BSD+libpthread), it seems to greatly impair performance.