Statistik kueri

Spanner menyediakan tabel bawaan yang menyimpan banyak statistik untuk kueri dan pernyataan DML yang paling banyak menggunakan CPU, dan semua kueri gabungan (termasuk kueri aliran perubahan).

Ketersediaan

Data SPANNER_SYS hanya tersedia melalui antarmuka SQL; misalnya:

Metode pembacaan tunggal lainnya yang disediakan Spanner tidak mendukung SPANNER_SYS.

Penggunaan CPU yang dikelompokkan berdasarkan kueri

Tabel berikut melacak kueri dengan penggunaan CPU tertinggi selama jangka waktu tertentu:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE: Kueri selama interval 1 menit
  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE: Kueri selama interval 10 menit
  • SPANNER_SYS.QUERY_STATS_TOP_HOUR: Kueri selama interval 1 jam

Tabel ini memiliki properti berikut:

  • Setiap tabel berisi data untuk interval waktu yang tidak tumpang-tindih dari panjang yang ditentukan oleh nama tabel.

  • Interval didasarkan pada waktu jam. Interval 1 menit berakhir pada menit, interval 10 menit berakhir setiap 10 menit dimulai pada jam, dan interval 1 jam berakhir pada jam.

    Misalnya, pada pukul 11.59.30, interval terbaru yang tersedia untuk kueri SQL adalah:

    • 1 menit: 11.58.00–11.58.59
    • 10 menit: 11.40–11.49.59
    • 1 jam: 10.00–10.59.59

  • Spanner mengelompokkan statistik berdasarkan teks kueri SQL. Jika kueri menggunakan parameter kueri, Spanner akan mengelompokkan semua eksekusi kueri tersebut ke dalam satu baris. Jika kueri menggunakan literal string, Spanner hanya akan mengelompokkan statistik jika teks kueri lengkap identik; jika ada teks yang berbeda, setiap kueri akan muncul sebagai baris terpisah. Untuk DML batch, Spanner menormalisasi batch dengan menghapus duplikat pernyataan identik berturut-turut sebelum membuat sidik jari.

  • Jika ada tag permintaan, FPRINT adalah hash tag permintaan. Jika tidak, ini adalah hash nilai TEXT.

  • Setiap baris berisi statistik untuk semua eksekusi kueri SQL tertentu yang statistiknya dicatat oleh Spanner selama interval yang ditentukan.

  • Jika Spanner tidak dapat menyimpan semua kueri yang dijalankan selama interval, sistem akan memprioritaskan kueri dengan penggunaan CPU tertinggi selama interval yang ditentukan.

  • Kueri yang dilacak mencakup kueri yang selesai, gagal, atau dibatalkan oleh pengguna.

  • Sebuah {i>subset<i} statistik dikhususkan untuk kueri yang berjalan, tetapi tidak selesai:

    • Jumlah eksekusi dan latensi rata-rata dalam hitungan detik di semua kueri yang tidak berhasil.

    • Jumlah eksekusi untuk kueri yang waktunya habis.

    • Jumlah eksekusi untuk kueri yang dibatalkan oleh pengguna atau gagal karena masalah konektivitas jaringan.

Skema tabel

Nama kolom Jenis Deskripsi
INTERVAL_END TIMESTAMP Akhir interval waktu saat eksekusi kueri yang disertakan terjadi.
REQUEST_TAG STRING Tag permintaan opsional untuk operasi kueri ini. Untuk mengetahui informasi selengkapnya tentang penggunaan tag, lihat Memecahkan masalah dengan tag permintaan.
QUERY_TYPE STRING Menunjukkan apakah kueri adalah PARTITIONED_QUERY atau QUERY. PARTITIONED_QUERY adalah kueri dengan partitionToken yang diperoleh dari PartitionQuery API. Semua kueri dan pernyataan DML lainnya dilambangkan dengan jenis kueri QUERY. Statistik kueri untuk DML Terpartisi tidak didukung.
TEXT STRING Teks kueri SQL, terpotong menjadi sekitar 64 KB.

