Alterar tabelas no Amazon Aurora usando a DDL rápida - Amazon Aurora

Alterar tabelas no Amazon Aurora usando a DDL rápida

O Amazon Aurora inclui otimizações para executar uma operação de ALTER TABLE localmente, de maneira quase instantânea. A operação é concluída sem exigir que a tabela seja copiada e sem causar impacto material em outras instruções de DML. Como a operação não consome armazenamento temporário para a cópia da tabela, as instruções de DDL são práticas mesmo para tabelas grandes em classes de instâncias pequenas.

O Aurora MySQL versão 3 é compatível com o recurso do MySQL 8.0 chamado DDL instantânea. O Aurora MySQL versão 2 utiliza uma implementação diferente, chamada DDL rápida.

DDL instantânea (Aurora MySQL versão 3)

A otimização realizada pelo Aurora MySQL versão 3 para melhorar a eficiência de algumas operações de DDL é chamada de DDL instantânea.

O Aurora MySQL versão 3 é compatível com a DDL instantânea da comunidade do MySQL 8.0. Você realiza uma operação de DDL instantânea utilizando a cláusula ALGORITHM=INSTANT com a instrução ALTER TABLE. Para obter detalhes de sintaxe e uso sobre a DDL instantânea, consulte ALTER TABLE e Operações online de DDL na documentação do MySQL.

Os seguintes exemplos demonstram o recurso de DDL instantânea. As instruções ALTER TABLE adicionam colunas e modificam valores de colunas padrão. Os exemplos incluem colunas regulares e virtuais, bem como tabelas regulares e particionadas. Em cada etapa, é possível ver os resultados emitindo instruções SHOW CREATE TABLE e DESCRIBE.

mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)) PARTITION BY KEY(b) PARTITIONS 6; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t3 (a INT, b INT) PARTITION BY LIST(a)( -> PARTITION mypart1 VALUES IN (1,3,5), -> PARTITION MyPart2 VALUES IN (2,4,6) -> ); Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE t3 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t4 (a INT, b INT) PARTITION BY RANGE(a) -> (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(1000), -> PARTITION p2 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE t4 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) /* Sub-partitioning example */ mysql> CREATE TABLE ts (id INT, purchased DATE, a INT, b INT) -> PARTITION BY RANGE( YEAR(purchased) ) -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (2000), -> PARTITION p2 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.10 sec) mysql> ALTER TABLE ts ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec)

DDL rápida (Aurora MySQL versão 2)

No MySQL, muitas operações de linguagem de definição de dados (DDL) exercem um impacto significativo na performance.

Por exemplo, suponha que você usará uma operação ALTER TABLE para adicionar uma coluna a uma tabela. Dependendo do algoritmo especificado para a operação, esta operação pode envolver o seguinte:

  • A criação de uma cópia completa da tabela

  • A criação de uma tabela temporária para processar operações simultâneas de linguagem de manipulação de dados (DML)

  • A reconstrução de todos os índices da tabela

  • A aplicação de bloqueios à tabela ao fazer alterações de DML simultâneos

  • O desaceleramento da taxa de transferência de DML simultâneos

A otimização realizada pelo Aurora MySQL versão 2 para melhorar a eficiência de algumas operações de DDL é chamada de DDL rápida.

No Aurora MySQL versão 3, o Aurora utiliza o recurso do MySQL 8.0 chamado de DDL instantânea. O Aurora MySQL versão 2 utiliza uma implementação diferente, chamada DDL rápida.

Importante

No momento, o modo de laboratório do Aurora deve estar habilitado para usar a DDL rápida para o Aurora MySQL. O uso da DDL rápida não é recomendado para clusters de banco de dados de produção. Para informações sobre como habilitar o modo de laboratório do Aurora, consulte Modo de laboratório do Amazon Aurora MySQL.

Limitações de DDL rápido

No momento, a DDL rápida apresenta as seguintes limitações:

  • O DDL rápido oferece suporte somente à adição de colunas anuláveis, sem valores padrão, ao final de uma tabela existente.

  • O Fast DDL não funciona com tabelas particionadas.

  • O DDL rápido não funciona com tabelas do InnoDB que usam o formato de linha REDUNDANT.

  • O Fast DDL não funciona com tabelas com índices de pesquisa de texto completo.

  • Se o tamanho de registro possível máximo para a operação de DDL for muito grande, a DDL rápida não será utilizada. O registro será muito grande se for maior do que a metade do tamanho da página. O tamanho máximo de um registro é calculado adicionando os tamanhos máximos de todas as colunas. Para colunas de tamanho variado, de acordo com os padrões InnoDB, bytes extern não são incluídos para computação.

Sintaxe DDL rápido

ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Esta instrução apresenta as seguintes opções:

  • tbl_nameo nome da tabela a ser modificada.

  • col_nameo nome da coluna a ser adicionada.

  • col_definitiona definição da coluna a ser adicionada.

    nota

    Você deve especificar uma definição de coluna anulável sem um valor padrão. Caso contrário, a DDL rápida não será usada.

Exemplos DDL rápido

