Importar dados para uma instância de banco de dados MySQL

Você pode usar várias técnicas diferentes para importar os dados para uma instância de banco de dados do RDS para MySQL. A melhor abordagem depende da origem dos dados, da quantidade de dados e se a importação é feita uma vez ou se é contínua. Se você estiver migrando um aplicativo junto com os dados, também considere a quantidade de tempo de inatividade que está disposto a esperar.

Visão geral

Localize técnicas para importar dados para a instância de banco de dados do RDS para MySQL na tabela a seguir.

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

A seguir, você pode encontrar informações técnicas adicionais relacionadas ao carregamento de dados no MySQL. Essas informações são destinadas a usuários avançados que estão familiarizados com a arquitetura do servidor MySQL.

Log binário

As cargas de dados resultam em uma penalidade de performance e exigem espaço livre em disco adicional (até quatro vezes mais) quando o registro em log binário está habilitado em comparação ao carregamento dos mesmos dados com o registro em log binário desabilitado. A gravidade da penalidade de performance e a quantidade de espaço livre em disco necessário são diretamente proporcionais ao tamanho das transações usadas para carregar os dados.

Tamanho da transação

O tamanho da transação desempenha um papel importante nas cargas de dados do MySQL. Ele tem uma grande influência no consumo de recursos, na utilização do espaço em disco, no processo de retomada, no tempo de recuperação e no 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. Conforme observado anteriormente, o registro binário é habilitado e desabilitado 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. Descrevemos também o impacto de grandes transações sobre o InnoDB e por que é importante manter o tamanho das transações 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 sofrida depende, em parte, da taxa de upload, de outras atividades do banco de dados que ocorrem durante o carregamento e da capacidade da instância de banco de dados do Amazon RDS.

Os logs binários também consomem espaço em disco aproximadamente igual à quantidade de dados carregados até que eles serem copiados e removidos. Felizmente, o Amazon RDS minimiza isso fazendo o backup e removendo logs binários frequentemente.

Transações grandes

Transações grandes resultam em uma penalidade 3X para IOPS e consumo de disco com o registro em log binário habilitado. Isso é devido ao cache de log binário que entorna no disco, consumindo espaço em disco e resultando em E/S adicional para cada gravação. O cache não pode ser gravado no log binário até a transação ser confirmada ou revertida e, por isso, consome espaço em disco proporcionalmente à quantidade de dados carregados. Quando a transação é confirmada, o cache deve ser copiado no log binário, criando uma terceira cópia dos dados no disco.

Devido a isso, deve haver pelo menos três vezes mais espaço livre em disco disponível para carregar os dados em comparação ao carregamento com o registro em log binário desabilitado. Por exemplo, 10 GiB de dados carregados como uma única transação consomem pelo menos 30 GiB de espaço em disco durante o carregamento. Consome 10 GiB para a tabela + 10 GiB para o cache de log binário + 10 GiB para o log binário propriamente dito. O arquivo de cache permanece no disco até a sessão que o criou ser encerrada ou até a sessão preencher novamente seu cache de log binário novamente durante outra transação. O log binário deve permanecer no disco até ser copiado para backup e, portanto, pode demorar algum tempo até que os 20 GiB extras sejam liberados.

Se os dados foram carregados usando LOAD DATA LOCAL INFILE, uma outra cópia dos dados será criada se o banco de dados tiver que ser recuperado após um backup feito antes do carregamento. Durante a recuperação, o MySQL extrai dados de log binário em um arquivo simples. Depois disso, o MySQL executa LOAD DATA LOCAL INFILE, assim como na transação original. No entanto, desta vez, o arquivo de entrada é um arquivo local para o servidor de banco de dados. Continuando com o exemplo anterior, a recuperação falha, a menos que existam pelo menos 40 GiB de espaço livre em disco disponível.

Desabilitar o registro em log binário

Sempre que possível, desabilite o registro em log binário durante grandes carregamentos de dados para evitar a sobrecarga de recursos e requisitos de espaço em disco adicionais. No Amazon RDS, desabilitar o log binário é tão simples quanto definir o período de retenção de backup como zero. Se você fizer isso, recomendamos que você faça um snapshot de banco de dados da instância de banco de dados imediatamente antes do carregamento. Ao fazer isso, você pode desfazer de forma rápida e fácil as alterações feitas durante o carregamento, se necessário.

Após o carregamento, defina o período de retenção de backup de volta a um valor apropriado (diferente de zero).

Não é possível definir o período de retenção de backup como zero se a instância de banco de dados for uma instância de banco de dados de origem para réplicas de leitura.

