Re: FreeBSD5.3-RC1 MySQL Performance

jesk wrote:
> Hello,
> 
> i found some time to make some performance tests with mysql under
> FreeBSD5.3-RC1. Hardware is a HP DL360 with 2x2,8GHz Xeon CPU´s, 2GB,
> deactivated HTT and u160/10krpm scsi drive. For reference values i took a
> RedHat Fedora with native threads (NPTL) on 2.6 kernel and the same
> hardware. for benchmarks i used super-smack with the default smack files.
> the MySQL backend was MyISAM.
> 
> with both setups the mysql was always under high load which seemed to me for
> a good sign to recognize expressive values on thread execution and mysql
> performance without loosing to much time in i/o.
> 
> the benchmark is executing 1000 sql-select queries*10 concurrent clients on
> a 90k row table with a random not really high cacheable where-statement on
> the index:
> ----
> 15985 queries per second
> (pthreads without process scope threads, sched_4bsd and preemption)
> 6139   queries per second
> (pthreads with process scope threads, sched_4bsd and preemption)
> 10779 queries per second
> (linuxthreads, sched_4bsd and preemption)
> fedora result:
> 11900 queries per second
> ----

Your results for system scope threads is actually pretty good and is
quite a bit higher than what I've gotten in my tests.  Your process
scope thread is about the same that I've seen, and indeed indicates
a serious problem with KSE process scope threads.  Hopefully the threads
folks can look at this soon.  Your Fedora results are quite a bit lower
than what I got on RHEL3, but that's a very different comparison.
Overall, I'm very encouraged by this; your test methods look very good
and I'm happy that the system scope test did so well.

> same test (same parameters) but with a update query first and then a select
> query on the same key i realized worse values for freebsd:
> ----
> 2027.52 queries per second
> (pthreads without process scope threads, sched_4bsd and preemption)
> 1146.66 queries per second
> (pthreads with process scope threads, sched_4bsd and preemption)
> 3040.78 queries per second
> (linuxthreads, sched_4bsd and preemption)
> fedora result:
> 3920.21 queries per second
> ----

The roughly agrees with what I've seen.  There was talk about the
difference between FreeBSD and Linux being due to things like the fsync
call being much more strict on FreeBSD.  I haven't had a chance to
investigate this yet.  It is interesting that linuxthreads did so much
better here.

> 
> i checked if i could tune up the update query procedure with writing on a
> ramdisk, but this wasnt a highly profit.
> if i could use the mixture of linuxthreads on updates and pthreads on select
> queries without the use of proc scope it would
> be a good answer to linux, but fedora wasnt reachable in its update
> operation..
> 
> 
> here the relevant used mysql values in this test:
> ----
> query_cache_size=64000000
> key_buffer_size=1024M
> table_cache=128
> thread_cache_size=128
> max_connections=1000
> ----
> 
> maybe someone got some hints for improvement of this situation...

Instrumenting mysqld to show if fsync is being used, whether it is 
blocking all threads, and how long it's taking on Linux vs. FreeBSD
would be very interesting.

Scott