Monitorando Mysql – Nagios & Munin

Monitorando o Mysql

Administrar uma estrutura baseada em LAMP demanda de muito monitoramento e análise de gráficos de performance. Ferramentas como Munin e Nagios são grandes aliados nesta tarefa e funcionam muito bem , experiência própria 🙂 . A versão do pacote munin-node distrbuída no repositório EPEL , para sistemas Red Hat like, conta com um plugin de monitoramento para o Mysql, plugin esse que permite um monitoramento muito abrangente do Banco de Dados. Este post será dividido em 2 tópicos, um descrevendo como fazer o monitoramento utilizando Nagios, e outro utilizando o Munin.

Nagios monitorando o Mysql

Por padrão a instalação do Nagios conta com um plugin de monitoramento do Mysql , porém mesmo assim, utilizo um plugin chamado “check_mysql_health” , disponível no link: http://labs.consol.de/lang/en/nagios/check_mysql_health/  .  Este plugin consegue monitorar diversas variáveis obtidas através do próprio Mysql , como pode exemplo:

connection-time
tmp-disk-tables
slave-lag

dentre muitas outras . Caso você tenha ficado curioso e queira saber onde o Mysql guarda essas informações utilizadas pelo check_mysql_health , conecte em seu Mysql Server e execute:

SHOW GLOBAL STATUS ;

Este comando irá gerar uma saída extensa, você pode , utilizando uma ferramenta como o Mysql Query Browser, exportar o resultado do comando acima para um CSV e analisá-lo. Com essas informações, é hora de preparármos o plugin para a sua utilização, a minha sugestão é que você tenha uma máquina virtual separada para fazer a compilação deste plugin, sendo quue bastará copiar o executável gerado para os servidores que vocẽ pretende monitorar. Feita essa consideração, vamos à parte prática, na tua VM (tá bom, se não quiser usar VM , faça isso direto no servidor, mas depois não diga que eu não avisei) :

cd ~

wget http://labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz

tar xvf check_mysql_health-2.1.3.tar.gz

cd check_mysql_health-2.1.3

./configure –prefix=/usr \
–with-perl: /usr/bin/perl \
–with-statefiles-dir: /var/tmp/check_mysql_health \
–with-nagios-user: nagios \
–with-nagios-group: nagios \
–with-mymodules-dir: /usr/lib/nagios/ \
–with-mymodules-dyn-dir: /usr/lib/nagios/

Vale lembrar neste momento que:

– É possível que o usuário/grupo utilizado em teu servidor de monitoramento não seja nagios/nagios ;

– Caso a arquitetura do teu sistema operacional seja 64 bits, os plugins do Nagios estarão em : /usr/lib64/nagios/plugins

Ao executar o processo de configuração do check_mysql_health , execute:

make

Após executar este comando , o plugin estará disponível no seguinte diretório “~/check_mysql_health-2.1.3/plugins-scripts” , o script é : check_mysql_health . Se este arquivo existir, posso dizer que estamos na metade do caminho para termos o monitoramento funcionando. Para que o monitoramento funcione corretamente, adicione um novo usuário no Mysql que será responsável por obter informações do monitoramento, para isso execute os comandos abaixo:

# Mysql Master

mysql -u root -p
GRANT SELECT ON *.* TO ‘<USUARIO>’@’localhost’ IDENTIFIED BY ‘<senha>’;
FLUSH PRIVILEGES;

# No Mysql Slave

mysql -u root -p
GRANT SELECT,SUPER,REPLICATION CLIENT ON *.* TO ‘<USUARIO>’@’localhost’ IDENTIFIED BY ‘<senha>’;
FLUSH PRIVILEGES;

DICA: Ainda é possível adicionar a seguinte restrição “WITH MAX_USER_CONNECTIONS 5;” para restringir a quantidade de conexões feitas pelo usuário de monitoramento. Encontrei esta informação em : http://blog.wl0.org/2010/09/careful-how-you-monitor-mysql/ .

Logado no servidor Mysql que será monitorado, instale as dependências para a execução do script copie-o para a pasta de plugins do Nagios:

yum install perl-Class-DBI perl-Math-BigInt-GMP perl-Cache perl-Cache-Cache perl-DBD-MySQL

# Em sistemas 64 bits:

cp check_mysql_health /usr/lib64/nagios/plugins

# Em sistemas 32 bits

cp check_mysql_health /usr/lib/nagios/plugins

Agora que o script encontra-se no seu diretório, altere as configurações do NRPE “/etc/nagios/nrpe.cfg” , adicionando as linhas abaixo:

#  Mysql Master

command[check_mysql]=/usr/lib64/nagios/plugins/check_mysql -H localhost -u <USUÁRIO> -p <SENHA>
command[check_mysql_connection]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode connection-time
command[check_mysql_uptime]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode uptime
command[check_mysql_log_waits]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode log-waits
command[check_mysql_table_lock]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode table-lock-contention
command[check_mysql_index_usage]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode index-usage
command[check_mysql_tmp_table]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode tmp-disk-tables
command[check_mysql_slow_queries]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode slow-queries
command[check_mysql_long_procs]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode long-running-procs

# Mysql Slave

