ApsaraDB RDS:Instans RDS for MySQL terkunci oleh file temporary

Gejala

Penyebab

Kueri yang melakukan pengurutan, pengelompokan, atau join tabel dapat membuat file tabel temporary yang menghabiskan ruang disk instans. binlog cache dari transaksi besar yang belum dikomit juga dapat menyebabkan masalah ini. Untuk mencegah kehilangan data, ApsaraDB RDS secara otomatis mengunci instans tersebut. Selama terkunci, semua operasi tulis diblokir.

Penyelesaian

Dalam keadaan darurat, tingkatkan kapasitas penyimpanan instans Anda. Instans akan dibuka kuncinya sekitar lima menit setelah penyimpanan diperluas. Untuk informasi lebih lanjut, lihat Change Configuration.

Jika Anda tidak dapat meningkatkan kapasitas penyimpanan, gunakan metode sesuai versi mesin utama instans Anda:

• MySQL 5.7 atau lebih lama: Restart Instance.

• MySQL 8.0: Saat instans terkunci, semua session pengguna secara otomatis dihentikan. Setelah session berakhir, transaksi mulai di-rollback. Waktu yang dibutuhkan untuk rollback bergantung pada kueri yang sedang berjalan. Ruang penyimpanan instans akan dilepaskan setelah transaksi sepenuhnya di-rollback.

  Jika instans tidak dibuka kuncinya secara otomatis setelah waktu yang lama, ikuti langkah-langkah berikut:

  1. Hubungkan ke instans menggunakan Data Management (DMS).

  2. Jalankan pernyataan SQL berikut untuk melihat session database:

     show processlist

  3. Pada hasilnya, klik kolom State untuk mengurutkan session. Cari session dengan status seperti 'Copy to tmp table' atau 'Sending data', lalu catat ID-nya. Perintah ini mengembalikan daftar proses dengan kolom Id, User, Host, db, Command, Time, dan State. Gunakan kolom Command dan State untuk menemukan session bermasalah tersebut.

  4. Jalankan pernyataan SQL berikut untuk menghentikan session:

     kill [$ID];

     Catatan

     Pada pernyataan tersebut, [$ID] adalah ID session yang telah Anda catat pada langkah sebelumnya. Penting: Sebelum menghentikan session, pastikan tindakan ini tidak memengaruhi layanan Anda.

Pencegahan

Setelah instans dibuka kuncinya, ikuti langkah-langkah berikut untuk mencegah masalah ini terulang:

• Konfigurasikan ekspansi penyimpanan otomatis. Fitur ini secara otomatis meningkatkan kapasitas penyimpanan instans saat ruang penyimpanannya hampir habis. Untuk informasi lebih lanjut, lihat Configure automatic storage expansion.

• Optimalkan kueri SQL yang membuat file temporary besar. Hindari penggunaan operasi ORDER BY dan GROUP BY secara berlebihan. Anda dapat meningkatkan nilai parameter tmp_table_size dan max_heap_table_size. Namun, peningkatan nilai-nilai ini hanya untuk mengurangi penggunaan disk tidak disarankan karena memori merupakan sumber daya yang lebih berharga daripada ruang disk. Jalankan perintah EXPLAIN pada pernyataan SQL untuk memeriksa apakah kueri tersebut menggunakan tabel temporary internal. Jika bidang Extra pada output berisi Using temporary, berarti kueri tersebut menggunakan tabel temporary internal.

  explain select * from alarm group by created_on order by default;

  Output berikut adalah contohnya. Pada hasilnya, nilai type sebesar ALL menunjukkan pemindaian tabel penuh. Kolom Extra berisi Using temporary dan Using filesort, yang menunjukkan bahwa kueri tersebut juga melakukan operasi pengurutan berbasis file.

• Untuk mengelola binlog cache, kurangi transaksi besar, terutama beberapa transaksi besar yang berjalan secara konkuren melalui koneksi berbeda. Jika transaksi besar sering terjadi, Anda dapat meningkatkan nilai parameter binlog_cache_size. Namun, kami tidak menyarankan menyesuaikan parameter ini hanya untuk menghemat ruang disk. Sebagai gantinya, gunakan koneksi singkat untuk menjalankan transaksi besar guna mengurangi overhead ruang temporary.

• Pantau penggunaan disk Anda dan bersihkan data atau terapkan sharding data. Pertahankan penggunaan disk di bawah 80%.

Informasi tambahan

Jika Anda tidak dapat segera membersihkan file temporary untuk membuka kunci instans, Anda dapat membebaskan ruang disk dengan membersihkan jenis file lainnya. Untuk informasi lebih lanjut, lihat topik-topik berikut:

• What do I do if an instance is in the Locked state?

• Resolve full disk space issues caused by data files

• Resolve full disk space issues caused by binlog files

• Resolve full disk space issues caused by system files

FAQ

• T: Apa yang harus saya lakukan jika tombol Restart Instance tidak tersedia di halaman Basic Information?

  J: Anda dapat memicu restart dengan mengikuti langkah-langkah berikut:

  1. Masuk ke Konsol ApsaraDB RDS dan buka halaman Instances. Di bilah navigasi atas, pilih wilayah tempat instans RDS berada. Lalu, temukan instans RDS tersebut dan klik ID instansnya.

  2. Di panel navigasi kiri, klik Parameters.

  3. Di tab Editable Parameters, temukan dan ubah parameter yang memiliki nilai Yes pada kolom Restart Required.

• T: Bagaimana cara menemukan pernyataan SQL yang menghasilkan banyak file temporary?

  J: Untuk mengidentifikasi pernyataan SQL yang menghasilkan file temporary berlebihan, ikuti langkah-langkah berikut:

  1. Aktifkan fitur SQL Explorer and Audit.

  2. Tunggu hingga masalah terjadi kembali. Kemudian, gunakan informasi pemantauan untuk memantau pembuatan file temporary dan menganalisis data yang ditangkap oleh SQL Explorer and Audit.

  3. Pada log eksekusi dari SQL Explorer and Audit, filter pernyataan SQL yang terkait dengan pembuatan file temporary.