InnoDB

As informações nesta seção fornecem um argumento forte para manter os tamanhos das transações pequenos ao usar o InnoDB.

Desfazer

O InnoDB gera uma operação Desfazer para dar suporte a recursos como a reversão de transações e o MVCC. A operação Desfazer é armazenada no espaço de tabela do sistema InnoDB (geralmente ibdata1) e é retida até ser removida pelo thread de limpeza. O thread de limpeza não pode avançar para além da operação Desfazer da transação ativa mais antiga e, portanto, é efetivamente bloqueado até que a transação seja confirmada ou conclua uma reversão. Se o banco de dados estiver processando outras transações durante o carregamento, sua operação Desfazer também se acumulará no espaço de tabela do sistema e não poderá ser removida, mesmo que elas sejam confirmadas e nenhuma outra transação precise da operação Desfazer para o MVCC. Nessa situação, todas as transações (incluindo transações somente leitura) que acessam qualquer uma das linhas alteradas por qualquer transação (não apenas a transação de carregamento) diminuem a velocidade. A lentidão ocorre porque as transações percorrem a operação Desfazer que poderia ter sido limpa se não fosse pela transação de carregamento de longa duração.

A operação Desfazer é armazenada no espaço de tabela do sistema e ele nunca diminui de tamanho. Portanto, grandes transações de carregamento de dados podem fazer com que o espaço de tabela do sistema se torne bastante grande, consumindo espaço em disco que não pode ser recuperado sem a recriação do banco de dados do zero.

Reversão

O InnoDB é otimizado para confirmações. Reverter uma transação grande pode demorar muito, muito tempo. Em alguns casos, pode ser mais rápido realizar uma recuperação point-in-time ou restaurar um snapshot de banco de dados.

Formato dos dados de entrada

O MySQL pode aceitar dados recebidos de uma destas duas formas: arquivos simples e SQL. Esta seção aponta algumas das principais vantagens e desvantagens de cada uma.

Arquivos simples

Carregando arquivos simples com LOAD DATA LOCAL INFILE pode ser o método mais rápido e menos dispendioso de carregar dados, desde que as transações sejam mantidas relativamente pequenas. Em comparação com o carregamento dos mesmos dados com o SQL, arquivos simples geralmente requerem menos tráfego de rede, reduzindo os custos de transmissão, e são carregados com muito mais rapidez devido à sobrecarga reduzida no banco de dados.

Uma única transação grande

LOAD DATA LOCAL INFILE carrega todo o arquivo simples como uma única transação. Isso não é necessariamente uma coisa ruim. Se o tamanho dos arquivos individuais puder ser mantido pequeno, isso tem uma série de vantagens:

Preste atenção

As vantagens de LOAD DATA LOCAL INFILE diminuem rapidamente à medida que o tamanho da transação aumenta. Se dividir um grande conjunto de dados em conjuntos menores não for uma opção, o SQL pode ser a melhor escolha.

SQL

O SQL tem uma vantagem principal em relação aos arquivos simples: é fácil manter os tamanhos das transações pequenos. No entanto, o SQL pode demorar significativamente mais tempo para carregar do que arquivos simples, e pode ser difícil determinar onde retomar o carregamento após uma falha. Por exemplo, arquivos mysqldump não são reiniciáveis. Se ocorrer uma falha ao carregar um arquivo mysqldump, o arquivo exige modificação ou substituição para que o carregamento possa continuar. A alternativa é restaurar para o momento específico antes do carregamento e reproduzir o arquivo depois que a causa da falha for corrigida.

Fazer verificações usando snapshots do Amazon RDS

Se você tem um carregamento que demorará várias horas ou até mesmo dias, o carregamento sem o registro em log binário não é uma perspectiva muito atraente, a menos que você possa fazer verificações periódicas. É aqui que o recurso snapshot de banco de dados do Amazon RDS é bastante útil. Um snapshot de banco de dados cria uma cópia point-in-time consistente da sua instância de banco de dados, que pode ser usada para restaurar o banco de dados para esse ponto no tempo após uma falha ou outro acidente.

Para criar uma verificação, basta tirar um snapshot de banco de dados. Qualquer snapshot de banco de dados anterior tirado para verificações pode ser removido sem afetar a durabilidade ou o tempo de restauração.

Os snapshots também são rápidos e, portanto, a verificação não aumenta significativamente o tempo de carregamento.

Diminuir o tempo de carregamento

Veja a seguir algumas dicas adicionais para reduzir os tempos de carregamento: