DDL instan (Aurora MySQL versi 3)DDL Cepat (Aurora MySQL versi 2)

Mengubah tabel di Amazon Aurora menggunakan DDL Cepat

Amazon Aurora menyediakan pengoptimalan untuk menjalankan operasi ALTER TABLE di tempat, hampir seketika. Operasi ini akan selesai tanpa mengharuskan tabel disalin dan tanpa memiliki dampak besar pada pernyataan DML lainnya. Karena operasi ini tidak menggunakan penyimpanan sementara untuk salinan tabel, hal ini membuat pernyataan DDL praktis bahkan untuk tabel besar pada kelas instans kecil.

Aurora MySQL versi 3 kompatibel dengan fitur MySQL 8.0 yang disebut DDL instan. Aurora MySQL versi 2 menggunakan implementasi berbeda yang disebut DDL Cepat.

DDL instan (Aurora MySQL versi 3)

Optimisasi yang dilakukan oleh Aurora MySQL versi 3 untuk meningkatkan efisiensi beberapa operasi DDL disebut DDL instan.

Aurora MySQL versi 3 kompatibel dengan DDL instan dari MySQL 8.0 komunitas. Anda melakukan operasi DDL instan dengan menggunakan klausa ALGORITHM=INSTANT dengan pernyataan ALTER TABLE. Untuk detail sintaksis dan penggunaan DDL instan, lihat ALTER TABLE dan Online DDL Operations dalam dokumentasi MySQL.

Contoh berikut menunjukkan fitur DDL instan. Pernyataan ALTER TABLE menambahkan kolom dan mengubah nilai kolom default. Contoh ini mencakup kolom reguler dan virtual, dan tabel reguler dan berpartisi. Pada setiap langkah, Anda dapat melihat hasilnya dengan mengeluarkan pernyataan SHOW CREATE TABLE dan 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 Cepat (Aurora MySQL versi 2)

Di MySQL, banyak operasi bahasa definisi data (DDL) memiliki dampak performa yang signifikan.

Misalnya, anggaplah bahwa Anda menggunakan operasi ALTER TABLE untuk menambahkan kolom ke tabel. Bergantung pada algoritma yang ditentukan untuk operasi, operasi ini dapat memerlukan hal-hal berikut:

Membuat salinan lengkap tabel
Membuat tabel sementara untuk memproses operasi bahasa manipulasi data (DML) konkuren
Membuat kembali semua indeks untuk tabel
Menerapkan kunci tabel sambil menerapkan perubahan DML konkuren
Memperlambat throughput DML konkuren

Optimisasi yang dilakukan oleh Aurora MySQL versi 2 untuk meningkatkan efisiensi beberapa operasi DDL disebut DDL Cepat.

Di Aurora MySQL versi 3, Aurora menggunakan fitur MySQL 8.0 yang disebut DDL instan. Aurora MySQL versi 2 menggunakan implementasi berbeda yang disebut DDL Cepat.

penting

Saat ini, mode lab Aurora harus diaktifkan untuk menggunakan DDL Cepat untuk Aurora MySQL. Kami tidak menyarankan penggunaan DDL Cepat untuk klaster DB produksi. Untuk informasi tentang mengaktifkan mode lab Aurora, lihat Amazon Aurora Mode lab saya SQL.

Batasan DDL Cepat

Saat ini, DDL Cepat memiliki batasan berikut:

DDL Cepat hanya mendukung penambahan kolom yang dapat dibuat null, tanpa nilai default, ke akhir tabel yang ada.
DDL Cepat tidak berfungsi untuk tabel yang dipartisi.
DDL Cepat tidak berfungsi untuk tabel InnoDB yang menggunakan format baris REDUNDANT.
DDL Cepat tidak berfungsi untuk tabel dengan indeks pencarian teks lengkap.
Jika ukuran catatan maksimum yang mungkin untuk operasi DDL terlalu besar, DDL Cepat tidak digunakan. Ukuran catatan terlalu besar jika lebih besar dari setengah ukuran halaman. Ukuran maksimum catatan dihitung dengan menjumlahkan ukuran maksimum semua kolom. Untuk kolom dengan ukuran bervariasi, yang mengikuti standar InnoDB, byte extern tidak disertakan untuk perhitungan.

Sintaksis DDL Cepat


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Pernyataan ini mengambil opsi berikut:

tbl_name – Nama tabel yang akan diubah.
col_name – Nama kolom yang akan ditambahkan.
col_definition – Definisi kolom yang akan ditambahkan.

catatan
Anda harus menentukan kolom yang dapat dibuat null tanpa nilai default. Jika tidak, DDL Cepat tidak digunakan.

Contoh DDL Cepat

Contoh berikut menunjukkan percepatan dari operasi DDL Cepat. Contoh SQL pertama menjalankan pernyataan ALTER TABLE pada tabel besar tanpa menggunakan DDL Cepat. Operasi ini membutuhkan waktu yang cukup lama. Salah satu contoh CLI ini menunjukkan cara mengaktifkan DDL Cepat untuk klaster. Kemudian, contoh SQL lainnya menjalankan pernyataan ALTER TABLE yang sama pada tabel yang identik. Dengan mengaktifkan DDL Cepat, operasinya sangat cepat.

Contoh ini menggunakan tabel ORDERS dari tolok ukur TPC-H, yang berisi 150 juta baris. Klaster ini sengaja menggunakan kelas instans yang relatif kecil untuk menunjukkan waktu yang diperlukan pernyataan ALTER TABLE saat Anda tidak dapat menggunakan DDL Cepat. Contoh ini membuat klon dari tabel asli yang berisi data identik. Dengan memeriksa pengaturan aurora_lab_mode, akan dikonfirmasi bahwa klaster tidak dapat menggunakan DDL Cepat karena mode lab tidak diaktifkan. Kemudian, pernyataan ALTER TABLE ADD COLUMN membutuhkan banyak waktu untuk menambahkan kolom baru di akhir tabel.


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)

Contoh ini melakukan persiapan tabel besar yang sama seperti contoh sebelumnya. Namun, Anda tidak bisa begitu saja mengaktifkan mode lab dalam sesi SQL interaktif. Pengaturan tersebut harus diaktifkan dalam grup parameter kustom. Tindakan tersebut dapat dilakukan dengan beralih dari sesi mysql dan menjalankan beberapa perintah AWS CLI atau menggunakan 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

Pengaktifan mode lab untuk klaster memerlukan beberapa pekerjaan dengan grup parameter. Contoh AWS CLI ini menggunakan grup parameter klaster untuk memastikan bahwa semua instans DB di klaster menggunakan nilai yang sama untuk pengaturan mode lab.


$ 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

Contoh berikut menunjukkan langkah-langkah yang tersisa setelah perubahan grup parameter berlaku. Contoh ini menguji pengaturan aurora_lab_mode untuk memastikan bahwa klaster dapat menggunakan DDL Cepat. Contoh ini kemudian menjalankan pernyataan ALTER TABLE untuk menambahkan kolom ke akhir tabel besar lainnya. Kali ini, pernyataan selesai dengan sangat cepat.


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)

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Menguji Amazon Aurora My SQL menggunakan kueri injeksi kesalahan

Menampilkan status volume untuk klaster DB Aurora