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, jika kueri Anda berjalan lebih lambat dari yang diperkirakan, atau lebih lambat dari proses sebelumnya, memahami apa yang terjadi dapat 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 yang 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 paket kueri di konsol. Setiap kotak mewakili a tahap dalam paket kueri seperti berikut:

Input: Membaca data dari tabel atau memilih kolom tertentu
Join: Menggabungkan data dari dua tabel berdasarkan kondisi JOIN
Aggregate: Melakukan perhitungan seperti SUM
Sort: Mengurutkan hasil

Tahapan terdiri dari langkah-langkah yang menjelaskan operasi individual yang dijalankan oleh setiap pekerja dalam satu tahap menjalankan. Anda dapat mengklik tahap untuk membukanya dan melihat langkah-langkahnya. Tahapan juga mencakup informasi waktu relatif dan absolut. Nama tahap meringkas langkah-langkah yang dilakukannya. Misalnya, tahap dengan join dalam namanya berarti langkah utama dalam tahap tersebut adalah operasi JOIN. Nama tahap yang memiliki + di akhir berarti tahap tersebut melakukan langkah-langkah penting tambahan. Misalnya, tahap dengan JOIN+ dalam namanya berarti 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 shuffle saat tahapan sedang dieksekusi. Angka di tepi menunjukkan perkiraan jumlah baris yang dipertukarkan antar-tahapan. Kuota memori shuffle berkorelasi dengan jumlah slot yang dialokasikan ke akun. Jika kuota shuffle terlampaui, memori shuffle dapat meluas ke disk dan menyebabkan performa kueri melambat secara drastis.

Melihat insight performa kueri

Konsol

Ikuti langkah-langkah berikut untuk melihat insight performa kueri:

Buka halaman BigQuery di Google Cloud konsol.

Buka halaman BigQuery
Di panel kiri, klik Explorer:

Jika Anda tidak melihat panel kiri, klik Luaskan panel kiri untuk membuka panel.
Di panel Explorer, klik Job history.
Klik Personal History atau Project History.
Dalam daftar tugas, identifikasi tugas kueri yang Anda minati. Klik Actions, lalu pilih View job in editor.
Pilih tab Execution graph untuk melihat representasi grafis setiap tahap kueri:

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 memilikinya.
Klik tahap untuk membuka panel detail tahap, tempat Anda dapat melihat informasi berikut:
- Informasi paket kueri untuk tahap tersebut.
- Langkah-langkah yang dieksekusi dalam tahap tersebut.
- Insight performa yang berlaku.
Opsional: Jika Anda memeriksa kueri yang sedang berjalan, klik Sync untuk memperbarui grafik eksekusi sehingga mencerminkan status kueri saat ini.
Opsional: Untuk menandai tahapan teratas berdasarkan durasi tahap pada grafik, klik Highlight top stages by duration.
Opsional: Untuk menandai tahapan teratas berdasarkan waktu slot yang digunakan pada grafik, klik Highlight top stages by processing.
Opsional: Untuk menyertakan tahapan redistribusi shuffle pada grafik, klik Show shuffle redistribution stages.

Gunakan opsi ini untuk menampilkan tahapan repartition dan coalesce yang tersembunyi dalam grafik eksekusi default.

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

Untuk kueri yang memiliki masalah regresi performa, insight performa juga ditampilkan di tab Job Information untuk kueri tersebut:

Tab informasi pekerjaan.

SQL

Di Google Cloud konsol, buka halaman BigQuery.

Buka BigQuery

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
      OR bi_engine_reasons IS NOT NULL
      OR high_cardinality_joins IS NOT NULL
      OR partition_skew IS NOT NULL
    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
  );

Klik Run.

Untuk mengetahui informasi selengkapnya tentang cara menjalankan kueri, lihat 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 mengatasinya.

Insight performa ditujukan untuk dua audiens:

Analis: Anda menjalankan kueri dalam project. Anda tertarik untuk mengetahui alasan kueri yang pernah Anda jalankan sebelumnya berjalan lebih lambat dari yang diperkirakan, dan 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 hal 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 bagian data yang menjadi input dan output dari suatu tahap. Satu slot mengambil tugas dan menjalankan bagian data tersebut untuk tahap tersebut. 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 cukup untuk menjalankannya.

Hal yang harus dilakukan jika Anda adalah 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 seorang administrator

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

