MySQL Performance Tuning: Parâmetros em ambiente OLTP

Estive em um cliente implementado vários servidores em um ambiente MySQL, utilizado por um sistema crítico.
Todos os servidores tem 70GB de RAM e 16 Cores, e os bancos de dados aproximadamente 200GB.

Utilizamos a edição Enterprise do MySQL, na versão 5.1.40.

Seguem as partes mais importantes do my.cnf, com comentários, que utilizei para um servidor deste porte. É uma aplicação Web, de perfil totalmente OLTP.

# Log binário, sempre é importante em um ambiente de produção, para garantir a recuperação.
max_binlog_size                         = 200M
expire_logs_days                        = 7
log-bin                                 = /var/log/mysql/mysql-bin

# Estas opções irão logar todos os SQLs que demorarem mais que 5 segundos. Usei este tempo porque é um sistema completamente OLTP.
slow_query_log_file                     = /var/log/mysql/mysql-slow.log
long_query_time                         = 5

# Já que o cliente utiliza grandes tabelas, fica mais fácil de visualizar sendo um arquivo por tabela, ao invés de tudo nos arquivos do Innodb.
# Isto poderia trazer uma melhoria de desempenho se as tabelas fossem espalhadas para discos diferentes, mas este não é o caso.
innodb_data_file_path                   = ibdata1:512M;ibdata2:512M:autoextend
innodb_file_per_table                   = 1

# Se sua aplicação recebe muitas conexões em pouco tempo, aumente este valor para deixar a thread pré-alocada, para acelerar a conexão.
thread_cache_size                       = 16

# Deixe este valor pelo menos igual ao valor de tabelas que você tem, ou espera ter.
table_open_cache                        = 9000

# Atenção nestes dois parâmetros. Se sua aplicação não costuma desconectar automaticamente do bancos de dados após concluir seu trabalho, deve deixar o timeout em um valor aceitável para permitir espaço para novas conexões, matando as que não estão fazendo mais nada.
max_connections                         = 800
wait_timeout                            = 360

# Mantenho o key_buffer relativamente pequeno pois ele é válido apenas para tabelas MyISAM, e o cliente utiliza apenas Innodb em sua aplicação.
key_buffer                              = 128M

# Esta opção é necessária se são utilizados CLOBs ou VARCHAR muitos grandes, o que ocorrem em algumas tabelas do cliente.
max_allowed_packet                      = 32M

# Estes parâmetros estão entre os mais importantes do MySQL, e os mais perigosos. São utitilizados para acelerar leituras de tabelas e ordenações.
# Eles são alocados por conexão, então não coloque este valor muito alto se tiver muitas conexões, ou a memória de sua máquina irá acabar rapidamente.
# Como a média de conexões do banco do cliente está entre 100-300, estes valores abaixo estão seguros, e bem maiores que o valor padrão.
read_buffer_size                        = 32M
read_rnd_buffer_size                    = 32M
sort_buffer                             = 32M

# Este buffer é utilizado quando um Join é executado sem utilizar índices, para acelerar os Full Table Scans necessários do Join.
# É alocado um deste buffer para cada Join sem índice, então também tenha cuidado, não o deixe muit grande.
join_buffer_size                        = 32M

# Esta é a configuração do Query Cache, para armazenar resultados de SQLs já executados.
query_cache_limit                       = 32M
query_cache_min_res_unit                = 4096
query_cache_size                        = 1G
query_cache_type                        = 1

# O valor recomendado deste valor é 2 x o número de CPUs + número de discos.
# Resolvi deixar como 32 (tenho 16 CPUs e 10 discos), e o desempenho está ótimo para este ambiente.
innodb_thread_concurrency               = 32

# Estes parâmetros são necessários para uma excelente performance de gravação.
# Em um RAID 10, foi o melhor valor que consegui através de vários testes.
innodb_log_file_size                    = 512M
innodb_log_files_in_group               = 7
innodb_log_buffer_size                  = 256M
innodb_flush_log_at_trx_commit          = 1

# Este é o principal parâmetro de performance para tabelas em Innodb, pois é o Caché de dados.
innodb_buffer_pool_size                 = 40G

# Este parâmetro limita o número de arquivos abertos de cada vez.
# Um valor seguro é deixa-lo em 5 x o número de tabelas (cada tabela pode ter 3 arquivos, mais Logs e outros arquivos) que você tem, ou espera ter.
innodb_open_files                       = 9000

# O parâmetro abaixo quer dizer que se uma tabela temporária for menor que 512MB, ela pode ser criada e utilizada em memória.
# Se for maior que 512MB, será feita em disco.
tmp_table_size                          = 512M

# Este é o diretório de tabelas temporárias, que colocamos em um RAID 10 para uma excelente velocidade.
tmpdir                                  = /tmp/

One comment

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.