テクノロジー共有|delete大表slave再生が遅い問題分析

作者:洪斌
に質問
master上でwhere条件のないdelete操作が実行され、この表は50万人以上のレコードを記録している.binlog_formatはmixedモードですがtransaction_isolationはRCモードなので、dml文はrowモードで記録されます.このテーブルには、プライマリ・キーに一意でないインデックスがありません.slave再生中に10時間以上も実行されなかった.
 
ぶんせき
まずslaveがrowモードでrelay logをどのように再生するかを理解します.rowモードでは、binlogにDML変更動作のイベント記述情報、BEFORE IMAGE、AFTER IMAGEが記録される.

+----------------------------+
| Header: table id |
| column information etc. |
+--------------+-------------+
| BEFORE IMAGE | AFTER IMAGE |
+--------------+-------------+
| BEFORE IMAGE | AFTER IMAGE |
+--------------+-------------+

DMLイベントタイプとimageの関係マトリクス

+------------------+--------------+-------------+
| EVENT TYPE | BEFORE IMAGE | AFTER IMAGE |
+------------------+--------------+-------------+
| WRITE_ROWS_EVENT | No | Yes |
+------------------+--------------+-------------+
| DELETE_ROWS_EVENT| Yes | No |
+------------------+--------------+-------------+
| UPDATE_ROWS_EVENT| Yes | Yes |
+------------------+--------------+-------------+

deleteとupdateには検索操作が含まれており、BIコンテンツ検索に基づいて対応するレコードを見つけて対応する操作を実行します.
rowモードbinlogに基づく再生は主にこの関数でRows_log_event::do_apply_イベントタイプに応じて対応するdo_を呼び出すイベントbefore_row_operations delete操作を例に
Delete_rows_log_event::do_before_row_operations、この関数はsql commandカウンタ(com_delete)を更新します.
次にRows_を呼び出しますlog_event::row_operations_scan_and_key_setupは必要なメモリ領域を割り当てる
Prepare memory structures for search operations. If search is performed:
1.using hash search => initialize the hash 2.using key => decide on key to use and allocate mem structures 3.using table scan => do nothing
どの検索ポリシーを選択するかは、Rows_によって異なります.log_event::decide_row_lookup_algorithm_and_keyの結果、その決定マトリクスはテーブルのインデックス情報とslave_に依存する.rows_search_Algorithmsパラメータの設定.Decision table:

I --> Index scan/search

T --> Table scan

H --> Hash scan

Hi --> Hash over index

Ht --> Hash over the entire table

|--------------+-----------+------+------+------|
| Index\Option | I , T , H | I, T | I, H | T, H |
|--------------+-----------+------+------+------|
| PK / UK | I | I | I | Hi |
| K | Hi | I | Hi | Hi |
| No Index | Ht | T | Ht | Ht |
|--------------+-----------+------+------+------|

デフォルトslave_rows_search_AlgorithmsはTABLE_SCAN,INDEX_SCAN、対応関数Rows_log_event::do_index_scan_and_update
INDEXならSCAN,HASH_SCAN、対応関数Rows_log_event::do_hash_scan_and_update
プライマリ・キーがない場合、binlogの各行のイベントを巡回し、そのイベントのBIで対応するレコードを検索し、AI情報に変更します.

for each row in the event do
{
search for the correct row to be modified using BI
replace the row in the table with the corresponding AI
}

HASHSCAN over tableの場合、binlogイベントのレコードに対してhashを実行し、hashテーブルに入れてから、テーブルの各行のレコードに対してhashを行い、hashテーブルのレコードと比較し、条件はAI部分を再生することに一致する.

for each row in the event do
{
hash the row.
}

for each row in the table do
{
key= hash the row;
if (key is present in the hash)
{
apply the AI to the row.
}
}

HASHSCAN over indexの場合、ユニークでないインデックスがある場合、binlogイベントのレコードに対してhashを実行する場合も、そのレコードのkeyを1つの重複したkeyリストセットに保存し、そのインデックスセットに基づいてレコードを検索し、見つかったレコードに対してhash操作を実行しhashテーブルのレコードと比較し、一致すればAI部分を再生する.

for each row in the event do
{
hash the row.
store the key in a list of distinct key.
}

for each row corresponding key values in the key list do
{
key= hash the row;
if (key is present in the hash)
{
apply the AI to the row.
}
}

以上の解析から,主キーがない場合にはHiの走査方式がHtとIndex scanよりも速くなることが推測される.
 
テスト
比較slave_rows_search_AlgorithmsはTABLE_SCAN,INDEX_SCANとINDEX_SCAN,HASH_SCANの2つのパラメータの設定の下で、deleteの大きい表のどの効率がもっと高いですか.

