MariaDB SHOW PROCESSLIST ve KILL ile Takılan Sorguları Yönetmek

SHOW PROCESSLIST ne gösterir?

Belgelere göre SHOW PROCESSLIST çalışan iş parçacıklarını listeler. MariaDB'de her bağlantı mariadbd içinde ayrı bir iş parçacığında çalışır; dolayısıyla listedeki her satır bir bağlantı ya da sunucunun kendi iç iş parçacıklarından biridir. PROCESS yetkiniz varsa bütün iş parçacıklarını, yoksa yalnızca kendi hesabınıza ait olanları görürsünüz. FULL anahtar sözcüğü olmadan Info sütununda ifadenin yalnızca ilk 100 karakteri gösterilir; uzun sorguları incelerken bu yüzden SHOW FULL PROCESSLIST kullanın.

SHOW [FULL] PROCESSLIST

Belgelerdeki örnek çıktıda iki satır var: biri olay zamanlayıcısının iç iş parçacığı, diğeri komutu çalıştıran oturumun kendisi.

SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             | Progress |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
|  2 | event_scheduler | localhost | NULL | Daemon  | 2693 | Waiting on empty queue | NULL             |    0.000 |
|  4 | root            | localhost | NULL | Query   |    0 | Table lock             | SHOW PROCESSLIST |    0.000 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+

Command değerlerinin anlamları belgelerde ayrı bir sayfada listelenir. En sık gördükleriniz şunlardır: Sleep istemcinin yeni bir ifade göndermesini bekleyen boşta bağlantıdır; Query bir ifadeyi çalıştırmaktadır; Daemon bir istemciye değil sunucunun kendisine hizmet eden iç iş parçacığıdır; Binlog Dump primary'de bir replikaya binary log gönderen iş parçacığıdır. State sütununda ise Sending data, Creating tmp table, Copying to tmp table on disk, Waiting for global read lock veya Killed gibi değerler görürsünüz. Örneğin Killed, belgelere göre iş parçacığının bir sonraki 'öldürüldü' bayrağı kontrolünde duracağı ve tuttuğu kilitlerin bırakılmasını beklediği anlamına gelir.

Boşta görünen bir bağlantı zararsız sanılabilir, ama Sleep durumundaki bir bağlantının açık bir işlemi olabilir. Böyle bir bağlantı tablolar üzerinde metadata kilidi tutmaya devam eder ve aşağıda anlatılan beklemelerin en yaygın sebebidir. Bu yüzden Time değeri çok büyük olan Sleep satırlarına özellikle dikkat edin.

INFORMATION_SCHEMA.PROCESSLIST ile süzmek

Aynı bilgi INFORMATION_SCHEMA.PROCESSLIST tablosunda ve mariadb-admin processlist komutunda da bulunur. Tablonun avantajı SQL ile süzülüp sıralanabilmesi ve ek sütunlar içermesidir: TIME_MS süreyi milisaniye cinsinden mikro saniye hassasiyetle verir, MEMORY_USED ve MAX_MEMORY_USED iş parçacığının bellek kullanımını, EXAMINED_ROWS incelenen satırları, QUERY_ID sorgu kimliğini gösterir. Belgelere göre SENT_ROWS sütunu MariaDB 11.3.0'dan, TMP_SPACE_USED sütunu MariaDB 11.5'ten itibaren vardır.

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
  FROM INFORMATION_SCHEMA.PROCESSLIST
 WHERE COMMAND <> 'Sleep'
 ORDER BY TIME DESC;

Bu sorgu boşta bekleyen bağlantıları dışarıda bırakıp en uzun süredir çalışanları en üste getirir. Belgeler birkaç inceliğe dikkat çeker. Birincisi, MariaDB'de TIME ve TIME_MS sütunları @TIMESTAMP ayarından etkilenmez; bu MySQL'den bir farktır ve süre bu sayede güvenilir biçimde ölçülür. Ama aynı sebeple bu sütun bir replikanın ne kadar geride olduğunu göstermez; bunun için SHOW REPLICA STATUS çıktısındaki Seconds_Behind_Master alanına bakılır. İkincisi, SHOW PROCESSLIST içindeki Progress toplam ilerlemeyi, tablodaki PROGRESS ise yalnızca mevcut aşamanın ilerlemesini gösterir. Üçüncüsü, bu tabloya yapılan sorgular kilit aldığı için Performance Schema açıksa belgeler onun THREADS tablosunu sorgulamayı önerir.

