Memecahkan masalah kinerja kueri untuk database Aurora MySQL - Amazon Aurora
Memahami waktu yang dihabiskan oleh pertanyaanMeninjau pengaturan pengoptimal kueri

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

Memecahkan masalah kinerja kueri untuk database Aurora MySQL

MySQL menyediakan kontrol pengoptimal kueri melalui variabel sistem yang memengaruhi cara rencana kueri dievaluasi, pengoptimalan yang dapat dialihkan, petunjuk pengoptimal dan indeks, dan model biaya pengoptimal. Titik data ini dapat membantu tidak hanya saat membandingkan lingkungan MySQL yang berbeda, tetapi juga untuk membandingkan rencana eksekusi kueri sebelumnya dengan rencana eksekusi saat ini, dan untuk memahami eksekusi keseluruhan kueri MySQL kapan saja.

Kinerja kueri tergantung pada banyak faktor, termasuk rencana eksekusi, skema tabel dan ukuran, statistik, sumber daya, indeks, dan konfigurasi parameter. Penyetelan kueri memerlukan identifikasi kemacetan dan mengoptimalkan jalur eksekusi.

  • Temukan rencana eksekusi untuk kueri dan periksa apakah kueri menggunakan indeks yang sesuai. Anda dapat mengoptimalkan kueri Anda dengan menggunakan EXPLAIN dan meninjau detail setiap paket.

  • Aurora MySQL versi 3 (kompatibel dengan MySQL 8.0 Community Edition) menggunakan pernyataan. EXPLAIN ANALYZE EXPLAIN ANALYZEPernyataan ini adalah alat profil yang menunjukkan di mana MySQL menghabiskan waktu pada kueri Anda dan mengapa. DenganEXPLAIN ANALYZE, Aurora MySQL merencanakan, menyiapkan, dan menjalankan kueri sambil menghitung baris dan mengukur waktu yang dihabiskan di berbagai titik rencana eksekusi. Saat kueri selesai, EXPLAIN ANALYZE mencetak rencana dan pengukurannya alih-alih hasil kueri.

  • Perbarui statistik skema Anda dengan menggunakan ANALYZE pernyataan. Pengoptimal kueri terkadang dapat memilih rencana eksekusi yang buruk karena statistik yang sudah ketinggalan zaman. Hal ini dapat menyebabkan kinerja kueri yang buruk karena perkiraan kardinalitas yang tidak akurat dari tabel dan indeks. last_updateKolom tabel innodb_table_stats menunjukkan terakhir kali statistik skema Anda diperbarui, yang merupakan indikator “kebuntuan” yang baik.

  • Masalah lain dapat terjadi, seperti kemiringan distribusi data, yang tidak diperhitungkan untuk kardinalitas tabel. Untuk informasi selengkapnya, lihat Memperkirakan kompleksitas TABEL ANALISIS untuk tabel InnoDB dan statistik Histogram di MySQL dalam dokumentasi MySQL.

Memahami waktu yang dihabiskan oleh pertanyaan

Berikut ini adalah cara untuk menentukan waktu yang dihabiskan oleh kueri:

Profil

Secara default, pembuatan profil dinonaktifkan. Aktifkan pembuatan profil, lalu jalankan kueri lambat dan tinjau profilnya.

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. Identifikasi tahap di mana waktu paling banyak dihabiskan. Menurut status utas Umum dalam dokumentasi MySQL, membaca dan memproses baris untuk pernyataan seringkali merupakan status SELECT yang paling lama berjalan selama masa kueri yang diberikan. Anda dapat menggunakan EXPLAIN pernyataan untuk memahami bagaimana MySQL menjalankan kueri ini.

  2. Tinjau log kueri lambat rows_sent untuk mengevaluasi rows_examined dan memastikan bahwa beban kerja serupa di setiap lingkungan. Untuk informasi selengkapnya, lihat Logging untuk database MySQL Aurora.

  3. Jalankan perintah berikut untuk tabel yang merupakan bagian dari kueri yang diidentifikasi:

    SHOW TABLE STATUS\G;

  4. Tangkap output berikut sebelum dan sesudah menjalankan kueri di setiap lingkungan:

    SHOW GLOBAL STATUS;

  5. Jalankan perintah berikut di setiap lingkungan untuk melihat apakah ada kueri/sesi lain yang memengaruhi kinerja kueri sampel ini.

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    Terkadang, ketika sumber daya di server sibuk, itu berdampak pada setiap operasi lain di server, termasuk kueri. Anda juga dapat menangkap informasi secara berkala saat kueri dijalankan atau menyiapkan cron pekerjaan untuk menangkap informasi pada interval yang berguna.

Skema Performa

Skema Kinerja memberikan informasi yang berguna tentang kinerja runtime server, sementara memiliki dampak minimal pada kinerja itu. Ini berbeda dariinformation_schema, yang menyediakan informasi skema tentang instance DB. Untuk informasi selengkapnya, lihat Ikhtisar Skema Kinerja untuk Performance Insights di Aurora My SQL for MariaDB atau My SQL.

Jejak pengoptimal kueri

Untuk memahami mengapa rencana kueri tertentu dipilih untuk dieksekusi, Anda dapat mengatur optimizer_trace untuk mengakses pengoptimal kueri MySQL.

Jalankan jejak pengoptimal untuk menampilkan informasi ekstensif tentang semua jalur yang tersedia untuk pengoptimal dan pilihannya.

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Meninjau pengaturan pengoptimal kueri

Aurora MySQL versi 3 (kompatibel dengan MySQL 8.0 Community Edition) memiliki banyak perubahan terkait pengoptimalan dibandingkan dengan Aurora MySQL versi 2 (kompatibel dengan MySQL 5.7 Community Edition). Jika Anda memiliki beberapa nilai kustom untukoptimizer_switch, kami sarankan Anda meninjau perbedaan default dan menetapkan optimizer_switch nilai yang paling sesuai untuk beban kerja Anda. Kami juga menyarankan Anda menguji opsi yang tersedia untuk Aurora MySQL versi 3 untuk memeriksa kinerja kueri Anda.

catatan

Aurora MySQL versi 3 menggunakan nilai default komunitas 20 untuk parameter innodb_stats_persistent_sample_pages.

Anda dapat menggunakan perintah berikut untuk menunjukkan optimizer_switch nilai-nilai:

SELECT @@optimizer_switch\G;

Tabel berikut menunjukkan optimizer_switch nilai default untuk Aurora MySQL versi 2 dan 3.

Pengaturan Aurora MySQL versi 2 Aurora MySQL versi 3
batched_key_access off off
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join off on
hash_join_cost_based on –
hypergraph_optimizer – off
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – off
use_index_extensions on on
use_invisible_indexes – off

Untuk informasi selengkapnya, lihat Pengoptimalan yang dapat dialihkan (MySQL 5.7) dan pengoptimalan Switchable (MySQL 8.0) dalam dokumentasi MySQL.