Faz sentido rodar “otimizar tabela” quando o banco de dados mysql é armazenado em SSD?

Question

O manual diz sobre "otimizar tabela":

"Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file."

Então, estou supondo que haverá algum ganho de desempenho, mesmo se o meio de armazenamento for SSD (pois a lista vinculada de linhas excluídas será eliminada). OTOH (apenas supondo novamente) o ganho de desempenho não será tão significativo quanto para um HDD, que também se beneficiaria de uma leitura sequencial mais rápida após a otimização.

Então vale a pena correr otimizar nesta situação? O ganho de desempenho será significativo o suficiente para compensar a diminuição na expectativa de vida de SSD (devido à reorganização desnecessária dos dados armazenados)? Estou falando de tabelas que são candidatas perfeitas para otimização (com linhas de tamanho variável e atualizações frequentes).

    

Answer 1

Com frequência, o desempenho do SSD, mesmo o desempenho de leitura, será limitado pela quantidade de operações de E / S por segundo no máximo de sua (s) unidade (s). Pegue o Intel X-25 E , por exemplo - velocidades de leitura máximas de até 250 MB / s, mas também um máximo de 35.000 operações de leitura por segundo, o que resulta em uma taxa de transferência máxima de 140 MB / s para blocos de 4 KB.

Se os seus dados não forem contíguos e , suas leituras típicas incorrerão em carregamento de E / S serial (ou seja, você veria tamanhos grandes de solicitações em um banco de dados não fragmentado para sua carga típica) , o desempenho se beneficiará da execução de OPTIMIZE TABLE .

De longe, o método mais fácil de conhecer é simplesmente verificar - execute um comando OPTIMIZE TABLE para as tabelas mais utilizadas, obtenha a típica carga de leitura / gravação e verifique o tamanho médio da solicitação (avgrq-sz ) usando iostat -x /dev/<device> . Se avgrq-sz for alto (> 32 setores), você se beneficiou do OPTIMIZE. Se não, provavelmente não há necessidade de se preocupar no futuro, já que seu padrão de acesso é bastante aleatório.

    

Answer 2

Há outra coisa a considerar ao usar OPTIMIZE TABLE

Qual mecanismo de armazenamento você está usando, MyISAM ou InnoDB ???

Se você estiver usando MyISAM , você pode de fato recuperar uma quantidade significativa de espaço em disco se uma tabela MyISAM exercer muitos INSERTs, UPDATEs e DELETEs (DIUs). Não se esqueça que OPTIMIZE TABLE chama internamente < href="http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html"> ANALYZE TABLE para atualizar as estatísticas das linhas do índice. As estatísticas de linha de índice podem ser muito prejudicadas em um ambiente pesado de DIU e podem impedir que o MySQL Query Optimizer faça boas escolhas para os planos de consultas EXPLAIN. Se a tabela MyISAM experimentar UPDATEs e DELETEs muito baixos, OPTIMIZE TABLE será desnecessário. Se a tabela MyISAM não tiver DELETEs, mas sim muitos INSERTs e UPDATEs, OPTIMZIE TABLE com muita moderação (uma vez por ano), mas execute ANALYZE TABLE em períodos discretos (talvez uma vez por mês).

Se estivermos falando InnoDB , vamos falar sobre suas configurações do InnoDB. Se você tiver innodb_file_per_table ativado, então OTIMIZAR TABELA é tudo bem e bom. No entanto, ANALYZE TABLE não tem efeito no InnoDB, uma vez que as estatísticas de índices são calculadas on-the-fly através de mergulhos nos índices de BTREE para aproximações de cardinalidade. Por favor, espere que os arquivos .ibd do InnoDB cresçam mais rápido que os seus correspondentes no MyISAM, porque tanto os dados quanto as páginas de índice residem no mesmo arquivo .ibd, enquanto os dados e índices do MyISAM residem em arquivos separados (.MYD e .MYI). Assim, o InnoDB precisará de operações mais frequentes do OPTIMIZE TABLE que o MyISAM.

Se você tiver innodb_file_per_table desativado, você deve reestruturar a infraestrutura do InnoDB antes de executar OPTIMIZE TABLE comandos contra tabelas InnoDB.