Statistik kunci

Spanner menyediakan statistik kunci yang memungkinkan Anda mengidentifikasi kunci baris dan kolom tabel yang menjadi sumber utama konflik kunci transaksi dalam database Anda selama jangka waktu tertentu. Anda dapat mengambil statistik ini dari tabel sistem SPANNER_SYS.LOCK_STATS* menggunakan pernyataan SQL.

Mengakses statistik kunci

Spanner menyediakan statistik kunci dalam skema SPANNER_SYS. Anda dapat menggunakan cara berikut untuk mengakses data SPANNER_SYS:

• Halaman Spanner Studio database di konsol Google Cloud

• Perintah gcloud spanner databases execute-sql.

• Dasbor Insight kunci.

• Metode executeSql atau executeStreamingSql.

  Metode baca tunggal berikut yang disediakan Spanner tidak mendukung SPANNER_SYS:

  • Melakukan pembacaan yang kuat dari satu baris atau beberapa baris dalam tabel.
  • Melakukan pembacaan basi dari satu baris atau beberapa baris dalam tabel.
  • Membaca dari satu baris atau beberapa baris dalam indeks sekunder.

Mengunci statistik menurut kunci baris

Tabel berikut melacak kunci baris dengan waktu tunggu tertinggi:

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE: Kunci baris dengan waktu tunggu kunci tertinggi selama interval 1 menit.

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE: Kunci baris dengan waktu tunggu penguncian tertinggi selama interval 10 menit.

• SPANNER_SYS.LOCK_STATS_TOP_HOUR: Kunci baris dengan waktu tunggu penguncian tertinggi selama interval 1 jam

Tabel ini memiliki properti berikut:

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

• Interval didasarkan pada waktu jam. Interval 1 menit berakhir pada menit ke-1, interval 10 menit berakhir setiap 10 menit dimulai pada jam ke-1, dan interval 1 jam berakhir pada jam ke-1. Setelah setiap interval, Spanner mengumpulkan data dari semua server, lalu menyediakan data tersebut di tabel SPANNER_SYS tidak lama setelahnya.

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

  • 1 menit: 11.58.00–11.58.59 AM
  • 10 menit: 11.40.00–11.49.59 AM
  • 1 jam: 10.00.00–10.59.59

• Spanner mengelompokkan statistik berdasarkan rentang kunci baris awal.

• Setiap baris berisi statistik untuk total waktu tunggu penguncian dari rentang kunci baris awal tertentu yang statistiknya diambil oleh Spanner selama interval yang ditentukan.

• Jika Spanner tidak dapat menyimpan informasi tentang setiap rentang kunci baris untuk menunggu kunci selama interval, sistem akan memprioritaskan rentang kunci baris dengan waktu tunggu kunci tertinggi selama interval yang ditentukan.

• Semua kolom dalam tabel dapat bernilai null.

Skema tabel

Mode kunci

Operasi Spanner memperoleh kunci saat operasi menjadi bagian dari transaksi baca-tulis. Transaksi hanya baca tidak memperoleh kunci. Spanner menggunakan mode penguncian yang berbeda untuk memaksimalkan jumlah transaksi yang memiliki akses ke sel data tertentu pada waktu tertentu. Setiap kunci memiliki karakteristik yang berbeda. Misalnya, beberapa kunci dapat dibagikan di antara beberapa transaksi, sementara yang lain tidak dapat dibagikan.

Konflik kunci dapat terjadi saat Anda mencoba mendapatkan salah satu mode kunci berikut dalam transaksi.

• ReaderShared Lock - Kunci yang memungkinkan pembacaan lain tetap mengakses data hingga transaksi Anda siap di-commit. Kunci bersama ini diperoleh saat transaksi baca-tulis membaca data.

• Kunci WriterShared - Kunci ini diperoleh saat transaksi baca-tulis mencoba melakukan commit penulisan.

