Aurora MySQL versi 3 menangani tabel sementara secara berbeda dari versi Aurora MySQL sebelumnya. Perilaku baru ini diwarisi dari MySQL 8. 0 Edisi Komunitas. Ada 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 penyortiran agregasi, tabel turunan, atau ekspresi tabel umum [CTE]
Tabel sementara buatan pengguna [atau eksplisit] – Dibuat oleh mesin MySQL Aurora saat Anda menggunakan pernyataan
CREATE TEMPORARY TABLE
Ada pertimbangan tambahan untuk tabel sementara internal dan buatan pengguna pada instans DB pembaca Aurora. Kami membahas perubahan ini di bagian berikut
Mesin penyimpanan untuk tabel sementara internal [implisit].
Saat membuat set hasil antara, Aurora MySQL awalnya mencoba menulis ke tabel sementara dalam memori. Ini mungkin tidak berhasil, karena tipe data yang tidak kompatibel atau batas yang dikonfigurasi. Jika demikian, tabel sementara akan diubah menjadi tabel sementara pada disk daripada disimpan di memori. Informasi lebih lanjut tentang ini dapat ditemukan di Penggunaan Tabel Sementara Internal di MySQL dalam dokumentasi MySQL
Di Aurora MySQL versi 3, cara kerja tabel sementara internal berbeda dengan versi Aurora MySQL sebelumnya. Alih-alih memilih antara mesin penyimpanan InnoDB dan MyISAM untuk tabel sementara tersebut, sekarang Anda memilih antara mesin penyimpanan TempTable
dan InnoDB
Dengan mesin penyimpanan TempTable
_, Anda dapat membuat pilihan tambahan tentang cara menangani data tertentu. Data yang terpengaruh meluap ke kumpulan memori yang menampung semua tabel sementara internal untuk instans DB
Pilihan tersebut dapat memengaruhi performa untuk kueri yang menghasilkan volume data sementara yang tinggi, misalnya saat melakukan agregasi seperti GROUP BY
pada tabel besar
Jika beban kerja Anda menyertakan kueri yang menghasilkan tabel sementara internal, konfirmasikan kinerja aplikasi Anda dengan perubahan ini dengan menjalankan tolok ukur dan memantau metrik terkait kinerja
Dalam beberapa kasus, jumlah data sementara sesuai dengan kumpulan memori TempTable
atau hanya meluap kumpulan memori dengan jumlah kecil. Dalam kasus ini, sebaiknya gunakan setelan TempTable
untuk tabel sementara internal dan file yang dipetakan memori untuk menyimpan data berlebih. Pengaturan ini adalah default
Mesin penyimpanan TempTable
_ adalah 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. Standarnya 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 beberapa kasus saat Anda menggunakan mesin penyimpanan TempTable
, data sementara mungkin melebihi ukuran kumpulan memori. Jika demikian, Aurora MySQL menyimpan data luapan menggunakan mekanisme sekunder
Anda dapat mengatur parameter untuk menentukan apakah data meluap ke file sementara yang dipetakan memori atau ke tabel sementara internal InnoDB pada disk. Format data yang berbeda dan kriteria luapan dari mekanisme luapan ini dapat memengaruhi kinerja kueri. Mereka melakukannya dengan mempengaruhi jumlah data yang ditulis ke disk dan permintaan throughput penyimpanan disk
Aurora MySQL menyimpan data luapan secara berbeda tergantung pada beberapa pertimbangan. Ini adalah pilihan tujuan limpahan data Anda dan apakah kueri dijalankan pada instans DB penulis atau pembaca
Pada contoh penulis, data yang meluap ke tabel sementara internal InnoDB disimpan dalam volume klaster Aurora
Pada instans penulis, data yang melimpah ke file sementara yang dipetakan memori berada di penyimpanan lokal pada instans Aurora MySQL versi 3
Pada instance pembaca, data luapan selalu berada di file sementara yang dipetakan memori pada penyimpanan lokal. Itu karena instans hanya baca tidak dapat menyimpan data apa pun di volume klaster Aurora
Parameter konfigurasi yang terkait dengan tabel sementara internal berlaku berbeda untuk instance penulis dan pembaca di klaster Anda. Untuk contoh pembaca, Aurora MySQL selalu menggunakan mesin penyimpanan TempTable
dan nilai 1 untuk
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
3. Ukuran untuk 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
4 default ke 1 GiB, untuk instans penulis dan pembaca, terlepas dari ukuran memori instans DB. Anda dapat menyesuaikan nilai ini sama seperti cara Anda melakukannya pada instance penulis. Namun, Anda tidak dapat menentukan nilai nol untuk 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
4 pada instance pembacaMengurangi masalah kepenuhan untuk tabel sementara internal pada Replika Aurora
Untuk menghindari masalah batasan ukuran untuk tabel sementara, setel parameter
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
6 dan 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
4 ke nilai gabungan yang sesuai dengan persyaratan beban kerja AndaBerhati-hatilah saat menyetel nilai parameter
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
6. Menetapkan nilai terlalu tinggi akan mengurangi memori yang tersedia pada instans database, yang dapat menyebabkan kondisi kehabisan memori. Pantau rata-rata memori yang dapat dibebaskan pada instans DB. Kemudian tentukan nilai yang sesuai untuk 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
6 sehingga Anda masih memiliki sisa memori bebas dalam jumlah wajar pada instans. Untuk informasi lebih lanjut, lihatPenting juga untuk memantau ukuran penyimpanan lokal dan konsumsi ruang tabel sementara. Untuk informasi selengkapnya tentang memantau penyimpanan lokal pada instans, lihat artikel AWS Knowledge Center Apa yang disimpan di penyimpanan lokal yang kompatibel dengan MySQL Aurora, dan bagaimana cara memecahkan masalah penyimpanan lokal?
Contoh 1
Anda tahu bahwa tabel sementara Anda berkembang menjadi ukuran kumulatif 20 GiB. Anda ingin menyetel tabel sementara dalam memori menjadi 2 GiB dan meningkatkan hingga maksimal 20 GiB pada disk
Tetapkan
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
6 ke 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>
CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 [HY000]: InnoDB is in read only mode.
1 dan 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
4 ke 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>
CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 [HY000]: InnoDB is in read only mode.
3. Nilai-nilai ini dalam bytePengaturan parameter ini memastikan bahwa tabel sementara Anda dapat bertambah hingga total kumulatif 22 GiB
Contoh 2
Ukuran instans Anda saat ini adalah 16xbesar atau lebih besar. Anda tidak mengetahui ukuran total tabel sementara yang mungkin Anda perlukan. Anda ingin dapat menggunakan memori hingga 4 GiB dan ukuran penyimpanan maksimum yang tersedia pada disk
Tetapkan
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
6 ke 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>
CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 [HY000]: InnoDB is in read only mode.
5 dan 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
4 ke 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>
CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 [HY000]: InnoDB is in read only mode.
7. Nilai-nilai ini dalam byteDi sini Anda menyetel
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
_4 ke 1 TiB, yang kurang dari penyimpanan lokal maksimum 1. 2 TiB pada instans Aurora DB 16xbesarPada ukuran instans yang lebih kecil, sesuaikan nilai
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
4 agar tidak memenuhi penyimpanan lokal yang tersedia. Misalnya, instans 2xlarge hanya memiliki penyimpanan lokal sebesar 160 GiB. Oleh karena itu, kami merekomendasikan pengaturan nilai kurang dari 160 GiB. Untuk informasi selengkapnya tentang penyimpanan lokal yang tersedia untuk ukuran instans DB, lihatTabel sementara [eksplisit] buatan pengguna pada instans DB pembaca
Anda dapat membuat tabel sementara eksplisit menggunakan kata kunci CREATE TEMPORARY TABLE
0 dalam pernyataan CREATE TEMPORARY TABLE
1 Anda. Tabel sementara eksplisit didukung pada instans DB penulis di klaster Aurora DB. Anda juga dapat menggunakan tabel sementara eksplisit pada instans DB pembaca, tetapi tabel tersebut tidak dapat menerapkan penggunaan mesin penyimpanan InnoDB
Untuk menghindari kesalahan saat membuat tabel sementara eksplisit pada instans DB pembaca MySQL Aurora, pastikan Anda menjalankan semua pernyataan CREATE TEMPORARY TABLE
dengan salah satu atau kedua cara berikut
Jangan tentukan klausa
CREATE TEMPORARY TABLE
3Jangan setel mode SQL ke
CREATE TEMPORARY TABLE
4
Kesalahan dan mitigasi pembuatan tabel sementara
Kesalahan yang Anda terima berbeda tergantung pada apakah Anda menggunakan pernyataan CREATE TEMPORARY TABLE
polos atau variasi CREATE TEMPORARY TABLE
6. Contoh berikut menunjukkan berbagai jenis kesalahan
Perilaku tabel sementara ini hanya berlaku untuk instance read-only. Contoh pertama ini mengonfirmasi jenis instance yang terhubung dengan sesi
mysql>
select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
| 1 |
+--------------------+
Untuk pernyataan CREATE TEMPORARY TABLE
_ biasa, pernyataan gagal saat mode SQL CREATE TEMPORARY TABLE
4 diaktifkan. Ketika CREATE TEMPORARY TABLE
_4 dimatikan [default], penggantian mesin yang sesuai dilakukan, 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
_6, pernyataan gagal apakah mode SQL CREATE TEMPORARY TABLE
4 diaktifkan atau tidak. MySQL Community Edition tidak mendukung penggantian mesin penyimpanan dengan pernyataan TempTable
2 atau CREATE TEMPORARY TABLE
6. Untuk pernyataan tersebut, hapus klausa CREATE TEMPORARY TABLE
3 dari kode SQL Anda
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>
CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 [HY000]: InnoDB is in read only mode.
_Untuk informasi selengkapnya tentang aspek penyimpanan dan implikasi kinerja tabel sementara di Aurora MySQL versi 3, lihat postingan blog Menggunakan mesin penyimpanan TempTable di Amazon RDS untuk MySQL dan Amazon Aurora MySQL