command[check_mysql]=/usr/lib64/nagios/plugins/check_mysql -H localhost -u <USUÁRIO> -p <SENHA>
command[check_mysql_connection]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode connection-time
command[check_mysql_uptime]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode uptime
command[check_mysql_log_waits]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode log-waits
command[check_mysql_table_lock]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode table-lock-contention
command[check_mysql_index_usage]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode index-usage
command[check_mysql_tmp_table]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode tmp-disk-tables
command[check_mysql_slow_queries]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode slow-queries
command[check_mysql_long_procs]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode long-running-procs
command[check_mysql_slave_io]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode slave-io-running
command[check_mysql_slave_running]=/usr/lib64/nagios/plugins/check_mysql_health –username <USUÁRIO> –password <SENHA> –mode slave-sql-running
command[check_mysql_slave_lag]=/usr/lib64/nagios/plugins/check_mysql_health –warning 60 –critical 240  –username <USUÁRIO> –password <SENHA> –mode slave-lag

Não se esqueça de reiniciar o NRPE após realizar as configurações citadas acima. Caso o NRPE esteja sendo executado via XINETD, execute “service xinetd restart” ou caso ele esteja sendo executado como daemon do sistema, execute “service nrpe restart” . Abaixo um exemplo de como o Nagios irá exibir os monitoramentos configurados acima:

Munin monitorando o Mysql

Atualmente o munin-node distribuído pelo repositório EPEL conta com um plugin muito bacana chamado “mysql_” , este plugin conta com uma série de gráficos. A sua configuração é simples e não exige pós-graduação Sysadmin Master Debugging 🙂 . O primeiro passo é adicionar as credenciais utilizadas pelo munin-node para conectar-se ao Mysql, faça isso editando o arquivo “/etc/munin/plugin.d/munin-node” , e adicionando as linhas baixo:

[mysql_*]
env.mysqlconnection DBI:mysql:mysql;host=127.0.0.1;port=3306
env.mysqluser <USUARIO>
env.mysqlpassword <SENHA>

Como o arquivo alterado terá usuário e senha válido no Mysql , altere as permissões do mesmo para que ninguém roube essas credenciais, faça isso executando:

chmod o-r /etc/munin/plugin-conf.d/munin-node

Para facilitar o processo de configuração deste plugin , criei uma pequena sequência de comandos que serão executados no Mysql Master e no Mysql Slave, abaixo os comandos:

# Mysql Master

graph=”bin_relay_log commands connections files_tables innodb_bpool \
innodb_bpool_act innodb_insert_buf innodb_io innodb_io_pend innodb_log \
innodb_rows  innodb_semaphores innodb_tnx network_traffic qcache qcache_mem \
select_types sorts table_locks tmp_tables”

cd /etc/munin/plugins
for i in $graph ; do
ln -s /usr/share/munin/plugins/mysql_ ./mysql_$i
done

# Mysql Slave

graph=”bin_relay_log commands connections files_tables innodb_bpool \
innodb_bpool_act innodb_insert_buf innodb_io innodb_io_pend innodb_log \
innodb_rows  innodb_semaphores innodb_tnx network_traffic qcache \
qcache_mem replication select_types sorts table_locks tmp_tables”

cd /etc/munin/plugins
for i in $graph ; do
ln -s /usr/share/munin/plugins/mysql_ ./mysql_$i
done

Após executar os comando acima, reinicie o munin-node com o comando: “service munin-node restart” . O Munin gerará diversos gráficos , para ilustrar veja o gráfico abaixo:

É isso galera, espero que este post ajude alguém ! Abraços.

Mysql tmp table – Fast-Post

Bom,

hoje, lendo a documentação do Mysql , me deparei com uma informação interessante, e resolvi compartilhá-la aqui, lá vai….Muitas vezes quando executamos o EXPLAIN antes de uma query, por exemplo:

mysql> explain select * from internalmail

A coluna “Extra” pode exibir o seguinte conteúdo:

Using temporary; Using filesort

Se este valor aparecer, tome cuidado e prepare-se para analisar o que os desenvolvedores estão fazendo 🙂 . De qualquer forma, existe um workaround capaz de amenizar este problema, que é : aumentar o valor da diretiva “tmp_table_size” (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size),  ou seja, a diretiva que controla o tamanho máximo que uma tabela temporária pode ter. Vale lembrar, antecipadamente que: se uma tabela temporária em memória , criada pelo Mysql, superar o valor estabelecido nesta variável, a tabela será automaticamente modificada para uma tabela em disco, o que será ainda pior, sendo assim, tome cuidado e observe sempre as variáveis:

Created_tmp_tables / Created_tmp_disk_tables

Estas variáveis são contadores que indicam , respectivamente, quantas tabelas temporarias em memória e em disco foram criadas. ** Para obter estes valores execute no shell do Mysql:

mysql> SHOW GLOBAL STATUS LIKE ‘%Created_tmp%’

Um detalhe que observei apenas hoje está contido no texto abaixo e é o calcanhar de aquiles da configuração das tabelas temporarias :

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. This differs from MEMORY tables explicitly created with CREATE TABLE: For such tables, the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

Se você leu o que está escrito acima, perceberá que a variável “max_heap_table_size”, também influencia no comportamento da criação de tabelas temporárias. Bom, agora vamos lá, como alterar esta variável….é simples abra o arquivo de configuração do Mysql e escreva:

tmp_table_size = <VALOR EM BYTES>

Se não quiser utilizar bytes pois não sabe fazer a conversão….utilize a letra “M” que indicará megabytes!

Bom fica ai a dica, espero que ajude algum ser humano ou alienígena !!