Statistik untuk beberapa kueri yang memiliki string tag yang sama dikelompokkan dalam satu baris dengan REQUEST_TAG yang cocok dengan string tag tersebut. Hanya teks dari salah satu kueri tersebut yang ditampilkan di kolom ini, terpotong menjadi sekitar 64 KB. Untuk DML batch, kumpulan pernyataan SQL disatukan menjadi satu baris, digabungkan menggunakan pembatas titik koma. Teks SQL identik yang berurutan dihapus duplikatnya sebelum dipotong.
TEXT_TRUNCATED BOOL Apakah teks kueri terpotong atau tidak.
TEXT_FINGERPRINT INT64 Hash nilai REQUEST_TAG jika ada; Jika tidak, hash nilai TEXT.
EXECUTION_COUNT INT64 Berapa kali Spanner melihat kueri selama interval.
AVG_LATENCY_SECONDS FLOAT64 Durasi rata-rata, dalam detik, untuk setiap eksekusi kueri dalam database. Rata-rata ini tidak mencakup waktu encoding dan transmisi untuk hasil yang ditetapkan serta overhead.
AVG_ROWS FLOAT64 Jumlah rata-rata baris yang ditampilkan kueri.
AVG_BYTES FLOAT64 Jumlah rata-rata byte data yang ditampilkan kueri, tidak termasuk overhead encoding transmisi.
AVG_ROWS_SCANNED FLOAT64 Jumlah rata-rata baris yang dipindai kueri, tidak termasuk nilai yang dihapus.
AVG_CPU_SECONDS FLOAT64 Jumlah rata-rata detik waktu CPU yang dihabiskan Spanner di semua operasi untuk mengeksekusi kueri.
ALL_FAILED_EXECUTION_COUNT INT64 Frekuensi kueri gagal selama interval.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Durasi rata-rata, dalam detik, untuk setiap eksekusi kueri yang gagal dalam database. Rata-rata ini tidak mencakup waktu encoding dan transmisi untuk hasil yang ditetapkan serta overhead.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Frekuensi kueri dibatalkan oleh pengguna atau gagal karena koneksi jaringan terputus selama interval.
TIMED_OUT_EXECUTION_COUNT INT64 Berapa kali waktu kueri habis selama interval.
AVG_BYTES_WRITTEN FLOAT64 Jumlah rata-rata byte yang ditulis oleh pernyataan.
AVG_ROWS_WRITTEN FLOAT64 Jumlah rata-rata baris yang diubah oleh pernyataan.
STATEMENT_COUNT INT64 Jumlah pernyataan yang digabungkan ke dalam entri ini. Untuk kueri reguler dan DML, ini sama dengan jumlah eksekusi. Untuk DML batch, Spanner mencatat jumlah pernyataan dalam batch.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 Frekuensi kueri dijalankan sebagai bagian dari transaksi baca-tulis. Kolom ini membantu Anda menentukan apakah Anda dapat menghindari pertentangan kunci dengan memindahkan kueri ke transaksi hanya baca.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Histogram waktu eksekusi kueri. Nilai ini diukur dalam detik.

Array berisi satu elemen dan memiliki jenis berikut:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
Untuk informasi selengkapnya tentang nilai, lihat Distribusi.

Untuk menghitung latensi persentil yang diinginkan dari distribusi, gunakan fungsi SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), yang menampilkan perkiraan persentil ke-n. Untuk contoh terkait, lihat Menemukan latensi persentil ke-99 untuk kueri.

Untuk informasi selengkapnya, lihat Persentil dan metrik nilai distribusi.

EXECUTION_COUNT, AVG_LATENCY_SECONDS, dan LATENCY_DISTRIBUTION untuk kueri yang gagal mencakup kueri yang gagal karena sintaksis yang salah atau mengalami error sementara, tetapi berhasil dicoba lagi.