Jika Anda menggunakan harga sesuai permintaan BigQuery's, kueri Anda akan menggunakan kumpulan slot bersama. Pertimbangkan untuk beralih ke harga analisis berbasis kapasitas dengan membeli reservasi sebagai gantinya. Reservasi memungkinkan Anda mencadangkan 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 cukup slot yang ditetapkan untuk menjalankan kueri dengan cukup cepat. Anda dapat menggunakan estimator slot untuk mendapatkan perkiraan ukuran reservasi yang harus Anda miliki agar dapat memproses tugas kueri secara efisien.
Untuk mengatasi hal ini, Anda dapat mencoba salah satu solusi berikut:
- Tambahkan lebih banyak slot (baik slot dasar pengukuran maupun slot reservasi maksimum) ke reservasi tersebut.
- Buat reservasi tambahan dan tetapkan ke project yang menjalankan kueri.
- Sebarkan kueri yang menggunakan banyak resource, baik dari waktu ke waktu dalam reservasi atau di berbagai reservasi.
Pastikan tabel yang Anda kueri adalah dikelompokkan. Pengelompokan membantu memastikan BigQuery dapat membaca kolom dengan data yang berkorelasi dengan cepat.
Pastikan tabel yang Anda kueri adalah dipartisi. Untuk tabel yang tidak dipartisi, BigQuery akan membaca seluruh tabel. Mempartisi tabel membantu memastikan Anda hanya mengkueri subset tabel yang Anda minati.

Kuota shuffle tidak mencukupi

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

Hal yang harus dilakukan jika Anda adalah 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 shuffle lebih ekstensif, terutama JOIN operasi dan GROUP BY klausa. Jika memungkinkan, mengurangi jumlah data dalam operasi ini dapat mengurangi penggunaan shuffle.

Hal yang harus dilakukan jika Anda adalah seorang administrator

Kurangi persaingan kuota shuffle dengan melakukan tindakan berikut:

Mirip dengan persaingan slot, jika Anda menggunakan harga sesuai permintaan BigQuery, kueri Anda akan menggunakan kumpulan slot bersama. Pertimbangkan untuk beralih ke harga analisis berbasis kapasitas dengan membeli reservasi sebagai gantinya. Reservasi memberi Anda slot khusus dan kapasitas shuffle untuk kueri project Anda.
Jika Anda menggunakan reservasi BigQuery, slot akan 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 shuffle secara ekstensif dengan mengkueri kolom period_shuffle_ram_usage_ratio di tampilan INFORMATION_SCHEMA.JOBS_TIMELINE view.

Untuk mengatasi hal ini, Anda dapat mencoba satu atau beberapa solusi berikut:
- Tambahkan lebih banyak slot ke reservasi tersebut.
- Buat reservasi tambahan dan tetapkan ke project yang menjalankan kueri.
- Sebarkan kueri yang menggunakan banyak shuffle, baik dari waktu ke waktu dalam reservasi atau di berbagai reservasi.

Untuk informasi pemecahan masalah tambahan, lihat Error batas ukuran shuffle di halaman Pemecahan Masalah BigQuery.

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 meningkat.

Hal yang harus dilakukan jika Anda adalah 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.

Hal yang harus dilakukan jika Anda adalah seorang analis

Periksa kondisi gabungan Anda untuk mengonfirmasi bahwa peningkatan ukuran tabel output sudah diperkirakan. 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.

Kecondongan partisi

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

Distribusi data yang condong dapat menyebabkan kueri berjalan lambat. Saat kueri dieksekusi, BigQuery akan membagi data menjadi partisi kecil untuk pemrosesan paralel. Kecondongan terjadi saat data didistribusikan secara tidak merata di seluruh partisi ini, sering kali karena nilai yang sering muncul dalam kunci gabungan atau pengelompokan, sehingga membuat beberapa partisi jauh lebih besar daripada yang lain. Karena satu slot memproses seluruh partisi dan tidak dapat berbagi pekerjaan, partisi yang berukuran terlalu besar dapat memperlambat pemrosesan, menyebabkan error "resource terlampaui", dan dalam kasus ekstrem, menyebabkan error pada slot.

Saat Anda menjalankan operasi JOIN, BigQuery akan mempartisi data di sisi kiri dan kanan gabungan berdasarkan kunci gabungan. Jika partisi terlalu besar, BigQuery akan mencoba menyeimbangkan kembali data. Jika kecondongan terlalu parah untuk diseimbangkan kembali sepenuhnya, insight kecondongan partisi akan ditambahkan ke tahap JOIN dalam grafik eksekusi.

Mengidentifikasi kecondongan partisi

Gunakan tab Execution graph di BigQuery Studio untuk menemukan tahap kueri yang mengalami kecondongan partisi. Insight ditandai pada tahap tersebut. Dari detail tahap, Anda dapat menentukan bagian teks kueri yang relevan dan tabel yang sedang diproses. Untuk mengetahui informasi selengkapnya, lihat Memahami langkah-langkah dengan teks kueri.

