Como o MyISAM e o InnoDB utilizam o HD Space?

7

Meu servidor MySQL está ficando sem espaço HD rapidamente. A maioria das minhas tabelas maiores usa o mecanismo InnoDB (sem motivo de missão crítica). Em um esforço para evitar o temido ' solte o banco de dados para recuperar o espaço em disco do innodb ', eu gostaria de entender melhor como os dois mecanismos armazenam dados no disco.

  1. Se eu usasse o MyISAM, seria possível recuperar mais facilmente o espaço em disco removendo as linhas? Existe um comando que eu teria que executar além da consulta delete / truncate / empty?
  2. Assumindo que o número 1 fosse verdadeiro: Seria possível / viável converter tabelas InnoDB existentes para o MyISAM e recuperar espaço dessa maneira?
por Mike B 28.06.2010 / 11:04

2 respostas

12

O uso do MySQL no DiskSpace é bastante previsível.

O information_schema pode revelar rapidamente quanto espaço é usado por ambos os mecanismos de armazenamento. No entanto, é muito melhor configurar o InnoDB com innodb_file_per_table . Dessa forma, você pode microgerenciar o espaço em disco de tabelas InnoDB individuais. Se você não tiver innodb_file_per_table, o ibdata1 crescerá e NUNCA AFIRMARÁ.

Eu escrevi artigos legais sobre como limpar o InnoDB de uma vez por todas.

Quanto ao MyISAM, você precisa executar periodicamente um dos seguintes procedimentos:

  • OPTIMIZE TABLE myisam-tablename ;
  • ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename ;

Estes irão comprimir o MyISAM para que não haja dados não utilizados e páginas de índice nos componentes da tabela MyISAM (.MYD e .MYI)

Você pode monitorar manualmente o uso do espaço em disco com esta consulta:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

Isso informará quanto espaço é ocupado pelos dados e pelo índice do mecanismo de armazenamento do mecanismo. Observe no final da consulta a cláusula: (SELECT 3 pw). Altere o número para gerar o relatório em diferentes unidades

(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

ATUALIZAÇÃO 2012-05-26 22:29 EDT

Quando se trata de InnoDB, se você usar innodb_file_per_table, você pode descobrir que há uma diferença entre o tamanho do arquivo .ibd e a soma de data_length + index_length.

Para uma tabela InnoDB mydb.mytable , aqui está a comparação que você deve fazer:

  • Obtenha o tamanho do arquivo executando ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
  • Obtenha o tamanho da tabela do ponto de vista da informação schemna: SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
  • Se o filesize > (data_length+index_length) * 1.1 , então você deve arrastar a tabela assim: ALTER TABLE mydb.mytable ENGINE=InnoDB;

Isso criará uma tabela temporária, copiará apenas páginas de dados e páginas de índice reais na tabela temporária, excluirá o original e renomeará a tabela temporária para mydb.mytable. Compactação de tabela instantânea com um comando. Por favor, planeje todas as compressões da tabela durante as horas de folga.

    
por 11.03.2011 / 09:48
3

MyISAM aloca para o disco refletindo o espaço real utilizado. Se você não estiver usando chaves estrangeiras, transações ou qualquer outro recurso exclusivo do InnoDB, poderá converter facilmente no MyISAM. O InnoDB é mais rápido para escrever e o MyISAM é mais rápido para leitura. Por fim, recomendo pesquisar as diferenças entre os mecanismos em detalhes antes de alterar arbitrariamente os mecanismos de armazenamento.

Aqui está um procedimento que usei para liberar espaço para tabelas InnoDB no passado:

Excluindo grandes blocos de dados do mysql innodb

    
por 28.06.2010 / 16:27