Statistik agregat

Ada juga tabel yang melacak data gabungan untuk semua kueri yang statistiknya ditangkap Spanner dalam jangka waktu tertentu:

  • SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: Kueri selama interval 1 menit
  • SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: Kueri selama interval 10 menit
  • SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: Kueri selama interval 1 jam

Tabel ini memiliki properti berikut:

  • Setiap tabel berisi data untuk interval waktu yang tidak tumpang-tindih dari panjang yang ditentukan oleh nama tabel.

  • Interval didasarkan pada waktu jam. Interval 1 menit berakhir pada menit, interval 10 menit berakhir setiap 10 menit dimulai pada jam, dan interval 1 jam berakhir pada jam.

    Misalnya, pada pukul 11.59.30, interval terbaru yang tersedia untuk kueri SQL adalah:

    • 1 menit: 11.58.00–11.58.59
    • 10 menit: 11.40–11.49.59
    • 1 jam: 10.00–10.59.59

  • Setiap baris berisi statistik untuk semua kueri yang dijalankan melalui database selama interval yang ditentukan, yang digabungkan bersama. Hanya ada satu baris per interval waktu dan mencakup kueri yang sudah selesai, kueri yang gagal, dan kueri yang dibatalkan oleh pengguna.

  • Statistik yang ditangkap dalam tabel TOTAL mungkin mencakup kueri yang tidak dicatat Spanner di tabel TOP.

  • Beberapa kolom dalam tabel ini ditampilkan sebagai metrik di Cloud Monitoring. Metrik yang diekspos adalah:

    • Jumlah eksekusi kueri
    • Kegagalan kueri
    • Latensi kueri
    • Jumlah baris yang ditampilkan
    • Jumlah baris yang dipindai
    • Jumlah byte yang ditampilkan
    • Membuat kueri waktu CPU

    Untuk informasi selengkapnya, lihat Metrik Spanner.

Skema tabel

Nama kolom Jenis Deskripsi
INTERVAL_END TIMESTAMP Akhir interval waktu saat eksekusi kueri yang disertakan terjadi.
EXECUTION_COUNT INT64 Berapa kali Spanner melihat kueri selama interval waktu tersebut.
AVG_LATENCY_SECONDS FLOAT64 Durasi rata-rata, dalam detik, untuk setiap eksekusi kueri dalam database. Rata-rata ini tidak mencakup waktu encoding dan transmisi untuk hasil yang ditetapkan serta overhead.
AVG_ROWS FLOAT64 Jumlah rata-rata baris yang ditampilkan kueri.
AVG_BYTES FLOAT64 Jumlah rata-rata byte data yang ditampilkan kueri, tidak termasuk overhead encoding transmisi.
AVG_ROWS_SCANNED FLOAT64 Jumlah rata-rata baris yang dipindai kueri, tidak termasuk nilai yang dihapus.
AVG_CPU_SECONDS FLOAT64 Jumlah rata-rata detik waktu CPU yang dihabiskan Spanner di semua operasi untuk mengeksekusi kueri.
ALL_FAILED_EXECUTION_COUNT INT64 Frekuensi kueri gagal selama interval.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Durasi rata-rata, dalam detik, untuk setiap eksekusi kueri yang gagal dalam database. Rata-rata ini tidak mencakup waktu encoding dan transmisi untuk hasil yang ditetapkan serta overhead.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Frekuensi kueri dibatalkan oleh pengguna atau gagal karena koneksi jaringan terputus selama interval.
TIMED_OUT_EXECUTION_COUNT INT64 Berapa kali waktu kueri habis selama interval.
AVG_BYTES_WRITTEN FLOAT64 Jumlah rata-rata byte yang ditulis oleh pernyataan.
AVG_ROWS_WRITTEN FLOAT64 Jumlah rata-rata baris yang diubah oleh pernyataan.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 Frekuensi kueri dijalankan sebagai bagian dari transaksi baca-tulis. Kolom ini membantu Anda menentukan apakah Anda dapat menghindari pertentangan kunci dengan memindahkan beberapa kueri ke transaksi hanya baca.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Histogram waktu eksekusi di seluruh kueri. Nilai tersebut diukur dalam detik.