• Exclusive Lock - kunci eksklusif diperoleh saat transaksi baca-tulis yang telah memperoleh kunci ReaderShared, mencoba menulis data setelah pembacaan selesai. Kunci eksklusif adalah upgrade dari kunci ReaderShared. Penguncian eksklusif adalah kasus khusus transaksi yang memegang kunci ReaderShared dan kunci WriterShared secara bersamaan. Tidak ada transaksi lain yang dapat memperoleh kunci apa pun pada sel yang sama.

• Kunci WriterSharedTimestamp - jenis kunci WriterShared khusus yang diperoleh saat menyisipkan baris baru ke dalam tabel yang memiliki stempel waktu commit sebagai bagian dari kunci utama. Jenis kunci ini mencegah peserta transaksi membuat baris yang sama persis dan, oleh karena itu, saling bertentangan. Spanner memperbarui kunci baris yang disisipkan agar cocok dengan stempel waktu commit transaksi yang melakukan penyisipan.

Untuk mengetahui informasi selengkapnya tentang jenis transaksi dan jenis kunci yang tersedia, lihat Transaksi.

Konflik mode kunci

Tabel berikut menunjukkan kemungkinan konflik antara mode kunci yang berbeda.

Penguncian WriterSharedTimestamp hanya digunakan saat menyisipkan baris baru dengan stempel waktu sebagai bagian dari kunci utamanya. Kunci WriterShared dan Exclusive digunakan saat menulis ke sel yang ada atau menyisipkan baris baru tanpa stempel waktu. Akibatnya, WriterSharedTimestamp tidak dapat berkonflik dengan jenis kunci lainnya, dan skenario tersebut ditampilkan sebagai Tidak berlaku dalam tabel sebelumnya.

Satu-satunya pengecualian adalah ReaderShared, yang dapat diterapkan ke baris yang tidak ada dan, oleh karena itu, berpotensi bertentangan dengan WriterSharedTimestamp. Misalnya, pemindaian tabel menyeluruh mengunci seluruh tabel, bahkan untuk baris yang belum dibuat, sehingga ReaderShared dapat berkonflik dengan WriterSharedTimestamp.

Apa yang dimaksud dengan kunci awal rentang baris?

Kolom ROW_RANGE_START_KEY mengidentifikasi kunci utama gabungan, atau kunci utama awal rentang baris, yang memiliki konflik penguncian. Skema berikut digunakan untuk mengilustrasikan contoh.

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  TrackId      INT64 NOT NULL,
  SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Users (
  UserId     INT64 NOT NULL,
  LastAccess TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  ...
) PRIMARY KEY (UserId, LastAccess);

Seperti yang ditunjukkan oleh tabel kunci baris dan rentang kunci baris berikut, rentang ditampilkan dengan tanda plus, '+', dalam kunci. Dalam kasus tersebut, kunci mewakili kunci awal rentang kunci tempat terjadinya konflik penguncian.

Statistik gabungan

SPANNER_SYS juga berisi tabel untuk menyimpan data gabungan untuk statistik kunci yang diambil oleh Spanner dalam jangka waktu tertentu:

• SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Statistik gabungan untuk semua penantian kunci selama interval 1 menit.

• SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Statistik gabungan untuk semua penantian kunci selama interval 10 menit.

• SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Statistik gabungan untuk semua penantian kunci selama interval 1 jam.

Tabel statistik gabungan memiliki properti berikut:

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

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

  Misalnya, pada pukul 11.59.30, interval terbaru yang tersedia untuk kueri SQL tentang statistik penguncian gabungan adalah:

  • 1 menit: 11.58.00–11.58.59 AM
  • 10 menit: 11.40.00–11.49.59 AM
  • 1 jam: 10.00.00–10.59.59

• Setiap baris berisi statistik untuk semua penantian kunci di database selama interval yang ditentukan, yang digabungkan. Hanya ada satu baris per interval waktu.

• Statistik yang diambil dalam tabel SPANNER_SYS.LOCK_STATS_TOTAL_* mencakup penantian kunci yang tidak diambil Spanner dalam tabel SPANNER_SYS.LOCK_STATS_TOP_*.

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

  • Waktu tunggu kunci

  Untuk mengetahui informasi selengkapnya, lihat Metrik Spanner.

