Início / dba / Perguntas / 29913
Accepted
inohiro
Asked: 2012-12-06 23:13:31 +0800 CST

Inserção InnoDB mais rápida

  • 772

Sou um estudante de pós-graduação pesquisando OLAP com Mondrian OLAP. Portanto, desejo inserir dados no InnoDB (MySQL 5.5) mais rapidamente no carregamento inicial. Nesse ambiente, o único usuário sou eu, então acho que pode permitir configurações mais soltas para velocidade de inserção. No momento, estou usando as seguintes técnicas.

  • desabilitarlog_bin
  • permitirskip-innodb-doublewrite
  • definido transaction_isolationcomo READ-COMMITTEDou READ-UNCOMMITTED(na verdade READ-COMMITED)
  • definido innodb_flush_log_at_trx_commitcomo 0ou 2(na verdade 0)
  • definido innodb_buffer_pool_sizepara 5 GB (o sistema tem 6 GB de RAM)

Existem mais técnicas para inserção mais rápida no InnoDB? E eu tenho que modificar innodb_io_read_threade innodb_io_write_thread? Se você precisar de mais informações, por favor me diga.

mysql innodb

4 respostas

  1. Best Answer

    SUGESTÃO #1

    Se sua máquina tiver vários núcleos, você precisará aumentar o seguinte:

    [mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

    Quem são esses?

    • innodb_read_io_threads - O número de threads de E/S para operações de leitura no InnoDB.
    • innodb_write_io_threads - O número de threads de E/S para operações de gravação no InnoDB.
    • innodb_io_capacity - Um limite superior na atividade de E/S executada pelas tarefas em segundo plano do InnoDB, como liberar páginas do buffer pool e mesclar dados do buffer de inserção.

    SUGESTÃO #2

    Para separar dados e índices do tablespace do sistema (ibdata1), você precisa realizar uma reestruturação completa do InnoDB. Parece complicado, mas é muito simples. Escrevi sobre isso no DBA StackExchange (29 de agosto de 2012) e no StackOverflow (29 de outubro de 2010) . Os passos básicos são

    • CorreSET GLOBAL innodb_fast_shutdown = 0;
    • mysqldump todos os dados para um dump SQL
    • service mysql stop
    • Exclua os seguintes arquivos
      • ibdata1
      • ib_logfile0
      • ib_logfile1
    • service mysql start

    Antes de executar service mysql start, adicione esta linha amy.cnf

    innodb_open_files=32768

    Dessa forma, haverá manipuladores de arquivos dedicados a cada tabela individual. O padrão é 300. Sabe-se que os manipuladores de arquivos são armazenados em cache. Haverá uma desaceleração se você definir isso muito alto e atingir o teto rapidamente . Este não deve ser o caso se você estiver trabalhando com um pequeno número de tabelas.

    • 12

  2. Há um documento inteiro dedicado ao carregamento de dados em massa no InnoDB. Os pontos principais:

    1. desative o autocommit para evitar uma descarga de log extra para cada instrução de inserção:SET autocommit=0;...sql import;COMMIT;

    2. desativar verificações estrangeiras e exclusivas (você não pode desativar todos os índices completamente):

      SET unique_checks=0;
SET foreign_key_checks=0;
    3. Possivelmente defina innodb_autoinc_lock_mode como 2, em vez de 1 (o padrão). Aqui está a documentação sobre esta configuração.

    O terceiro pode ou não ajudá-lo, então sugiro ler esse link para ver como você está carregando os dados inicialmente. Por exemplo, se você estiver dividindo as cargas em várias inserções para executar simultaneamente, isso definitivamente ajudará você a definir o valor como 2. Se você estiver fazendo uma grande inserção de várias linhas, não fará muito (se houver) para ajuda.

    Como você está desligando o log binário para esta inserção inicial, não deve se preocupar com as lacunas nos números de incremento automático (se estiver fazendo inserções simultâneas).

    • 6

  3. Você pode usar os seguintes métodos para acelerar as inserções:

    • Se estiver inserindo várias linhas do mesmo cliente ao mesmo tempo, use INSERTinstruções com várias VALUESlistas para inserir várias linhas de uma vez. Isso é consideravelmente mais rápido (muitas vezes mais rápido em alguns casos) do que usar INSERTinstruções de linha única separadas. Se estiver adicionando dados a uma tabela não vazia, você pode ajustar a variável bulk_insert_buffer_size para tornar a inserção de dados ainda mais rápida.
    • Ao carregar uma tabela de um arquivo de texto, use LOAD DATA INFILE. Isso geralmente é 20 vezes mais rápido do que usar INSERTinstruções. Ver
    • Aproveite o fato de que as colunas têm valores padrão. Insira valores explicitamente apenas quando o valor a ser inserido for diferente do padrão. Isso reduz a análise que o MySQL deve fazer e melhora a velocidade de inserção.
    • Consulte a Seção 9.5.5, “ Carregamento de dados em massa para tabelas InnoDB ” para obter dicas específicas para tabelas InnoDB.
    • 1

  4. Plano A: INSERTs "Batch" -- várias linhas por instrução INSERT. Sugira cerca de 1.000 linhas por instrução. autocommit=on, sem BEGIN...COMMIT explícito

    Plano B: CARREGAR DADOS

    Se você inserir muitas linhas de uma vez, o InnoDB deve fazer mais trabalho para poder reverter a inserção se houver uma falha. Por esse motivo, discordo do autocommit=off, que colocaria todo o conjunto em uma única transação.

    LOAD DATA de todo o conjunto de linhas pode ter o mesmo problema, mas é bem rápido.

    buffer_pool = 5G de 6G está prestes a ser muito grande. Se houver alguma troca, o desempenho cairá.

    O particionamento provavelmente o tornaria mais lento.

    SHOW CREATE TABLE -- Chaves secundárias podem ser uma desvantagem séria.

    Você está usando InnoDB? ou XtraDB?

    • 0

relate perguntas