Sunucu --skip-grant-tables ile başlatılmışsa belgelere göre tablonun HOST sütunu her oturum için boş dize olur, SHOW PROCESSLIST ise bağlantının ana bilgisayar adını göstermeye devam eder. Bu kipte HOST sütununa göre süzme güvenilir değildir; root parolası sıfırlarken bu durumla karşılaşabilirsiniz (bkz. root şifresi sıfırlama).

KILL ile sorgu ve bağlantı sonlandırmak

KILL [HARD | SOFT] { {CONNECTION|QUERY} thread_id | QUERY ID query_id | USER user_name }

KILL ifadesinin dört biçimi vardır. KILL CONNECTION, değiştirici yazılmamış KILL ile aynıdır ve verilen iş parçacığına ait bağlantıyı sonlandırır. KILL QUERY bağlantıyı açık bırakıp yalnızca o an çalışan ifadeyi durdurur. KILL QUERY ID iş parçacığı kimliği yerine INFORMATION_SCHEMA.PROCESSLIST tablosundaki QUERY_ID ile çalışır; sorgu kimliği bağlantıyı değil belirli bir sorguyu gösterdiği için, siz komutu yazarken bağlantı başka bir sorguya geçtiyse yanlış sorguyu durdurmazsınız. KILL ... USER ise bir kullanıcının bütün bağlantılarını ve sorgularını sonlandırır; kullanıcı adı tek başına, kullanici@host biçiminde ya da CURRENT_USER olarak verilebilir.

KILL QUERY 42;
KILL 42;
KILL USER 'raporcu';

İşlemlerle ilişkisi önemlidir. Belgelere göre etkin bir işlemi olan bağlantı sonlandırılırsa işlem geri alınır. Yalnızca sorgu öldürülürse işlem açık kalır; uygulama bağlantıyı kullanmaya devam ederse ya COMMIT ya ROLLBACK yapması gerekir. Bu yüzden kilit tutan boşta bir bağlantıyı serbest bırakmak için KILL QUERY işe yaramaz, bağlantının kendisini kapatmanız gerekir.

HARD (varsayılan) ifadeyi olabildiğince çabuk durdurur. SOFT ise tabloyu tutarsız bırakabilecek kritik işlemleri yarıda kesmez; belgeler MyISAM ve Aria tablolarında REPAIR TABLE ya da dizin oluşturma gibi işlemleri öldürmenin tablonun bozulmasına yol açabileceğini ve bunu önlemek için SOFT kullanılmasını önerir. Yetki tarafında, PROCESS yetkisiyle bütün iş parçacıklarını görebilir, CONNECTION ADMIN yetkisiyle hepsini sonlandırabilirsiniz; bu yetkiler yoksa yalnızca kendi iş parçacıklarınızı görür ve sonlandırırsınız.

Belgelerde geçen hata iletileri şunlardır. Var olmayan bir iş parçacığı kimliği verirseniz:

ERROR 1094 (HY000): Unknown thread id: <thread_id>

Var olmayan bir sorgu kimliği verirseniz:

ERROR 1957 (HY000): Unknown query id: <query_id>

Bağlantısı sonlandırılan istemci ise şu hatayı alır:

ERROR 1317 (70100): Query execution was interrupted

Kullanıcı adıyla verilen KILL USER, bağlı olmayan ya da hiç var olmayan kullanıcılar için hata üretmez; sonucun işe yarayıp yaramadığını belgelere göre ROW_COUNT() ile kontrol edebilirsiniz. Aynı işi kabuktan yapmak için mariadb-admin processlist ve mariadb-admin kill komutları vardır:

mariadb-admin processlist
mariadb-admin kill 42

Metadata kilitleri

MariaDB metadata kilitlemesi uygular: bir işlem bir tabloyu kullandığında, işlem bitene kadar tablonun tanımını (metadata) kilitler. Belgelere göre bu, işlemsel olmayan tablolar, view'lar ve kilitli tablolarla ilişkili saklı fonksiyonlar ile tetikleyiciler için de geçerlidir. Kilitli bir tabloyu değiştiren ALTER TABLE gibi bir DDL kuyruğa girer ve kilit kalkana kadar bekler; LOCK TABLES ... WRITE da aynı şekilde kuyruğa girer. Önemli bir ayrıntı: savepoint'e kısmi geri dönüş kilidi bırakmaz, kilit işlemin sonuna kadar kalır.

