Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?
Philip M. Gollucci wrote: Is it worth having the port remove that recommendation from the /usr/local/share/mysql/*.cnf files ? It probably is worth it, yeah. Kris ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?
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 ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?
Kris Kennaway wrote: > libthr has been around (and performing better than libkse) since the 5.x > days and has been recommended for use since 6.0. Yeah I knew it had been around -- missed the recommend part. >> sysctl kern.timecounter.choice >>kern.timecounter.choice: TSC(-100) ACPI-fast(1000) i8254(0) >> dummy(-100) >> >> sysctl kern.timecounter.hardware >> kern.timecounter.hardware: TSC I meant to say that this made a rather large difference. >> 3) /tmp is a md0 malloc backed device Eh -- I'll switch it. > > Should be swap backed, but it won't make much difference on your workload. > >> (I'm thinking of using tmpfs in 7.0 when I switch) > > tmpfs is not yet production-ready even though it performs better. Yeah that I knew, but I haven't had any issues with it on any of my desktops that run it. >> 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 > idle. Ah boo!!! I have DBs that are SELECT heavy and those that are WRITE heavy. I suppose I'll attached the my.cnf I'm using. Is it worth having the port remove that recommendation from the /usr/local/share/mysql/*.cnf files ? -- Philip M. Gollucci ([EMAIL PROTECTED]) c:323.219.4708 o:703.749.9295x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?
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. -- Philip M. Gollucci ([EMAIL PROTECTED]) c:323.219.4708 o:703.749.9295x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
