Date: Tue, 18 Sep 2007 21:22:30 +0200 From: Kris Kennaway <kris@FreeBSD.org> To: "Philip M. Gollucci" <philip@ridecharge.com> Cc: "freebsd-questions@freebsd.org" <freebsd-questions@freebsd.org> Subject: Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7? Message-ID: <46F02576.2020503@FreeBSD.org> In-Reply-To: <46EFEFF8.7070202@ridecharge.com> References: <26ddd1750709141351i3646e9bdg8d8b7e93461167f9@mail.gmail.com> <bef9a7920709141441r5c228a8bu1fcf2ea15868c3c@mail.gmail.com> <26ddd1750709151014x2112b022r9bcb999fbf1e7e49@mail.gmail.com> <46EC270A.3020100@FreeBSD.org> <8cb6106e0709151421h7bfdeb6fo7dc671820294e9c7@mail.gmail.com> <46EC4F59.7070104@FreeBSD.org> <8cb6106e0709151435p72cd328by63895421f3a63ea4@mail.gmail.com> <46EC50DA.6000104@FreeBSD.org> <8cb6106e0709151446x4c54a520u2d5cd543ba1541e@mail.gmail.com> <46EC55B8.2090107@FreeBSD.org> <46EEE3ED.6080304@ridecharge.com> <46EF21C2.7010104@FreeBSD.org> <46EFED9B.7010300@ridecharge.com> <46EFEFF8.7070202@ridecharge.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?46F02576.2020503>