IO Espera causando muito lentidão (EXT4 JDB2 em 99% IO) durante o Mysql Commit

Question

Estou escrevendo um indexador, usando python, que indexa documentos e inseri-los em banco de dados, antes era único processo, mas agora eu fiz para multiprocessamento com 4 processos paralelos em execução.Depois de cada extração de texto, ele insere no banco de dados e faz um commit.

Agora, atingindo o problema IO, o principal problema de IO não é o meu processo, mas o sistema de jornada jdb2 da EXT4. Ele está em 99,99% e a CPU da carcaça aguarda o IO a cada commit do MySQL.

Eu vi muitos tendo esse problema na internet e sua solução é montar usando barreira = 0. Isso desativaria o Journaling totalmente? Meus servidores têm UPS e tentador fazer isso, devo?

    

Answer 1

Sempre haverá uma troca entre resiliência e desempenho.

Com o MySQL no ext4, o padrão barrier = 1, na verdade, causa uma lentidão, no entanto, a primeira ação não deve ser desabilitar o registro em diário ou ativar dados = writeback.

Primeiro, se a resiliência é de grande importância, um RAID apoiado por bateria certamente vale a pena.

As opções de montagem que eu escolhi, especialmente em RAID não suportado por bateria, são:

/dev/mapper/vg-mysql--data  /var/lib/mysql/data ext4  defaults,noatime,nodiratime,barrier=1,data=ordered  0 0

Isso intencionalmente não está usando dados = writeback porque eu não quero arriscar a corrupção do sistema de arquivos, resultando em "dados antigos que aparecem em arquivos após uma falha e recuperação de diário" (a citação é de man mount ).

A configuração ideal em my.cnf para resiliência total em torno de configurações relacionadas a E / S é:

[mysqld]
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

Eu optei pela seguinte sequência de trade-offs para aumentar o desempenho:

1. sync_binlog = 0 : esta é a primeira configuração do MySQL que eu mudo para longe da resiliência total. A razão para isso é que ele proporciona uma melhoria significativa no desempenho, especialmente quando binlog_format=row (infelizmente necessário para Jira). Estou usando bastante réplicas do MySQL no cluster que, se o log binário fosse corrompido por um cenário de perda de energia, eu faria uma cópia binária de outra réplica.
2. innodb_flush_log_at_trx_commit = 2 : Enquanto um valor de 1 é necessário para total conformidade com o ACID, com um valor de 2 "o buffer de log é gravado no arquivo em cada confirmação, mas a operação de flush para disco não é executada nele. No entanto, o fluxo no arquivo de log ocorre uma vez por segundo também quando o valor é 2. Observe que a liberação de uma vez por segundo não é 100% garantida para acontecer a cada segundo, devido a problemas de agendamento do processo. " (citação da documentação do MySQL)
3. Atualize as opções de montagem para usar data=writeback . Note que se este for o seu sistema de arquivos raiz, você também precisará passar uma opção de linha de comando do kernel. Eu juntei alguns passos sobre isso em coderwall .
4. Teste vários valores de innodb_flush_method . O_DIRECT é mostrado para melhorar o desempenho em algumas cargas de trabalho, mas não é certo que isso funcionará em seu ambiente.
5. Faça o upgrade para SSDs. Nesse caso, você também deseja aumentar innodb_io_capacity e ajustar configurações como innodb_adaptive_flushing , innodb_read_io_threads , innodb_write_io_threads , innodb_purge_threads e outras configurações possíveis.

Answer 2

Coloca o banco de dados em um sistema de arquivos sem journaling. Pelo menos servidores maiores (oracle, sql server) têm sua própria função de diário (log de transações) e otimizam sua IO de acordo. Você tem registro e banco de dados em sistemas de arquivos e discos separados e conta com a funcionalidade interna do banco de dados para lidar com o IO incorreto. Normalmente não há alterações no sistema de arquivos (de instalação maior), exceto a data de gravação, porque os arquivos não se expandem - eles seriam gerados com o tamanho "final" (ok, os administradores podem alterar isso) e as alterações são como eu disse controladas pelo banco de dados log de transação de nível.