Skema tabel

Contoh kueri

Berikut adalah contoh pernyataan SQL yang dapat Anda gunakan untuk mengambil statistik penguncian. Anda dapat menjalankan pernyataan SQL ini menggunakan library klien, gcloud spanner, atau Google Cloud console.

Mencantumkan statistik kunci untuk interval 1 menit sebelumnya

Kueri berikut menampilkan informasi menunggu kunci untuk setiap kunci baris dengan konflik kunci, termasuk fraksi total konflik kunci, selama interval waktu 1 menit terbaru.

Fungsi CAST() mengonversi kolom row_range_start_key BYTES menjadi STRING.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_minute t, spanner_sys.lock_stats_top_minute s
WHERE t.interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.lock_stats_total_minute)
AND s.interval_end = t.interval_end
ORDER BY s.lock_wait_seconds DESC;

Output kueri

¹ _exists adalah kolom internal yang digunakan untuk memeriksa apakah baris tertentu ada atau tidak.

Retensi data

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

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE dan SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Interval yang mencakup 6 jam sebelumnya.

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE dan SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Interval yang mencakup 4 hari sebelumnya.

• SPANNER_SYS.LOCK_STATS_TOP_HOUR dan SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Interval yang mencakup 30 hari sebelumnya.

Memecahkan masalah konflik kunci di database menggunakan statistik kunci

Anda dapat menggunakan SQL atau dasbor Insight penguncian untuk melihat konflik penguncian di database Anda.

Topik berikut menunjukkan cara menyelidiki konflik kunci tersebut menggunakan kode SQL.

Pilih jangka waktu yang akan diselidiki

Anda memeriksa Metrik latensi untuk database Spanner dan menemukan periode waktu saat aplikasi Anda mengalami latensi dan penggunaan CPU yang tinggi. Misalnya, masalah mulai terjadi sekitar pukul 22.50 pada 12 November 2020.

Menentukan apakah latensi commit transaksi meningkat bersama dengan waktu tunggu kunci selama periode yang dipilih

Kunci diperoleh oleh transaksi, jadi jika konflik kunci menyebabkan waktu tunggu yang lama, kita akan dapat melihat peningkatan latensi commit transaksi bersama dengan peningkatan waktu tunggu kunci.

Setelah memilih jangka waktu untuk memulai penyelidikan, kita akan menggabungkan statistik transaksi TXN_STATS_TOTAL_10MINUTE dengan statistik penguncian LOCK_STATS_TOTAL_10MINUTE pada waktu tersebut untuk membantu kita memahami apakah peningkatan latensi commit rata-rata disebabkan oleh peningkatan waktu tunggu penguncian.

SELECT t.interval_end, t.avg_commit_latency_seconds, l.total_lock_wait_seconds
FROM spanner_sys.txn_stats_total_10minute t
LEFT JOIN spanner_sys.lock_stats_total_10minute l
ON t.interval_end = l.interval_end
WHERE
  t.interval_end >= "2020-11-12T21:50:00Z"
  AND t.interval_end <= "2020-11-12T23:50:00Z"
ORDER BY interval_end;

Ambil data berikut sebagai contoh hasil yang kita dapatkan dari kueri.

Hasil sebelumnya menunjukkan peningkatan yang signifikan pada avg_commit_latency_seconds dan total_lock_wait_seconds selama periode waktu yang sama dari 12-11-2020 22.40.00 hingga 12-11-2020 22.50.00, dan menurun setelah itu. Satu hal yang perlu diperhatikan adalah bahwa avg_commit_latency_seconds adalah waktu rata-rata yang dihabiskan hanya untuk langkah penerapan. Di sisi lain, total_lock_wait_seconds adalah waktu penguncian gabungan untuk periode tersebut, sehingga waktu terlihat jauh lebih lama daripada waktu penerapan transaksi.

Setelah mengonfirmasi bahwa waktu tunggu kunci terkait erat dengan peningkatan latensi penulisan, kita akan menyelidiki pada langkah berikutnya baris dan kolom mana yang menyebabkan waktu tunggu yang lama.

