Um desafio que enfrentamos como DBAs é diminuir o espaço ocupado pelo banco de dados. A primeira opção costuma ser o expurgo das tabelas, mas, quando isso não é possível, como liberar espaço no datafile?
Enfrentei esse problema em um cliente e, com o auxílio de um utilitário da Percona, consegui contornar a situação.
Como o utilitário funciona: basicamente, o pt-duplicate-key-checker faz uma varredura no catálogo do MySQL em busca de índices duplicados e de índices que podem ser substituídos por outros sem que as queries que os utilizam percam performance.
Após a verificação, podemos remover (drop) esses índices, o que libera espaço e também melhora a performance das operações de escrita, como INSERT, UPDATE e DELETE.
O primeiro passo é instalar o Percona Toolkit no seu s.o:
- Página de download oficial: https://www.percona.com/downloads/percona-toolkit/
- Guia de instalação: https://docs.percona.com/percona-toolkit/installation.html
Após a instalação, devemos seguir com a execução do utilitário para listar os índices e podemos fazer de várias formas.
- Listar os índices de toda a instância:
| pt-duplicate-key-checker –host=localhost –user=root –ask-pass |
- Listar os índices de uma base especifica:
| pt-duplicate-key-checker –host=localhost –user=root –ask-pass –databases=nome_da_base |
- Listar os índices de uma tabela especifica:
| pt-duplicate-key-checker –host=localhost –user=root –ask-pass –databases=nome_da_base –tables=nome_da_tabela |
Obs: Podemos redirecionar o arquivo utilizando o > (recriar o arquivo do zero) ou >> (sobrescrever) para gerar um arquivo e o Unix não soltar o resultado diretamente na tela.
Após executar os comandos do utilitário, teremos o seguinte resultado:
|
# ######################################################################## # Key idx_cliente_id ends with a prefix of the clustered index # ######################################################################## # Size Duplicate Indexes 18432 |
Primeiro irá aparecer os índices que foram capturados pela ferramenta e a descrição explicando se ele é duplicado ou pode ser substituído por outro, junto do comando para realizar o drop dele.
Em seguida temos um sumário dizendo o tamanho dos índices capturados, o total de índices duplicados e o total de índices.
Após realizar a coleta, o ideal por segurança é deixar os índices invisíveis por um tempo, até que confirme que a query que utilizava o índice anterior não está sofrendo com problemas de performance, porém, isso é possível apenas a partir do MySQL 8.0.
Exemplo:
| ALTER TABLE loja_exemplo.pedidos ALTER INDEX idx_cliente_id INVISIBLE; |
Quando deixamos o índice invisível a query parte a ignorar ele, como se ele estivesse dropado, porém, ele continua tendo os dados atualizados dentro dele, se mantendo integro.
Caso haja algum imprevisto relacionado a query que utilizava o índice que ficou invisível, podemos dar rollback e deixa-lo visível novamente.
Exemplo:
| ALTER TABLE loja_exemplo.pedidos ALTER INDEX idx_cliente_id VISIBLE; |
Após esse comando as Queries voltam a enxergar o índice e passa a ser uma opção de utilização novamente.
Depois de certo tempo em que foi confirmado que não houve nenhum problema, podemos seguir com o drop dos índices. Um ponto importante é a indisponibilidade da tabela enquanto está ocorrendo o drop do índice, logo, o ideal é realizar essa operação ou fora de horário comercial / horário de maior utilização do ambiente.
Porém, caso seja necessário a alteração em horário comercial, ou caso seja uma tabela muito grande onde a alteração vai demorar e ela não pode ficar muito tempo offline, podemos utilizar outro utilitário da Percona para fazer a alteração, o pt-online-schema-change que realiza a alteração sem que a tabela seja bloqueada.
Após o drop dos índices, espaço será liberado e as operações de escrita terão um ganho de performance.
- Documentação oficial pt-duplicate-key-checket: https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html
