Tuning no MySQL

Referência: JOSUÉ, Marcelo. Tuning no MySQL. SQL Magazine, ed. 94, ano 12, p. 26 - 32.


Atualmente as instituições, empresas e organização estão cada vez mais dependentes dos sistemas de informação para gerenciar e manter os seus negócios em operação e atingir as metas estabelecidas. Neste contexto, devido à alta dependência em relação a todos os componentes de softwares que sustentam o sistema como um todo, qualquer falha ou indisponibilidade, ou até mesmo problemas de desempenho em qualquer destes elementos pode culminar em grandes prejuízos para estas corporações.

Procedimentos Gerais
Para implementação de tunning no MySQL, o primeiro passo é verificar os parâmetros disponíveis para ajuste bem como o valor de cada um. A Listagem 1 apresenta o comando que, digitado no console do sistema operacional, exibe tais informações.
Liatagem 1: Comando para exibição dos valores dos parâmetros do MySQL.
“shell > mysql –verbose –help”
Listagem 2: Comando para exibição dos valores dos parâmetros do MySQL utilizando o pronpt do MySQL.
“mysql> SHOW VARIABLES”;;
 A maioria dos parâmetros podem ser modificados por linha de comando, outros devem ser configurados no arquivo de configuração que geralmente se encontra em /etc/mysql/my.cnf ou c://mysql/my.ini em sistemas Windows. Neste arquivo é possível definir os valores para cada parâmetro, sendo que alteração de alguns (os que não são dinâmicos) exigem que o serviço do MySQL seja reiniciado. Em sistemas Linux, o serviço é reiniciado geralmente, com o comando /etc/ini.d/mysql restart, em sistemas Windows com set STOP mysql e em seguida net START mysql.

Parâmetros Importantes

Cada parâmetro aceita valores numéricos, basta colocar ao lado da variável um sinal de igualdade um número (parâmetro), por exemplo: key_bufer_size = 1024, neste caso 1024 bytes. Valores com multiplicadores também podem ser utilizados, tais como 128k, 64M, 1G etc, perceba que a letra B não aparece ao lado, é apenas a letra do multiplicador que deve ser utilizada.