Tentukan array sebagai berikut:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
Untuk informasi selengkapnya tentang nilai, lihat Distribusi.

Untuk menghitung latensi persentil yang diinginkan dari distribusi, gunakan fungsi SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), yang menampilkan perkiraan persentil ke-n. Untuk contoh terkait, lihat Menemukan latensi persentil ke-99 untuk kueri.

Untuk informasi selengkapnya, lihat Persentil dan metrik nilai distribusi.

Retensi data

Setidaknya, Spanner menyimpan data untuk setiap tabel selama jangka waktu berikut:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE dan SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: Interval yang mencakup 6 jam sebelumnya.

  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE dan SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: Interval yang mencakup 4 hari sebelumnya.

  • SPANNER_SYS.QUERY_STATS_TOP_HOUR dan SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: Interval yang mencakup 30 hari sebelumnya.

Contoh kueri

Bagian ini mencakup beberapa contoh pernyataan SQL yang mengambil statistik kueri. Anda dapat menjalankan pernyataan SQL ini menggunakan library klien, Google Cloud CLI, atau Konsol Google Cloud.

Buatlah daftar statistik dasar untuk setiap kueri dalam jangka waktu tertentu

Kueri berikut menampilkan data mentah untuk kueri teratas pada menit sebelumnya:

SELECT text,
       request_tag,
       interval_end,
       execution_count,
       avg_latency_seconds,
       avg_rows,
       avg_bytes,
       avg_rows_scanned,
       avg_cpu_seconds
FROM spanner_sys.query_stats_top_minute
ORDER BY interval_end DESC;

Membuat daftar kueri dengan penggunaan CPU tertinggi

Kueri berikut menampilkan kueri dengan penggunaan CPU tertinggi pada jam sebelumnya:

SELECT text,
       request_tag,
       execution_count AS count,
       avg_latency_seconds AS latency,
       avg_cpu_seconds AS cpu,
       execution_count * avg_cpu_seconds AS total_cpu
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY total_cpu DESC;

Menemukan total jumlah eksekusi dalam jangka waktu tertentu

Kueri berikut menampilkan jumlah total kueri yang dieksekusi dalam interval 1 menit lengkap terbaru:

SELECT interval_end,
       execution_count
FROM spanner_sys.query_stats_total_minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_minute);

Menemukan latensi rata-rata untuk sebuah kueri

Kueri berikut menampilkan informasi latensi rata-rata untuk kueri tertentu:

SELECT avg_latency_seconds
FROM spanner_sys.query_stats_top_hour
WHERE text LIKE "SELECT x FROM table WHERE x=@foo;";

Menemukan latensi persentil ke-99 untuk kueri

Kueri berikut menampilkan persentil ke-99 waktu eksekusi di seluruh kueri yang berjalan dalam 10 menit sebelumnya:

SELECT interval_end, avg_latency_seconds, SPANNER_SYS.DISTRIBUTION_PERCENTILE(latency_distribution[OFFSET(0)], 99.0)
  AS percentile_latency
FROM spanner_sys.query_stats_total_10minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_total_10minute)
ORDER BY interval_end;

Membandingkan latensi Rata-rata dengan latensi persentil ke-99 akan membantu mengidentifikasi kemungkinan kueri pencilan dengan waktu eksekusi yang tinggi.

Menemukan kueri yang memindai data paling banyak

Anda dapat menggunakan jumlah baris yang dipindai oleh kueri sebagai ukuran jumlah data yang dipindai kueri. Kueri berikut menampilkan jumlah baris yang dipindai oleh kueri yang dijalankan pada jam sebelumnya:

SELECT text,
       execution_count,
       avg_rows_scanned
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY avg_rows_scanned DESC;

