servidor mysql principalmente pára de funcionar quando o log binário desativado

Question

O desempenho foi um pouco mais lento do que eu gostaria em um servidor mysql, e nós não usamos replicação e não precisamos de restaurações pontuais. Portanto, uma maneira recomendada de melhorar o desempenho parece estar desativando o registro de bin. Eu tenho o seguinte no meu my.cnf:

    ## Replication / Transaction Logging #
    binlog_format           = row
    log-bin                 = /var/lib/mysql/mysql-bin.log
    expire_logs_days        = 3
    sync-binlog             = 1

Eu comentei todas as quatro linhas e reiniciei o mysql. O serviço começou bem, não há nada para indicar problemas em qualquer log de erro ou logs de consulta lentos. Tudo parece bem, exceto que o desempenho de gravação diminui para níveis inutilizáveis. Ele continua a funcionar embora. Descomentar as quatro linhas acima e reiniciar o serviço imediatamente restaurou os níveis de desempenho anteriores.

O nível de desempenho anterior não é o que eu quero, porém, eu quero a melhora de desempenho que deve vir com a desativação do logging.

Por que isso aconteceu? Como posso desativar com sucesso o registro em bin para ganhar desempenho?

Especificações:

• todas as tabelas são innodb (algumas bem grandes)
• mysql Ver 14.14 Distrib 5.6.19, para debian-linux-gnu (x86_64) usando o wrapper EditLine
• Servidor LTS do Ubuntu 12.04

Answer 1

sync-binlog

Quando você comentou sync-binlog = 1 , o que aconteceu?

Você fez com que o sync_binlog padrão fosse 0. O que acontece depois ? A documentação do MySQL diz:

The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file.

Isso significa que o mysqld está à mercê do sistema operacional para liberar as alterações do disco para os logs binários. Isso indica que deve haver muitos arquivos abertos ( run lsof ) que o sistema operacional precisa liberar para o disco. O log binário atual para o MySQL tem que estar no meio daquele atolamento de log de arquivos abertos que precisam de liberação periódica para o disco, especialmente se muitos desses arquivos abertos estão sendo gravados regularmente.

A documentação do MySQL diz ainda:

A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Isso significa simplesmente que a descarga para o disco realizada por sync-binlog=1 deu alguma estabilidade ao servidor, já que as alterações de disco estavam sendo liberadas para o disco e liberavam o sistema operacional, o desempenho era um pouco melhor. Sem isso, o mysqld tinha a mesma prioridade que qualquer outro processo (aplicativo ou sistema operacional) que tivesse arquivos abertos.

InnoDB

Aqui está uma representação pictórica do InnoDB (do CTO Vadim Tkachenko de Percona)

VocêpodequererforçaroInnoDBaliberarodiscodeformamaisagressivaeaindaassimterumbomdesempenho.

• Defina innodb_flush_method para que O_DIRECT faça o InnoDB gerenciar seu próprio liberação de alterações no disco. Dada a imagem, o InnoDB explicitamente liberará para o Buffer de Gravação Dupla no Espaço de Tabela do Sistema (ibdata1), bem como os arquivos .ibd das tabelas do Buffer Pool. Em um sistema com um controlador RAID de hardware e um cache de gravação com suporte de bateria, o O_DIRECT pode ajudar a evitar o buffer duplo entre o buffer pool do InnoDB e o cache do sistema de arquivos do sistema operacional.
• Defina innodb_write_io_threads a 8 (ou 16)
• Defina innodb_log_buffer_size como 128M para reduzir a E / S de disco
• Defina innodb_log_file_size como 1G

Portanto, adicione essas configurações ao my.cnf (é necessário reiniciar)

[mysqld]
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 8
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G

Epílogo

Se você quiser desabilitar o log binário, ajuste o InnoDB para liberar melhor com compensar