Mendapatkan insight performa kueri

Grafik eksekusi untuk kueri adalah representasi visual dari langkah-langkah yang dilakukan BigQuery untuk menjalankan kueri. Dokumen ini menjelaskan cara menggunakan grafik eksekusi kueri untuk mendiagnosis masalah performa kueri dan melihat insight performa kueri.

BigQuery menawarkan performa kueri yang kuat, tetapi juga merupakan sistem terdistribusi yang kompleks dengan banyak faktor internal dan eksternal yang dapat memengaruhi kecepatan kueri. Sifat deklaratif SQL juga dapat menyembunyikan kompleksitas eksekusi kueri. Artinya, saat kueri Anda berjalan lebih lambat dari yang diharapkan, atau lebih lambat dari sebelumnya, memahami apa yang terjadi bisa menjadi tantangan.

Grafik eksekusi kueri menyediakan antarmuka grafis dinamis untuk memeriksa paket kueri dan detail performa kueri. Anda dapat meninjau grafik eksekusi kueri untuk kueri yang sedang berjalan atau telah selesai.

Anda juga dapat menggunakan grafik eksekusi kueri untuk mendapatkan insight performa kueri. Insight performa memberikan saran terbaik untuk membantu Anda meningkatkan performa kueri. Karena performa kueri memiliki banyak aspek, insight performa mungkin hanya memberikan gambaran sebagian dari performa kueri secara keseluruhan.

Izin yang diperlukan

Untuk menggunakan grafik eksekusi kueri, Anda harus memiliki izin berikut:

  • bigquery.jobs.get
  • bigquery.jobs.listAll

Izin ini tersedia melalui peran Identity and Access Management (IAM) bawaan BigQuery berikut:

  • roles/bigquery.admin
  • roles/bigquery.resourceAdmin
  • roles/bigquery.resourceEditor
  • roles/bigquery.resourceViewer

Struktur grafik eksekusi

Grafik eksekusi kueri memberikan tampilan grafis dari paket kueri di konsol. Setiap kotak mewakili tahap dalam rencana kueri seperti berikut:

  • Input: Membaca data dari tabel atau memilih kolom tertentu
  • Gabung: Menggabungkan data dari dua tabel berdasarkan kondisi JOIN
  • Agregasi: Melakukan penghitungan seperti SUM
  • Urutkan: Mengurutkan hasil

Tahap terdiri dari langkah-langkah yang menjelaskan operasi individual yang dijalankan oleh setiap pekerja dalam satu tahap. Anda dapat mengklik tahap untuk membukanya dan melihat langkah-langkahnya. Tahapan juga mencakup informasi pengaturan waktu relatif dan absolut. Nama tahap merangkum langkah-langkah yang dilakukan. Misalnya, tahap dengan join dalam namanya berarti bahwa langkah utama dalam tahap tersebut adalah operasi JOIN. Nama tahapan yang memiliki + di akhir berarti tahapan tersebut melakukan langkah-langkah penting tambahan. Misalnya, tahap dengan JOIN+ dalam namanya berarti bahwa tahap tersebut melakukan operasi gabungan dan langkah-langkah penting lainnya.

Garis yang menghubungkan tahapan mewakili pertukaran data perantara antar-tahapan. BigQuery menyimpan data perantara dalam memori pengacakan saat tahap sedang dieksekusi. Angka di tepi menunjukkan perkiraan jumlah baris yang dipertukarkan antar-tahapan. Kuota memori pengacakan berkorelasi dengan jumlah slot yang dialokasikan ke akun. Jika kuota shuffle terlampaui, memori shuffle dapat meluap ke disk dan menyebabkan performa kueri menurun secara drastis.

Melihat insight performa kueri

Konsol

