Início / dba / Perguntas / 4303
Accepted
RPK
Asked: 2011-08-05 04:15:17 +0800 CST

Otimizando as configurações padrão do InnoDB

  • 772

Olhei para my.ini e vi várias configurações padrão. Meu banco de dados está sendo executado em um único PC autônomo. Eu quero otimizar o desempenho do InnoDB e do MySQL em geral para desempenho. Não há restrição de espaço em disco. Quais configurações padrão devo alterar para otimizar para melhor desempenho, confiabilidade e possíveis backups pontuais [alta disponibilidade].

Editado

No momento, sempre que executo "Otimizar tabelas" via Manutenção no MySQL Administrator, ele mostra:

A tabela não oferece suporte para otimizar, fazendo recriar + analisar em vez disso

em todas as mesas. Todas as minhas tabelas são InnoDB, mas por que elas não suportam o Optimize?

mysql performance

3 respostas

  1. Best Answer

    A maneira de ajustar o InnoDB é centrada em

    • InnoDB Buffer Pool: Ele armazena páginas de dados e páginas de índice. A quantidade de dados e índice que você pode armazenar em cache não é uma função das restrições de espaço em disco, mas uma função da memória disponível e do espaço em disco atualmente usado pelo InnoDB.
    • InnoDB MetaData : Por padrão, o arquivo ibdata1 normalmente abriga tudo e qualquer coisa do InnoDB. Isso incluiria páginas de dados, páginas de índice, metadados de tabela, dados MVCC .

    Aqui está uma fórmula que usei nos últimos 5 anos para calcular o InnoDB Buffer Pool com base no espaço em disco usado pelos dados e páginas de índice do InnoDB :

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;
    • Use (SELECT 0 Power1024) para Bytes
    • Use (SELECIONE 1 Power1024) para KB
    • Use (SELECIONE 2 Power1024) para MB
    • Use (SELECIONE 3 Power1024) para GB
    • Use (SELECT 4 Power1024) para TB (me envie um e-mail se você tiver TerraBytes de RAM)

    Claro, eu disse uma função de memória disponível e espaço em disco usado atualmente pelo InnoDB. A partir daqui, basta usar o bom senso. O número recomendado da consulta acima NÃO DEVE EXCEDER 75% DA RAM INSTALADA !!! Essa é a regra prática mais simples para dimensionar o InnoDB Buffer Pool.

    Você também deve definir innodb_flush_method como O_DIRECT , pois fornecerá gravações síncronas estáveis ​​do InnoDB. Também escrevi um post sobre como otimizar o armazenamento em disco para InnoDB .

    Com relação à mensagem Tabela não suporta otimizar, fazendo recriar + analisar em vez disso , a razão pela qual você recebe essa mensagem de erro é o fato de que o mecanismo de armazenamento é InnoDB. Mecanicamente, OPTIMIZE TABLE apenas copia a tabela para uma tabela temporária e executa ANALYZE TABLE .

    Na realidade, ANALYZE TABLE contra InnoDB é completamente inútil. Mesmo se você executar ANALYZE TABLE em uma tabela InnoDB, o mecanismo de armazenamento InnoDB realiza mergulhos no índice para aproximações de cardinalidade repetidas vezes, destruindo assim as estatísticas que você acabou de compilar. Na verdade, a Percona realizou alguns testes no ANALYZE TABLE e também chegou à mesma conclusão .

    Aqui estão outras postagens que fiz ao longo do ano sobre InnoDB Tuning

    • 13

  2. Aqui está um artigo mais antigo sobre as variáveis ​​importantes para 'sintonizar grosseiramente'. O mais importante provavelmente sendoinnodb_buffer_pool_size

    Eu recomendo atualizar para o MySQL 5.5 (se você ainda não o estiver executando). Eles fizeram algumas mudanças no desempenho do InnoDB. Eles até fornecem uma seção principal sobre como otimizar o InnoDB agora que é o mecanismo padrão: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html

    Quanto aos backups pontuais, você deve examinar o log binário . O básico para habilitar isso é definir a log-binvariável em seu my.cnf

    • 3

  3. Optimize vem em muitas cores.

    Acho que seu primeiro passo deve ser decidir o que "otimizar" significa para você. Para algumas pessoas, significa "consultas SELECT mais rápidas". Para outros, significa "melhor equilíbrio entre desempenho de SELECT e desempenho de INSERT". Para outros ainda, "desempenho de INSERT mais rápido".

    Você precisa decidir quais são seus critérios e como saberá se suas alterações ajudam antes de iniciar o ajuste.

    Em seguida, coloque seus arquivos de configuração e opções de inicialização sob controle de versão e comece a experimentar. Documente os conselhos que você segue e onde os encontrou. (O conselho muda com o tempo, conforme a base de código e o hardware mudam.) Coloque esses documentos sob controle de versão também.

    • 2

relate perguntas