Como localizar e corrigir tabelas MySQL fragmentadas

Question

Eu usei o MySQLTuner, que apontou algumas tabelas foram fragmentadas. Eu usei

mysqlcheck --optimize -A

para otimizar todas as tabelas. Ele consertou algumas tabelas, mas o MySQLTuner ainda encontra 19 tabelas fragmentadas. Como posso ver quais tabelas precisam ser desfragmentadas? Talvez o OPTIMIZE TABLE funcione onde o mysqlcheck não funcionou? Ou o que mais eu deveria tentar?

    

Answer 1

a resposta curta:

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

A resposta "Você deve saber"

primeiro, você deve entender que as tabelas do Mysql ficam fragmentadas quando uma linha é atualizada, então é uma situação normal. Quando uma tabela é criada, digamos, importada usando um dump com dados, todas as linhas são armazenadas sem fragmentação em muitas páginas de tamanho fixo. Quando você atualiza uma linha de tamanho variável, a página que contém essa linha é dividida em duas ou mais páginas para armazenar as alterações, e essas novas duas (ou mais) páginas contêm espaços em branco preenchendo o espaço não utilizado.

Isso não afeta o desempenho, a menos que a fragmentação cresça demais. O que é muita fragmentação, bem, vamos ver a consulta que você está procurando:

  select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

O DATA_LENGTH e o INDEX_LENGTH são o espaço que seus dados e índices estão usando e DATA_FREE é a quantidade total de bytes não utilizados em todas as páginas da tabela (fragmentação).

Aqui está um exemplo de uma tabela de produção real

| ENGINE | TABLE_NAME               | data_length | index_length | data_free |
| InnoDB | comments                 |         896 |          316 |         5 |

Neste caso, temos uma tabela usando (896 + 316) = 1212 MB e os dados têm um espaço livre de 5 MB. Isso significa uma "proporção de fragmentação" de:

5/1212 = 0.0041

... Qual é realmente uma "taxa de fragmentação" baixa.

Eu tenho trabalhado com tabelas com uma proporção próxima de 0,2 (significando 20% de espaços em branco) e nunca noto uma diminuição nas consultas, mesmo se eu otimizar a tabela, o desempenho é o mesmo. Mas aplicar uma tabela de otimização em uma mesa de 800MB leva muito tempo e bloqueia a tabela por vários minutos, o que é impraticável na produção.

Então, se você considerar o que ganha no desempenho e o tempo perdido para otimizar uma tabela, prefiro NÃO OTIMIZAR.

Se você acha que é melhor para o armazenamento, veja sua proporção e veja quanto espaço você pode economizar quando otimizar. Geralmente não é muito, então eu prefiro não otimizar.

E, se você otimizar, a próxima atualização criará espaços em branco dividindo uma página em dois ou mais. Mas é mais rápido atualizar uma tabela fragmentada do que uma não fragmentada, porque se a tabela estiver fragmentada, uma atualização em uma linha não necessariamente dividirá uma página.

Espero que isso ajude você.

    

Answer 2

Apenas para adicionar à resposta de Felipe-Rojas , você pode calcular a taxa de fragmentos como parte da consulta:

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables  where  DATA_FREE > 0 order by frag_ratio desc;

Se uma tabela estiver fragmentada em uma pequena porcentagem (menos de 5%?), você provavelmente poderá deixá-la em paz.

Qualquer valor maior e você precisará avaliar com base no uso do banco de dados, bloqueio de tabelas, etc., sobre a importância de desfragmentar a tabela.

    

Answer 3

O Optimize Table resolverá de fato o problema que você está tendo.

Se você tiver apenas alguns bancos de dados, poderá usar o PHPMyAdmin para percorrer todos os seus bancos de dados. Selecione as tabelas com sobrecarga e selecione para otimizar.

Se você tiver muitos bancos de dados, outro método provavelmente seria preferível.

Eu uso a seguinte configuração de script PHP no cron para ser executada a cada hora.

$DB = new mysqli ('localhost', 'DbUser', 'DbPassword');
$results = $DB->query('show databases');
$allDbs = array();
while ($row = $results->fetch_array(MYSQLI_NUM))
{
    $allDbs[] = $row[0];
}
$results->close();
foreach ($allDbs as $dbName)
{
    if ($dbName != 'information_schema' && $dbName != 'mysql')
    {
        $DB->select_db($dbName);
        $results = $DB->query('SHOW TABLE STATUS WHERE Data_free > 0');
        if ($results->num_rows > 0)
        {
            while ($row = $results->fetch_assoc())
            {
                $DB->query('optimize table ' . $row['Name']);
            }
        }
        $results->close();
    }
}
$DB->close();

    

Answer 4

Me deparei com esta página e descobri que as perguntas de Felipe-Rojas e sysadmiral são muito úteis. Mas, no meu caso, eu estava executando a consulta no phpMyAdmin do WHM e obter apenas TABLE_NAME não era tão útil, pois o banco de dados não estava listado e vários bancos de dados têm os mesmos nomes de tabelas. Então, simplesmente adicionando TABLE_SCHEMA fornecerá essa coluna também.

select  ENGINE, TABLE_SCHEMA, TABLE_NAME, Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables  where  DATA_FREE > 0 order by frag_ratio desc

mostra o banco de dados

ENGINE  | TABLE_SCHEMA  | TABLE_NAME    | data_length   | index_length  | data_free | frag_ratio

InnoDB  | db_name       | db_table      | 0             | 0             | 8         | 170.6667

Para "consertar" eu usei o link da tabela Desfragmentar no phpMyAdmin para cada uma das tabelas que resultaram em alto "frag_ratio" para o qual o phpMyAdmin é executado:

ALTER TABLE 'table_name' ENGINE = InnoDB;