Contoh

Kueri berikut menggabungkan informasi repositori dengan informasi file. Kecondongan dapat terjadi jika beberapa repositori memiliki file yang jauh lebih banyak daripada yang lain.

SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10
GROUP BY r.repo_name

Kunci gabungannya adalah repo_name. Di tabel sample_repos, repo_name diharapkan unik. Namun, di tabel sample_files, repo_name dapat muncul berkali-kali. Jika beberapa nilai repo_name muncul secara tidak proporsional di sample_files, hal ini akan menyebabkan kecondongan data.

Untuk mengonfirmasi apakah kecondongan data ada atau tidak, analisis distribusi kunci gabungan di tabel yang lebih besar (dalam hal ini sample_files). Jalankan kueri berikut untuk menilai distribusi repo_name:

SELECT repo_name, COUNT(*) AS occurrences
FROM `bigquery-public-data.github_repos.sample_files`
GROUP BY repo_name
ORDER BY occurrences DESC

Untuk tabel yang sangat besar, gunakan APPROX_TOP_COUNT fungsi untuk memperkirakan nilai yang paling sering muncul secara efisien.

SELECT APPROX_TOP_COUNT(repo_name, 100)
FROM `bigquery-public-data.github_repos.sample_files`

Jika jumlah untuk nilai teratas jauh lebih besar daripada yang lain, kecondongan data akan muncul.

Mengurangi kecondongan partisi

Anda dapat menggunakan strategi berikut untuk mengatasi kecondongan partisi:

Filter data Anda lebih awal. Kurangi jumlah data yang diproses dengan menerapkan filter sedini mungkin dalam kueri Anda. Hal ini dapat mengurangi jumlah baris yang terkait dengan kunci condong sebelum mencapai operasi seperti JOIN atau GROUP BY.

Pisahkan kueri untuk mengisolasi kunci condong. Jika kecondongan disebabkan oleh beberapa nilai kunci tertentu, mirip dengan kolom repo_name dalam contoh sebelumnya, pertimbangkan untuk memisahkan kueri. Proses data untuk kunci condong secara terpisah dari data lainnya, lalu gabungkan hasilnya menggunakan UNION ALL.

Contoh: Mengisolasi kunci yang sering digunakan.

-- Query for the skewed key
SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10 AND r.repo_name = 'popular_repo'
GROUP BY r.repo_name

UNION ALL

-- Query for all other keys
SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10 AND r.repo_name != 'popular_repo'
GROUP BY r.repo_name

Menangani NULL dan nilai default: Penyebab umum kecondongan adalah sejumlah besar baris dengan NULL atau nilai string kosong di kolom kunci. Jika Anda tidak memerlukan baris ini untuk analisis, filter baris tersebut menggunakan klausa WHERE sebelum JOIN atau GROUP BY.
Mengurutkan ulang operasi: Dalam kueri dengan beberapa gabungan, urutan dapat menjadi penting. Jika memungkinkan, lakukan gabungan yang mengurangi jumlah baris secara signifikan lebih awal dalam kueri.
Menggunakan fungsi perkiraan: Untuk agregasi pada data condong, pertimbangkan apakah hasil perkiraan dapat diterima. Fungsi seperti APPROX_COUNT_DISTINCT lebih toleran terhadap kecondongan data daripada fungsi yang tepat seperti COUNT(DISTINCT).

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 proses kueri sebelumnya:
- Lihat 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 tidak saling bersaing.
- Jika nilai Wait ms lebih tinggi dari yang seharusnya untuk satu tahap, lihat tahap sebelumnya untuk melihat apakah bottleneck telah diperkenalkan di sana. Hal-hal seperti perubahan substansial 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 data dalam jumlah besar secara tidak terduga. Salah satu penyebab umum hal ini adalah saat langkah memproses an INNER JOIN yang memiliki kunci duplikat di kedua sisi gabungan. Hal ini dapat menampilkan data dalam jumlah besar secara tidak terduga.
Gunakan grafik eksekusi untuk melihat tahapan teratas berdasarkan durasi dan pemrosesan. Pertimbangkan jumlah data yang dihasilkan dan apakah data tersebut sebanding dengan ukuran tabel yang direferensikan dalam kueri. Jika tidak, tinjau langkah-langkah dalam tahapan tersebut untuk melihat apakah ada yang dapat menghasilkan data sementara dalam jumlah yang tidak terduga.

Langkah berikutnya

Tinjau panduan pengoptimalan kueri untuk mendapatkan tips tentang cara meningkatkan performa kueri.