Ikuti langkah-langkah berikut untuk melihat insight performa kueri:

  1. Buka halaman BigQuery di konsol Google Cloud .

    Buka halaman BigQuery

  2. Di Editor, klik Personal History atau Project History.

  3. Dalam daftar tugas, identifikasi tugas kueri yang Anda minati. Klik Tindakan, lalu pilih Buka kueri di editor.

  4. Pilih tab Execution Graph untuk melihat representasi grafis setiap tahap kueri:

    Paket kueri grafis dalam grafik eksekusi.

    Untuk menentukan apakah tahap kueri memiliki insight performa, lihat ikon yang ditampilkan. Tahapan yang memiliki ikon informasi memiliki insight performa. Tahapan yang memiliki ikon centang tidak.

  5. Klik tahap untuk membuka panel detail tahap, tempat Anda dapat melihat informasi berikut:

    Detail tahap kueri.

  6. Opsional: Jika Anda sedang memeriksa kueri yang sedang berjalan, klik Sinkronkan untuk memperbarui grafik eksekusi sehingga mencerminkan status kueri saat ini.

    Menyinkronkan grafik ke kueri yang sedang berjalan.

  7. Opsional: Untuk menandai tahap teratas berdasarkan durasi tahap pada grafik, klik Tandai tahap teratas berdasarkan durasi.

    Menampilkan tahap teratas menurut durasi.

  8. Opsional: Untuk menandai tahap teratas berdasarkan waktu slot yang digunakan pada grafik, klik Highlight tahap teratas berdasarkan pemrosesan.

    Menampilkan tahap teratas menurut pemrosesan.

  9. Opsional: Untuk menyertakan tahap redistribusi pengacakan pada grafik, klik Tampilkan tahap redistribusi pengacakan.

    Menampilkan tahap teratas menurut pemrosesan.

    Gunakan opsi ini untuk menampilkan tahap repartisi dan penggabungan yang disembunyikan dalam grafik eksekusi default.

    Tahapan partisi ulang dan penggabungan diperkenalkan saat kueri berjalan, dan digunakan untuk meningkatkan distribusi data di seluruh pekerja yang memproses kueri. Karena tahap ini tidak terkait dengan teks kueri Anda, tahap ini disembunyikan untuk menyederhanakan paket kueri yang ditampilkan.

Untuk kueri apa pun yang memiliki masalah regresi performa, insight performa juga ditampilkan di tab Informasi Tugas untuk kueri tersebut:

Tab informasi pekerjaan.

SQL

  1. Di Google Cloud konsol, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE slot_contention OR insufficient_shuffle_quota
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage IS NOT NULL
  );

  3. Klik Run.

Untuk mengetahui informasi selengkapnya tentang cara menjalankan kueri, lihat artikel Menjalankan kueri interaktif.

API

Anda bisa mendapatkan insight performa kueri dalam format non-grafis dengan memanggil metode API jobs.list dan memeriksa informasi JobStatistics2 yang ditampilkan.

Menafsirkan insight performa kueri

Gunakan bagian ini untuk mempelajari lebih lanjut arti insight performa dan cara menindaklanjutinya.

Insight performa ditujukan untuk dua audiens:

  • Analis: Anda menjalankan kueri dalam project. Anda tertarik untuk mengetahui mengapa kueri yang pernah Anda jalankan sebelumnya berjalan lebih lambat secara tidak terduga, dan untuk mendapatkan tips tentang cara meningkatkan performa kueri. Anda memiliki izin yang dijelaskan dalam Izin yang diperlukan.

  • Administrator data lake atau data warehouse: Anda mengelola resource dan reservasi BigQuery organisasi Anda. Anda memiliki izin yang terkait dengan peran Admin BigQuery.

Setiap bagian berikut memberikan panduan tentang tindakan yang dapat Anda lakukan untuk mengatasi insight performa yang Anda terima, berdasarkan peran yang Anda miliki.

Persaingan slot

Saat Anda menjalankan kueri, BigQuery akan mencoba membagi pekerjaan yang diperlukan oleh kueri Anda menjadi tugas. Tugas adalah satu irisan data yang dimasukkan ke dan dikeluarkan dari tahap. Satu slot mengambil tugas dan mengeksekusi irisan data tersebut untuk tahap. Idealnya, slot BigQuery menjalankan tugas ini secara paralel untuk mencapai performa tinggi. Persaingan slot terjadi saat kueri Anda memiliki banyak tugas yang siap untuk mulai dieksekusi, tetapi BigQuery tidak dapat memperoleh slot yang tersedia dalam jumlah yang cukup untuk mengeksekusinya.

Yang harus dilakukan jika Anda seorang analis

Kurangi data yang Anda proses dalam kueri dengan mengikuti panduan di Mengurangi data yang diproses dalam kueri.

Hal yang harus dilakukan jika Anda adalah administrator

