Considerações sobre a importação de dados para o MariaDB

O conteúdo a seguir apresenta informações técnicas relacionadas ao carregamento de dados no MariaDB. Este conteúdo é destinado a usuários que estão familiarizados com a arquitetura do servidor MariaDB.

registro em log binário

A habilitação do registro em log binário reduz a performance do carregamento de dados e requer até quatro vezes mais espaço em disco em comparação com o registro em log desabilitado. O tamanho da transação usada para carregar os dados afeta diretamente a performance do sistema e as necessidades de espaço em disco. As transações maiores exigem mais recursos.

Tamanho da transação

O tamanho da transação influencia os seguintes aspectos dos carregamentos de dados do MariaDB:

• Consumo de recursos

• Utilização de espaço em disco

• Processo de retomada

• Tempo de recuperação

• Formato de entrada (arquivos simples ou SQL)

Esta seção descreve como o tamanho da transação afeta o registro em log binário e justifica a desabilitação do registro em log binário durante grandes cargas de dados. Você pode habilitar e desabilitar o registro em log binário configurando o período de retenção de backup automatizado do Amazon RDS. Valores diferentes de zero habilitam o registro em log binário, enquanto um valor de zero o desabilita. Para ter mais informações, consulte Backup retention period (Período de retenção de backup).

Esta seção descreve também o impacto de grandes transações sobre o InnoDB e por que é importante que o tamanho das transações seja pequeno.

Transações pequenas

Para pequenas transações, o registro em log binário duplica o número de gravações em disco necessárias para carregar os dados. Esse efeito pode degradar severamente a performance de outras sessões de banco de dados e aumentar o tempo necessário para carregar os dados. A degradação experimentada depende em parte dos seguintes fatores:

• Taxa de upload

• Outra atividade do banco de dados que ocorre durante o carregamento

• Capacidade da instância de banco de dados do Amazon RDS

Os logs binários também consomem um espaço em disco aproximadamente igual à quantidade de dados carregados enquanto os logs são copiados e removidos. O Amazon RDS minimiza isso fazendo backup e removendo os logs binários com frequência.

Transações grandes

Quanto a transações grandes, o registro em log binário triplica as IOPS e o uso do disco pelos seguintes motivos:

• O cache de log binário armazena os dados da transação temporariamente no disco.

• Esse cache se amplia de acordo com o tamanho da transação, o que consome espaço em disco.

• Quando a transação (de confirmação ou reversão) é concluída, o sistema copia o cache para o log binário.

Esse processo cria três cópias dos dados:

• os dados originais;

• o cache no disco;

• a entrada final do log binário.

Cada operação de gravação incorre em E/S adicional, afetando ainda mais a performance.

Por isso, o registro em log binário requer o triplo de espaço em disco em comparação com o registro desabilitado. Por exemplo, carregar 10 GiB de dados como uma única transação cria três cópias:

• 10 GiB para os dados da tabela;

• 10 GiB para o cache de log binário;

• 10 GiB para o arquivo de log binário.

O total de espaço em disco temporário necessário é 30 GiB.

Considerações importantes sobre o espaço em disco:

• O arquivo de cache persiste até que a sessão termine ou uma nova transação crie outro cache.

• O log binário permanece até o momento em que ele é copiado, possivelmente mantendo 20 GiB (cache e log) por um período prolongado.

Se você usar LOAD DATA LOCAL INFILE para carregar os dados, a recuperação de dados criará uma quarta cópia caso o banco de dados tenha que ser recuperado de um backup feito antes do carregamento. Durante a recuperação, o MariaDB extrai dados de log binário em um arquivo simples. Em seguida, o MariaDB executa LOAD DATA LOCAL INFILE. Com base no exemplo anterior, essa recuperação requer um total de espaço em disco temporário de 40 GiB ou 10 GiB cada para tabela, cache, log e arquivo local. Sem pelo menos 40 GiB de espaço livre em disco, a recuperação falhará.

Otimizar grandes carregamentos de dados