Menemukan pernyataan yang menulis data paling banyak

Anda dapat menggunakan jumlah baris yang ditulis (atau byte yang ditulis) oleh DML sebagai ukuran jumlah data yang diubah oleh kueri. Kueri berikut menampilkan jumlah baris yang ditulis oleh pernyataan DML yang dieksekusi pada jam sebelumnya:

SELECT text,
       execution_count,
       avg_rows_written
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY avg_rows_written DESC;

Total penggunaan CPU di semua kueri

Kueri berikut menampilkan jumlah jam CPU yang digunakan dalam jam sebelumnya:

SELECT (avg_cpu_seconds * execution_count / 60 / 60)
  AS total_cpu_hours
FROM spanner_sys.query_stats_total_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_total_hour);

Membuat daftar kueri yang gagal dalam jangka waktu tertentu

Kueri berikut menampilkan data mentah termasuk jumlah eksekusi dan latensi rata-rata kueri yang gagal untuk kueri teratas pada menit sebelumnya:

SELECT text,
       request_tag,
       interval_end,
       execution_count,
       all_failed_execution_count,
       all_failed_avg_latency_seconds,
       avg_latency_seconds,
       avg_rows,
       avg_bytes,
       avg_rows_scanned,
       avg_cpu_seconds
FROM spanner_sys.query_stats_top_minute
WHERE all_failed_execution_count > 0
ORDER BY interval_end;

Menemukan total jumlah error dalam jangka waktu tertentu

Kueri berikut menampilkan jumlah total kueri yang gagal dieksekusi dalam interval 1 menit lengkap terbaru.

SELECT interval_end,
       all_failed_execution_count
FROM spanner_sys.query_stats_total_minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_minute)
ORDER BY interval_end;

Membuat daftar kueri yang paling banyak waktu tunggunya

Kueri berikut menampilkan kueri dengan jumlah waktu tunggu tertinggi pada jam sebelumnya.

SELECT text,
       execution_count AS count,
       timed_out_execution_count AS timeout_count,
       avg_latency_seconds AS latency,
       avg_cpu_seconds AS cpu,
       execution_count * avg_cpu_seconds AS total_cpu
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY timed_out_execution_count DESC;

Menemukan latensi rata-rata dari eksekusi yang berhasil dan gagal untuk sebuah kueri

Kueri berikut menampilkan latensi rata-rata gabungan, latensi rata-rata untuk eksekusi yang berhasil, dan latensi rata-rata untuk eksekusi yang gagal pada kueri tertentu.

SELECT avg_latency_seconds AS combined_avg_latency,
       all_failed_avg_latency_seconds AS failed_execution_latency,
       ( avg_latency_seconds * execution_count -
         all_failed_avg_latency_seconds * all_failed_execution_count
       ) / (
       execution_count - all_failed_execution_count ) AS success_execution_latency
FROM   spanner_sys.query_stats_top_hour
WHERE  text LIKE "select x from table where x=@foo;";

Memecahkan masalah penggunaan CPU yang tinggi atau latensi kueri yang meningkat dengan statistik kueri

Statistik kueri berguna saat Anda perlu menyelidiki penggunaan CPU yang tinggi pada database Spanner atau jika Anda hanya mencoba memahami bentuk kueri yang menggunakan CPU paling banyak di database. Memeriksa kueri yang menggunakan resource database dalam jumlah yang signifikan akan memberikan potensi kepada pengguna Spanner untuk mengurangi biaya operasional dan meningkatkan latensi sistem secara umum.

Anda dapat menggunakan kode SQL atau dasbor Insight kueri untuk menyelidiki kueri yang bermasalah di database Anda. Topik berikut menunjukkan cara menyelidiki kueri tersebut menggunakan kode SQL.