CREATE TABLE `ants_bnzbw_temp` (
`accrued_status` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`contract_no` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`business_date` date DEFAULT NULL,
`prin_bal` int(11) DEFAULT NULL COMMENT,
`ovd_prin_bal` int(11) DEFAULT NULL COMMENT ,
`ovd_int_bal` int(11) DEFAULT NULL COMMENT ,
`int_amt` int(11) DEFAULT NULL COMMENT ,
`ovd_prin_pnlt_amt` int(11) DEFAULT NULL COMMENT ,
`ovd_int_pnlt_amt` int(11) DEFAULT NULL COMMENT,
KEY `accrued_status` (`accrued_status`) USING BTREE,
KEY `contract_no` (`contract_no`) USING BTREE,
KEY `business_date` (`business_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

master [localhost] {msandbox} (test) > select count(*) from ants_bnzbw_temp;
+----------+
| count(*) |
+----------+
| 522490 |
+----------+
1 row in set (0.15 sec)

master [localhost] {msandbox} (test) > delete from ants_bnzbw_temp;
Query OK, 522490 rows affected (25.86 sec)

ホストslave 1

slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN'

トランザクション実行は約2000 s(トランザクション実行時間がリアルタイムで追跡されていない)

SET @@SESSION.GTID_NEXT= '00020594-1111-1111-1111-111111111111:237'/*!*/;
# at 221356832
#180102 14:04:48 server id 1 end_log_pos 221356895 CRC32 0xafdd018f Query thread_id=20 exec_time=25 error_code=0

---TRANSACTION 5582, ACTIVE 1447 sec
mysql tables in use 1, locked 1
2581 lock struct(s), heap size 319696, 799680 row lock(s), undo log entries 399840

呼び出しスタックサンプリング

frame #0: 0x000000010f94f331 mysqld`page_rec_get_next_low(unsigned char const*, unsigned long) + 81
frame #1: 0x000000010f94bc28 mysqld`row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) + 9192
frame #2: 0x000000010f86d27e mysqld`ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) + 734
frame #3: 0x000000010f036b6c mysqld`handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) + 140
frame #4: 0x000000010f6d5a94 mysqld`Rows_log_event::next_record_scan(bool) + 324
frame #5: 0x000000010f6d66cf mysqld`Rows_log_event::do_scan_and_update(Relay_log_info const*) + 159
frame #6: 0x000000010f6d7198 mysqld`Rows_log_event::do_apply_event(Relay_log_info const*) + 1064
frame #7: 0x000000010f718d42 mysqld`apply_event_and_update_pos(Log_event**, THD*, Relay_log_info*) + 530
frame #8: 0x000000010f711f46 mysqld`handle_slave_sql + 4438

ホストslave 2

slave_rows_search_algorithms='TABLE_SCAN,INDEX_SCAN'

トランザクションは11145 sを超え、まだ実行されていません.

---TRANSACTION 4520, ACTIVE 11145 sec
mysql tables in use 1, locked 1
622 lock struct(s), heap size 90320, 191792 row lock(s), undo log entries 95896

呼び出しスタックサンプリング

* frame #0: 0x0000000109fd9c3a mysqld`btr_search_s_lock(dict_index_t const*) + 58
frame #1: 0x0000000109fdb37f mysqld`btr_search_guess_on_hash(dict_index_t*, btr_search_t*, dtuple_t const*, unsigned long, unsigned long, btr_cur_t*, unsigned long, mtr_t*) + 479
frame #2: 0x0000000109fc84a9 mysqld`btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*) + 649
frame #3: 0x000000010a177324 mysqld`row_search_on_row_ref(btr_pcur_t*, unsigned long, dict_table_t const*, dtuple_t const*, mtr_t*) + 164
frame #4: 0x000000010a17746f mysqld`row_get_clust_rec(unsigned long, unsigned char const*, dict_index_t*, dict_index_t**, mtr_t*) + 175
frame #5: 0x000000010a1988e5 mysqld`row_vers_impl_x_locked(unsigned char const*, dict_index_t*, unsigned long const*) + 293
frame #6: 0x000000010a0f39db mysqld`lock_rec_convert_impl_to_expl(buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*) + 603
frame #7: 0x000000010a0f4914 mysqld`lock_sec_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, lock_mode, unsigned long, que_thr_t*) + 596
frame #8: 0x000000010a1802f1 mysqld`sel_set_rec_lock(btr_pcur_t*, unsigned char const*, dict_index_t*, unsigned long const*, unsigned long, unsigned long, que_thr_t*, mtr_t*) + 193
frame #9: 0x000000010a17e280 mysqld`row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) + 6720
frame #10: 0x000000010a0a027e mysqld`ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) + 734
frame #11: 0x0000000109869b6c mysqld`handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) + 140
frame #12: 0x0000000109f09065 mysqld`Rows_log_event::do_index_scan_and_update(Relay_log_info const*) + 821
frame #13: 0x0000000109f0a198 mysqld`Rows_log_event::do_apply_event(Relay_log_info const*) + 1064
frame #14: 0x0000000109f4bd42 mysqld`apply_event_and_update_pos(Log_event**, THD*, Relay_log_info*) + 530
frame #15: 0x0000000109f44f46 mysqld`handle_slave_sql + 4438

 
結論
テストによるslave_の使用rows_search_algorithms= INDEX_SCAN,HASH_SCANがこのシーンでbinlogを再生するように構成すると、パフォーマンスが大幅に改善され、メモリのオーバーヘッドが発生するため、hashテーブルを作成するのに十分なメモリを確保すると、パフォーマンスの向上が見られます.
この問題に関する改善提案:
1.where条件のないdeleteまたはupdate操作の大きなテーブルを回避し、全テーブルdeleteが必要な場合はtruncate操作を使用します.
2.binlog rowモードでは、テーブル構造にプライマリ・キーがあることが望ましい
3.slave_をrows_search_AlgorithmsをINDEX_に設定SCAN,HASH_SCANは、一定の性能改善があります.