Os exemplos a seguir demonstram a aceleração de operações de DDL rápida. O primeiro exemplo SQL executa instruções ALTER TABLE em uma tabela grande sem usar a DDL rápida. Esta operação leva um tempo considerável. Um exemplo da CLI mostra como permitir a DDL rápida para o cluster. Em seguida, outro exemplo SQL executa as mesmas instruções ALTER TABLE em uma tabela idêntica. Com a DDL rápida habilitada, a operação é muito rápida.

Este exemplo usa a tabela deORDERS do benchmark TPC-H, contendo 150 milhões de linhas. Este cluster usa intencionalmente uma classe de instância relativamente pequena, para demonstrar quanto tempo as instruções ALTER TABLE podem demorar quando você não pode usar a DDL rápida. O exemplo cria um clone da tabela original contendo dados idênticos. Verificar a configuração de aurora_lab_mode confirma que o cluster não pode usar a DDL rápida, porque o modo de laboratório não está habilitado. Em seguida, as instruções de ALTER TABLE ADD COLUMN levam um tempo considerável para adicionar novas colunas no final da tabela.

mysql> create table orders_regular_ddl like orders; Query OK, 0 rows affected (0.06 sec) mysql> insert into orders_regular_ddl select * from orders; Query OK, 150000000 rows affected (1 hour 1 min 25.46 sec) mysql> select @@aurora_lab_mode; +-------------------+ | @@aurora_lab_mode | +-------------------+ | 0 | +-------------------+ mysql> ALTER TABLE orders_regular_ddl ADD COLUMN o_refunded boolean; Query OK, 0 rows affected (40 min 31.41 sec) mysql> ALTER TABLE orders_regular_ddl ADD COLUMN o_coverletter varchar(512); Query OK, 0 rows affected (40 min 44.45 sec)

Este exemplo faz a mesma preparação de uma tabela grande que o exemplo anterior. No entanto, você não pode simplesmente habilitar o modo de laboratório em uma sessão SQL interativa. Essa configuração deve ser ativada em um grupo de parâmetro personalizado. Isso requer a troca da sessão mysql e a execução de alguns comandos de CLI da AWS ou usar o AWS Management Console.

mysql> create table orders_fast_ddl like orders; Query OK, 0 rows affected (0.02 sec) mysql> insert into orders_fast_ddl select * from orders; Query OK, 150000000 rows affected (58 min 3.25 sec) mysql> set aurora_lab_mode=1; ERROR 1238 (HY000): Variable 'aurora_lab_mode' is a read only variable

Habilitar o modo de laboratório para o cluster requer algum trabalho com um grupo de parâmetro. Este exemplo de AWS CLI usa um grupo de parâmetros de cluster, para garantir que todas as instâncias de banco de dados no cluster usem o mesmo valor para a configuração do modo de laboratório.

$ aws rds create-db-cluster-parameter-group \ --db-parameter-group-family aurora5.7 \ --db-cluster-parameter-group-name lab-mode-enabled-57 --description 'TBD' $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --query '*[*].[ParameterName,ParameterValue]' \ --output text | grep aurora_lab_mode aurora_lab_mode 0 $ aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --parameters ParameterName=aurora_lab_mode,ParameterValue=1,ApplyMethod=pending-reboot { "DBClusterParameterGroupName": "lab-mode-enabled-57" } # Assign the custom parameter group to the cluster that's going to use Fast DDL. $ aws rds modify-db-cluster --db-cluster-identifier tpch100g \ --db-cluster-parameter-group-name lab-mode-enabled-57 { "DBClusterIdentifier": "tpch100g", "DBClusterParameterGroup": "lab-mode-enabled-57", "Engine": "aurora-mysql", "EngineVersion": "5.7.mysql_aurora.2.10.2", "Status": "available" } # Reboot the primary instance for the cluster tpch100g: $ aws rds reboot-db-instance --db-instance-identifier instance-2020-12-22-5208 { "DBInstanceIdentifier": "instance-2020-12-22-5208", "DBInstanceStatus": "rebooting" } $ aws rds describe-db-clusters --db-cluster-identifier tpch100g \ --query '*[].[DBClusterParameterGroup]' --output text lab-mode-enabled-57 $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --query '*[*].{ParameterName:ParameterName,ParameterValue:ParameterValue}' \ --output text | grep aurora_lab_mode aurora_lab_mode 1

O exemplo a seguir mostra as etapas restantes depois que a alteração do grupo de parâmetro tem efeito. Ele testa a configuração de aurora_lab_mode para certificar-se de que o cluster pode usar a DDL rápida. Em seguida, ele executa as instruções de ALTER TABLE para adicionar colunas ao final de outra tabela grande. Desta vez, as instruções terminam muito rapidamente.

mysql> select @@aurora_lab_mode; +-------------------+ | @@aurora_lab_mode | +-------------------+ | 1 | +-------------------+ mysql> ALTER TABLE orders_fast_ddl ADD COLUMN o_refunded boolean; Query OK, 0 rows affected (1.51 sec) mysql> ALTER TABLE orders_fast_ddl ADD COLUMN o_coverletter varchar(512); Query OK, 0 rows affected (0.40 sec)