Key_bufer_size
Este parâmetro indica o tamanho do bufer utilizado para indexar blocos de dados contidos nas tabelas. Este valor pdoe ser de 20 a 40% da memoria principal (RAM) disponível. O tamanho correto depende da quantidade de indicies e do tamanho das tabelas.
O valor máximo para este parâmetro é 4GB em plataformas de 32 bits. A partir do MySQL 5.0.52, valores maiores que 4GB são permitidos para plataformas de 64GB (exceto Windows 64 bits, para quais grandes valores são limitados em 4GB, com um aviso para o usuário).
Você pode aumentar o valor para obter um melhor tratamento de índicies para todas as as consultas e inserções múltiplas. Em um sistema cuja principal função é a de executar o MySQL usando o mecanismo de armazenamento MyISAM, 25% do total da memoria principal da máquina é um valor aceitável para estes parâmetros. No entanto, você deve estar ciente do que se você definir o valor muito grande (por exemplo, mais de 50% do total de memória da máquina), seu sistema pode começar a paginar (utilizar o HD para gravar dados em que precisam ser processados e se tornar extremamente lento. Lembre-se, MyISAM usa cache do sistema operacional para armazenar os dados.O valor máximo deste parâmetro não deve ser maior do que o tamanho dos arquivos com extensão MYI.

myisam_sort_buffer-size
Este parâmetro define o tamanho de memoria que é alocado ao ordenar índices MyISAM durante uma reparação de tabela (REPAIR) ou estiver criando índicies (CREATE INDEX) ou alterar estruturas de tabela (ALTER TABLE).

join_buffer_size
Este parâmetro define o valor para memória utilizada nas operações que exigem a união de tabelas. O valor máximo para este parâmetro é 4GB. Não é necessário especificar um valor alto neste parâmetro, poiso sistema perde performance caso a maioria das operações não utilize um espaço grande da memória.

red_buffer_size
Cada thread que faz a consulta sequencial aloca um buffer deste tamanho para cada tabela lida. Se você fizer várias consultas sequenciais, você pode aumentar esse valor, cujo padrão é 131072. O valor desta variável deve ser um múltiplo de 4KB. Se ele estiver definindo para um valor que não é um múltiplo de 4KB, o seu valor será arredondado par ao múltiplo mais próximo de 4KB. O valor máximo é 2GB.

sort_bufer_size
Para cada sessão que precisa fazer uma ordenação (ORDER BY ou GROUP BY) o servidor aloca uma área de memória do tamanho definido nesta variável. É importante tomar cuidado para não defini-lo maior do que o necessário para a maioria das consultas que envolvam ordenação. É melhor aumenta-la como uma configuração de sessão apenas para as sessões que necessitam de um tamanho maior. No Linux, existem limiares de 256KB e 2MB, sendo que valores maiores podem desacelerar significativamente alocação de memoria, assim você deve considerar ficar nestes valores ou abaixo.

thread-chache
Criação de threads em excesso pode ocasionarmuito processamento. Se seu banco de dados atende muitos usuários simultaneamente este valor deve ser elevado, caso contrário o valor 16 é suficiente.

thread-cache-size
Este valor e importante para manter em cache informações usuários que se desconectaram do servidor. Este parâmetro pode ser aumentado para melhorar o desempenho se você tem um muitas conexões a todo momento. Normalmente, isso não fornece uma melhor de performance notável.

table_cache
Este parâmetro define o tamanho de memoria para as tabelas abertas em todas as threads (em uso). Aumentar este valor, aumenta o numero de descritores de arquivos que o serviço do MySQL necessita. Se seu sistema trabalha com muitas tabelas e não utiliza o comando FLUSH TABLS muitas vezes, então você deve aumentar o valor da variável table_cache. Para encontrar o valor ideal para este parâmetro, deve ser analista a quantidade de tabelas e o tamanho de cada um.

query_cache_type
Este parâmetro permite definir o cache de consultas padrão para cada usuário do banco de dados. Cada usuário pode definir mais cache utilizando sessão. Por padrão o valor e 1M.

query_cache_limit
Este parâmetro server para limitar o tamanho de armazenamento dos resultados das consultas, seu valor padrão e 1M.

query_cache_size
Se seu banco recebe muitas solicitações de consultas, este parâmetro deve ter um valor alto, o valor padrão e zero. Valores possíveis são múltiplos de 1024, outros valores serão arredondados para o múltiplo mais próximo. Valores tais como 128M, 256M, 312M também são aceitos.

innod_additional_mem_pool_size
O tamanho em bytes de memorias que as tabelas do tipo InnoDB utiliza para armazenamento informações de dicionário e outras estruturas de dados internas. Quanto mais tabelas você tiver, em sua aplicação, mais memoria você precisara alocar neste parâmetro. Se o InnoDB ficar sem memoria neste parâmetro ele começa a alocar memoria do sistema operacional. O valor padrão e de 1MB.

Innodb_flush_log_at_trx_commit
Se o valor desta variável e zero, o buffer de log e gravado no arquivo de log uma vez por segundo. Quando o valor e 1 (o padrão, para cumprir as características ACID), o buffer de log é gravado no arquivo de log a cada commit de transação. Quando o valor for 2, o buffer de log e gravado para o arquivo a cada commit, no entanto, o rubor no arquivo de log ocorre uma vez por segundo também quando o valor 2.
Você pode obter um melhor desempenho definindo o valor diferente de 1, mas então você pode perder dados em uma transação. Como zero qualquer problema no processo mysql pode apagar o ultimo segundo de transações. Com um valor de 2, então, apenas uma falha do sistema operacional ou uma queda de energia pode apagar o ultimo segundo de transação.

Innodb_log_buffer_size
Este parâmetro define o tamanho em bytes do buffer que o InnoDB usa para gravar os arquivos de log no disco. O valor padrão de 1MB. Os valores variam de 1MB de 8MB. Um buffer de log grande permite grandes operações para executar sem a necessidade de escrever no log em disco antes de finalizar as transações. Assim, se você tiver grandes transações, configure o buffer de log maior.

Innodb_buffer_pool_size
Este e o tamanho do buffer de memoria para armazenar os dados e indicies de suas tabelas. O valor padrão de 8MB. Quanto maior for esse valor, menos o disco sera acessado para acesso a dados em tabelas. Em um servidor de banco de dados dedicado, você pode definir isso para ate 80% do tamanho da memoria principal. No entanto, não atribua um valor muito grande porque a concorrência para a memoria principal pode causar paginação no sistema operacional. Além disso, o tempo necessário para inicializar o buffer e aproximadamente proporcional ao seu tamanho. Em grandes instalações, este tempo de inicialização pode ser significativo. Por exemplo, em um servidor Linux x86_64, a inicialização de um buffer 10 GB demora cerca de 6 segundos.

Innodb_log_file-size
Este parâmetro define o tamanho em bytes de cada arquivo de log. O tamanho combinado dos arquivos de log deve ser menor que 4 GB. O valor padrão é 5MB. Quanto maior o valor, menos atividades é necessário na área de buffer, economizando trabalho em disco (memoria secundaria). Mas arquivos de log maiores também significam que a recuperação é mais lenta em caso de falhas e recuperação em tabelas.

Innodb_thread_currency
InnoDB tenta manter o numero de threads do sitema operacional menor ou igual ao limite dados por esta variavel. Uma vez que o numero de threads atinja esse limite, as próximas tarefas serão colocadas em uma fila do tipo FIFO (First In First Out, que significa o primeiro a entrar será o primeiro a sair). Threads que esperam por bloqueios não são contabilizados no numero de threads em execução simultaneamente.
O valor correto para esta variavel depende do ambiente e carga de trabalho. Você deverá tentar uma serie de valores diferentes para determinar o valor adequado. Um valor recomendado é de 2 vezes o numero de CPUs mais o numero de discos.
O alcance desta variavel é 0 a 100. Um valor de 20 ou superior a interpretado como ocorrência infinita nas versões antes do MySQL 5.0.19. A partir da versão 5.0.19, você pode desabilitar a verificação de simultaneidade, definindo o valor para 0.