Re: Help for very bad perf for MySQL
Le 26/11/2007 à 22:34:34-0800, Ted Mittelstaedt a écrit Sorry yeasterday I don't have time to answer you. I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Start with the obvious stuff first. How big is the database? How big is system ram? If you have less ram than you have database then mysql will have to go to the hard disk for the select which will kill it's performance. Well : Database size ~ 180Mo Ram of server = 4 Go 2 processeurs. Nothing run on this server (charge is near zero). The disk I/O is running very fast. The make buildworld is fast too (I don't have measure but it's «fast» ;-)) Regards. -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Mar 27 nov 2007 13:58:17 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Le 26/11/2007 à 13:31:12+0100, Jan Catrysse a écrit I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). 6 seconds seem to be an awful lot. What kind of query are you running on what kind of database / contents? I don't really known it's some scientifical data. But the problem is on a basic linux pc (with SATA disk) the time is 0.6 sec with same request and same data. And it's for web applications. At 6 sec for one request it's become very long for the visitor because the application make many requests. Regards -- Albert SHIH Did you try pinpointing down the problem to make sure their is not another bottleneck? Is the system running in production environment for the moment or are you the sole user? No the server is empty (only root can logging) and no service running (other thant Mysql and apache). And when I try this test the load of the server is near zero. How did you install MySQL? I my experience (but I can be wrong) the default settings give the best performance on 5.x MySQL FreeBSD 6.2. So no Linux threads and stuff... Directly from the ports. Regards. JAS -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Mar 27 nov 2007 14:00:40 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Le 26/11/2007 à 13:29:35+0100, Ivan Voras a écrit Albert Shih wrote: Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. For starts, if you didn't do it already, copy /usr/local/share/mysql/my-huge.cnf to /etc/my.cnf and try again. These are just some general settings, they might or might not help you. It's change nothing but thanks for you answer Regards. JAS -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Mar 27 nov 2007 14:02:06 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Le 26/11/2007 à 07:20:43-0500, Philip M. Gollucci a écrit Albert Shih wrote: Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. Well -- we'll need more information, but as your say, if its not threading related what makes you think its FreeBSD. You'd probably have better luck over on [EMAIL PROTECTED] Thanks for this information. Also, your my.cnf is the next step. [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Regards. JAS -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Mar 27 nov 2007 14:02:31 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
On Mon, 26 Nov 2007 13:03:19 +0100 Albert Shih [EMAIL PROTECTED] wrote: I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. I don't really known it's some scientifical data. But the problem is on a basic linux pc (with SATA disk) the time is 0.6 sec with same request and same data. And it's for web applications. At 6 sec for one request it's become very long for the visitor because the application make many requests. It may also be a simple database administration issue: If selects are taking so long, I'd strongly suspect that an INDEX table is either missing or damaged. Are you 100% sure that the database schema is *identical* on the Linux and FreeBSD machines? Perhaps dropping and rebuilding the index tables could speed things up? You could also try to listen to the disks while that slow select is performed: if the disks are thrashing, AND the swap activity is not really higher than else (vmstat -s, or top), it's a dead giveaway that mysqld is doing more disk i/o than necessary, i.e. check the index tables. If on the contrary the disks are quiet while the select runs, check if mysqld is accumulating CPU time (with top): if it is NOT, I'd guess it is some issue with the threading library, i.e. some threads are deadlocked and waiting. -cpghost. -- Cordula's Web. http://www.cordula.ws/ ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Help for very bad perf for MySQL
-Original Message- From: Albert Shih [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 5:00 AM To: Ted Mittelstaedt Cc: freebsd-questions@freebsd.org Subject: Re: Help for very bad perf for MySQL Le 26/11/2007 à 22:34:34-0800, Ted Mittelstaedt a écrit Sorry yeasterday I don't have time to answer you. I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Start with the obvious stuff first. How big is the database? How big is system ram? If you have less ram than you have database then mysql will have to go to the hard disk for the select which will kill it's performance. Well : Database size ~ 180Mo Ram of server = 4 Go 2 processeurs. Nothing run on this server (charge is near zero). The disk I/O is running very fast. The make buildworld is fast too (I don't have measure but it's «fast» ;-)) Is Hyperthreading enabled (by default it is not under FreeBSD) mysql is heavily dependent on threading, if it is not built and linked into the freebsd threads package you will get poor performance. Some folks have installed the linux compat libs and linked mysql into the linux threads package and reported good results. Ted ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Is Hyperthreading enabled (by default it is not under FreeBSD) mysql is heavily dependent on threading, if it is not built and linked into the freebsd threads package you will get poor performance. Some folks have installed the linux compat libs and linked mysql into the linux threads package and reported good results. Actually, on 6.2, it's better to use libthr instead of libpthread. This can be done for MySQL only, but to test this without recompiling MySQL, he can: % echo libpthread.so.2 libthr.so.2 /etc/libmap.conf Then restart the mysql server and test again. I noticed a huge increase in performance on 6.2 with libthr instead of libpthread. It wasn't a 10x improvement, though, so there is definitely something else going on with his setup. Regards, Josh ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Help for very bad perf for MySQL
-Original Message- From: [EMAIL PROTECTED] [mailto:owner-freebsd- [EMAIL PROTECTED] On Behalf Of Josh Carroll Sent: Tuesday, November 27, 2007 8:12 AM To: Ted Mittelstaedt Cc: [EMAIL PROTECTED]; freebsd-questions@freebsd.org Subject: Re: Help for very bad perf for MySQL Is Hyperthreading enabled (by default it is not under FreeBSD) mysql is heavily dependent on threading, if it is not built and linked into the freebsd threads package you will get poor performance. Some folks have installed the linux compat libs and linked mysql into the linux threads package and reported good results. Actually, on 6.2, it's better to use libthr instead of libpthread. This can be done for MySQL only, but to test this without recompiling MySQL, he can: % echo libpthread.so.2 libthr.so.2 /etc/libmap.conf Then restart the mysql server and test again. I noticed a huge increase in performance on 6.2 with libthr instead of libpthread. It wasn't a 10x improvement, though, so there is definitely something else going on with his setup. Regards, Josh Here are some things that helped us on a high-volume MySQL server. -- /etc/sysctl.conf -- (these can be added dynamically from the command line) kern.threads.max_groups_per_proc=4 kern.threads.max_threads_per_proc=4 kern.maxfiles=65535 kern.maxfilesperproc=65535 -- /boot/loader.conf -- (You'll have to reboot for these to take effect) kern.maxdsiz=1073741824 # 1GB kern.dfldsiz=1073741824 # 1GB kern.maxssiz=134217728 # 128MB -- /etc/libmap.conf -- (as Josh said) [mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so Regards, Mike ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Help for very bad perf for MySQL
Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Regards -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Lun 26 nov 2007 12:46:06 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Help for very bad perf for MySQL
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Albert Shih Sent: Monday, November 26, 2007 12:50 PM To: freebsd-questions@freebsd.org Subject: Help for very bad perf for MySQL Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Regards -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Lun 26 nov 2007 12:46:06 CET 6 seconds seem to be an awful lot. What kind of query are you running on what kind of database / contents? Regs, Jan ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Le 26/11/2007 à 13:01:47+0100, Jan Catrysse a écrit -Original Message- I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Regards -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Lun 26 nov 2007 12:46:06 CET 6 seconds seem to be an awful lot. What kind of query are you running on what kind of database / contents? I don't really known it's some scientifical data. But the problem is on a basic linux pc (with SATA disk) the time is 0.6 sec with same request and same data. And it's for web applications. At 6 sec for one request it's become very long for the visitor because the application make many requests. Regards -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Lun 26 nov 2007 13:00:32 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Help for very bad perf for MySQL
Albert Shih wrote: Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. Well -- we'll need more information, but as your say, if its not threading related what makes you think its FreeBSD. You'd probably have better luck over on [EMAIL PROTECTED] A good start would be the query itself, and the output of EXPLAIN for that query. Also, your my.cnf is the next step. -- Philip M. Gollucci ([EMAIL PROTECTED]) o:703.549.2050x206 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: Help for very bad perf for MySQL
Albert Shih wrote: Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. For starts, if you didn't do it already, copy /usr/local/share/mysql/my-huge.cnf to /etc/my.cnf and try again. These are just some general settings, they might or might not help you. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Help for very bad perf for MySQL
I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). 6 seconds seem to be an awful lot. What kind of query are you running on what kind of database / contents? I don't really known it's some scientifical data. But the problem is on a basic linux pc (with SATA disk) the time is 0.6 sec with same request and same data. And it's for web applications. At 6 sec for one request it's become very long for the visitor because the application make many requests. Regards -- Albert SHIH Did you try pinpointing down the problem to make sure their is not another bottleneck? Is the system running in production environment for the moment or are you the sole user? How did you install MySQL? I my experience (but I can be wrong) the default settings give the best performance on 5.x MySQL FreeBSD 6.2. So no Linux threads and stuff... Regs, Jan ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Help for very bad perf for MySQL
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Albert Shih Sent: Monday, November 26, 2007 3:50 AM To: freebsd-questions@freebsd.org Subject: Help for very bad perf for MySQL Hi all I've already send a message. But I don't receive any answer :-(. I try again and hope there more solution I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm running Mysql 5.X on this server and the performance of MySQL is very bad. For some complexe select I've got ~6secondes (on some basic Linux it's take 0.6 sec). And I think this is nothing about thead (that's mean I don't think FreeBSD 7.0 can solve my problem) because it's just for one select. The server have two SAS 10 000 tr/m disks. Anyone have some advise to tunning FreeBSD or MySQL for increase the perf ? Start with the obvious stuff first. How big is the database? How big is system ram? If you have less ram than you have database then mysql will have to go to the hard disk for the select which will kill it's performance. Ted ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