No caso de grandes carregamentos de dados, desabilite o registro em log binário para reduzir os custos indiretos e os requisitos de espaço em disco. Você pode desabilitar o registro em log binário definindo o período de retenção de backup como 0. Após o término do carregamento, substitua o período de retenção de backup por um valor apropriado diferente de zero. Para ter mais informações, consulte Modificar uma instância de banco de dados do Amazon RDS e Período de retenção de backup na tabela de configurações.

nota

Se a instância de banco de dados for uma instância de banco de dados de origem para réplicas de leitura, não será possível definir o período de retenção de backup como 0.

Antes de carregar os dados, recomendamos criar um snapshot do banco de dados. Para ter mais informações, consulte Gerenciar backups manuais.

InnoDB

As informações a seguir sobre o registro em log de desfazer e as opções de recuperação ajudam a manter as transações do InnoDB pequenas para otimizar a performance do banco de dados.

Conceitos básicos sobre o registro em log de desfazer no InnoDB

Desfazer é um mecanismo de registro em log que permite a reversão de transações e comporta o controle de simultaneidade de várias versões (MVCC).

Para o MariaDB 10.11 e versões anteriores, os logs de desfazer são armazenados no espaço de tabela do sistema InnoDB (geralmente ibdata1) e são retidos até que o thread de limpeza os remova. Por esse motivo, grandes transações de carregamento de dados podem fazer com que o espaço de tabela do sistema se torne muito grande e consuma espaço em disco que não pode ser recuperado sem recriar o banco de dados.

Para todas as versões do MariaDB, o thread de limpeza só deve remover quaisquer logs de desfazer depois que a transação ativa mais antiga for confirmada ou revertida. Se o banco de dados estiver processando outras transações durante o carregamento, os logs de desfazer também se acumularão e não poderão ser removidos, mesmo que as transações sejam confirmadas e nenhuma outra precise dos logs de desfazer para o MVCC. Nessa situação, todas as transações, incluindo as transações somente leitura, ficam mais lentas. Essa desaceleração ocorre porque todas as transações acessam todas as linhas que qualquer transação, não apenas a transação de carregamento, altera. Na prática, as transações devem examinar os logs de desfazer cuja eliminação foi impedida pelas transações de carregamento de longa duração durante uma limpeza de logs de desfazer. Isso afeta a performance de qualquer operação que acesse linhas modificadas.

Opções de recuperação de transações do InnoDB

Embora o InnoDB otimize as operações de confirmação, a reversão de grandes transações é lenta. Para uma recuperação mais rápida, execute uma recuperação para um ponto no tempo ou restaure um snapshot do banco de dados. Para obter mais informações, consulte Recuperação para um ponto no tempo e Restaurar uma instância de banco de dados.

Formatos de importação de dados

O MariaDB aceita dois formatos de importação de dados: arquivos simples e SQL. Analise as informações sobre cada formato para determinar a melhor opção para suas necessidades.

Arquivos simples

Para transações pequenas, carregue arquivos simples com LOAD DATA LOCAL INFILE. Esse formato de importação de dados pode oferecer os seguintes benefícios em relação ao uso do SQL:

• regras de tráfego de rede;

• custos de transmissão de dados mais baixos;

• redução dos custos indireto de processamento do banco de dados;

• processamento de fluxos.

LOAD DATA LOCAL INFILE carrega todo o arquivo simples como uma única transação. Mantenha os arquivos individuais com um tamanho pequeno para obter as seguintes vantagens:

• Capacidade de retomar: é fácil manter o controle de quais arquivos foram carregados. Se surgir um problema durante o carregamento, você poderá continuar de onde parou. Você pode precisar retransmitir alguns dados ao Amazon RDS, mas, com arquivos pequenos, a quantidade retransmitida é mínima.

• Carregamento de dados em paralelo: se você tiver IOPS e largura de banda da rede suficientes para carregamento de um único arquivo, o carregamento em paralelo pode economizar tempo.

• Controle da taxa de carregamento: se o carregamento de dados tiver um impacto negativo em outros processos, você poderá controlar a taxa de carregamento aumentando o intervalo entre os arquivos.

