Mysql performance

[Michael Vince]

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 at 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 at 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;
>
>  
>