Tingkatkan ketersediaan slot atau kurangi penggunaan slot dengan melakukan tindakan berikut:

  • Jika Anda menggunakan harga sesuai permintaan BigQuery, kueri Anda akan menggunakan kumpulan slot bersama. Sebaiknya beralih ke harga analisis berbasis kapasitas dengan membeli reservasi. Dengan reservasi, Anda dapat memesan slot khusus untuk kueri organisasi Anda.

  • Jika Anda menggunakan reservasi BigQuery, pastikan ada cukup slot dalam reservasi yang ditetapkan ke project yang menjalankan kueri. Reservasi mungkin tidak memiliki cukup slot dalam skenario berikut:

    • Ada tugas lain yang menggunakan slot reservasi. Anda dapat menggunakan Diagram Resource Admin untuk melihat cara organisasi Anda menggunakan reservasi.
    • Reservasi tidak memiliki slot yang ditetapkan yang cukup untuk menjalankan kueri dengan cukup cepat. Anda dapat menggunakan estimator slot untuk mendapatkan perkiraan ukuran reservasi yang harus Anda buat agar dapat memproses tugas kueri secara efisien.

    Untuk mengatasinya, Anda dapat mencoba salah satu solusi berikut:

    • Tambahkan lebih banyak slot (baik slot dasar pengukuran atau slot reservasi maksimum) ke reservasi tersebut.
    • Buat pemesanan tambahan dan tetapkan ke project yang menjalankan kueri.
    • Sebarkan kueri yang menggunakan banyak resource, baik dari waktu ke waktu dalam satu reservasi atau di berbagai reservasi.

  • Pastikan tabel yang Anda kueri dikelompokkan. Pengelompokan membantu memastikan BigQuery dapat membaca kolom dengan data yang berkorelasi dengan cepat.

  • Pastikan tabel yang Anda kueri dipartisi. Untuk tabel yang tidak dipartisi, BigQuery membaca seluruh tabel. Mempartisi tabel membantu memastikan bahwa Anda hanya membuat kueri pada subset tabel yang Anda minati.

Kuota pengacakan tidak mencukupi

Sebelum menjalankan kueri, BigQuery membagi logika kueri Anda menjadi beberapa tahapan. Slot BigQuery akan menjalankan tugas untuk setiap tahap. Saat slot menyelesaikan eksekusi tugas tahap, slot akan menyimpan hasil sementara di shuffle. Tahap berikutnya dalam kueri Anda membaca data dari pengacakan untuk melanjutkan eksekusi kueri Anda. Kuota pengacakan tidak mencukupi terjadi saat Anda memiliki lebih banyak data yang perlu ditulis ke pengacakan daripada kapasitas pengacakan yang Anda miliki.

Yang harus dilakukan jika Anda seorang analis

Mirip dengan persaingan slot, mengurangi jumlah data yang diproses kueri Anda dapat mengurangi penggunaan shuffle. Untuk melakukannya, ikuti panduan di Mengurangi data yang diproses dalam kueri.

Operasi tertentu di SQL cenderung menggunakan pengacakan secara lebih ekstensif, terutama operasi JOIN dan klausa GROUP BY. Jika memungkinkan, mengurangi jumlah data dalam operasi ini dapat mengurangi penggunaan pengacakan.

Hal yang harus dilakukan jika Anda adalah administrator

Kurangi pertentangan kuota pengacakan dengan melakukan tindakan berikut:

  • Mirip dengan persaingan slot, jika Anda menggunakan harga sesuai permintaan BigQuery, kueri Anda akan menggunakan kumpulan slot bersama. Sebaiknya beralih ke harga analisis berbasis kapasitas dengan membeli reservasi. Dengan reservasi, Anda mendapatkan slot khusus dan kapasitas pengacakan untuk kueri project Anda.

  • Jika Anda menggunakan reservasi BigQuery, slot dilengkapi dengan kapasitas shuffle khusus. Jika reservasi Anda menjalankan beberapa kueri yang menggunakan shuffle secara ekstensif, hal ini dapat menyebabkan kueri lain yang berjalan secara paralel tidak mendapatkan kapasitas shuffle yang cukup. Anda dapat mengidentifikasi tugas mana yang menggunakan kapasitas pengacakan secara ekstensif dengan membuat kueri pada kolom period_shuffle_ram_usage_ratio di tampilan INFORMATION_SCHEMA.JOBS_TIMELINE.

    Untuk mengatasinya, Anda dapat mencoba satu atau beberapa solusi berikut:

    • Tambahkan lebih banyak slot ke reservasi tersebut.
    • Buat pemesanan tambahan dan tetapkan ke project yang menjalankan kueri.
    • Sebarkan kueri yang intensif dalam pengacakan, baik dari waktu ke waktu dalam pemesanan atau di berbagai pemesanan.

Perubahan skala input data

Mendapatkan insight performa ini menunjukkan bahwa kueri Anda membaca setidaknya 50% lebih banyak data untuk tabel input tertentu daripada saat terakhir kali Anda menjalankan kueri. Anda dapat menggunakan histori perubahan tabel untuk melihat apakah ukuran tabel yang digunakan dalam kueri baru-baru ini bertambah.

