Date: Tue, 31 May 2005 20:27:12 +1000 From: Michael Vince <michael@roq.com> To: freebsd-database@freebsd.org Subject: Re: Mysql performance Message-ID: <429C3C00.4070208@roq.com> In-Reply-To: <200505261845.58035.pierre-gilles@netaktiv.com> References: <200505261845.58035.pierre-gilles@netaktiv.com>
next in thread | previous in thread | raw e-mail | index | archive | help
I have been doing some benchmark testing on a Dell 1850 Dual P4 server with FreeBSD 5.4 Release for MySQL I didn't write anything down just kind of did these tests while watching TV on this remote server from my laptop over ssh, but I think I remember everything quite well. There might be something useful to other people in my testing. I was testing using supersmack http://vegan.net/tony/supersmack/ I have only been about to give about 5% faster performance from complier based optimization. About 5% extra performance on top of that by my version choice of MySQL 100% performance increase in my choice of my.cnf configuration file. I don't think I checked but something remotely close to 100% performance increase by compiling SMP into my kernel :) I found no noticeable performance increase with Linux Threads compiled into MySQL By adding these to the /etc/make.conf file and recompiling the kernel I believe it helped a tiny bit, but maybe it makes things less stable that goes for MySQL compiler optimizations as well. CPUTYPE=pentium4 CFLAGS= -O2 -pipe Installing MySQL with this portupgrade command gave maybe 5% extra performance with the compiler optimization choices from 'BUILD_OPTIMIZED=yes' which uses -O3 , build without 'BUILD_OPTIMIZED=yes' to use the options from make.conf which should be safer. Building as static is suppose to improve performance according to the MySQL docs. portupgrade -RN -m 'BUILD_STATIC=yes BUILD_OPTIMIZED=yes' /usr/ports/databases/mysql41-server And I found that the mysql40-server port is about 5% faster as well over 4.1 , I have no idea why. portupgrade -RN -m 'BUILD_STATIC=yes BUILD_OPTIMIZED=yes' /usr/ports/databases/mysql40-server These are more obvious increases in default performance. I got almost exactly 100% performance increase ( doubled the amount of querys a second according to super-smack) using the my-large.cnf configuration for MySQL. This is because MySQL in default configuration is well suited to a small database / memory size or people trying it out on their 486's. cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf Kernel compiled with SMP cd /usr/src ; make buildkernel KERNCONF=SMP Regards, Mike Mialon Pierre-Gilles wrote: > Hi, > >I run mysql on a dual-opteron with 2Go of Ram. >I don't use the linuxthread. >I use the mysql-server-4.1.10a on FreeBSD 5.3 AMD64 >I don't understand why I have only one > /usr/local/libexec/mysqld running in ps aux. >And I'm looking for a good tutorial to speed up the mysql-server on a FreeBSD >box. > >The output is the content of mysql query slow... Horrible !!! ># User@Host: ># Query_time: 837 Lock_time: 0 Rows_sent: 1 Rows_examined: 454060 >SET timestamp=1117125449; >SELECT >articles.id_article,articles.id_rubrique,articles.id_secteur,articles.surtitre,articles.titre,articles.soustitre,articles.date,articles.date_redac,articles.date_modif,articles.visites,articles.popularite,articles.statut,articles.accepter_forum,articles.lang,articles.id_trad,MOD(articles.id_article >* UNIX_TIMESTAMP(), 32767) & UNIX_TIMESTAMP() AS alea FROM spip_articles AS >articles,spip_mots_articles AS lien_mot,spip_mots AS mots WHERE >articles.id_article=lien_mot.id_article AND lien_mot.id_mot=mots.id_mot AND >mots.titre='Bandeau' AND articles.statut='publie' GROUP BY >articles.id_article ORDER BY alea LIMIT 0,1; ># Time: 050526 18:37:31 ># User@Host: ># Query_time: 798 Lock_time: 0 Rows_sent: 1 Rows_examined: 454060 >SET timestamp=1117125451; >SELECT >articles.id_article,articles.id_rubrique,articles.id_secteur,articles.surtitre,articles.titre,articles.soustitre,articles.date,articles.date_redac,articles.date_modif,articles.visites,articles.popularite,articles.statut,articles.accepter_forum,articles.lang,articles.id_trad,MOD(articles.id_article >* UNIX_TIMESTAMP(), 32767) & UNIX_TIMESTAMP() AS alea FROM spip_articles AS >articles,spip_mots_articles AS lien_mot,spip_mots AS mots WHERE >articles.id_article=lien_mot.id_article AND lien_mot.id_mot=mots.id_mot AND >mots.titre='Bandeau' AND articles.statut='publie' GROUP BY >articles.id_article ORDER BY alea LIMIT 0,1; > > >
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?429C3C00.4070208>