Dalam posting ini, kita akan belajar tentang alat yang tersedia untuk menyelidiki masalah dengan kunci InnoDB yang mencegah kueri dijalankan. Show Dua perintah yang dapat digunakan di semua versi MySQL untuk menyelidiki kunci yang mencegah kueri InnoDB untuk melanjutkan adalah SHOW [FULL] PROCESSLIST dan SHOW ENGINE INNODB STATUS. Selain itu di MySQL 5.1 menggunakan Plugin InnoDB dan di MySQL 5.5 dan yang lebih baru, dimungkinkan untuk menggunakan tabel InnoDB di Skema Informasi untuk mendapatkan detail tentang kunci InnoDB. Masing-masing metode ini akan dibahas di bawah ini.
MENUNJUKKAN [FULL] DAFTAR PROSESKeluaran daftar proses adalah alat paling dasar untuk menyelidiki masalah penguncian InnoDB. Sebagai contoh: mysql> SHOW PROCESSLIST; +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 4 | Waiting for next activation | NULL | | 328 | root | localhost | locktest | Sleep | 222 | | NULL | | 332 | root | localhost | locktest | Query | 38 | updating | UPDATE t1 SET val="c2" WHERE id = 3 | | 340 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ 4 rows in set (0.00 sec) Jadi dalam hal ini mencurigakan bahwa pembaruan baris tunggal menggunakan KUNCI UTAMA (kolom t1.id) untuk menemukan baris membutuhkan waktu 38 detik. Kueri kandidat yang dapat mencegah UPDATE untuk melanjutkan setidaknya harus lebih lama dari waktu yang telah menunggu UPDATE, namun itu bisa sulit ditentukan dari daftar proses. Masalahnya adalah bahwa InnoDB bersifat transaksional, yang berarti bahwa itu mungkin bukan kueri yang berjalan saat ini yang memegang kunci. Misalnya mengambil output berikut: mysql> SHOW PROCESSLIST; +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 0 | Waiting for next activation | NULL | | 328 | root | localhost | locktest | Query | 1 | User sleep | SELECT COUNT(*) FROM t1 WHERE 0 = SLEEP(10) | | 332 | root | localhost | locktest | Query | 40 | updating | UPDATE t1 SET val="c2" WHERE id = 3 | | 340 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ 4 rows in set (0.00 sec) Ini adalah contoh yang sama dari atas, tetapi koneksi dengan Id = 328 belum mulai menjalankan kueri lain. Jadi di mana pada keluaran pertama dimungkinkan untuk menggunakan kolom Waktu untuk memesan kueri, pada keluaran kedua kolom Id tampaknya menjadi kandidat yang lebih baik. Namun bahkan itu mungkin terlalu sederhana: Sementara kolom Id akan memberi tahu urutan koneksi yang dibuat, itu adalah urutan pengambilan kunci. Ini sama sekali tidak mungkin untuk ditentukan dari daftar proses, jadi paling baik output SHOW PROCESSLIST dapat digunakan untuk memberikan petunjuk tentang apa yang sedang terjadi. Namun dalam kombinasi dengan output SHOW ENGINE INNODB STATUS, dapat memberikan informasi yang berharga. TAMPILKAN STATUS INNODB MESINOutput status InnoDB jauh lebih baik daripada daftar proses untuk memberikan wawasan tentang transaksi mana yang terkunci. Melanjutkan contoh dari atas, output dapat terlihat seperti: mysql> SHOW ENGINE INNODB STATUSG *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2013-02-25 11:05:21 7f5d5b6cd700 INNODB MONITOR OUTPUT ===================================== ... ------------ TRANSACTIONS ------------ Trx id counter 497211 Purge done for trx's n:o = 497195, sees Catatan: Output dari SHOW ENGINE INNODB STATUS bervariasi antar versi. Output di atas adalah dari MySQL 5.6.10. Dari output ini dapat dilihat query ‘UPDATE t1 SET val=”c2″ WEHRE id = 3’ lock mana yang sedang menunggu, dan dimungkinkan untuk melihat ada transaksi yang sudah aktif selama 600 detik yang memegang satu kunci (lihat 1 kunci baris), batalkan entri log 1). Jadi ini mengidentifikasi transaksi yang memegang kunci. Rincian dalam daftar transaksi juga mengungkapkan id utas MySQL (328 untuk transaksi yang memegang kunci). Anda dapat menggunakan informasi ini untuk kembali ke output daftar proses dan melihat misalnya nama pengguna dan host untuk koneksi yang menahan kunci. Agar InnoDB menyertakan informasi tentang kunci yang saat ini tidak ada konflik, Anda harus mengaktifkan opsi innodb_status_output_locks: mysql> SET GLOBAL innodb_status_output_locks = ON; Query OK, 0 rows affected (0.00 sec) Tip: Jika Anda menggunakan Connector/J (JDBC), Anda dapat meminta agar output SHOW ENGINE INNODB STATUS disertakan dalam pesan pengecualian saat terjadi kebuntuan. Untuk melakukannya, setel includeInnodbStatusInDeadlockExceptions=true saat membuat koneksi. Jika Anda mengaktifkan ini, Anda mungkin juga ingin mempertimbangkan untuk mengaktifkan includeThreadNamesAsStatementComment. Skema SistemDi MySQL 5.6 dan yang lebih baru Anda dapat menggunakan innodb_lock_waits dalam skema sys untuk menemukan transaksi yang menunggu kunci dan transaksi yang memegang kunci pemblokiran. Skema sys diinstal secara default di MySQL 5.7 dan yang lebih baru. Di MySQL 5.6 Anda harus menginstalnya secara manual. Tampilan innodb_lock_wais menggunakan tabel Skema Informasi yang sama seperti yang dibahas di bawah ini. Untuk menemukan semua kasus saat ini transaksi mana yang menunggu kunci dan siapa yang memblokir, Anda dapat menggunakan: mysql> SELECT * FROM sys.innodb_lock_waitsG
*************************** 1. row ***************************
wait_started: 2019-05-20 08:25:11
wait_age: 00:00:09
wait_age_secs: 9
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 6419
waiting_trx_started: 2019-05-20 08:25:11
waiting_trx_age: 00:00:09
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 10
waiting_query: UPDATE world.city SET Population = Population + 1 WHERE ID = 130
waiting_lock_id: 139676533684912:3:7:41:139676430164472
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 6415
blocking_pid: 9
blocking_query: NULL
blocking_lock_id: 139676533684016:3:7:41:139676430158520
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2019-05-20 08:20:56
blocking_trx_age: 00:04:24
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.00 sec)Dalam contoh di atas, blocking_query adalah NULL. Ini karena transaksi yang memegang kunci saat ini tidak menjalankan kueri apa pun. Di MySQL 5.6 dan yang lebih baru, kueri yang paling baru dieksekusi dapat ditemukan di tabel performance_schema.events_statements_current dan kueri lama menggunakan tabel riwayat pernyataan. Lihat juga Tabel Peristiwa Pernyataan Skema Kinerja di Manual Referensi. Kueri secara default akan dipotong menjadi 64 karakter atau seperti yang dikonfigurasikan dengan opsi statement_truncate_len Sys Schema. Untuk mengubah batas pemotongan untuk sesi, setel @sys.statement_truncate_len variabel, misalnya: mysql> SET @sys.statement_truncate_len = 16;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM sys.innodb_lock_waitsG
*************************** 1. row ***************************
wait_started: 2019-05-20 08:25:11
wait_age: 00:00:20
wait_age_secs: 20
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 6419
waiting_trx_started: 2019-05-20 08:25:11
waiting_trx_age: 00:00:20
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 10
waiting_query: UPDATE ... = 130
waiting_lock_id: 139676533684912:3:7:41:139676430164472
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 6415
blocking_pid: 9
blocking_query: NULL
blocking_lock_id: 139676533684016:3:7:41:139676430158520
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2019-05-20 08:20:56
blocking_trx_age: 00:04:35
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.00 sec)Agar kueri tidak dipotong, gunakan tampilan x$innodb_lock_waits sebagai gantinya. Catatan: Tabel information_schema.INNODB_TRX menyimpan paling banyak 1024 byte pertama dari kueri, jadi tidak mungkin mendapatkan lebih banyak kueri bahkan dengan tampilan x$innodb_lock_waits. Tabel Kunci Skema KinerjaDi MySQL 8, tabel Skema Informasi dengan kunci InnoDB yang dijelaskan setelah bagian ini diganti dengan tabel data_locks dan data_lock_wais di Skema Performa. Ini adalah tabel yang digunakan oleh tampilan sys.innodb_lock_waits di atas. Dalam kebanyakan kasus, disarankan untuk menggunakan tampilan skema sys karena lebih mudah digunakan. Namun, dalam beberapa kasus, Anda mungkin ingin menggunakan Skema Kinerja secara langsung untuk mendapatkan detail selengkapnya tentang kunci. Dua tabel untuk kunci data adalah:
Tidak seperti tabel Skema Informasi di 5.7 dan sebelumnya (lihat nanti), tabel data_locks berisi semua kunci InnoDB yang saat ini ditahan. Sebagai contoh: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE world.city SET Population = Population + 1 WHERE ID = 130; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM performance_schema.data_locks WHERE THREAD_ID = PS_CURRENT_THREAD_ID()G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139676533684016:1060:139676430161560
ENGINE_TRANSACTION_ID: 6413
THREAD_ID: 47
EVENT_ID: 15
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139676430161560
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139676533684016:3:7:41:139676430158520
ENGINE_TRANSACTION_ID: 6413
THREAD_ID: 47
EVENT_ID: 15
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139676430158520
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 130
2 rows in set (0.00 sec)Itu PS_CURRENT_THREAD_ID() tersedia di MySQL 8.0.16 dan yang lebih baru; sebelum itu gunakan sys.ps_thread_id(NULL). Seperti yang dapat dilihat, tabel Skema Kinerja mendukung mesin penyimpanan selain InnoDB, tetapi saat ini InnoDB adalah satu-satunya mesin penyimpanan yang telah menerapkan dukungan. Anda dapat melihat kunci level tabel dan kunci level baris dan apakah mode celah digunakan. Kolom LOCK_DATA memiliki nilai yang digunakan untuk menentukan baris mana yang akan dikunci. Karena kondisi dalam kueri UPDATE adalah WHERE ID = 130, LOCK_DATA memiliki nilai 130 dalam kasus ini. Untuk menyelidiki mengapa terjadi lock wait, Anda perlu menggunakan tabel data_lock_waits. Anda dapat bergabung kembali pada tabel data_locks untuk mendapatkan informasi lebih lanjut tentang kunci yang terlibat. Ada dua kolom di data_lock_waits yang secara efektif merupakan kunci asing dari kolom ENGINE_LOCK_ID dari tabel data_locks:
Contoh cara mengkueri kunci tunggu (berdasarkan tampilan sys.innodb_lock_waits) adalah: SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
CONCAT(sys.quote_identifier(rl.object_schema), '.', sys.quote_identifier(rl.object_name)) AS locked_table,
rl.object_schema AS locked_table_schema,
rl.object_name AS locked_table_name,
rl.partition_name AS locked_table_partition,
rl.subpartition_name AS locked_table_subpartition,
rl.index_name AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
rl.engine_lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
bl.engine_lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = CAST(w.blocking_engine_transaction_id AS CHAR)
INNER JOIN information_schema.innodb_trx r ON r.trx_id = CAST(w.requesting_engine_transaction_id AS CHAR)
INNER JOIN performance_schema.data_locks bl ON bl.engine_lock_id = w.blocking_engine_lock_id
INNER JOIN performance_schema.data_locks rl ON rl.engine_lock_id = w.requesting_engine_lock_id
ORDER BY r.trx_wait_started;Perhatikan dua gabungan terakhir antara tabel data_lock_waits dan data_locks. Kueri juga bergabung pada tampilan information_schema.innodb_trx. Itu akan dibahas di bagian selanjutnya. Tabel Skema Informasi InnoDBDengan MySQL 5.1 menggunakan Plugin InnoDB dan di MySQL 5.5 dan yang lebih baru, ada cara lain untuk mendapatkan informasi tentang kunci. Tiga tabel menarik untuk menyelidiki kunci:
Di MySQL 5.1 dengan Plugin InnoDB, tabel Skema Informasi tidak diaktifkan secara default dan harus diaktifkan secara manual, misalnya mengatur opsi konfigurasi pemuatan plugin seperti: [mysqld] plugin-load = "innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_buffer_page=ha_innodb_plugin.so;innodb_buffer_page_lru=ha_innodb_plugin.so;innodb_buffer_pool_stats=ha_innodb_plugin.so" Nilai untuk pemuatan plugin semuanya harus dalam satu baris. Keuntungan dari tabel Skema Informasi dibandingkan daftar proses dan keluaran status InnoDB adalah memungkinkan untuk menulis kueri yang secara langsung memberikan informasi tentang transaksi dan penguncian yang terlibat dalam skenario kunci tunggu. Contoh kueri semacam itu adalah: SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
rl.lock_index AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
r.trx_query AS waiting_query,
rl.lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
b.trx_query AS blocking_query,
bl.lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_startedGUntuk contoh yang dibahas tentang, output dari kueri ini adalah: *************************** 1. row ***************************
wait_started: 2015-11-24 11:29:48
wait_age: 00:00:04
wait_age_secs: 4
locked_table: `locktest`.`t1`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 5399
waiting_trx_started: 2015-11-24 11:29:48
waiting_trx_age: 00:00:04
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 3
waiting_query: UPDATE t1 SET val="c2" WHERE id = 3
waiting_lock_id: 5399:45:3:6
waiting_lock_mode: X
blocking_trx_id: 5397
blocking_pid: 5
blocking_query: NULL
blocking_lock_id: 5397:45:3:6
blocking_lock_mode: X
blocking_trx_started: 2015-11-24 11:28:49
blocking_trx_age: 00:01:03
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 5
sql_kill_blocking_connection: KILL 5
1 row in set (0.00 sec)Removed: Tabel kunci InnoDB di Skema Informasi telah dihapus pada MySQL 8 dan diganti dengan tabel di Skema Kinerja (lihat di atas). Di MySQL 5.6 dan yang lebih baru dengan skema sys terinstal, umumnya disarankan untuk menggunakan tampilan sys.innodb_lock_waits sebagai gantinya. Tampilan itu telah diperbarui di MySQL 8, sehingga menggunakan tabel baru. Lihat di atas untuk detail penggunaan tampilan. Di MySQL 5.7.14 dan yang lebih baru, tabel Skema Informasi akan menyebabkan peringatan penghentian. Peringatan tersebut dimaksudkan untuk memastikan Anda mengetahui bahwa di MySQL 8, Anda perlu menggunakan tabel Skema Kinerja. |
Ada tidak mysql 5.7 full processlist
Pos Terkait
Copyright © 2024 toptenid.com Inc.
