[FUG-BR] Optimizações do MySQL 5.0 e 5.1

[Marcelo Gondim]

Olá Pessoal,

Devido aos problemas que tive com o MySQL no FreeBSD resolvi fazer umas 
pesquisas sobre este no FreeBSD e gostaria de compartilhar com todos e 
até saber a opinião de todos vocês sobre esse assunto.

Vasculhando no site mysql.com estava lendo 2 notas [1] [2] sobre MySQL 
no FreeBSD, embora eu tenha achado eles antigas percebi alguns 
comentários não muito legais como o abaixo:

Alternatively, you can link MySQL on FreeBSD 4.x against the 
LinuxThreads library, which avoids a few of the problems that the native 
FreeBSD thread implementation has. For a very good comparison of 
LinuxThreads versus native threads, see Jeremy Zawodny's article 
/FreeBSD or Linux for your MySQL Server?/ at 
http://jeremy.zawodny.com/blog/archives/000697.html.

Interessante é que o Jeremy não aconselhava o MySQL no FreeBSD mas 
depois em outro artigo dele, ele mudou de opinião quando passou à usar o 
MySQL com LinuxThreads.

Pelo que andei lendo a libthr, que é default, parece ser bem melhor que 
a LinuxThreads e por isso a LinuxThreads está quebrada quando tentamos 
compilar o MySQL com ele. Nas notas do site do mysql aconselham à usar o 
LinuxThreads para evitar alguns problemas.

Alguém tem algo à comentar sobre isso?

Olhando as opções de compilação do MySQL 5.0 e 5.1 também percebi 
diferenças de nas opções:

MySQL 5.0:

WITH_CHARSET=charset    Define the primary built-in charset (latin1).
WITH_XCHARSET=list      Define other built-in charsets (may be 'all').
WITH_COLLATION=collate  Define default collation (latin1_swedish_ci).
WITH_OPENSSL=yes        Enable secure connections.
WITH_LINUXTHREADS=yes   Use the linuxthreads pthread library.
WITH_PROC_SCOPE_PTH=yes Use process scope threads (try it if you use 
libpthread).
BUILD_OPTIMIZED=yes     Enable compiler optimizations (use it if you 
need speed).
BUILD_STATIC=yes        Build a static version of mysqld. (use it if you 
need even more speed).
WITHOUT_THR_ALARM=yes   Disable signals (this reduces kernel lock 
contention on SMP, but has the side effect that you can't kill clients 
that are sleeping).
WITHOUT_INNODB=yes      Disable support for InnoDB table handler.
WITH_ARCHIVE=yes        Enable support for Archive Storage Engine.
WITH_CSV=yes            Enable support for CSV Storage Engine.
WITH_FEDERATED=yes      Enable support for Federated Storage Engine.
WITH_NDB=yes            Enable support for NDB Cluster.

MySQL 5.1:

WITH_CHARSET=charset    Define the primary built-in charset (latin1).
WITH_XCHARSET=list      Define other built-in charsets (may be 'all')
WITH_COLLATION=collate  Define default collation (latin1_swedish_ci).
WITH_OPENSSL=yes        Enable secure connections (define WITHOUT_YASSL 
for backward compatibility).
WITH_LINUXTHREADS=yes   Use the linuxthreads pthread library
WITH_PROC_SCOPE_PTH=yes Use process scope threads (try it if you use 
libpthread).
WITH_FAST_MUTEXES=yes   Replace mutexes with spinlocks.
BUILD_OPTIMIZED=yes     Enable compiler optimizations (use it if you 
need speed).
BUILD_STATIC=yes        Build a static version of mysqld. (use it if you 
need even more speed).
WITH_NDB=yes            Enable support for NDB Cluster.

Reparem que existem algumas diferenças do 5.0 para o 5.1. No 5.1 temos o 
WITH_FAST_MUTEXES que me chamou a atenção. Alguém já usou ele e pode me 
dizer se existe ganho de performance usando-o em um cenários com mais de 
3000 conexões simultâneas no mysql?

Outra questão é qual das versões está mais rápida e estável? 5.0, 5.1 ou 
5.5? Gostaria também de ler comentários e experiências sobre.  :)

O que tenho percebido é que comparando os 2 sistemas FreeBSD e Linux 
usando a mesma versão de MySQL, configurações e aplicação em Apache e 
PHP o comportamento é parecido no MySQL em ambos mas o consumo de 
processamento é muito maior no FreeBSD. Enquanto que no Linux percebi 
que o consumo de processamento fica em torno de 85% à 300% algumas vezes 
mas mantendo um load de 1.x à 2.x, no FreeBSD esse mesmo ambiente 
consumia 150% à 600% e algumas vezes vi até 1200%. Quando chegava em 
400% de uso no MySQL o load já subia para 12.x e até mais. Em 
determinado momento estourava e chegava em load de 800.x. Porque isso 
acontece ainda não descobri, aumentei diversas sysctls e loader.conf e 
mesmo assim não resolvia o problema. Abaixo como estavam sysctl.conf e 
loader.conf:

sysctl.conf:
=========
kern.maxproc=10000
kern.threads.max_threads_per_proc=4096
kern.ipc.somaxconn=4096
kern.ipc.maxsockets=204800
kern.ipc.nmbclusters=262144
kern.maxfiles=204800
kern.maxfilesperproc=200000
net.inet.ip.redirect=0
net.inet.ip.sourceroute=0
net.inet.ip.accept_sourceroute=0
net.inet.icmp.maskrepl=0
net.inet.icmp.log_redirect=0
net.inet.icmp.drop_redirect=1
net.inet.tcp.drop_synfin=1
net.inet.udp.blackhole=1
net.inet.tcp.blackhole=2
net.inet6.icmp6.nodeinfo=0
net.inet6.ip6.use_tempaddr=1
net.inet6.ip6.prefer_tempaddr=1
net.inet6.icmp6.rediraccept=0
net.inet.ip.fw.dyn_buckets=65536
net.inet.ip.fw.dyn_max=65536
net.inet.ip.fw.dyn_ack_lifetime=120
net.inet.ip.fw.dyn_syn_lifetime=10
net.inet.ip.fw.dyn_fin_lifetime=1
net.inet.ip.fw.dyn_short_lifetime=5

loader.conf:
==========
loader_logo="beastie"
kern.maxusers=1024
kern.ipc.semmnu=256
kern.ipc.semmns=1024
kern.ipc.semmni=520
kern.ipc.semume=100
kern.ipc.shmmni=256
kern.ipc.msgseg=32767
kern.ipc.msgssz=32
kern.ipc.msgmnb=65535
kern.ipc.msgtql=2046

O servidor que testei é esse:

hw.machine: amd64
hw.model: Intel(R) Xeon(R) CPU           E5645  @ 2.40GHz
hw.ncpu: 12
hw.byteorder: 1234
hw.physmem: 51457007616
hw.usermem: 44779876352

FreeBSD teste.localhost.net 9.1-STABLE FreeBSD 9.1-STABLE #0 r245225: 
Wed Jan 9 16:28:50 BRST 2013 
root em teste.localhost.net:/usr/obj/usr/src/sys/TESTE amd64

Gostaria dos comentários de vocês e ideias para futuros testes.  :)

[1] http://dev.mysql.com/doc/refman/5.0/en/freebsd.html
[2] http://dev.mysql.com/doc/mysql-linuxunix-excerpt/5.0/en/freebsd.html

[]'s
Gondim