Início / dba / Perguntas / 16747
Accepted
Ran
Asked: 2012-04-20 02:07:00 +0800 CST

MySql - Limpar ibdata1

  • 772

Eu tenho um banco de dados cujo tamanho total é ~ 44 GB, dos quais ibdata1 é ~ 35 GB. Isso não faz sentido, já que o tamanho dos dados não deve ser superior a 10 GB.

Eu uso a seguinte consulta para obter uma estimativa do tamanho dos dados:

    SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  30;

Alguma ideia de como limpar o ibdata1 e por que ele cresceu tanto?

BTW, eu uso innodb_file_per_table

mysql innodb

2 respostas

  1. Best Answer

    Andreas obteve esta resposta primeiro em termos do que fazer . +1 para Andreas !!!

    Gostaria de esclarecer por que essa resposta é a única maneira e como fazê-lo.

    Por padrão, o ibdata1 normalmente abriga quatro tipos de informações

    • Dados da Tabela
    • Índices de tabela
    • Dados MVCC (Multiversioning Concurrency Control)
    • Metadados da tabela

    Executar OPTIMIZE TABLE em uma tabela InnoDB armazenada ibdata1 tornará as coisas piores porque aqui está o que ele faz:

    • Torna os dados e índices da tabela contíguos dentro de ibdata1
    • Faz o ibdata1 crescer porque os dados contíguos são anexados ao ibdata1

    Você pode separar dados de tabela e índices de tabela de ibdata1 e gerenciá-los de forma independente usando innodb_file_per_table . Dessa forma, apenas MVCC e Table MetaData residiriam em ibdata1.

    Se você já o usa, deve ter um ambiente de alta gravação que armazene muitos MVCC para oferecer suporte ao isolamento de transações. Uma vez concluídas as transações com o MVCC, o espaço é simplesmente abandonado para reutilização.

    Para reduzir o ibdata1 de uma vez por todas, você deve fazer o seguinte:

    PASSO 01) MySQLDump todos os bancos de dados em um arquivo de texto SQL (chame-o SQLData.sql)

    PASSO 02) Elimine todos os bancos de dados (exceto o esquema mysql)

    PASSO 03)service mysql stop

    PASSO 04) Adicione as seguintes linhas ao /etc/my.cnf

    [mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

    Nota: Seja qual for o seu conjunto para innodb_buffer_pool_size, certifique-se de que innodb_log_file_size seja 25% de innodb_buffer_pool_size.

    PASSO 05)rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

    Neste ponto, deve haver apenas o esquema mysql em /var/lib/mysql

    PASSO 06)service mysql start

    Isso recriará ibdata1 em 10 MB, ib_logfile0 e ib_logfile1 em 1 G cada

    PASSO 07) Recarregue SQLData.sql no mysql

    ibdata1 crescerá, mas conterá apenas metadados de tabela

    Cada tabela InnoDB existirá fora do ibdata1

    Suponha que você tenha uma tabela InnoDB chamada mydb.mytable. Se você entrar em /var/lib/mysql/mydb, verá dois arquivos representando a tabela

    • mytable.frm (cabeçalho do mecanismo de armazenamento)
    • mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

    ibdata1 nunca mais conterá dados e índices do InnoDB.

    Com a opção innodb_file_per_table em /etc/my.cnf, você pode executar OPTIMIZE TABLE mydb.mytable;e o arquivo /var/lib/mysql/mydb/mytable.ibd será reduzido.

    Eu gostaria de sugerir uma fórmula para você consultar sobre o dimensionamento do seu InnoDB Buffer Pool do meu post em Quais são as principais diferenças entre InnoDB e MyISAM?

    • 13

  2. Meu melhor palpite é que em algum momento foi tão grande.

    ibdata1 nunca encolherá, portanto, manterá seu tamanho máximo. Talvez você tenha tido algumas tabelas antes de fazer innodb_file_per_table que eram grandes.

    A única maneira que conheço de reduzi-lo é fazer um dump, excluir os arquivos (ibdata, iblog e tudo .idb) e carregar o dump.

    • 3

relate perguntas