Belgelerdeki örnek bunu iki bağlantıyla gösterir. İşlemsel olmayan bir MEMORY tablosu kullanılır; birinci bağlantı bir işlem başlatıp satır ekler:

CREATE TABLE t (a INT) ENGINE = MEMORY;
START TRANSACTION;
INSERT INTO t SET a=1;

İkinci bağlantı tabloya sütun eklemeye çalışır ve istemcisi bekler:

ALTER TABLE t ADD COLUMN b INT;

Birinci bağlantı COMMIT yaptığında ikinci bağlantının komutu sonunda tamamlanır. Belgelerdeki çıktı, beklemenin süresini de gösterir:

COMMIT;

Query OK, 1 row affected (35.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

Tablo işlemsel olmasa bile kilidin işlem sonuna kadar tutulduğuna dikkat edin. Gerçek hayatta en sık senaryo, bir uygulamanın veya açık unutulmuş bir konsol oturumunun START TRANSACTION sonrasında bir SELECT yapıp beklemesidir. O sırada çalıştırılan bir şema değişikliği, bu işlem bitene kadar kuyrukta bekler ve dışarıdan bakınca 'takılmış' gibi görünür.

Bekleme süresinin üst sınırını lock_wait_timeout belirler (saniye cinsinden). Süre aşılırsa şu hata döner:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Varsayılan değer konusunda belgelerde iki farklı bilgi var: metadata kilitleme sayfası 31536000 (1 yıl) derken sistem değişkenleri sayfası 86400 (1 gün) gösterir. Hangisi olursa olsun pratikte çok uzundur; sunucunuzdaki değeri SELECT @@lock_wait_timeout; ile görebilir, şema değişikliği yapan oturumda daha kısa bir değer verebilirsiniz. Böylece DDL sonsuza kadar beklemek yerine hata verip çıkar ve arkasında birikme olmaz.

SET SESSION lock_wait_timeout = 10;
ALTER TABLE t ADD COLUMN b INT;

Kilidi kimin tuttuğunu bulmak

METADATA_LOCK_INFO eklentisi, etkin metadata kilitlerini gösteren INFORMATION_SCHEMA.METADATA_LOCK_INFO tablosunu oluşturur. Eklentinin kütüphanesi MariaDB ile gelir ama varsayılan olarak kurulu değildir. Sunucuyu yeniden başlatmadan kurmak için:

INSTALL SONAME 'metadata_lock_info';

Kalıcı olması için seçenek dosyasına plugin_load_add = metadata_lock_info eklenebilir. Belgelerdeki örnek çıktıda 31 numaralı iş parçacığının dbname.exotics tablosunda bir tablo metadata kilidi tuttuğu görülür:

SELECT * FROM information_schema.metadata_lock_info;
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
| THREAD_ID | LOCK_MODE                | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA    | TABLE_NAME  |
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Global read lock     |                 |             |
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Commit lock          |                 |             |
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Schema metadata lock | dbname          |             |
|        31 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT  | Table metadata lock  | dbname          | exotics     |
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
4 rows in set (0.00 sec)

THREAD_ID değeri SHOW PROCESSLIST çıktısındaki Id ile eşleşir; böylece kilidi tutan bağlantıyı bulup gerekirse KILL ile kapatabilirsiniz. Belgeler ayrıca PROCESSLIST ile METADATA_LOCK_INFO tablolarını birleştirip 'şu iş parçacığı şunun tarafından kilitlenmiş' cümleleri üreten bir sorgu örneği de verir. Performance Schema kullanıyorsanız metadata_locks tablosu da aynı bilgiyi içerir.

InnoDB satır kilidi beklemeleri

Metadata kilidi tablonun yapısıyla ilgilidir; iki işlemin aynı satırı değiştirmeye çalışması ise InnoDB satır kilitleriyle ilgilidir. Belgelere göre bir InnoDB işlemi satır kilidi için innodb_lock_wait_timeout saniye kadar bekler (varsayılan 50), sonra yukarıdakiyle aynı metinli 1205 hatasını alır. Bu durumda işlemin tamamı değil yalnızca son ifade geri alınır; işlemin tamamının geri alınması için innodb_rollback_on_timeout ayarı vardır. İşlem mantığı ve hata kodları için transaction yazısı ile sık hatalar yazısına bakabilirsiniz.

Kimin kimi beklettiğini görmek için INFORMATION_SCHEMA.INNODB_TRX tablosu o anda çalışan bütün InnoDB işlemlerini listeler. TRX_STATE sütunu RUNNING, LOCK WAIT, ROLLING BACK veya COMMITTING değerlerinden birini alır, TRX_STARTED işlemin başlama zamanını, TRX_MYSQL_THREAD_ID ise PROCESSLIST tablosundaki iş parçacığı kimliğini verir. INNODB_LOCK_WAITS tablosu bekleyen ve engelleyen işlem kimliklerini eşleştirir ve PROCESS yetkisi ister. Aşağıdaki sorgu, bekleyen her işlem için onu engelleyen işlemin iş parçacığını getirir:

SELECT w.REQUESTING_TRX_ID, r.TRX_MYSQL_THREAD_ID AS bekleyen,
       w.BLOCKING_TRX_ID, b.TRX_MYSQL_THREAD_ID AS engelleyen,
       b.TRX_STARTED, b.TRX_QUERY
  FROM information_schema.INNODB_LOCK_WAITS w
  JOIN information_schema.INNODB_TRX r ON r.TRX_ID = w.REQUESTING_TRX_ID
  JOIN information_schema.INNODB_TRX b ON b.TRX_ID = w.BLOCKING_TRX_ID;

Belgeler bu tabloların işlem süreç listesinden farklı bir anlık görüntü kullandığını, bu yüzden bir kaydın birinde görünüp ötekinde görünmeyebileceğini not eder. TRX_QUERY işlemin o anda çalıştırdığı SQL'i gösterdiği için, engelleyen işlemde bu değer boşsa işlem o an bir ifade çalıştırmıyor demektir; çoğu zaman yukarıda anlatılan açık unutulmuş işlem senaryosudur. Kilitlenme (deadlock) yaşandıysa SHOW ENGINE INNODB STATUS çıktısındaki LATEST DETECTED DEADLOCK bölümü, belgelere göre ilgili işlemleri, çalıştırdıkları ifadeleri, tuttukları ve istedikleri kilitleri ve geri alınan işlemi gösterir.

Uzun sorguları baştan sınırlamak

Sorgu çıktıktan sonra öldürmek yerine üst sınır koymak çoğu zaman daha iyidir. max_statement_time sıfırdan farklı bir değere ayarlandığında sunucu bu süreyi (saniye) aşan sorguları durdurmaya çalışır; varsayılan 0'dır, yani sınır yoktur. Belgelere göre değer kesirli olabilir (10 milisaniye için 0.01), genel, oturum, kullanıcı ya da tek sorgu düzeyinde verilebilir ve saklı yordamların içindeki ifadeler hariç bütün sorgulara uygulanır. Durdurulan sorgu içinde bulunduğu işlemi ya da bağlantıyı etkilemez. Tek bir sorgu için SET STATEMENT kullanılır:

SET STATEMENT max_statement_time=100 FOR
  SELECT field1 FROM table_name ORDER BY field1;

MariaDB 12.0'dan itibaren bir SELECT ayrıca milisaniye cinsinden MAX_EXECUTION_TIME optimizer ipucuyla sınırlanabilir. Kullanıcı bazında sınır için GRANT ... MAX_STATEMENT_TIME sözdizimi vardır. Belgeler bir uyarı da ekler: durdurma anlık değildir, sunucu zamanlayıcıyı yürütme sırasında belirli noktalarda kontrol eder; bazı işlemler bu kontrolü uzun süre yapmadığı için sorgu sınırı belirgin biçimde aşabilir. Bu yüzden belgeler max_statement_time'ı kaynak tükenmesine karşı tek koruma olarak görmemeyi önerir. Replikalar bu değişkenden etkilenmez; MariaDB 10.10'dan itibaren replikalar için slave_max_statement_time vardır.

SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM t1;

Açık unutulan işlemler için idle_transaction_timeout değişkeni, boşta bekleyen bir işlemi olan bağlantıyı belirtilen saniye sonunda kapatır; varsayılan 0 olduğu için bağlantılar hiç kapatılmaz. Salt okunur ve yazma işlemleri için ayrı değerler veren idle_readonly_transaction_timeout ve idle_write_transaction_timeout da vardır. Bu ayar yukarıdaki metadata kilidi senaryosunun kalıcı çözümlerinden biridir, ama uygulamanız uzun süre açık işlem tutuyorsa hata almaya başlayabilir; değeri uygulamanın davranışına göre seçin.

Uygulamalı örnek: takılan ALTER TABLE

Bir ALTER TABLE siparis ADD COLUMN ... komutu dakikalardır dönmüyor diyelim. Adım adım ilerleyelim. Önce başka bir oturumdan süreç listesine bakarsınız; ALTER satırını ve uzun süredir Sleep durumunda duran bağlantıları görürsünüz. Ardından açık InnoDB işlemlerini, en eskisi üstte olacak şekilde listelersiniz:

SHOW FULL PROCESSLIST;
SELECT TRX_MYSQL_THREAD_ID, TRX_STATE, TRX_STARTED, TRX_QUERY
  FROM information_schema.INNODB_TRX
 ORDER BY TRX_STARTED;

Çok önce başlamış ve TRX_QUERY değeri boş bir işlem görürseniz, bu genellikle tabloya dokunup işlemini bitirmemiş bir bağlantıdır. METADATA_LOCK_INFO eklentisi kuruluysa o iş parçacığının siparis tablosunda kilit tuttuğunu da doğrulayabilirsiniz. Bağlantının kime ait olduğunu User ve Host sütunlarından öğrenip, mümkünse uygulama tarafında işlemi bitirttirin. Mümkün değilse bağlantıyı kapatırsınız; açık işlemi geri alınır ve ALTER devam eder:

KILL 1234;

Sonraki sefer için iki önlem alırsınız: şema değişikliklerini kısa bir lock_wait_timeout ile çalıştırmak ve uygulamada açık kalan işlemleri idle_transaction_timeout ile sınırlamak. Büyük tablolarda ALTER TABLE'ın kendisinin nasıl daha az kilitle çalıştırılacağı ALTER TABLE yazısında anlatılıyor.

Sık yapılan hatalar

• FULL olmadan bakmak: Info sütunu 100 karakterde kesilir ve uzun sorguları tanıyamazsınız.
• Kilit tutan boşta bağlantıya KILL QUERY göndermek: Çalışan bir sorgu olmadığı için işe yaramaz ve işlem açık kalır; bağlantıyı KILL ile kapatın.
• Yanlış Id'yi öldürmek: Komutu yazarken bağlantı başka bir işe geçmiş olabilir. Sorgu düzeyinde güvenli hedefleme için KILL QUERY ID kullanın.
• MyISAM onarımını HARD ile kesmek: Belgelere göre tablo bozulabilir; SOFT kullanın.
• Replikasyon gecikmesini TIME sütunuyla ölçmek: Belgeler bunun yanlış olduğunu söyler; Seconds_Behind_Master alanına bakın.
• max_statement_time'a tamamen güvenmek: Durdurma anlık değildir ve bazı işlemler sınırı aşabilir.

Sık Sorulan Sorular

Neden bütün bağlantıları göremiyorum?

PROCESS yetkiniz yoksa belgelere göre yalnızca kendi hesabınıza ait iş parçacıklarını görürsünüz. Başkalarının bağlantılarını sonlandırmak için de CONNECTION ADMIN yetkisi gerekir. Yetki yönetimi için GRANT ve REVOKE yazısına bakın.

KILL sonrasında bağlantı Killed durumunda kalıyor, neden?

Belgelere göre Killed durumu, iş parçacığının 'öldürüldü' bayrağını bir sonraki kontrol edişinde duracağını ve bunun için tuttuğu kilitlerin bırakılmasını beklemesi gerektiğini gösterir. Sonlandırma anlık değildir; bağlantı etkin bir işlem içindeyse ayrıca bu işlem geri alınır. Satır bir süre listede görünmeye devam edebilir.

SHOW PROCESSLIST ile INFORMATION_SCHEMA.PROCESSLIST arasında fark var mı?

Temel bilgi aynıdır, ama tablo TIME_MS, MEMORY_USED, EXAMINED_ROWS ve QUERY_ID gibi ek sütunlar içerir ve SQL ile süzülebilir. Progress değerleri de farklı hesaplanır: ifade toplam ilerlemeyi, tablo yalnızca mevcut aşamanın ilerlemesini gösterir.

Belirli bir kullanıcının bütün bağlantılarını nasıl kapatırım?

KILL USER 'kullanici'; o kullanıcının bütün bağlantılarını ve sorgularını sonlandırır. Kullanıcı bağlı değilse hata vermez; etkiyi ROW_COUNT() ile kontrol edebilirsiniz.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · Transaction ve izolasyon seviyeleri · Hata ve yavaş sorgu günlükleri · ALTER TABLE · Sık hatalar ve çözümleri · EXPLAIN ve ANALYZE