Yang harus dilakukan jika Anda seorang analis

Kurangi data yang Anda proses dalam kueri dengan mengikuti panduan di Mengurangi data yang diproses dalam kueri.

Penggabungan kardinalitas tinggi

Jika kueri berisi gabungan dengan kunci non-unik di kedua sisi gabungan, ukuran tabel output dapat jauh lebih besar daripada ukuran salah satu tabel input. Insight ini menunjukkan bahwa rasio baris output terhadap baris input tinggi dan menawarkan informasi tentang jumlah baris ini.

Yang harus dilakukan jika Anda seorang analis

Periksa kondisi gabungan Anda untuk mengonfirmasi bahwa peningkatan ukuran tabel output sudah sesuai dengan yang diharapkan. Hindari penggunaan cross join. Jika Anda harus menggunakan cross join, coba gunakan klausa GROUP BY untuk melakukan pra-agregasi hasil, atau gunakan fungsi jendela. Untuk mengetahui informasi selengkapnya, lihat Mengurangi data sebelum menggunakan JOIN.

Ketidakseimbangan partisi

Untuk memberikan masukan atau meminta dukungan terkait fitur ini, kirim email ke bq-query-inspector-feedback@google.com.

Distribusi data yang miring dapat menyebabkan kueri berjalan lambat. Saat kueri dieksekusi, BigQuery akan membagi data menjadi partisi kecil. Anda tidak dapat membagikan partisi antar-slot. Oleh karena itu, jika data didistribusikan secara tidak merata, beberapa partisi menjadi sangat besar, yang menyebabkan slot yang memproses partisi berukuran besar tersebut mengalami error.

Kemiringan terjadi dalam JOIN tahap. Saat Anda menjalankan operasi JOIN, BigQuery membagi data di sisi kanan dan sisi kiri operasi JOIN menjadi partisi. Jika partisi terlalu besar, data akan diseimbangkan kembali oleh tahap partisi ulang. Jika kemiringan terlalu parah dan BigQuery tidak dapat menyeimbangkan ulang lebih lanjut, insight kemiringan partisi akan ditambahkan ke tahap 'JOIN'. Proses ini dikenal sebagai tahap repartisi. Jika BigQuery mendeteksi partisi besar yang tidak dapat dibagi lebih lanjut, insight kemiringan partisi akan ditambahkan ke tahap JOIN.

Yang harus dilakukan jika Anda seorang analis

Untuk menghindari kemiringan partisi, filter data Anda sedini mungkin.

Menafsirkan informasi tahap kueri

Selain menggunakan insight performa kueri, Anda juga dapat menggunakan panduan berikut saat meninjau detail tahap kueri untuk membantu menentukan apakah ada masalah dengan kueri:

  • Jika nilai Wait ms untuk satu atau beberapa tahap tinggi dibandingkan dengan eksekusi kueri sebelumnya:
    • Periksa apakah Anda memiliki cukup slot yang tersedia untuk mengakomodasi beban kerja Anda. Jika tidak, lakukan load balancing saat Anda menjalankan kueri yang menggunakan banyak resource agar kueri tersebut tidak saling bersaing.
    • Jika nilai Wait ms lebih tinggi daripada hanya untuk satu tahap, lihat tahap sebelumnya untuk melihat apakah ada hambatan yang terjadi di sana. Hal-hal seperti perubahan besar pada data atau skema tabel yang terlibat dalam kueri dapat memengaruhi performa kueri.
  • Jika nilai Shuffle output bytes untuk suatu tahap tinggi dibandingkan dengan proses kueri sebelumnya, atau dibandingkan dengan tahap sebelumnya, evaluasi langkah-langkah yang diproses dalam tahap tersebut untuk melihat apakah ada yang membuat jumlah data yang sangat besar secara tidak terduga. Salah satu penyebab umumnya adalah saat langkah memproses INNER JOIN dengan kunci duplikat di kedua sisi gabungan. Hal ini dapat menampilkan data dalam jumlah besar yang tidak terduga.
  • Gunakan grafik eksekusi untuk melihat tahap teratas berdasarkan durasi dan pemrosesan. Pertimbangkan jumlah data yang dihasilkan dan apakah data tersebut sebanding dengan ukuran tabel yang dirujuk dalam kueri. Jika tidak, tinjau langkah-langkah di tahap tersebut untuk melihat apakah ada yang dapat menghasilkan jumlah data sementara yang tidak terduga.

Langkah berikutnya