MySQL'de Tablo Kilit Çakışmalarını İzlemek

· Murat Erol · 3 dk okuma · MySQL , Performans , Veritabanı

Selamlar, bu yazıda MySQL'de tablo kilit çakışmalarını (table lock contention) nasıl tespit ederiz, ona bakacağız. Konu kuru gibi gözüküyor olabilir ama eminim aklınıza şu sahne gelmiştir: sabah saat 9'u biraz geçer, dashboard'larda kırmızı alarm patlar, kullanıcılardan 'sayfa açılmıyor' mesajları yağar, oysa CPU rahat, disk rahat, ağ rahat. İşte bu klasik bir kilit çakışması semptomudur. Hadi başlayalım.

Önce semptomu doğru okuyalım

Bir sorgunun yavaşladığı her duruma 'kilit var galiba' demek doğru değil. Yavaşlık çoğu zaman index eksiğinden, plan değişikliğinden, ya da bir batch job'ın ortalığı süpürmesinden kaynaklanır. Kilit çakışmasının imzası farklı: aynı sorgu bazen 5ms'de dönüyor, bazen 8 saniye bekliyor, bazen de timeout yiyor. Yani süre dağılımı bimodal. Bunu Grafana'da histogram olarak çizdiğinizde iki tepeli bir şey görürseniz büyük ihtimalle kilit söz konusudur.

Bir başka işaret de şu: yavaşlama belirli saatlere bağlı. Sabah 9'da rapor cron'u koşuyor ve LOCK TABLES ile uzunca bir süre tabloyu tutuyorsa, o saatte gelen yazma istekleri kuyruğa girer. Diğer saatlerde her şey yağ gibi.

Global sayaçlara hızlı bakış

İlk durağımız performance_schema.global_status. Burada iki sayaç var, ikisini de oranlamamız lazım:

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Table_locks_waited', 'Table_locks_immediate');

Table_locks_immediate beklemeden alınan kilitler, Table_locks_waited ise sıraya girmek zorunda kalanlar. Tek başlarına bir anlam ifade etmezler, oranlamak gerek:

SELECT ROUND(waited / (waited + immediate) * 100, 2) AS lock_wait_ratio_pct
FROM (
  SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Table_locks_waited')    AS waited,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Table_locks_immediate') AS immediate
) t;

Şahsi kanaatim, %1'in altı normal, %1-5 arası 'gözünü aç', %5 üstü 'müdahale et'. Ama bu sayaç MySQL açıldığından beri kümülatif olduğu için tek bir okumayla yanıltıcı olabilir; iki dakika arayla iki ölçüm alıp delta'sına bakmak daha sağlıklı.

Suçlu tabloyu bulmak

Oran yüksekse, suçlu tabloyu bulmamız lazım. performance_schema.table_lock_waits_summary_by_table bunu söyler:

SELECT
  object_schema,
  object_name,
  COUNT_READ + COUNT_WRITE AS total_accesses,
  SUM_TIMER_WAIT / 1e12   AS total_wait_sec
FROM performance_schema.table_lock_waits_summary_by_table
WHERE SUM_TIMER_WAIT > 0
ORDER BY total_wait_sec DESC
LIMIT 10;

Tipik olarak listenin tepesinde bir-iki tablo durur. O tabloya SHOW CREATE TABLE çekip engine'ine bakın - hâlâ MyISAM ise hikaye orada bitiyor zaten.

Anlık çatışmayı yakalamak

Şu an ne oluyor sorusunun cevabı için sys şemasındaki view daha pratik:

SELECT
  waiting_pid     AS waiting_thread,
  waiting_query,
  blocking_pid    AS blocking_thread,
  blocking_query
FROM sys.schema_table_lock_waits;

InnoDB tarafında satır kilidi şüpheniz varsa SHOW ENGINE INNODB STATUS\G çıktısının LATEST DETECTED DEADLOCK ve TRANSACTIONS bölümlerine bakın; ya da performance_schema.data_locks ile data_lock_waits join'i daha modern yöntem:

SELECT r.trx_id AS waiting_trx, b.trx_id AS blocking_trx, w.OBJECT_NAME
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID  = b.trx_id;

Sık karşılaşılan tuzaklar

  • MyISAM tablosunu InnoDB sanmak: Eski bir tablo yıllar önce import edilmiş olabilir, kimse engine'e bakmamıştır. ALTER TABLE <TblName> ENGINE=InnoDB çoğu zaman tek başına problemi çözer.
  • LOCK TABLES'ı uygulama kodunda bırakmak: Eski PHP scriptlerinde sıkça görülür. Transaction + SELECT ... FOR UPDATE neredeyse her zaman daha iyi.
  • DDL'i mesai saatinde almak: ALTER TABLE varsayılan olarak metadata lock alır. MySQL 8'de ALGORITHM=INPLACE, LOCK=NONE kombinasyonu mümkünse onu seçin.
  • Sayacı tek seferlik okumak: Kümülatif değer servis açıldığından beri birikir; yorumlamak için delta lazım. mysqladmin extended-status ile watch -n 5 kombinasyonu ya da basit bir bash döngüsü iş görür.

Kapanış

Tablo kilit çakışması çoğunlukla mimari değil, miras bir kararın ürünü - eski engine, eski LOCK TABLES çağrısı, kötü zamanlanmış DDL. Bence vakit harcanması gereken yer bu üç kalem. Önce ölçün, sonra suçluyu bulun, sonra çözün; sırasını şaşırmayın. Umarım faydalı olur, bir sonraki yazıda görüşmek üzere.