Meskipun contoh di bawah ini berfokus pada penggunaan CPU, langkah-langkah serupa dapat diikuti untuk memecahkan masalah latensi kueri yang meningkat dan menemukan kueri dengan latensi tertinggi. Cukup pilih interval waktu dan kueri berdasarkan latensi, bukan penggunaan CPU.

Pilih jangka waktu untuk diselidiki

Mulai investigasi Anda dengan mencari waktu saat aplikasi mulai mengalami penggunaan CPU yang tinggi. Misalnya, masalah mulai terjadi sekitar pukul 17.00 pada 24 Juli 2020 UTC.

Mengumpulkan statistik kueri untuk jangka waktu yang dipilih

Setelah memilih jangka waktu untuk memulai investigasi, kami akan melihat statistik yang dikumpulkan di tabel QUERY_STATS_TOTAL_10MINUTE pada sekitar waktu tersebut. Hasil kueri ini mungkin menunjukkan bagaimana statistik CPU dan kueri lainnya berubah selama periode waktu tersebut.

Kueri berikut menampilkan statistik kueri gabungan dari 16:30 hingga 17:30 UTC. Kita menggunakan ROUND dalam kueri untuk membatasi jumlah angka desimal untuk tujuan tampilan.

SELECT interval_end,
       execution_count AS count,
       ROUND(avg_latency_seconds,2) AS latency,
       ROUND(avg_rows,2) AS rows_returned,
       ROUND(avg_bytes,2) AS bytes,
       ROUND(avg_rows_scanned,2) AS rows_scanned,
       ROUND(avg_cpu_seconds,3) AS avg_cpu
FROM spanner_sys.query_stats_total_10minute
WHERE
  interval_end >= "2020-07-24T16:30:00Z"
  AND interval_end <= "2020-07-24T17:30:00Z"
ORDER BY interval_end;

Menjalankan kueri memberikan hasil berikut.

interval_end count latency rows_returned byte rows_scanned avg_cpu
2020-07-24T16:30:00Z 6 0,06 5,00 536,00 16,67 0,035
2020-07-24T16:40:00Z 55 0.02 0,22 25,29 0,22 0,004
2020-07-24T16:50:00Z 102 0.02 0,30 33,35 0,30 0,004
2020-07-24T17:00:00Z 154 1.06 4.42 486.33 7792208.12 4.633
2020-07-24T17:10:00Z 94 0.02 1,68 106,84 1,68 0,006
2020-07-24T17:20:00Z 110 0.02 0,38 34,60 0,38 0,005
2020-07-24T17:30:00Z 47 0.02 0,23 24,96 0,23 0,004

Pada tabel sebelumnya, kita melihat bahwa waktu CPU rata-rata, kolom avg_cpu di tabel hasil, tertinggi dalam interval yang disorot yang berakhir pada pukul 17.00. Kita juga melihat rata-rata jumlah baris yang dipindai jauh lebih tinggi. Hal ini menunjukkan bahwa kueri yang lebih mahal berjalan antara pukul 16.50 dan 17.00. Mari kita pilih interval itu untuk diselidiki lebih lanjut di langkah berikutnya.

Menemukan kueri yang menyebabkan penggunaan CPU yang tinggi

Dengan interval waktu untuk menyelidiki yang dipilih, sekarang kita membuat kueri tabel QUERY_STATS_TOP_10MINUTE. Hasil kueri ini dapat membantu menunjukkan kueri yang menyebabkan penggunaan CPU yang tinggi.

SELECT text_fingerprint AS fingerprint,
       execution_count AS count,
       ROUND(avg_latency_seconds,2) AS latency,
       ROUND(avg_cpu_seconds,3) AS cpu,
       ROUND(execution_count * avg_cpu_seconds,3) AS total_cpu
FROM spanner_sys.query_stats_top_10MINUTE
WHERE
  interval_end = "2020-07-24T17:00:00Z"
ORDER BY total_cpu DESC;

Menjalankan kueri ini memberikan hasil berikut.

