Início / dba / Perguntas / 301972
Accepted
Nir
Asked: 2021-11-02 05:14:42 +0800 CST

A coluna suspensa com percona não reduz o tamanho da tabela

  • 772

Eu tenho uma tabela no Aurora MySQL 5.7. table tem poucas partições com 800m de linhas e pesa 2tb. Recentemente eu deixei cair algumas colunas usando percona. Surpreendentemente, o tamanho da tabela não mudou (olhando em information_schema.tables.

A maneira como o percona faz uma mudança é usando a nova tabela _<table_name>_newcom gatilhos na tabela original. ele cria uma nova tabela vazia com o mesmo DDL, executa as alterações que desejamos e copia tudo para a nova tabela com os gatilhos para mantê-la atualizada. uma vez que os dados são sincronizados - percona renomeia as tabelas e descarta a antiga. Portanto, a tabela foi construída do zero (sem travamento).

No entanto, depois de executar alter table optimize partition, vi o tamanho reduzido para 250gb. Alguém tem explicação ou sabe o que fiz de errado?

comando pt:

pt-online-schema-change --user $MYSQL_DBA_USER --password $MYSQL_DBA_PASS --host $MYSQL_WRITER D=db,t=table_data --alter "drop column a1, drop column a2"  --execute --max-load Threads_running=18446744073709551606 --critical-load Threads_running=18446744073709551606 --recursion-method=none

comando de otimização:

MySQL [(db)]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';
+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  610884663 |        1847.7273712158203 |         202.40484619140625 |            0.0322265625 |           3247 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
1 row in set (0.00 sec)


MySQL [db]> ALTER TABLE table_data OPTIMIZE PARTITION p20210601;
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                    |
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| db.table_data | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| db.table_data | optimize | status   | OK                                                                                          |
+------------------------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (5 hours 39 min 40.95 sec)

MySQL [db]>
MySQL [db]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';


+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  736965899 |        104.25639343261719 |         155.98052978515625 |            0.0244140625 |            151 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
mysql mysql-5.7

1 respostas

  1. Best Answer

    Por várias razões, o MySQL não libera espaço livre de volta para o sistema operacional em várias situações. (O tópico comum: o MySQL escolhe a velocidade sobre o espaço.)

    Nesse caso específico, havia duas opções (embora você provavelmente não tenha percebido esses detalhes).

    • O padrão ALTER ... DROP COLUMN ...é executado rapidamente simplesmente alterando a definição da tabela e não se preocupando com o espaço consumido pela coluna.

    • ALTER ... ALGORITHM=COPY ... DROP COLUMN ...copia a tabela e refaz os índices. Isso é lento, mas recupera o espaço livre. Mas é muito mais lento, especialmente para uma mesa grande. OPTIMIZE TABLEfoi efetivamente feito como parte da "cópia".

    Você está preso à escolha clássica do computador - velocidade versus espaço.

    O particionamento raramente é útil; você determinou que ele fornece algum benefício?

    Há um "bug" em OPTIMIZE PARTITION-- ele reconstrói todas as partições, não apenas a que você especificou. (Para reconstruir uma única partição, use REORGANIZE.)

    • 1

relate perguntas