Rekomendasi untuk SQL fitur Saya di Aurora My SQL - Amazon Aurora

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Rekomendasi untuk SQL fitur Saya di Aurora My SQL

Fitur berikut tersedia dalam kompatibilitas Aurora My SQL for MySQL. Namun, fitur-fitur ini memiliki masalah performa, skalabilitas, stabilitas, atau kompatibilitas di lingkungan Aurora. Oleh karena itu, kami menyarankan Anda mengikuti pedoman tertentu dalam penggunaan fitur-fitur ini. Misalnya, kami sarankan Anda tidak menggunakan fitur tertentu untuk deployment Aurora produksi.

Menggunakan replikasi multithreaded di Aurora My SQL

Dengan replikasi log biner multithreaded, sebuah SQL thread membaca peristiwa dari log relai dan mengantrekannya agar thread pekerja dapat diterapkan. SQL Thread SQL pekerja dikelola oleh utas koordinator. Peristiwa log biner diterapkan secara paralel jika memungkinkan.

Replikasi multithreaded didukung di Aurora My versi SQL 3, dan di Aurora My versi 2.12.1 dan lebih tinggi. SQL

Untuk SQL versi Aurora My yang lebih rendah dari 3,04, Aurora menggunakan replikasi single-threaded secara default ketika cluster Aurora SQL My DB digunakan sebagai replika baca untuk replikasi log biner.

Versi sebelumnya dari Aurora My SQL versi 2 mewarisi beberapa masalah mengenai replikasi multithreaded dari My Community Edition. SQL Untuk versi tersebut, kami menyarankan Anda untuk tidak menggunakan replikasi multithreaded dalam produksi.

Jika Anda menggunakan replikasi multithreaded, kami sarankan Anda mengujinya secara menyeluruh.

Untuk informasi selengkapnya tentang replikasi di Amazon Aurora, lihat Replikasi dengan Amazon Aurora. Untuk informasi lebih lanjut tentang replikasi multithreaded di Aurora My, lihat. SQL Replikasi log biner multithreaded

Memohon AWS Lambda fungsi menggunakan SQL fungsi saya asli

Sebaiknya gunakan SQL fungsi Saya asli lambda_sync dan lambda_async untuk memanggil fungsi Lambda.

Jika Anda menggunakan prosedur mysql.lambda_async yang sudah dihentikan, kami sarankan agar Anda menggabungkan panggilan ke prosedur mysql.lambda_async dalam prosedur tersimpan. Anda dapat memanggil prosedur tersimpan ini dari berbagai sumber, seperti pemicu atau kode klien. Pendekatan ini dapat membantu menghindari masalah ketidakcocokan impedansi dan memudahkan pemrogram basis data Anda untuk menginvokasi fungsi Lambda.

Untuk informasi selengkapnya tentang menginvokasi fungsi Lambda dari Amazon Aurora, lihat Memanggil fungsi Lambda dari cluster Amazon Aurora My DB SQL.

Menghindari transaksi XA dengan Amazon Aurora My SQL

Kami menyarankan Anda untuk tidak menggunakan transaksi eXtended Architecture (XA) dengan Aurora SQL My, karena mereka dapat menyebabkan waktu pemulihan yang lama jika XA berada dalam keadaan. PREPARED Jika Anda harus menggunakan transaksi XA dengan Aurora SQL My, ikuti praktik terbaik berikut:

  • Jangan tinggalkan transaksi XA terbuka pada status PREPARED.

  • Pertahankan transaksi XA sekecil mungkin.

Untuk informasi selengkapnya tentang penggunaan transaksi XA dengan MySQL, lihat transaksi XA di dokumentasi SayaSQL.

Menyimpan kunci asing dihidupkan selama DML pernyataan

Kami sangat menyarankan agar Anda tidak menjalankan pernyataan data definition language (DDL) ketika foreign_key_checks variabel disetel ke 0 (off).

Jika Anda perlu menyisipkan atau memperbarui baris yang memerlukan pelanggaran sementara terhadap kunci asing, ikuti langkah-langkah berikut:

  1. Atur foreign_key_checks ke 0.

  2. Buat bahasa manipulasi data Anda (DML) berubah.

  3. Pastikan bahwa perubahan yang telah Anda selesaikan tidak melanggar batasan kunci asing.

  4. Atur foreign_key_checks ke 1 (aktif).