Você também pode nos informar qual é a sua camada de hardware. A maioria das pessoas subestima que IOPS é o fator limitante para um banco de dados e acha que um pequeno conjunto de discos é um ambiente adequado para um grande banco de dados . Enquanto alguns de nós trabalhamos em bancos de dados usando um número maior de discos, potencialmente suportando um número maior de IOPS.

    

Answer 3

É bem provável que o seu back-end de E / S não esteja lidando bem com a carga. Você deve certificar-se de que seu sistema de arquivos não é um dado de registro no diário. Sugiro usar os parâmetros data=writeback,relatime,nobarrier para montar a partição de dados do seu banco de dados como a primeira otimização rápida e suja.

Além disso, deduzindo de seus sintomas, você aparentemente não está usando o cache de gravação com seu controlador. Você deve verificar se está usando um cache de gravação apoiado por bateria ou flash em seu controlador e ativá-lo - isso deve proporcionar um aumento significativo de desempenho sem aumentar muito o risco de perda ou corrupção de dados. Observe que usar o cache de gravação sem uma bateria ou backup em flash aumenta significativamente o risco de perda ou corrupção de dados - portanto, faça isso apenas para fins de teste e / ou a perda.

    

Answer 4

Essa é uma pergunta antiga, mas enfrentamos os mesmos problemas (altas esperas de I / O e péssimas velocidades de inserção / atualização) na semana passada em um novo servidor dedicado e essa solução resolve esse problema diretamente.

Desabilitando o registro no diário com tune2fs -O "^has_journal" /dev/<drive> foi a solução mais rápida, pois elimina a espera por IO devido ao processo JDB2. Mas isso não é recomendado, a menos que você tenha uma unidade com bateria, porque perderá dados no caso de uma falha. Tabelas InnoDB são seguras se você tiver doublewrite ativado no MySQL. Mas arquivos como .frm, logs, etc não são seguros. Nós tentamos mover esses arquivos para outra unidade (especialmente os logs bin), mas o jdb2 IO wait ainda persistia. Então não nos deixou muito confortáveis.

data=writeback,relatime,nobarrier não ajudou a acelerar as gravações / leituras, tanto quanto desabilitou o registro em diário na partição inteira. Mais opções para o ext4 estão no doc EXT4 .

O verdadeiro culpado no nosso caso foi sync_binlog . Nós tínhamos definido como 1 em /etc/mysql/my.cnf e estava matando o desempenho.

Percona valida isso aqui . Nós definimos como padrão de 0 e o desempenho aumentou mais de 500%.

    

Answer 5

Qual mecanismo de banco de dados você está usando para inserir esses dados?

Se for MyISAM: deve bloquear a tabela inteira durante uma gravação, então a execução de threads de inserção concorrentes matará QUALQUER sistema, não importa o quão poderoso seja.

Certifique-se de usar o InnoDB para essas tabelas.

    

Answer 6

Além disso, não está diretamente relacionado ao mysql, mas alguns HDs têm problemas com o ext4 devido ao gerenciamento de energia agressivo ... quando isso acontece, a carga da máquina aumenta sem nenhuma atividade aparente.

Tente desativá-lo. primeiro verifique qualquer valor que você tenha (se precisar colocá-lo de volta sem reinicializar) e depois desative-o.

Verifique o valor atual:

    hdparm -B /dev/sda

Desativar

   hdparm -B 255 /dev/sda

(ou qualquer que seja o seu HD) e teste. Provavelmente não ajudará na maioria dos problemas, mas pode ajudar alguns usuários. A reinicialização irá redefinir o valor ou substituir manualmente o 255 pelo valor anterior.

Se isso ajudar, verifique a /etc/default/hdparm ou /etc/hdparm.conf para uma configuração mais permanente, definindo-a na inicialização.