sidik jari count latency cpu total_cpu
5505124206529314852 30 3.88 17.635 529.039
1697951036096498470 10 4.49 18.388 183.882
2295109096748351518 1 0,33 0,048 0,048
11618299167612903606 1 0,25 0,021 0,021
10302798842433860499 1 0,04 0,006 0,006
123771704548746223 1 0,04 0,006 0,006
4216063638051261350 1 0,04 0,006 0,006
3654744714919476398 1 0,04 0,006 0,006
2999453161628434990 1 0,04 0,006 0,006
823179738756093706 1 0.02 0,005 0,0056

2 kueri teratas, yang ditandai dalam tabel hasil, merupakan pencilan dalam hal CPU dan latensi rata-rata, serta jumlah eksekusi dan total CPU. Mari kita selidiki kueri pertama yang tercantum dalam hasil ini.

Membandingkan kueri yang dijalankan dari waktu ke waktu

Setelah mempersempit investigasi, kita dapat mengalihkan perhatian ke tabel QUERY_STATS_TOP_MINUTE. Dengan membandingkan waktu berjalan untuk kueri tertentu, kita dapat mencari korelasi antara jumlah baris atau byte yang ditampilkan, atau jumlah baris yang dipindai dan peningkatan CPU atau latensi. Penyimpangan dapat menunjukkan ketidakseragaman dalam data. Jumlah baris yang dipindai secara konsisten tinggi dapat mengindikasikan kurangnya indeks yang sesuai atau pengurutan gabungan yang kurang optimal.

Mari kita selidiki kueri yang menunjukkan penggunaan CPU rata-rata tertinggi dan latensi tertinggi dengan menjalankan pernyataan berikut yang memfilter text_fingerprint dari kueri tersebut.

SELECT interval_end,
       ROUND(avg_latency_seconds,2) AS latency,
       avg_rows AS rows_returned,
       avg_bytes AS bytes_returned,
       avg_rows_scanned AS rows_scanned,
       ROUND(avg_cpu_seconds,3) AS cpu,
FROM spanner_sys.query_stats_top_minute
WHERE text_fingerprint = 5505124206529314852
ORDER BY interval_end DESC;

Menjalankan kueri ini akan menampilkan hasil berikut.

interval_end latency rows_returned bytes_returned rows_scanned cpu
2020-07-24T17:00:00Z 4,55 21 2365 30000000 19,255
2020-07-24T16:00:00Z 3,62 21 2365 30000000 17,255
2020-07-24T15:00:00Z 4,37 21 2365 30000000 18,350
2020-07-24T14:00:00Z 4,02 21 2365 30000000 17,748
2020-07-24T13:00:00Z 3,12 21 2365 30000000 16,380
2020-07-24T12:00:00Z 3.45 21 2365 30000000 15,476
2020-07-24T11:00:00Z 4,94 21 2365 30000000 22,611
2020-07-24T10:00:00Z 6,48 21 2365 30000000 21,265
2020-07-24T09:00:00Z 0,23 21 2365 5 0,040
2020-07-24T08:00:00Z 0,04 21 2365 5 0,021
2020-07-24T07:00:00Z 0,09 21 2365 5 0,030

Dengan memeriksa hasil sebelumnya, kita melihat bahwa jumlah baris yang dipindai, CPU yang digunakan, dan latensi berubah secara signifikan sekitar pukul 09.00. Untuk memahami alasan jumlah ini meningkat secara drastis, kita akan memeriksa teks kueri dan melihat apakah perubahan dalam skema mungkin telah memengaruhi kueri.

Gunakan kueri berikut untuk mengambil teks kueri untuk kueri yang sedang kita selidiki.

SELECT text,
       text_truncated
FROM spanner_sys.query_stats_top_hour
WHERE text_fingerprint = 5505124206529314852
LIMIT 1;

Hasil dari kueri tersebut adalah sebagai berikut.

teks text_truncated
select * dari pesanan dengan o_custkey = 36901; false

