Melhores configurações de cache do MySQL para 8GB de RAM servidor MySQL dedicado usando apenas InnoDB (banco de dados de 5GB)

Question

Eu sou um grande noob quando se trata de configurar o MySQL para performance. E honestamente, não estou preocupado com o ajuste fino para extrair o máximo de desempenho do MySQL, mas sei que a coisa mais importante a ser feita é que alguns dos melhores resultados são configurar caches / buffers corretamente.

Eu tentei manter as coisas simples usando apenas o InnoDB como um mecanismo de armazenamento. E eu tenho um servidor dedicado para o MySQL. Tem 8GB de RAM, como devo alocar isso para maximizar o desempenho? Eu gostaria de poder caber todo o meu banco de dados na memória para o melhor desempenho. O banco de dados é de cerca de 5gb. Isso é possível?

Quanta memória devo alocar para o cache de consulta? Quanto ao buffer pool do InnoDB? Quanto para o resto do computador (ou seja, processos não relacionados ao MySQL)? Etc.

Como não estou usando o MyISAM, não preciso colocar muita memória no cache de chaves, correto?

    

Answer 1

Isso é difícil sem saber muito sobre o próprio banco de dados. Existem algumas ferramentas que você deve conhecer;

• link
• MySQL Tuning Primer

Sobre o armazenamento de todo o banco de dados na memória; Quaisquer consultas que estão fazendo alterações no banco de dados permanecerão abertas até que a gravação seja executada no disco. A única coisa que pode evitar que o disco seja um gargalo é um controlador de disco com um cache de gravação.

Eu começaria com as seguintes alterações dos padrões:

key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

innodb_buffer_pool_size = 4G 

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve

Depois, vejo como as coisas correm e tento diferentes coisas com base (entre outras coisas) na saída das ferramentas mencionadas acima. Também gostaria de fazer um gráfico das tendências com uma ferramenta de monitoramento, como Munin ou Cactos , para ver com que tipo de carga de trabalho estou realmente lidando. Pessoalmente, tenho uma grande experiência com os plugins do MySQL fornecidos com o Munin.

    

Answer 2

IMHO você deve ser capaz de ir com

innodb_buffer_pool_size=5G

Isso seria 62,5% da RAM com uma quantidade suficiente de RAM para o sistema operacional do servidor mais a memória para conexões de banco de dados

@kvisle recomendado usando mysqltuner.pl. Esse script é excelente para julgar a quantidade de RAM a ser dedicada a join_buffer_size, sort_buffer_size, read_buffer_size e read_rnd_buffer_size. Esses 4 buffers somados são multiplicados por max_connections. Essa resposta é adicionada aos buffers estáticos (innodb_buffer_pool_size + key_buffer_size). As somas combinadas são relatadas. Se essa soma combinada exceder 80% da RAM, é quando você deve diminuir esses tamanhos de buffer. mysqltuner.pl será muito útil a este respeito.

Como todos os seus dados são InnoDB, você pode tornar o key_buffer_size (buffer de cache de chave para índices MyISAM) muito baixo (eu recomendo 64M).

Aqui está um post que eu feita no DBA StackExchange para calcular um tamanho recomendado de innodb_buffer_pool_size .

ATUALIZAÇÃO 2011-10-15 19:55 EDT

Se você sabe que terá 5 GB de dados, minha primeira recomendação foi OK. No entanto, esqueci de adicionar uma coisa:

[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M

O tamanho do arquivo de log deve ser de 25% do InnoDB Buffer Pool

ATUALIZAÇÃO 2011-10-16 13:36 EDT

A regra de 25% baseia-se estritamente no uso de dois arquivos de log. Embora seja possível usar vários arquivos de log innodb, dois geralmente funcionam melhor.

Outros expressaram usando 25%

• link
• link
• link

No entanto, com toda a honestidade, alguém da empresa original da InnoBase Oy expressou não usar a regra de 25% porque de ter maior Buffers Pool InnoDB .

Naturalmente, a regra de 25% não funciona quando se tem grandes quantidades de RAM. Na verdade, o maior innodb_log_file_size permitido usando apenas 2 arquivos de log é 2047M, já que o tamanho combinado do arquivo de log deve ser menor que 4G (4096M)

CASE IN POINT: Um dos clientes do meu empregador tem um servidor de banco de dados com 192 GB de RAM. Não há como ter arquivos de log 48G. Eu simlpy usar o tamanho máximo do arquivo para um arquivo de log innodb, 2047M. O comentário do @Kvisle sobre a minha resposta simplesmente dá um link dizendo que você não precisa se limitar a dois arquivos de log. Se você tiver N arquivos de log, eles não poderão totalizar 4G. Minha regra de 25% é apenas em um mundo perfeito (DB Server com 8GB ou menos).