Selain itu, ikuti praktik terbaik lainnya untuk batasan kunci asing:

  • Pastikan bahwa aplikasi klien tidak mengatur variabel foreign_key_checks ke 0 sebagai bagian dari variabel init_connect.

  • Jika pemulihan dari cadangan logis seperti mysqldump gagal atau tidak selesai, pastikan bahwa foreign_key_checks diatur ke 1 sebelum memulai operasi lain pada sesi yang sama. Cadangan logis mengatur foreign_key_checks ke 0 saat dimulai.

Mengonfigurasi seberapa sering buffer log di-flush

Dalam My SQL Community Edition, untuk membuat transaksi tahan lama, buffer log InnoDB harus dibilas ke penyimpanan yang tahan lama. Anda menggunakan parameter innodb_flush_log_at_trx_commit untuk mengonfigurasi seberapa sering buffer log di-flush ke disk.

Saat Anda mengatur parameter innodb_flush_log_at_trx_commit ke nilai default 1, buffer log akan di-flush pada setiap commit transaksi. Pengaturan ini membantu menjaga basis data tetap ACIDsesuai. Kami menyarankan Anda untuk tetap mempertahankan pengaturan default 1.

Mengubah innodb_flush_log_at_trx_commit ke nilai nondefault dapat membantu mengurangi latensi bahasa manipulasi data (DML), tetapi mengorbankan daya tahan catatan log. Kurangnya daya tahan ini membuat database ACID tidak sesuai. Kami menyarankan agar database Anda ACID patuh untuk menghindari risiko kehilangan data jika server restart. Untuk informasi selengkapnya tentang parameter ini, lihat innodb_flush_log_at_trx_commit dalam dokumentasi Saya. SQL

Di Aurora MySQL, pemrosesan log ulang diturunkan ke lapisan penyimpanan, jadi tidak ada pembilasan ke file log yang terjadi pada instance DB. Ketika sebuah penulisan dikeluarkan, log redo akan dikirim dari instans DB penulis langsung ke volume klaster Aurora. Satu-satunya penulisan yang melintasi jaringan adalah catatan log redo. Tidak ada halaman yang akan ditulis dari tingkat basis data.

Secara default, setiap thread yang melakukan transaksi menunggu konfirmasi dari volume cluster Aurora. Konfirmasi ini menunjukkan bahwa catatan ini dan semua catatan log redo sebelumnya telah ditulis dan mencapai kuorum. Mempersistensi catatan log dan mencapai kuorum akan membuat transaksi durabel, baik melalui commit otomatis maupun commit eksplisit. Untuk informasi selengkapnya tentang arsitektur penyimpanan Aurora, lihat Amazon Aurora storage demystified.

Aurora My SQL tidak menyiram log ke file data seperti yang dilakukan Edisi SQL Komunitas Saya. Namun, Anda dapat menggunakan parameter innodb_flush_log_at_trx_commit untuk melonggarkan batasan durabilitas saat menulis catatan log redo ke volume klaster Aurora.

Untuk Aurora SQL Versi saya 2:

  • innodb_flush_log_at_trx_commit= 0 atau 2 — Database tidak menunggu konfirmasi bahwa catatan log ulang ditulis ke volume cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 — Database menunggu konfirmasi bahwa catatan redo log ditulis ke volume cluster Aurora.

Untuk Aurora SQL Versi saya 3:

  • innodb_flush_log_at_trx_commit= 0 — Database tidak menunggu konfirmasi bahwa catatan redo log ditulis ke volume cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 atau 2 — Database menunggu konfirmasi bahwa catatan log ulang ditulis ke volume cluster Aurora.

Oleh karena itu, untuk mendapatkan perilaku nondefault yang sama di Aurora SQL My versi 3 yang Anda lakukan dengan nilai yang disetel ke 0 atau 2 di Aurora SQL My versi 2, atur parameternya ke 0.

