Perilaku tabel sementara baru di Aurora MySQL versi 3

Aurora MySQL versi 3 menangani tabel sementara secara berbeda dari Aurora MySQL versi sebelumnya. Perilaku baru ini diwarisi dari MySQL 8.0 Community Edition. Berikut dua jenis tabel sementara yang dapat dibuat dengan Aurora MySQL versi 3:

• Tabel sementara internal (atau implisit) — Dibuat oleh mesin Aurora MySQL untuk menangani operasi seperti pengurutan agregasi, tabel turunan, atau ekspresi tabel umum (CTE).

• Tabel sementara yang dibuat pengguna (atau eksplisit) — Dibuat oleh mesin Aurora MySQL saat Anda menggunakan pernyataan CREATE TEMPORARY TABLE.

Terdapat pertimbangan tambahan untuk tabel sementara internal dan yang dibuat pengguna pada instans DB pembaca Aurora. Kita akan membahas perubahan ini di bagian berikut.

Mesin penyimpanan untuk tabel sementara internal (implisit)

Saat membuat set hasil perantara, Aurora MySQL awalnya mencoba menulis ke tabel sementara dalam memori. Langkah ini mungkin tidak berhasil karena tipe data yang tidak kompatibel atau batas yang dikonfigurasi. Jika demikian, tabel sementara dikonversi ke tabel sementara di disk, bukan disimpan di memori. Informasi selengkapnya tentang hal ini dapat ditemukan di Internal Temporary Table Use in MySQL dalam dokumentasi MySQL.

Di Aurora MySQL versi 3, cara kerja tabel sementara internal berbeda dengan Aurora MySQL versi sebelumnya. Alih-alih memilih antara mesin penyimpanan InnoDB dan MyISAM untuk tabel sementara seperti itu, sekarang Anda memilih antara mesin penyimpanan dan mesin penyimpanan. TempTable MEMORY

Dengan mesin penyimpanan TempTable, Anda dapat membuat pilihan tambahan untuk cara menangani data tertentu. Data yang terpengaruh memenuhi kumpulan memori yang menampung semua tabel sementara internal untuk instans DB.

Pilihan tersebut dapat memengaruhi performa kueri yang menghasilkan volume data sementara yang tinggi, misalnya saat melakukan agregasi seperti GROUP BY pada tabel besar.

Tip

Jika beban kerja Anda mencakup kueri yang menghasilkan tabel sementara internal, konfirmasikan performa aplikasi Anda dengan perubahan ini melalui upaya menjalankan tolok ukur dan memantau metrik terkait performa.

Dalam kasus tertentu, jumlah data sementara sesuai dengan kumpulan memori TempTable atau hanya memenuhi kumpulan memori dalam jumlah kecil. Jika demikian, sebaiknya gunakan pengaturan TempTable untuk tabel sementara internal dan file yang dipetakan memori untuk menyimpan data overflow apa pun. Ini adalah pengaturan default.

Mesin penyimpanan TempTable adalah pengaturan default. TempTable menggunakan kumpulan memori umum untuk semua tabel sementara yang menggunakan mesin ini, bukan batas memori maksimum per tabel. Ukuran kumpulan memori ini ditentukan oleh parameter temptable_max_ram. Nilai default adalah 1 GiB pada instans DB dengan memori 16 GiB atau lebih, dan 16 MB pada instans DB dengan memori kurang dari 16 GiB. Ukuran kumpulan memori memengaruhi konsumsi memori tingkat sesi.

Dalam kasus tertentu, bila Anda menggunakan mesin penyimpanan TempTable, data sementara mungkin melebihi ukuran kumpulan memori. Jika demikian, Aurora MySQL akan menyimpan data overflow tersebut menggunakan mekanisme sekunder.

Anda dapat mengatur parameter temptable_max_mmap untuk memilih apakah data akan memenuhi file sementara yang dipetakan memori atau tabel sementara internal InnoDB di disk. Format data yang berbeda dan kriteria overflow dari mekanisme overflow ini dapat memengaruhi performa kueri. Hal ini dilakukan dengan memengaruhi jumlah data yang ditulis ke disk dan permintaan atas throughput penyimpanan disk.

