MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?

[Kris Kennaway]

Philip M. Gollucci wrote:
> Philip M. Gollucci wrote:
>>>> my.cnf
>>>> innodb_thread_concurrency = 8
>>> You want '0' or performance will suck.  There's a basic architectural
>>> flaw in how mysql handles non-zero concurrency values here (innodb
>>> accesses are serialized by a global mutex that protects a counter to
>>> check if it should try to allow more innodb concurrency.  Duh.)
>>>
>>> Anyway, assuming your disks can keep up you should see a big performance
>>> boost when you switch to 7.0.  This is a fairly big "if" though: I don't
>>> know if it's even feasible for a write-heavy database to saturate 8 CPUs
>>> instead of being bottlenecked by disk speeds and leaving the CPUs mostly
> from /usr/local/share/my-innodb-heavy-4G.cnf
> 
> # This permits the application to give the threads system a hint for the
> # desired number of threads that should be run at the same time.  This
> # value only makes sense on systems that support the
> # thread_concurrency()
> # function call (Sun Solaris, for example).
> # You should try [number of CPUs]*(2..4) for thread_concurrency
> thread_concurrency = 8
> 
> # Number of IO threads to use for async IO operations. This value is
> # hardcoded to 4 on Unix, but on Windows disk I/O may benefit from a
> # larger number.
> innodb_file_io_threads = 4
> 
> # Number of threads allowed inside the InnoDB kernel. The optimal value
> # depends highly on the application, hardware as well as the OS
> # scheduler properties. A too high value may lead to thread thrashing.
> innodb_thread_concurrency = 16
> 
> 
> Apparently its only set in this file.
> 
> We should probably submit a bug to MySQL rather then add a patch to
> ports or do both and remove the ports when its released.
> 
> 

I believe the performance bug is well known actually, at least to the 
www.mysqlperformanceblog.com people which is where I got my test config 
from.  I discovered the reason for it recently when I accidentally ran 
with a default config (innodb_thread_concurrency defaulted to 8 for me) 
and spent some time tracking down why performance was terrible until I 
set it back to 0.

Kris