Grandes transações reduzem os benefícios de usar LOAD DATA LOCAL INFILE para importar dados. Quando você não conseguir dividir uma grande quantidade de dados em arquivos menores, considere a possibilidade de usar o SQL.

SQL

O SQL tem uma vantagem fundamental em relação aos arquivos simples: é fácil manter as transações com um tamanho pequeno. Contudo, o tempo de carregamento do SQL pode ser significativamente mais longo do que o dos arquivos simples. Além disso, após uma falha, pode ser difícil determinar onde retomar. Não é possível reiniciar os arquivos mariadb-dump. Se ocorrer uma falha ao carregar um arquivo mariadb-dump, você deve modificá-lo ou substituí-lo para que o carregamento possa continuar. Ou, alternativamente, depois de corrigir a causa da falha, você pode restaurar para o ponto no tempo anterior ao carregamento e reenviar o arquivo. Para ter mais informações, consulte Recuperação para um ponto no tempo.

Usar snapshots de banco de dados do Amazon RDS para pontos de verificação de banco de dados

Se você carrega dados por longos períodos, como horas ou dias, sem registro em log binário, use snapshots de banco de dados para oferecer pontos de verificação periódicos em prol da segurança dos dados. Cada snapshot de banco de dados cria uma cópia consistente da instância de banco de dados, que serve como ponto de recuperação durante falhas do sistema ou eventos de corrupção de dados. Como os snapshots de banco de dados são rápidos, os pontos de verificação frequentes têm um impacto mínimo na performance do carregamento. Você pode excluir snapshots de banco de dados anteriores sem afetar a durabilidade ou os recursos de recuperação do banco de dados. Para ter mais informações sobre snapshots de banco de dados, consulte Gerenciar backups manuais.

Reduzir os tempos de carregamento do banco de dados

Os seguintes itens são dicas adicionais para reduzir os tempos de carregamento:

• Crie todos os índices secundários antes de carregar dados em bancos de dados do MariaDB. Ao contrário de outros sistemas de banco de dados, o MariaDB reconstrói a tabela inteira ao adicionar ou modificar índices secundários. Esse processo cria outra tabela com alterações no índice, copia todos os dados e descarta a tabela original.

• Carregue os dados na ordem da chave primária. Quanto a tabelas do InnoDB, isso pode reduzir os tempos de carregamento em 75% a 80% e diminuir o tamanho do arquivo de dados em 50%.

• Desabilite as restrições de chave estrangeira definindo foreign_key_checks como 0. Isso com frequência é necessário para arquivos simples carregados com LOAD DATA LOCAL INFILE. Com relação a qualquer carregamento, desabilitar as verificações de chave estrangeira acelera o carregamento de dados. Após a conclusão do carregamento, habilite novamente as restrições configurando foreign_key_checks como 1 e verifique os dados.

• Carregue os dados em paralelo, a menos que esteja se aproximando de um limite de recursos. Para habilitar o carregamento simultâneo em vários segmentos da tabela, use tabelas particionadas quando apropriado.

• Para reduzir os custos indiretos de execução do SQL, combine várias instruções INSERT em operações INSERT únicas de vários valores. mariadb-dump implementa essa otimização automaticamente.

• Reduza as operações de E/S de log do InnoDB configurando innodb_flush_log_at_trx_commit como 0. Após a conclusão do carregamento, restaure innodb_flush_log_at_trx_commit para 1.

  Atenção

  Ao definir innodb_flush_log_at_trx_commit como 0, o InnoDB descarrega os respectivos logs a cada segundo em vez de a cada confirmação. Essa configuração aumenta a performance, mas pode gerar risco de perda de transações durante falhas no sistema.

• Se você estiver carregando dados em uma instância de banco de dados que não tem réplicas de leitura, defina sync_binlog como 0. Após a conclusão do carregamento, restaure sync_binlog parameter para 1.

• Carregue os dados antes de converter a instância de banco de dados em uma implantação multi-AZ. Se a instância de banco de dados já utiliza uma implantação multi-AZ, não recomendamos mudar para uma implantação single-AZ para carregamento de dados. Se fizer isso, obterá apenas pequenas melhorias.