Aurora MySQL menyimpan data overflow secara berbeda tergantung pada pilihan tujuan overflow data Anda dan apakah kueri berjalan pada instans DB penulis atau pembaca:

• Pada instans penulis, data yang memenuhi tabel sementara internal InnoDB disimpan dalam volume klaster Aurora.

• Pada instans penulis, data yang memenuhi file sementara yang dipetakan memori disimpan di penyimpanan lokal pada instans Aurora MySQL versi 3.

• Pada instans pembaca, data overflow selalu berada pada file sementara yang dipetakan memori di penyimpanan lokal. Hal ini karena instans hanya baca tidak dapat menyimpan data apa pun pada volume klaster Aurora.

Parameter konfigurasi terkait tabel sementara internal berlaku untuk instans penulis dan pembaca dengan cara yang berbeda di klaster Anda:

• Pada instans pembaca, Aurora MySQL selalu menggunakan mesin penyimpanan TempTable.

• Ukuran default temptable_max_mmap adalah 1 GiB untuk instans penulis dan pembaca, berapa pun ukuran memori instans DB-nya. Anda dapat menyesuaikan nilai ini pada instans penulis dan pembaca.

• Mengatur temptable_max_mmap ke 0 akan menonaktifkan penggunaan file sementara yang dipetakan memori pada instans penulis.

• Anda tidak dapat mengatur temptable_max_mmap ke 0 pada instans pembaca.

catatan

Kami tidak menyarankan penggunaan parameter temptable_use_mmap. Parameter tersebut telah dihentikan, dan dukungannya direncanakan akan dihapus dalam rilis MySQL mendatang.

Membatasi ukuran tabel sementara internal dalam memori

Sebagaimana dibahas dalam Mesin penyimpanan untuk tabel sementara internal (implisit), Anda dapat mengontrol sumber daya tabel sementara secara global dengan menggunakan pengaturan temptable_max_ram dan temptable_max_mmap.

Anda juga dapat membatasi ukuran setiap tabel sementara internal dalam memori menggunakan parameter DB tmp_table_size. Batas ini dimaksudkan untuk mencegah setiap kueri mengonsumsi sumber daya tabel sementara global dalam jumlah besar, yang dapat memengaruhi performa kueri bersamaan yang memerlukan sumber daya ini.

Parameter tmp_table_size menetapkan ukuran maksimum tabel sementara yang dibuat oleh mesin penyimpanan MEMORY di Aurora MySQL versi 3.

Di Aurora MySQL versi 3.04 dan yang lebih tinggi, tmp_table_size juga menetapkan ukuran maksimum tabel sementara yang dibuat oleh mesin penyimpanan TempTable saat parameter DB aurora_tmptable_enable_per_table_limit diatur ke ON. Perilaku ini dinonaktifkan secara default (OFF), yang merupakan perilaku serupa seperti di Aurora MySQL versi 3.03 dan yang lebih rendah.

• Bila aurora_tmptable_enable_per_table_limit diatur ke OFF, tmp_table_size tidak dipertimbangkan untuk tabel sementara internal dalam memori yang dibuat oleh mesin penyimpanan TempTable.

  Namun, batas sumber daya TempTable global masih berlaku. Bila batas sumber daya TempTable global tercapai, Aurora MySQL memiliki perilaku berikut:

  • Instans DB penulis – Aurora MySQL secara otomatis mengonversi tabel sementara dalam memori menjadi tabel sementara di disk InnoDB.

  • Instans DB pembaca – Kueri berakhir dengan kesalahan.

    ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

• Bila aurora_tmptable_enable_per_table_limit diatur ke ON, Aurora MySQL memiliki perilaku berikut saat batas tmp_table_size tercapai:

  • Instans DB penulis – Aurora MySQL secara otomatis mengonversi tabel sementara dalam memori menjadi tabel sementara di disk InnoDB.

  • Instans DB pembaca – Kueri berakhir dengan kesalahan.

    ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

    Dalam kasus ini, batas sumber daya TempTable global dan batas per tabel berlaku.