Saat memeriksa teks kueri yang ditampilkan, kita mendapati bahwa kueri tersebut memfilter kolom yang disebut o_custkey. Ini adalah kolom non-kunci pada tabel orders. Saat ini, dulu ada indeks pada kolom tersebut yang menurun sekitar pukul 09.00. Hal ini menjelaskan perubahan biaya untuk kueri ini. Kita dapat menambahkan indeks kembali atau, jika kueri jarang dijalankan, memutuskan untuk tidak memiliki indeks dan menerima biaya baca yang lebih tinggi.

Investigasi kami sejauh ini berfokus pada kueri yang berhasil diselesaikan dan kami menemukan satu alasan mengapa database mengalami beberapa penurunan performa. Pada langkah berikutnya, kita akan berfokus pada kueri yang gagal atau dibatalkan dan menunjukkan cara memeriksa data tersebut untuk mendapatkan lebih banyak insight.

Menyelidiki kueri yang gagal

Kueri yang tidak berhasil diselesaikan masih memakai resource sebelum waktu habis, dibatalkan, atau gagal. Spanner melacak jumlah eksekusi dan resource yang digunakan oleh kueri yang gagal serta kueri yang berhasil.

Untuk memeriksa apakah kueri yang gagal merupakan kontributor signifikan bagi pemanfaatan sistem, pertama-tama kita dapat memeriksa jumlah kueri yang gagal dalam interval waktu yang diinginkan.

SELECT interval_end,
       all_failed_execution_count AS failed_count,
       all_failed_avg_latency_seconds AS latency
FROM spanner_sys.query_stats_total_minute
WHERE
  interval_end >= "2020-07-24T16:50:00Z"
  AND interval_end <= "2020-07-24T17:00:00Z"
ORDER BY interval_end;
interval_end failed_count latency
2020-07-24T16:52:00Z 1 15,211391
2020-07-24T16:53:00Z 3 58,312232

Dengan menyelidiki lebih lanjut, kita dapat mencari kueri yang paling mungkin akan gagal menggunakan kueri berikut.

SELECT interval_end,
       text_fingerprint,
       execution_count,
       avg_latency_seconds AS avg_latency,
       all_failed_execution_count AS failed_count,
       all_failed_avg_latency_seconds AS failed_latency,
       cancelled_or_disconnected_execution_count AS cancel_count,
       timed_out_execution_count AS to_count
FROM spanner_sys.query_stats_top_minute
WHERE all_failed_execution_count > 0
ORDER BY interval_end;
interval_end text_fingerprint execution_count failed_count cancel_count to_count
2020-07-24T16:52:00Z 5505124206529314852 3 1 1 0
2020-07-24T16:53:00Z 1697951036096498470 2 1 1 0
2020-07-24T16:53:00Z 5505124206529314852 5 2 1 1

Seperti yang ditunjukkan tabel sebelumnya, kueri dengan sidik jari 5505124206529314852 telah gagal beberapa kali selama interval waktu yang berbeda. Dengan mempertimbangkan pola kegagalan seperti ini, menarik untuk membandingkan latensi operasi yang berhasil dan gagal.

SELECT interval_end,
       avg_latency_seconds AS combined_avg_latency,
       all_failed_avg_latency_seconds AS failed_execution_latency,
       ( avg_latency_seconds * execution_count -
         all_failed_avg_latency_seconds * all_failed_execution_count
       ) / (
       execution_count - all_failed_execution_count ) AS success_execution_latency
FROM   spanner_sys.query_stats_top_hour
WHERE  text_fingerprint = 5505124206529314852;
interval_end combined_avg_latency failed_execution_latency success_execution_latency
2020-07-24T17:00:00Z 3,880420 13,830709 2,774832

Menerapkan praktik terbaik

Setelah mengidentifikasi kueri kandidat untuk pengoptimalan, selanjutnya kita dapat melihat profil kueri dan mencoba mengoptimalkannya menggunakan praktik terbaik SQL.

Langkah selanjutnya