Meskipun pengaturan ini dapat menurunkan DML latensi ke klien, mereka juga dapat mengakibatkan kehilangan data jika terjadi failover atau restart. Oleh karena itu, sebaiknya pertahankan parameter innodb_flush_log_at_trx_commit yang diatur ke nilai default 1.

Meskipun kehilangan data dapat terjadi di My SQL Community Edition dan Aurora MySQL, perilaku berbeda di setiap database karena arsitekturnya yang berbeda. Perbedaan arsitektur ini dapat menyebabkan berbagai tingkat kehilangan data. Untuk memastikan bahwa database Anda ACID sesuai, selalu atur innodb_flush_log_at_trx_commit ke 1.

catatan

Di Aurora My SQL versi 3, sebelum Anda dapat mengubah innodb_flush_log_at_trx_commit ke nilai selain 1, Anda harus terlebih dahulu mengubah nilai innodb_trx_commit_allow_data_loss menjadi 1. Dengan melakukannya, berarti Anda memahami adanya risiko kehilangan data.

Meminimalkan dan memecahkan masalah Aurora Kebuntuan saya SQL

Pengguna yang menjalankan beban kerja yang secara rutin mengalami pelanggaran batasan pada indeks sekunder unik atau kunci asing, saat memodifikasi catatan pada halaman data yang sama secara konkuren, mungkin akan lebih sering mengalami deadlock dan kehabisan waktu tunggu kunci. Kebuntuan dan batas waktu ini disebabkan oleh perbaikan bug My SQL Community Edition.

Perbaikan ini disertakan dalam My SQL Community Edition versi 5.7.26 dan lebih tinggi, dan di-backport ke Aurora SQL My versi 2.10.3 dan yang lebih tinggi. Perbaikan ini diperlukan untuk menegakkan serialisasi, dengan menerapkan penguncian tambahan untuk jenis operasi bahasa manipulasi data (DML) ini, pada perubahan yang dibuat pada catatan dalam tabel InnoDB. Masalah ini terungkap sebagai bagian dari penyelidikan masalah kebuntuan yang diperkenalkan oleh perbaikan bug My SQL Community Edition sebelumnya.

Perbaikan ini mengubah penanganan internal untuk rollback sebagian pembaruan tuple (baris) di mesin penyimpanan InnoDB. Operasi yang menghasilkan pelanggaran batasan pada kunci asing atau indeks sekunder unik menyebabkan rollback sebagian. Ini termasuk, tetapi tidak terbatas pada, pernyataan INSERT...ON DUPLICATE KEY UPDATE, REPLACE INTO, dan INSERT IGNORE konkuren (upsert).

Dalam konteks ini, rollback sebagian tidak mengacu pada rollback transaksi tingkat aplikasi, melainkan rollback InnoDB internal terhadap perubahan ke indeks berklaster ketika pelanggaran batasan ditemui. Misalnya, nilai kunci duplikat ditemukan selama operasi upsert.

Dalam operasi penyisipan normal, InnoDB secara atomis membuat entri indeks berklaster dan sekunder untuk setiap indeks. Jika InnoDB mendeteksi nilai duplikat pada indeks sekunder unik selama operasi upsert, entri yang disisipkan dalam indeks berklaster harus dikembalikan (rollback sebagian), dan pembaruan kemudian harus diterapkan ke baris duplikat yang ada. Selama langkah rollback sebagian internal ini, InnoDB harus mengunci setiap catatan yang dilihat sebagai bagian dari operasi. Perbaikan ini memastikan kemampuan serialisasi transaksi dengan menerapkan penguncian tambahan setelah rollback sebagian.

Meminimalkan deadlock InnoDB