Temukan kunci baris dan kolom yang memiliki waktu tunggu penguncian yang lama selama periode yang dipilih

Untuk mengetahui kunci baris dan kolom mana yang mengalami waktu tunggu kunci yang tinggi selama periode yang sedang kami selidiki, kami membuat kueri pada tabel LOCK_STAT_TOP_10MINUTE, yang mencantumkan kunci baris dan kolom yang paling berkontribusi pada waktu tunggu kunci.

Fungsi CAST() dalam kueri berikut mengonversi kolom BYTES row_range_start_key menjadi STRING.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_10minute t, spanner_sys.lock_stats_top_10minute s
WHERE
  t.interval_end = "2020-11-12T22:50:00Z" and s.interval_end = t.interval_end;

Dari tabel hasil ini, kita dapat melihat bahwa konflik terjadi pada tabel Singers di kunci SingerId=32. Singers.SingerInfo adalah kolom tempat konflik kunci terjadi antara ReaderShared dan WriterShared.

Ini adalah jenis konflik umum saat ada satu transaksi yang mencoba membaca sel tertentu dan transaksi lainnya mencoba menulis ke sel yang sama. Sekarang kita mengetahui sel data persisnya yang menjadi perebutan kunci transaksi, jadi di langkah berikutnya kita akan mengidentifikasi transaksi yang memperebutkan kunci.

Menemukan transaksi mana yang mengakses kolom yang terlibat dalam konflik kunci

Untuk mengidentifikasi transaksi yang mengalami latensi commit yang signifikan dalam interval waktu tertentu karena konflik kunci, Anda perlu membuat kueri untuk kolom berikut dari tabel SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE:

• fprint
• read_columns
• write_constructive_columns
• avg_commit_latency_seconds

Anda perlu memfilter kolom terkunci yang diidentifikasi dari tabel SPANNER_SYS.LOCK_STATS_TOP_10MINUTE:

• Transaksi yang membaca kolom apa pun yang menimbulkan konflik penguncian saat mencoba mendapatkan kunci ReaderShared.

• Transaksi yang menulis ke kolom mana pun yang mengalami konflik penguncian saat mencoba mendapatkan kunci WriterShared.

SELECT
  fprint,
  read_columns,
  write_constructive_columns,
  avg_commit_latency_seconds
