O que são as instâncias é qual o papel desempenhado por elas em um banco de dados

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.

O banco de dados do sistema 'mysql' contém informações de autenticação e autorização necessárias para fazer login em sua instância de banco de dados e acessar seus dados. Soltar, alterar, renomear ou truncar tabelas, dados ou outros conteúdos do banco de dados 'mysql' em sua instância de banco de dados pode resultar em erros e pode tornar a instância de banco de dados e seus dados inacessíveis. Se isso ocorrer, você pode restaurar a instância de banco de dados a partir de um snapshot usando o comando AWS CLI da restore-db-instance-from-db-snapshot. Você pode recuperar a instância de banco de dados usando o comando AWS CLI da restore-db-instance-to-point-in-time.

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. Todos os comentários relacionados a LOAD DATA LOCAL INFILE também se aplicam ao mysqlimport.

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:

• Capacidade de retomada – É fácil manter o controle de quais arquivos foram carregados. Se surgir um problema durante o carregamento, você poderá continuar de onde parou com pouco esforço. Alguns dados podem ter que ser retransmitidos ao Amazon RDS, mas com arquivos pequenos, a quantidade retransmitida é mínima.

• Carregar dados em paralelo – Se você tem IOPS e largura de banda de sobra com uma único carregamento de arquivo, o carregamento em paralelo pode economizar tempo.

• Acelerar a taxa de carregamento – O carregamento de dados tem impacto negativo em outros processos? Acelere o carregamento aumentando o intervalo entre os arquivos.

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:

• Crie todos os índices secundários antes do carregamento. Isso é contra intuitivo para quem está familiarizado com outros bancos de dados. Adicionar ou modificar um índice secundário faz com que o MySQL crie uma nova tabela com as alterações de índice, copie os dados da tabela existente para a nova tabela e descarte a tabela original.

• Carregue dados na ordem PK. Isso é particularmente útil para tabelas do InnoDB, em que os tempos de carregamento podem ser reduzidos em 75 a 80% e o tamanho do arquivo de dados é reduzido pela metade.

• Desabilite as restrições de chaves estrangeiras foreign_key_checks=0. Para arquivos simples carregados com LOAD DATA LOCAL INFILE, isso é necessário em muitos casos. Para qualquer carregamento, desabilitar as verificações de FK proporciona ganhos de performance significativos. Apenas certifique-se de habilitar as restrições e verificar os dados após o carregamento.

• Faça o carregamento em paralelo, a menos que esteja próximo de um limite de recursos. Use tabelas particionadas quando apropriado.

• Use inserções de vários valores ao carregar com o SQL para minimizar a sobrecarga ao executar instruções. Ao usar mysqldump, isso é feito automaticamente.

• Reduza a E/S de log do InnoDB innodb_flush_log_at_trx_commit=0

• Se você estiver carregando dados em uma instância de banco de dados que não tem réplicas de leitura, defina o parâmetro sync_binlog como 0 enquanto carrega os dados. Quando o carregamento de dados for concluído, configure o parâmetro sync_binlog de volta como 1.

• Carregue dados antes de converter a instância de banco de dados para uma implantação multi-AZ. Contudo, se a instância de banco de dados já utiliza uma Implantação multi-AZ, mudar para uma implantação single-AZ para o carregamento de dados não é recomendado, porque ela só fornece melhorias marginais.

Usar innodb_flush_log_at_trx_commit=0 faz com que o InnoDB descarregue seus logs a cada segundo em vez de em cada confirmação. Isso proporciona uma vantagem de velocidade significativa, mas pode levar à perda de dados durante uma falha. Use com cautela.

Tópicos

• Restauração de um backup em uma instância de banco de dados MySQL
• Importar dados de um banco de dados MariaDB ou MySQL para uma instância de banco de dados MariaDB ou MySQL
• Importar dados para uma instância de banco de dados MariaDB ou MySQL do Amazon RDS com tempo de inatividade reduzido
• Importar dados de qualquer fonte para uma instância de banco de dados MariaDB ou MySQL

Que são as instâncias e qual o papel desempenhado por elas em um banco de dados?

Como saber a instância do SQL Server?

Qual o papel desempenhado pelo arquivo de log?

Como criar uma instância SQL Server?