Archive for abril \22\-02:00 2011

Tuning no MySQL

Posted on abril 22, 2011. Filed under: MySQL, Tuning |

Neste Post iremos abordar alguns parâmetros da configuração do MySQL que influenciam na perfomance do banco de dados. A idéia é que você, leitor, tenha conhecimento para poder manipular esses parâmetros e melhorar o desempenho do seu banco de dados. Estou abordando somente alguns parâmetros envolvidos: max_connections, table_cache, thread_cache_size, query_cache_size.  Existem outros parâmetros que podem ajudar a melhorar a perfomance, mas irei abordar em posts futuros.

max_connections:

Determina o número máximo de conexões simultâneas permitidas. Por padrão o valor é 100. Caso sua aplicação esteja recebendo a mensagem de erro “Too many connections” significa que estourou o limite máximo de conexões simultâneas, portanto esta na hora de aumentar o valor desse parâmetro. O valor desse parâmetro influência no consumo de memória do servidor, portano esteja atento à esse ponto. Para monitorar as conexões ativas no momento, use o comando SHOW PROCESSLIST no console do mysql. Exemplo:

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: internet
Host: 192.168.0.10:36461
db: testedb
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 2. row ***************************
Id: 2
User: internet
Host: 192.168.0.10:36460
db: testedb
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 3. row ***************************
Id: 3
User: internet
Host: 192.168.0.10:36461
db: testedb
Command: Query
Time: 0
State: Writing to net
Info: select * from tabela_teste
3 rows in set (0.00 sec)

Nesse exemplo, no momento da excução do comando SHOW PROCESSLIST, haviam 3 processos simultânos em execução. A saída traz outras informações interessantes conforme podem observar acima. Exemplo:

max_connections = 200

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

table_cache:

table_cache é o número de tabelas abertas em cache para todos threads. Aumentar esse valor aumenta o número de file descriptors que o MySQL irá precisar abrir para trabalhar. Esse parâmetro esta relacionado com o max_connections: Caso tenha o valor de 200 para max_connections, você deve setar table_cache para 200 * N, onde N é o número máximo de tabelas usada por join em qualquer das queries que você estiver usando na sua aplicação. Aumentar muito o valor desse parâmetro pode fazer com que o MySQL exceda o número máximo de file descriptors abertos por processo. Este é um limite definido pelo S.O. Tenha certeza que o seu S.O.  permita abrir o número de file descritptors definido em table_cache. No Linux, para checar o número máximo de file descriptors permitidos use o comando abaixo:

# cat /proc/sys/fs/file-max
2210424

Exemplo:

table_cache = 800

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

query_cache_size:

É a quantidade de memória alocada para o cache de resultado de queries. O valor padrão é 0. Isso siginifica que o cache é desabilitado por padrão. Os valores permitidos são múltiplos de 1024.  O valor mínimo para esse parâmetro é 40Kb, reservado para alocação de suas estruturas. O tamanho exato depende da arquitetura do sistema. Aconselho setar o valor por volta de 128M, é claro que você deve avaliar a estrutura do seu servidor antes de trabalhar com esses valores, principalmente memória.

query_cache_size = 128M

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

thread_cache_size:

Quantidade de threads que  o servidor irá cachear para re-utilização. Quando um cliente desconecta a thread irá para um cache podendo dessa forma ser reutilizadaDessa maneira diminuimos a criação de novas threads para atender novos clientes. Esse parâmetro pode ser aumentado para melhorar a performance caso seu servidor tenha muitas conexões por segundo.  Através de um cálculo utilizando o número de conexões criadas e o número de threads novas criadas é possível determinar a eficiência desse cache, e então determinar um valor mais apropriado para esse parâmetro. A taxa de acerto baseado no cálculo abaixo deve estar o mais próximo possível de 99%:

100 - ((Threads_created / Connections) * 100)

Threads_created é o número de thread criadas desde que o MySQL foi iniciado. Connections é o número total de conexões criadas desde que o servidor do MySQL foi iniciado.  Nossa intenção é que o número de threads criadas seja o menor possível em relação ao número total de conexões realizadas.

Para obter o número de threads criadas, execute o comando abaixo no console do mysql:

mysql> SHOW STATUS LIKE '%thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 30    |
| Threads_connected      | 5     |
| Threads_created        | 600   |
| Threads_running        | 1     |
+------------------------+-------+
6 rows in set (0.00 sec)

O campo que nos interessa na lista acima é o Threads_created, valor 600. Portanto, desde que o MySQL foi iniciado somente 600 threads novas foram criadas.

Para obter o número de conexões criadas desde a inicialização do MySql, execute o comando abaixo no console do MySQL:

mysql> SHOW STATUS LIKE '%connections%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Connections          | 10000    |
| Max_used_connections | 600      |
+----------------------+----------+
2 rows in set (0.00 sec)

O valor que nos interessa é o 10000.  Vamos agora ao nosso cálculo:

100 - ((600 / 10000) * 100)

O Resultado é 94% (significa que 94% das conexões utilizaram threads em cache). O ideal é que o resultado esteja pelo menos por volta de 99%. Portanto, o valor do nosso parâmetro, thread_cache_size, esta baixo e precisa ser aumentado. Aconselho ir aumentado esse campo até que o nosso calculo resulte por volta de 99%. No exemplo acima, o valor do campo thread_cache_size esta 30. Podemos ir aumentando de 10 em 10 até que cheguemos no resultado desejado.

Exemplo:

thread_cache_size = 60
PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

O objetivo deste post foi abordar alguns parâmetros do MySQL que influenciam no desempenho do banco de dados. Não abordei todos, porém ao trabalhar com esses parâmetros acima o desempenho do seu banco irá melhorar de forma significativa. Já tive grandes resultados fazendo esses tunings em um banco que recebe muitos acessos. Espero que aproveitem.





Ler Post Completo | Make a Comment ( 6 so far )

Dump de stored procedures no MySQL

Posted on abril 14, 2011. Filed under: MySQL |

Por padrão, ao utilizar o comando mysqldump para gerar dump de um banco de dados, as rotinas ou stored procedures, não são geradas no dump. Isso pode ser um problema, pois ao utilizar o comando mysqldump temos em mente que toda a estrutura e todos os dados do banco de dados estão sendo enviados para o dump. Acabei descobrindo isso por acaso, após migrar um banco de dados, o desenvolvedor recalmou que a procedure dele não existia mais no banco de dados novo.

Para que as stored procedures sejam enviadas para o dump, você deve passar o parâmetro –routines para o mysqldump. Por exemplo:

# mysqldump --routines nome-banco

PS: Só lembrando um detalhe, as triggers são geradas no dump por padrão.

Caso você deseje gerar no dump somente das stored procedures do seu banco de dados, como foi o que aconteceu no meu caso, pois na minha migração faltou somente as procedures, use o comando abaixo:

# mysqldump --triggers=false --routines --no-create-info --no-data --no-create-db nome-banco > rotinas.sql

O arquivo rotinas.sql vai conter somente as stored procedures do seu banco de dados nome-banco. É só importá-las no banco de destino.

Ler Post Completo | Make a Comment ( None so far )

Monitorando consultas lentas no MySQL

Posted on abril 6, 2011. Filed under: MySQL, Tuning |

Muitas vezes enfrentamos problemas com desempenho em nossos sites, principalmentes aqueles que recebem muitos acessos. Descobrir onde esta o gargalo é um trabalho um tanto complexo principalmente porquê temos que avaliar uma série de variáveis. (CPU, memória, link, limites no banco de dados, limites no serviço de web, índices, entre outros). Neste post vou focar somente como avaliar problemas com lentidão no MySQL, mais especificamente monitorar consultas lentas. Esse recurso já me ajudou várias vezes a resolver problemas de desempenho. Simples e eficiente.

Para habilitar a monitoração de consultas lentas, basta adicionar as seguintes linhas no arquivo de configuração do MySQL (/etc/my.conf ou /etc/mysql/my.cnf), na seção [mysqld]:

log-slow-queries
long_query_time = 2

A linha log-slow-queries irá habilitar o log de consultas lentas. A linha long_query_time = 2 diz ao mysql considerar consultas lentas aquelas que demorarem mais que 2 segundos para retornar, você pode especificar a quantidade de segundos que quiser.
O valor padrão para essa variável é de 10 segundos. Depedendo da quantidade de acessos ao seu site, o valor de 10 segundos pode ser alto. Em um site com bastantes acessos setei essa variável para 2 segundos.
Cabe você analisar sua situação.

Por padrão, o mysql irá criar o arquivo host_name-slow.log dentro do diretório data do mysql. Caso você queira especificar um caminho para o arquivo de log de consultas lentas que seja diferente do caminho padrão você pode especificar através da diretiva log-slow-queries:

log-slow-queries = [caminho do arquivo de log]

Para finalizar, segue abaixo exemplo de um registro de consulta lenta inserido no arquivo de log:

# Time: 110406 17:00:00
# User@Host: admin[admin] @ usuario.localnet [1.1.1.1]
# Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 5643851
select count(*) from backuplog;

O interessante observar nesse log, é o campo Query_time que indica o tempo gasto na consulta (9s) e o campo Time que indica a data/hora da ocorrencia. Logo abaixo segue a consulta que gerou o log — select count(*) from backuplog;

Ler Post Completo | Make a Comment ( 3 so far )

Liked it here?
Why not try sites on the blogroll...

%d blogueiros gostam disto: