Como compactar ou restringir o arquivo de dados ibdata1 do MySQL

Question

Nosso aplicativo está tendo dados enormes e cheios de inserções e exclusões. Então, em 3 semanas, o arquivo ibdata1 atingiu 30 GB. Então eu pensei que nós podemos restringir o tamanho do arquivo ibdata1 e alteramos a variável innodb_data_file_path para "ibdata1: 10M: autoextend: max: 32G".

Quando o arquivo chegar a 32 GB, ele está continuamente jogando erros, pois a tabela está cheia para cada instrução de inserção.

Por favor, ajude-me a fazer isso para compactar ou limitar o tamanho do arquivo de dados.

Como precisamos executar nosso aplicativo por anos.

    

Answer 1

Eu recomendo armazenar tabelas InnoDB em arquivos separados, um arquivo por tabela. Torna mais fácil gerenciá-los e ver quais tabelas ocupam mais espaço de armazenamento. Coloque o seguinte em /etc/mysql/my.cnf (ou onde quer que esteja no seu sistema) [mysqld] seção:

innodb_file_per_table

Depois disso, é fácil identificar as tabelas que estão usando muito espaço. Então você precisa recriar a (s) tabela (s) problemática (s) periodicamente (uma vez por dia, uma vez por mês, qualquer coisa) fazendo o seguinte:

ALTER TABLE tablename ENGINE=InnoDB;

Isso levará algum tempo para ser executado em grandes tabelas. Ele recriará os arquivos da tabela a partir do zero e eliminará a fragmentação e os furos que se somam aos tamanhos dos arquivos. Enquanto o ALTER TABLE está sendo executado, ele usa o dobro da capacidade de disco necessária. Não tenho certeza se você pode fazer isso a menos que você esteja executando no modo "arquivo por tabela".

UPDATE

Eu queria acrescentar que para se livrar do arquivo inflado existente "arquivo único para todas as tabelas" do InnoDB ibdata1 , você precisa fazer o seguinte:

1. exporta todas as tabelas InnoDB com o mysqldump
2. desligamento do mysqld
3. coloque a linha "innodb_file_per_table" no seu my.cnf
4. rm ibdata * ib_logfile * no diretório de dados do mysql
5. iniciar o mysqld
6. importe seus dados dos quais você fez backup na etapa 1

Answer 2

Para ativar a compactação em tabelas específicas, você deve:

• defina innodb_file_per_table para ON
• defina innodb_file_format para Barracuda
• reinicie o MySQL e crie novamente o tablespace.
• alterar a definição da tabela usando ALTER TABLE name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

O formato

Barracuda row é suportado por MySQL 5.5 ou MySQL 5.1 com plug-in do InnoDB .

    

Answer 3

Existe uma razão específica pela qual a compactação do ibdata1 é muito importante.

O que entra no ibdata1? Quatro coisas:

• Dados da tabela
• Índices da tabela
• Metadados de tabela
• Dados do MVCC (Controle de Concorrência Multiversionada)

Você precisará fazer duas coisas:

• Limpe o ibdata1 separando dados e índices dele (respondi a esse mesmo tipo de pergunta em 29 de outubro de 2010 e 4 de fevereiro de 2011 ). É semelhante à resposta de @ snap, mas como @snap mencionou este método primeiro nesta questão , ele deve receber elogios (aceitação de sua resposta) por isso.
• Remova essa capa 32G de innodb_data_file_path. Ele deve ler 'ibdata1: 10M: autoextend' (que é o valor padrão de qualquer maneira)

CAVEAT

A razão pela qual innodb_data_file_path deve ser o padrão retorna ao que é armazenado em ibdata1. Mesmo se você remover todos os dados e índices especificados por @snap, os metadados da tabela e os Dados do MVCC ainda serão gravados em ibdata1. Os metadados da tabela ainda podem fazer o ibdata1 crescer se houver um volume muito alto de comandos DDL (CREATE TABLEs, DROP TABLEs, ALTER TABLEs, etc.). O MVCC Data ainda pode fazer o ibdata1 crescer se houver um alto volume de transações que fazem instantâneos de grandes quantidades de dados por transações. Como esse é o caso do ibdata1, pode ser o mais enxuto e mesquinho possível. Portanto, vamos innodb_data_file_path = ibdata1: 10M: autoextend em /etc/my.cnf

BTW: +1 de mim para @snap !!!

    

Answer 4

Este é um excelente guia postado aqui para uma explicação detalhada & resolução:

link