Início / dba / Perguntas / 10840
Accepted
Dolan Antenucci
Asked: 2012-01-18 14:39:56 +0800 CST

Como posso otimizar minha configuração do mysql para criar meu índice mais rapidamente?

  • 772

Eu tenho um servidor rodando Ubuntu 10.04 com Mysql 5.1x instalado via pacote. O sistema tem 128 GB de RAM, 8 núcleos e 4 TB de espaço livre onde Mysql e Mysql tmp são armazenados.

Eu tenho um MyISAM assim:

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

Eu inseri 800mil registros (cerca de 350gb de dados antes de inserir), então tentei adicionar o seguinte índice:

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

(Em relação ao DISABLE KEYScomando, vi que sugerido em outro lugar para usar ANTES de inserir dados e, como o comando nunca chegou a ENABLE KEYS, presumo que não esteja servindo a nenhum propósito para mim. Acabei de incluir principalmente para ser completo em minha descrição do que estou estou fazendo.)

Quando inicio o trabalho de indexação, o primeiro status que aparece SHOW PROCESSLISTé "copiando para a tabela tmp".

Depois de algumas horas, volto a verificar e o status permanece na mensagem "Reparar com Keycache" mesmo após 24 horas. Tentei executar o trabalho em um servidor um pouco mais antigo e, após 3 dias, ele ainda permanece com o status "Reparar com cache de chave". Por causa disso, cancelei o comando create-index nesta máquina mais nova.

Eu li que o "Reparar com Keycache" pode ser muito lento e "Reparar por classificação" é o preferido em muitos casos.

Com base em alguns Stack Exchange e postagens aleatórias online, adicionei as seguintes configurações ao meu servidor Mysql:

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

Reiniciei o trabalho e o mesmo processo aconteceu novamente (copie para o arquivo tmp e repare por meio do cache de chaves).

Depois de matar o trabalho, notei que em mysql/error.log havia uma mensagem, "myisam_sort_buffer_size é muito pequeno". Isso aconteceu no início do dia e não exatamente quando eu matei o trabalho.

perguntas

  • Estou indo pelo caminho errado? Eu só quero poder pesquisar meus dados rapidamente por alguma chave (uniqname).

  • Existe algum benefício em começar de novo com minha tabela, adicionar o índice desde o início, usar o DISABLE KEYScomando, inserir meus registros de 800mil e depois ENABLE KEYS? Eu li em outro lugar que isso pode impedir a cópia da tabela tmp (o que pode me poupar apenas algumas horas?)

  • Eu quero este 'Reparar por triagem'?

mysql index

1 respostas

  1. Best Answer

    Para começar, eu não tocaria nos tamanhos dos buffers ainda. Os tamanhos que você tem na pergunta são monstruosamente grandes demais.

    Aqui está outra observação: você tem dados BLOB. Ai, sua mesa temporária vai ocupar espaço rapidamente. Você poderia fazer algo assim:

    Crie um disco RAM de 32 GB chamado /var/tmpfs adicionando esta linha a /etc/fstab

    none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

    Em seguida, crie uma pasta chamada /mysqltmp e monte o disco RAM nela

    mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

    Adicione isso ao my.cnf e reinicie o mysql

    [mysqld]
tmpdir=/mysqltmp

    Agora, qualquer tabela tmp feita via DDL vai parar no disco RAM.

    Aqui está mais uma observação: Por que não criar uma tabela separada que mantenha os dados BLOB longe dos nomes exclusivos?

    CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

    Isso evitará qualquer movimentação de dados BLOB durante a indexação.

    A partir daqui, você sempre teria que ingressar no data_store usando seu nome assim:

    SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

    Fazer essas alterações evitará toda essa confusão de lidar com cache de chaves, discos de RAM e tabelas tmp.

    De uma chance !!!

    • 6

relate perguntas