Anda dapat mengambil pendekatan berikut untuk mengurangi frekuensi deadlock dalam instans basis data Anda. Contoh lainnya dapat ditemukan di SQLdokumentasi Saya.

  1. Untuk mengurangi kemungkinan deadlock, lakukan transaksi segera setelah melakukan serangkaian perubahan terkait. Anda dapat melakukannya dengan memecah transaksi besar (beberapa pembaruan baris di antara commit) menjadi lebih kecil. Jika Anda memasukkan baris secara batch, cobalah untuk mengurangi ukuran penyisipan batch, terutama saat menggunakan operasi upsert yang disebutkan sebelumnya.

    Untuk mengurangi jumlah kemungkinan rollback sebagian, Anda dapat mencoba beberapa pendekatan berikut:

    1. Ganti operasi penyisipan batch dengan memasukkan satu baris pada satu waktu. Hal ini dapat mengurangi jumlah waktu saat kunci ditahan oleh transaksi yang mungkin memiliki konflik.

    2. Alih-alih menggunakanREPLACE INTO, tulis ulang SQL pernyataan sebagai transaksi multistatement seperti berikut:

      BEGIN; DELETE conflicting rows; INSERT new rows; COMMIT;
    3. Alih-alih menggunakanINSERT...ON DUPLICATE KEY UPDATE, tulis ulang SQL pernyataan sebagai transaksi multistatement seperti berikut:

      BEGIN; SELECT rows that conflict on secondary indexes; UPDATE conflicting rows; INSERT new rows; COMMIT;
  2. Hindari transaksi yang berjalan lama, aktif atau idle, yang mungkin menahan kunci. Ini termasuk sesi SQL klien saya interaktif yang mungkin terbuka untuk waktu yang lama dengan transaksi tanpa komitmen. Saat mengoptimalkan ukuran transaksi atau ukuran batch, dampaknya dapat bervariasi tergantung pada sejumlah faktor seperti konkurensi, jumlah duplikat, dan struktur tabel. Setiap perubahan harus diterapkan dan diuji berdasarkan beban kerja Anda.

  3. Dalam beberapa situasi, deadlock dapat terjadi ketika dua transaksi mencoba mengakses set data yang sama, baik dalam satu maupun beberapa tabel, dalam urutan yang berbeda-beda. Untuk mencegah hal ini, Anda dapat memodifikasi transaksi untuk mengakses data dalam urutan yang sama, sehingga menserialisasi akses. Misalnya, buat antrean transaksi yang akan diselesaikan. Pendekatan ini dapat membantu menghindari deadlock ketika beberapa transaksi terjadi secara bersamaan.

  4. Menambahkan indeks yang dipilih dengan cermat ke tabel Anda dapat meningkatkan selektivitas dan mengurangi kebutuhan untuk mengakses baris, sehingga mengurangi penguncian.

  5. Jika Anda mengalami penguncian celah, Anda dapat memodifikasi tingkat isolasi transaksi menjadi READ COMMITTED untuk sesi atau transaksi agar mencegahnya. Untuk informasi selengkapnya tentang tingkat isolasi InnoDB dan perilakunya, lihat Tingkat isolasi transaksi dalam dokumentasi SayaSQL.

catatan

Meskipun Anda dapat mengambil tindakan pencegahan untuk mengurangi kemungkinan deadlock terjadi, deadlock adalah perilaku basis data yang sudah diperkirakan dan tetap dapat terjadi. Aplikasi harus memiliki logika yang diperlukan untuk menangani deadlock ketika terjadi. Misalnya, terapkan logika percobaan ulang dan backing-off dalam aplikasi. Yang terbaik adalah mengatasi akar penyebab masalahnya, tetapi jika deadlock memang terjadi, aplikasi memiliki opsi untuk menunggu dan mencoba lagi.

Memantau deadlock InnoDB

Kebuntuan dapat terjadi di My SQL ketika transaksi aplikasi mencoba mengambil kunci tingkat meja dan tingkat baris dengan cara yang menghasilkan penantian melingkar. Deadlock InnoDB yang sesekali tidak selalu menjadi masalah karena mesin penyimpanan InnoDB mendeteksi kondisi ini dengan segera dan melakukan rollback terhadap salah satu transaksi secara otomatis. Jika Anda sering mengalami deadlock, kami sarankan untuk meninjau dan memodifikasi aplikasi Anda untuk mengurangi masalah performa dan menghindari deadlock. Ketika deteksi deadlock diaktifkan (default), InnoDB secara otomatis mendeteksi deadlock transaksi dan melakukan rollback transaksi untuk mengatasi deadlock. InnoDB mencoba memilih transaksi kecil untuk di-rollback. Ukuran transaksi akan ditentukan berdasarkan jumlah baris yang disisipkan, diperbarui, atau dihapus.