MySQL 5.0.22 , FreeBSD 6.1-STABLE: Benchmark

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.

_______________________________________________
freebsd-performance@xxxxxxxxxxx mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@xxxxxxxxxxx"