FROM spanner_sys.txn_stats_top_10minute t2
WHERE (
  EXISTS (
    SELECT * FROM t2.read_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "ReaderShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
OR
  EXISTS (
    SELECT * FROM t2.write_constructive_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "WriterShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
)
AND t2.interval_end ="2020-11-12T23:50:00Z"
ORDER BY avg_commit_latency_seconds DESC;

Hasil kueri diurutkan berdasarkan kolom avg_commit_latency_seconds sehingga Anda dapat melihat transaksi yang mengalami latensi penerapan tertinggi terlebih dahulu.

Hasil kueri menunjukkan bahwa dua transaksi mencoba mengakses kolom Singers.SingerInfo, yang merupakan kolom yang mengalami konflik penguncian selama jangka waktu tersebut. Setelah mengidentifikasi transaksi yang menyebabkan konflik penguncian, Anda dapat menganalisis transaksi menggunakan sidik jarinya, fprint, untuk mengidentifikasi potensi masalah yang menyebabkan konflik penguncian.

Setelah meninjau transaksi dengan fprint=1866043996151916800, Anda dapat menggunakan kolom read_columns dan write_constructive_columns untuk mengidentifikasi bagian kode aplikasi yang memicu transaksi. Kemudian, Anda dapat melihat DML pokok yang tidak memfilter kunci utama, SingerId. Hal ini menyebabkan pemindaian tabel penuh dan mengunci tabel hingga transaksi dilakukan.

Untuk mengatasi konflik kunci, Anda dapat melakukan hal berikut:

1. Gunakan transaksi hanya baca untuk mengidentifikasi nilai SingerId yang diperlukan.
2. Gunakan transaksi baca-tulis terpisah untuk memperbarui baris untuk nilai SingerId yang diperlukan.

Menerapkan praktik terbaik untuk mengurangi persaingan kunci

Dalam contoh skenario kami, kami dapat menggunakan statistik smart lock dan statistik transaksi untuk mempersempit masalah kami pada transaksi yang tidak menggunakan kunci utama tabel kami saat melakukan pembaruan. Kami menemukan ide untuk meningkatkan kualitas transaksi berdasarkan apakah kami mengetahui kunci baris yang ingin diperbarui sebelumnya atau tidak.

Saat melihat potensi masalah dalam solusi Anda, atau bahkan saat mendesain solusi, pertimbangkan praktik terbaik berikut untuk mengurangi jumlah konflik penguncian dalam database Anda.

• Hindari pembacaan dalam jumlah besar di dalam transaksi baca-tulis.

• Gunakan transaksi hanya baca jika memungkinkan, karena transaksi ini tidak memperoleh kunci apa pun.

• Hindari pemindaian tabel penuh dalam transaksi baca-tulis. Hal ini mencakup penulisan DML bersyarat pada kunci utama atau penetapan rentang kunci tertentu saat menggunakan Read API.

• Jaga agar periode penguncian tetap singkat dengan melakukan perubahan sesegera mungkin setelah Anda membaca data dalam transaksi baca-tulis. Transaksi Baca-tulis menjamin bahwa data tetap tidak berubah setelah Anda membaca data hingga Anda berhasil melakukan perubahan. Untuk mencapainya, transaksi memerlukan penguncian sel data selama pembacaan dan selama penerapan. Akibatnya, jika Anda dapat menjaga periode penguncian tetap singkat, transaksi cenderung tidak mengalami konflik penguncian.

• Lebih baik lakukan transaksi kecil daripada transaksi besar, atau pertimbangkan DML yang Dipartisi untuk transaksi DML yang berjalan lama. Transaksi yang berjalan lama akan memperoleh kunci dalam waktu yang lama, jadi pertimbangkan untuk memecah transaksi yang menyentuh ribuan baris menjadi beberapa transaksi yang lebih kecil yang memperbarui ratusan baris jika memungkinkan.

• Jika Anda tidak memerlukan jaminan yang diberikan oleh transaksi baca-tulis, hindari membaca data apa pun dalam transaksi baca-tulis sebelum melakukan perubahan, misalnya, dengan membaca data dalam transaksi hanya baca yang terpisah. Sebagian besar konflik penguncian terjadi karena jaminan yang kuat, untuk memastikan data tetap tidak berubah antara pembacaan dan penerapan. Jadi, jika transaksi baca-tulis tidak membaca data apa pun, transaksi tersebut tidak perlu mengunci sel dalam waktu yang lama.

• Tentukan hanya kumpulan kolom minimal yang diperlukan dalam transaksi baca-tulis. Karena kunci Spanner adalah per sel data, saat transaksi baca-tulis membaca kolom yang berlebihan, transaksi tersebut akan memperoleh kunci ReaderShared pada sel ini. Hal ini dapat menyebabkan konflik kunci saat transaksi lain mendapatkan kunci WriterShared pada operasi tulis ke kolom yang berlebihan. Misalnya, pertimbangkan untuk menentukan sekumpulan kolom, bukan * saat membaca.

• Minimalkan panggilan API dalam transaksi baca-tulis. Latensi panggilan API dapat menyebabkan pertentangan kunci di Spanner, karena panggilan API tergantung pada penundaan jaringan serta penundaan sisi layanan. Sebaiknya lakukan panggilan API di luar transaksi baca-tulis jika memungkinkan. Jika Anda harus menjalankan panggilan API di dalam transaksi baca-tulis, pastikan untuk memantau latensi panggilan API Anda untuk meminimalkan dampak pada periode pengambilan kunci.

• Ikuti praktik terbaik desain skema.

Langkah berikutnya

• Pelajari alat Introspeksi lainnya.
• Pelajari informasi lain yang disimpan Spanner untuk setiap database dalam tabel skema informasi database.
• Pelajari lebih lanjut praktik terbaik SQL untuk Spanner.