> > Because with native threads, enabling the second CPU (and thus locking
> > MySQL to one thread, one process, -period-, because running another on
> > another port isnt viable) makes the job take just over twice as long.
> This explains your point of view: I never tried to run MySQL on
> dual-processor machines.
*nod*. On a single CPU, native threads gives pretty excellent
performance, in both 4.7-R and 5.0-R. I haven't run a single CPU
linuxthreads test yet (I think I'll go fire one off...)
> Absolutely. Most of FreeBSD's 4.x kernel functions are not reentrant: to
> solve the problem of data corruption if two or more cpu's access the same
> procedure, a global locking mechanism is implemented. What it means, in
> practice, is that if two processes calls the same kernel function, one has
> to wait. They are served sequentially.
> The mechanism worked surprising well for most applications, but not for
> In MySQL, if I recall right, there's one process that forks many threads,
> one for every request. Threads are splitted to many cpus, but because they
> compete to access the same functions, some threads have to wait. This could
> explain why you experience halved performance.
> In FreeBSD 5.x all kernel functions are fully reentrant. They did a great
> job in many areas of the OS, often a complete rewrite to gain more
> performance and features.
> I've not tested FreeBSD 5.0 and MySQL yet, nor I plan to do it soon, because
> I'm happy with FreeBSD 4.x in my work. If you do, please let me (us) know.
I have to say, this is the best explanation of why things work the way
they do I've seen yet. Thank you.
As far as 5.0, the box I'm testing on is running 5.0-R. Compiling with
native threads gives near-identical performance to 4.7-R with native
threads (however, there is a small performance increase in a couple steps
of the job, which is nice to see. Since the hardware configuration did
not change, I chalk this up to 5.0-R's rewrite affecting other parts of
the equation). My assumption right now (and this is based on not having
taken the time to peruse the code, so I'm likely wrong - ignore me if I
am) is that MySQL says, "OK, we're running on FreeBSD. Do we have more
than one CPU present? Yes? OK, lock to one thread." If this is true, we
are willing to "disable" this protective code (if feasible) and see what
kind of performance/stability we get out of MySQL on 5.0-R with native
threads running full bore, unrestricted.
Again, this is just the ramblings of a madman (er, ok, the ramblings of a
tired sysadmin :).
> > If not, this project can't wait. I'll probably have to coerce the boss
> > into building a separate db server from the others (which wouldn't be so
> > bad) on Linux or Solaris.
> Multi-cpu support in Solaris is stable, is efficient, has many years of
> development and it's ready to use. There's probably a reason why MySQL AB
> develops MySQL on Solaris first.
I wasn't aware that they developed on Solaris first, but I've had
first-hand experience of Solaris multi-cpu support. It's -great-. I
almost got a chance to replace an Oracle cluster with MySQL (because the
support contracts are much cheaper :) in a previous life, but that life
got the axe too soon.
Again, thanks for that explanation.