catatan

Parameter aurora_tmptable_enable_per_table_limit tidak berpengaruh saat internal_tmp_mem_storage_engine diatur ke MEMORY. Dalam hal ini, ukuran maksimum tabel sementara dalam memori ditentukan oleh nilai tmp_table_size atau max_heap_table_size, mana pun yang lebih kecil.

Contoh berikut menunjukkan perilaku parameter aurora_tmptable_enable_per_table_limit untuk instans DB penulis dan pembaca.

contoh instans DB penulis dengan aurora_tmptable_enable_per_table_limit diatur ke OFF

Tabel sementara dalam memori tidak dikonversi ke tabel sementara di disk InnoDB.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  0 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

contoh instans DB penulis dengan aurora_tmptable_enable_per_table_limit diatur ke ON

Tabel sementara dalam memori dikonversi ke tabel sementara di disk InnoDB.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  0 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n)  |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

contoh instans DB pembaca dengan aurora_tmptable_enable_per_table_limit diatur ke OFF

Kueri selesai tanpa kesalahan karena tmp_table_size tidak berlaku dan batas sumber daya TempTable global belum tercapai.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)

contoh instans DB pembaca dengan aurora_tmptable_enable_per_table_limit diatur ke OFF

Kueri ini mencapai batas TempTable sumber daya global dengan aurora_tmptable_enable_per_table_limit disetel ke OFF. Kueri berakhir dengan kesalahan pada instans pembaca.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full

contoh instans DB pembaca dengan aurora_tmptable_enable_per_table_limit diatur ke ON

Kueri berakhir dengan kesalahan saat batas tmp_table_size tercapai.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  1 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full

Mengurangi masalah kepenuhan untuk tabel sementara internal di Aurora Replicas

Untuk mencegah masalah pembatasan ukuran pada tabel sementara, atur parameter temptable_max_ram dan temptable_max_mmap ke nilai gabungan yang dapat memenuhi persyaratan beban kerja Anda.

Berhati-hatilah saat mengatur nilai parameter temptable_max_ram. Mengatur nilai terlalu tinggi mengurangi memori yang tersedia pada instance database, yang dapat menyebabkan suatu out-of-memory kondisi. Pantau memori rata-rata yang dapat dikosongkan pada instans DB. Lalu, tentukan nilai yang sesuai untuk temptable_max_ram, sehingga Anda akan tetap memiliki memori bebas dalam jumlah wajar yang tersisa pada instans. Untuk informasi selengkapnya, lihat Masalah memori yang dapat dikosongkan di Amazon Aurora.

Penting juga bagi Anda untuk memantau ukuran penyimpanan lokal dan konsumsi ruang tabel sementara. Untuk informasi selengkapnya tentang pemantauan penyimpanan lokal pada sebuah instans, lihat artikel Pusat AWS Pengetahuan Apa yang disimpan di penyimpanan lokal yang kompatibel dengan Aurora MySQL, dan bagaimana cara mengatasi masalah penyimpanan lokal? .

catatan

Prosedur ini tidak berfungsi bila parameter aurora_tmptable_enable_per_table_limit diatur ke ON. Untuk informasi selengkapnya, lihat Membatasi ukuran tabel sementara internal dalam memori.

contoh 1

Anda tahu bahwa tabel sementara Anda berkembang menjadi berukuran kumulatif 20 GiB. Anda ingin mengatur tabel sementara dalam memori ke 2 GiB dan mengembangkannya menjadi maksimum 20 GiB pada disk.

Atur temptable_max_ram ke 2,147,483,648 dan temptable_max_mmap ke 21,474,836,480. Nilai ini dihitung dalam byte.

Pengaturan parameter ini memastikan bahwa tabel sementara Anda dapat berkembang menjadi total kumulatif 22 GiB.

