Se você possui um servidor dedicado e vem reparando que este está ficando meio lento devido a grande utilização de recursos pelo MySQL, seus HDs estão lentos devido ao alto I/O. A dica de hoje talvez te ajudará a melhorar isto.
O MySQL pode utilizar tabelas temporárias durante a execução de uma consulta, existem alguns métodos para evitar isto já na fase de desenvolvimento das aplicações , entretanto, as vezes este processo não pode ser totalmente evitado e por padrão, o MySQL utilizará o seu diretório de arquivos temporários para armazenamento destas tabelas, e é aí que mora o problema: este diretório geralmente está montado em um dispositivo de disco rígido e dependendo de seu servidor, o acesso as tabelas temporárias vai estar compartilhando de recursos de leitura e escrita em disco com outros serviços, como e-mails e webserver, por exemplo. Nesse caso, como o desempenho dependerá da velocidade de entrada e saída do disco, não só o banco de dados mas todo o disco se tornará extremamente lento…
Mas e aí, qual é a solução? TMPFS!
O TMPFS é um sistema de arquivos localizado na memória RAM de seu servidor, se o servidor possuir memória RAM suficiente disponível, os arquivos gravados lá vão ignorar o I/O de disco rígido por completo, o que certamente proporcionará um desempenho significativamente mais rápido pelo fato destes dispositivos serem bem mais rápidos que os discos rígidos convencionais.
Legal, isso parece interessante.. como vou utilizar esse sistema de arquivos em memória?
Primeiramente teremos que criar um diretório vazio em seu servidor, este diretório será utilizado para montar o sistema de arquivos da memória, isto pode ser feito utilizando o comando mkdir, como pode ser visto abaixo:
root@server [~]# mkdir /tmpfs
No exemplo acima nós criamos um diretório chamado ‘tmpfs’ na raíz do disco principal, agora vamos montar o sistema de arquivos da memória neste diretório para que, posteriormente, seja possível utilizá-lo como disco para as tabelas temporárias do MySQL. O comando para isto será este:
root@server [~]# mount tmpfs /tmpfs -t tmpfs
Note que, desta forma, se o servidor precisar ser reinicializado a configuração será perdida, uma vez que este ‘device’ não está configurado para ser montado automaticamente, se não quiser repetir comandos, basta adicionar uma linha referente a ativação do mesmo no arquivo /etc/fstab.
Agora vamos verificar se tudo ocorreu com sucesso até aqui, usaremos o comando abaixo:
root@server [~]# df -h | grep tmpfs
Caso tudo tenha ocorrido bem, o resultado deste comando será algo similar a isto:
root@server [~]# df -h | grep tmpfs
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 4.0K 2.0G 1% /tmpfs
Ok, já montamos o tmpfs e agora vamos fazer o MySQL usar este diretório para as suas tabelas temporárias, isto poderá ser feito de forma simples, basta adicionar a linha abaixo em seu arquivo de configuração (geralmente /etc/my.cnf) e reinicializar o serviço.
tmpdir = /tmpfs
É importante informar que este tipo de alteração só trará ganho de desempenho para servidores cujo overload no serviço é causado pelo excesso de conexões a estas tabelas temporárias em disco, em servidores onde isto não existe, a alteração provavelmente não trará notáveis ganhos de desempenho. Para saber se o seu servidor se encaixa neste perfil, basta executar o comando abaixo durante o pico de acessos ao serviço.
root@server [~]# watch -n0 "mysqladmin proc"
Se você perceber uma grande quantidade de queries com o status ‘Copying to tmp table’, esta configuração certamente irá lhe ajudar bastante, caso contrário, recomendamos que verifique se existe alguma interação mal projetada em seus scripts que possam estar levando seu banco de dados a não funcionar de forma apropriada.
Se o seu servidor se encaixa no perfil adequado para este tweak, uma boa dica é monitorar o iowait de seu servidor antes de fazer o ajuste e depois compará-lo com o iowait após o ajuste, isto pode ser feito através do comando iostat, que em algumas distribuições está inserido no pacote sysstat.
Efeitos colaterais da utilização deste tipo de configuração
Em todas as nossas pesquisas e testes, não localizamos nenhum efeito colateral notável além da boa utilização de memória RAM, uma vez que este tweak vai alocar uma certa quantidade de memória, ou seja, se o seu servidor não possuir uma boa quantidade de memória livre, a recomendação é não fazer isto.
De acordo com o bugtrack do MySQL, existe um bug que faz com que a diretiva tmpdir não seja respeitada por execuções de GROUP BY. Ou seja, nestes casos, o nosso velho amigo disco rígido continuará sendo utilizado.
Bom dia à todos!
