Como usar a maior parte da memória disponível no MySQL

Question

Eu tenho um servidor MySQL que possui tabelas InnoDB e MyISAM. O tablespace InnoDB é bem pequeno em 4 GB. O MyISAM é grande ~ 250 GB no total, dos quais 50 GB são para índices.

Nosso servidor tem 32 GB de RAM, mas geralmente usa apenas ~ 8 GB. Nosso key_buffer_size é de apenas 2 GB. Mas nossa taxa de acertos de cache é de aproximadamente 95%. Eu acho difícil acreditar ..

Veja nossas principais estatísticas:

| Key_blocks_not_flushed | 1868 | | Key_blocks_unused | 109806 | | Key_blocks_used | 1714736 | | Key_read_requests | 19224818713 | | Key_reads | 60742294 | | Key_write_requests | 1607946768 | | Key_writes | 64788819 |

key_cache_block_size é o padrão em 1024.

Temos 52 GB de dados de índice e o cache de chaves de 2 GB é suficiente para obter uma taxa de acerto de 95%. Isso é possível? No outro lado, o conjunto de dados é de 200 GB e, como o MyISAM usa o cache do sistema operacional (Centos), eu esperaria que ele usasse muito mais memória para armazenar em cache os dados do myisam acessados. Mas neste estágio eu vejo que key_buffer é completamente usado, o tamanho do nosso buffer pool para innodb é de 4GB e também é completamente usado para adicionar até 6GB. O que significa que os dados são armazenados em cache usando apenas 1 GB?

Minha pergunta é como eu poderia verificar onde toda a memória livre poderia ser usada? Como eu poderia verificar se o MyISAM atinge o cache do SO para leituras de dados em vez de disco?

    

Answer 1

Eu posso acreditar facilmente em uma taxa de acertos de cache de 95%. Ouça suas estatísticas:

Você afirmou que tem 50 GB de índices para tabelas MyISAM

O cache de chave é projetado para cachear páginas de índice para tabelas MyISAM. Esta opção é definida pela opção key_buffer_size

Você disse que estava em 2G. Não é de admirar que a taxa de acerto seja de 95%. Blocos de índice estão sendo paginados para dentro e para fora do cache de chaves constantemente. Dezenas de consultas que precisam de uma linha específica são carregadas no keycache depois que o MySQL descobre que as informações do índice (.MYI) pela primeira consulta necessária não estavam lá inicialmente. Todas as consultas subsequentes que exigem a mesma informação .MYI para pesquisar dados (.MYD) já serão armazenadas em cache após a primeira consulta ser carregada. O MySQL NÃO CACHE MyISAM Data em seus próprios caches.

Você deve conseguir definir key_buffer_size para 8G.

Veja um trecho do link :

Você pode aumentar o valor para obter melhor manipulação de índice para todas as leituras e gravações múltiplas; Em um sistema cuja função principal é executar o MySQL usando o mecanismo de armazenamento MyISAM, 25% da memória total da máquina é um valor aceitável para essa variável. No entanto, você deve estar ciente de que, se você tornar o valor muito grande (por exemplo, mais de 50% da memória total da máquina), seu sistema poderá começar a paginar e ficará extremamente lento. Isso ocorre porque o MySQL depende do sistema operacional para executar o armazenamento em cache do sistema de arquivos para leituras de dados, portanto você deve deixar algum espaço para o cache do sistema de arquivos. Você também deve considerar os requisitos de memória de quaisquer outros mecanismos de armazenamento que você possa estar usando além do MyISAM.

Este mesmo URL explica que o tamanho máximo de key_buffer_size para o SO de 32 bits é 4G. Você não deve passar de 8G para key_buffer_size com sua configuração de servidor.

Quanto às leituras de dados, a única variável de status de que você precisa é Key_reads. Isso dá uma indicação de quantas vezes uma página de índice deve ser buscada em um arquivo .MYI.

O que você precisa monitorar para sua taxa de acertos de cache é o seguinte

KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC

KeyCache Hit Ratio (KHR) é, portanto, esta fórmula

KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA

Você deseja uma taxa de acertos do KeyCache 99 +%

Agora, no flipsize das coisas, vamos discutir o InnoDB.

Seu innodb_buffer_pool_size deve ser definido como 4G. Isso acontece porque o buffer pool do InnoDB armazena os dados e as páginas de índice. Você pode dar ao innodb_buffer_pool uma figura mais específica arredondada usando o seguinte:

SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;