contoh 2

Ukuran instans Anda saat ini adalah 16xlarge atau lebih besar. Anda tidak mengetahui ukuran total tabel sementara yang mungkin Anda perlukan. Anda ingin dapat menggunakan hingga 4 GiB dalam memori dan hingga ukuran penyimpanan maksimum yang tersedia pada disk.

Atur temptable_max_ram ke 4,294,967,296 dan temptable_max_mmap ke 1,099,511,627,776. Nilai ini dihitung dalam byte.

Di sini, Anda mengatur temptable_max_mmap ke 1 TiB, yang lebih kecil dari penyimpanan lokal maksimum sebesar 1,2 TiB pada instans DB Aurora 16xlarge.

Pada ukuran instans yang lebih kecil, sesuaikan nilai temptable_max_mmap agar tidak mengisi penyimpanan lokal yang tersedia. Misalnya, instans 2xlarge hanya memiliki penyimpanan lokal yang tersedia sebesar 160 GiB. Karena itu, sebaiknya atur nilainya menjadi kurang dari 160 GiB. Untuk informasi selengkapnya tentang penyimpanan lokal yang tersedia untuk ukuran instans DB, lihat Batas penyimpanan sementara untuk Aurora MySQL.

Tabel sementara yang dibuat pengguna (eksplisit) pada instans DB pembaca

Anda dapat membuat tabel sementara eksplisit menggunakan kata kunci TEMPORARY dalam pernyataan CREATE TABLE. Tabel sementara eksplisit didukung pada instans DB penulis di klaster DB Aurora. Anda juga dapat menggunakan tabel sementara eksplisit pada instans DB pembaca, tetapi tabel tidak dapat menerapkan penggunaan mesin penyimpanan InnoDB.

Untuk mencegah timbulnya kesalahan saat membuat tabel sementara eksplisit pada instans DB pembaca Aurora MySQL, pastikan Anda menjalankan semua pernyataan CREATE TEMPORARY TABLE dengan salah satu atau kedua cara berikut:

• Jangan tentukan klausa ENGINE=InnoDB.

• Jangan atur mode SQL ke NO_ENGINE_SUBSTITUTION.

Kesalahan dan mitigasi pembuatan tabel sementara

Kesalahan yang Anda terima berbeda tergantung pada apakah Anda menggunakan pernyataan CREATE TEMPORARY TABLE biasa atau variasi CREATE TEMPORARY TABLE AS SELECT. Contoh berikut menunjukkan berbagai jenis kesalahan.

Perilaku tabel sementara ini hanya berlaku untuk instans hanya-baca. Contoh pertama ini mengonfirmasi jenis instans yang terhubung dengan sesi.

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+

Untuk pernyataan CREATE TEMPORARY TABLE biasa, pernyataan akan gagal saat mode SQL NO_ENGINE_SUBSTITUTION diaktifkan. Ketika NO_ENGINE_SUBSTITUTION dinonaktifkan (default), substitusi mesin yang sesuai dibuat, dan pembuatan tabel sementara berhasil.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql>  CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;

mysql> SHOW CREATE TABLE tt4\G
*************************** 1. row ***************************
       Table: tt4
Create Table: CREATE TEMPORARY TABLE `tt4` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Untuk pernyataan CREATE TEMPORARY TABLE AS SELECT, pernyataan akan gagal saat mode SQL NO_ENGINE_SUBSTITUTION diaktifkan. Ketika NO_ENGINE_SUBSTITUTION dinonaktifkan (default), substitusi mesin yang sesuai dibuat, dan pembuatan tabel sementara berhasil.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table                                             |
+-------+----------------------------------------------------------+
| tt3   | CREATE TEMPORARY TABLE `tt3` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)

Untuk informasi selengkapnya tentang aspek penyimpanan dan implikasi kinerja tabel sementara di Aurora MySQL versi 3, lihat posting blog TempTable Gunakan mesin penyimpanan di Amazon RDS for MySQL dan Amazon Aurora MySQL.