MySQL の I/O エラーの原因と解決策 (最適化の提案付き)-mysql チュートリアル-php.cn

この記事は、MySQL がテーブルを削除するときの I/O エラーの原因を分析したものです。まず、問題の現象を観察し、関連する問題を調査する必要があります。つまり、メインスレッドが ibuf (スペース、ページ) と削除操作実行プロセス中に相互排他を保証するロックはありません。非同期 I/O が完了した後のマージ操作と削除操作のみが相互排他的です。すべての説明はこの記事で詳しく説明されています。 apache php mysql

begin!

問題現象

最近、MySQLのテストにsysbenchを使用しましたが、テスト時間が長かったので、prepare->gtの順でバックグラウンドで実行するスクリプトを書きました。 ;run->クリーンアップを使用します。実行後、ログを確認したところ、MySQL サービスのエラーログに次のようなエラーレポートが複数ありました:

[ERROR] InnoDB: Trying to do I/O to a tablespace which does not exist. I/O type: read, page: [page id: space=32, page number=57890], I/O length: 16384 bytes。

ログイン後にコピー

I/O エラーがあったようですが、MySQL プロセスでエラーが発生しました。クラッシュせず、sysbench クライアントはエラーを報告しませんでした。

問題発見のプロセス

エラーの時間記録とスクリプト出力の各段階の時点の比較に基づいて、その時点でスクリプトによって実行されていたコマンドは次のとおりであると判明しました。

sysbench --tables=100 --table-size=4000000 --threads=50 --mysql-db=sbtest --time=300 oltp_delete cleanup

ログイン後にコピー

ユースケースを手動で再度実行しましたが、同じことは再度発生しませんでした。ただし、スクリプトを実行すると、このエラーメッセージが依然として表示されることがあります。最初は、実行とクリーンアップの間の間隔が長すぎてこの問題が発生する可能性があるのではないかと考えられます。 100Gのデータを一度に実行するには時間がかかり、再現コストも高いため、まずはユースケースのデータ量を減らすようにしましょう。 -table-size=4000000 を 2000000 に変更します。この時点でスクリプトを実行すると、この問題は発生しません。最終的に、-table-size=3000000 を安定してトリガーでき、再発時間の一部が短縮されます。間隔が長すぎて再現できないかどうかを確認するために、実行ステージとクリーンアップステージの間に 10 秒間スリープするようにスクリプトを変更しました。予想通り、このエラーメッセージは表示されません。 5 秒間のスリープに変更した場合でもトリガーされる可能性はありますが、エラーレポートの数は減少しました。

問題調査

対応するバージョン mysql5.7.22 のコードを確認すると、このエラーは 1 か所でのみ発生していることがわかりました。それは、fil0fil.cc ファイルの 5578 行目の fil_io() 関数内です。 gdb を使用して直接デバッグし、この場所にブレークポイントを追加し、再現可能なスクリプトを実行して次のスタックを取得します:

(gdb) bt
#0  fil_io (type=..., sync=sync@entry=false, page_id=..., page_size=..., byte_offset=byte_offset@entry=0, len=16384, buf=0x7f9ead544000, message=message@entry=0x7f9ea8ce9c78) at mysql-5.7.22/storage/innobase/fil/fil0fil.cc:5580
#1  0x00000000010f99fa in buf_read_page_low (err=0x7f9ddaffc72c, sync=<optimized out>, type=0, mode=<optimized out>, page_id=..., page_size=..., unzip=true) at mysql-5.7.22/storage/innobase/buf/buf0rea.cc:195
#2  0x00000000010fc5fa in buf_read_ibuf_merge_pages (sync=sync@entry=false, space_ids=space_ids@entry=0x7f9ddaffc7e0, page_nos=page_nos@entry=0x7f9ddaffc7a0, n_stored=2) at mysql-5.7.22/storage/innobase/buf/buf0rea.cc:834
#3  0x0000000000f3a86c in ibuf_merge_pages (n_pages=n_pages@entry=0x7f9ddaffce30, sync=sync@entry=false) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2552
#4  0x0000000000f3a94a in ibuf_merge (sync=false, sync=false, n_pages=0x7f9ddaffce30) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2656
#5  ibuf_merge_in_background (full=full@entry=false) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2721
#6  0x000000000102bcf4 in srv_master_do_active_tasks () at mysql-5.7.22/storage/innobase/srv/srv0srv.cc:2132
#7  srv_master_thread (arg=<optimized out>) at mysql-5.7.22/storage/innobase/srv/srv0srv.cc:2383
#8  0x00007fa003eeddc5 in start_thread () from /lib64/libpthread.so.0
#9  0x00007fa002aab74d in clone () from /lib64/libc.so.6

ログイン後にコピー

明らかに、これは挿入バッファーのマージ操作を実行するバックグラウンドスレッドです。このとき、space->stop_new_opsがtrueである、つまり、処理対象のページが属するスペースが削除されていることが分かる。削除されているスペースをなぜ操作したいのですか?これには、挿入バッファ機能、挿入バッファのマージプロセス、およびテーブルの削除プロセスを調査する必要があります。

挿入バッファの背景知識

挿入バッファは特殊なデータ構造 (B+ ツリー) であり、補助インデックスページがバッファプールにない場合、変更をキャッシュし、後でロード時に他の操作によってページを読み取ります。バッファプールに入れます。 MySQL が最初にこの関数を導入したときは、挿入操作のみをキャッシュできたため、挿入バッファーと呼ばれていました。現在では、これらの操作は INSERT、UPDATE、または DELETE (DML) にできるため、変更バッファーと呼ばれています (この記事では引き続き挿入と説明します)。バッファ) ですが、ソースコードでは引き続き ibuf が識別子として使用されます。この機能は、複数の更新を同じページにキャッシュし、それらを 1 回限りの更新操作にマージし、IO を削減し、ランダム IO を順次 IO に変換します。これにより、ランダム IO によって引き起こされるパフォーマンスの低下を回避し、データベースの書き込みパフォーマンスを向上させることができます。

问题结论

情况很明确了，主线程获取ibuf的（space，page）的过程与删除操作执行的过程并没有锁保证互斥，只有async I/O完成之后的merge操作与删除操作才有互斥。如果后台线程开始ibuf merge并已经执行过了第2步的检测，但还没有执行到第3步检测，此时用户线程开始做删除表的操作，并设置好stop_new_ops标记但还没有执行到第5步删除表空间缓存，就会出现这个错误信息。两线程的交互如下图所示：

MySQL の I/O エラーの原因と解決策 (最適化の提案付き)

不出意外的话，在打中断点时必然有线程在执行对应表的删除操作。果然我们可以发现如下堆栈：

Thread 118 (Thread 0x7f9de0111700 (LWP 5234)):
#0  0x00007fa003ef1e8e in pthread_cond_broadcast@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x0000000000f82f41 in broadcast (this=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:184
#2  set (this=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:75
#3  os_event_set (event=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:483
#4  0x00000000010ec8a4 in signal (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ut0mutex.ic:105
#5  exit (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ib0mutex.h:690
#6  exit (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ib0mutex.h:961
#7  buf_flush_yield (bpage=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:405
#8  buf_flush_try_yield (processed=<optimized out>, bpage=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:449
#9  buf_flush_or_remove_pages (trx=<optimized out>, flush=<optimized out>, observer=<optimized out>, id=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:632
#10 buf_flush_dirty_pages (buf_pool=<optimized out>, id=<optimized out>, observer=<optimized out>, flush=<optimized out>, trx=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:693
#11 0x00000000010f6de7 in buf_LRU_remove_pages (trx=0x0, buf_remove=BUF_REMOVE_FLUSH_NO_WRITE, id=55, buf_pool=0x31e55e8) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:893
#12 buf_LRU_flush_or_remove_pages (id=id@entry=55, buf_remove=buf_remove@entry=BUF_REMOVE_FLUSH_NO_WRITE, trx=trx@entry=0x0) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:951
#13 0x000000000114e488 in fil_delete_tablespace (id=id@entry=55, buf_remove=buf_remove@entry=BUF_REMOVE_FLUSH_NO_WRITE) at mysql-5.7.22/storage/innobase/fil/fil0fil.cc:2800
#14 0x0000000000fe77bd in row_drop_single_table_tablespace (trx=0x0, is_encrypted=false, is_temp=false, filepath=0x7f9d7c209f38 "./sbtest/sbtest25.ibd", tablename=0x7f9d7c209dc8 "sbtest/sbtest25", space_id=55) at mysql-5.7.22/storage/innobase/row/row0mysql.cc:4189
#15 row_drop_table_for_mysql (name=name@entry=0x7f9de010e020 "sbtest/sbtest25", trx=trx@entry=0x7f9ff9515750, drop_db=<optimized out>, nonatomic=<optimized out>, nonatomic@entry=true, handler=handler@entry=0x0) at mysql-5.7.22/storage/innobase/row/row0mysql.cc:4741
#16 0x0000000000f092f3 in ha_innobase::delete_table (this=<optimized out>, name=0x7f9de010f5e0 "./sbtest/sbtest25") at mysql-5.7.22/storage/innobase/handler/ha_innodb.cc:12539
#17 0x0000000000801a30 in ha_delete_table (thd=thd@entry=0x7f9d7c1f6910, table_type=table_type@entry=0x2ebd100, path=path@entry=0x7f9de010f5e0 "./sbtest/sbtest25", db=db@entry=0x7f9d7c00e560 "sbtest", alias=0x7f9d7c00df98 "sbtest25", generate_warning=generate_warning@entry=true) at mysql-5.7.22/sql/handler.cc:2586
#18 0x0000000000d0a6af in mysql_rm_table_no_locks (thd=thd@entry=0x7f9d7c1f6910, tables=tables@entry=0x7f9d7c00dfe0, if_exists=true, drop_temporary=false, drop_view=drop_view@entry=false, dont_log_query=dont_log_query@entry=false) at mysql-5.7.22/sql/sql_table.cc:2546
#19 0x0000000000d0ba58 in mysql_rm_table (thd=thd@entry=0x7f9d7c1f6910, tables=tables@entry=0x7f9d7c00dfe0, if_exists=<optimized out>, drop_temporary=<optimized out>) at mysql-5.7.22/sql/sql_table.cc:2196
#20 0x0000000000c9d90b in mysql_execute_command (thd=thd@entry=0x7f9d7c1f6910, first_level=first_level@entry=true) at mysql-5.7.22/sql/sql_parse.cc:3589
#21 0x0000000000ca1edd in mysql_parse (thd=thd@entry=0x7f9d7c1f6910, parser_state=parser_state@entry=0x7f9de01107a0) at mysql-5.7.22/sql/sql_parse.cc:5582
#22 0x0000000000ca2a20 in dispatch_command (thd=thd@entry=0x7f9d7c1f6910, com_data=com_data@entry=0x7f9de0110e00, command=COM_QUERY) at mysql-5.7.22/sql/sql_parse.cc:1458
#23 0x0000000000ca4377 in do_command (thd=thd@entry=0x7f9d7c1f6910) at mysql-5.7.22/sql/sql_parse.cc:999
#24 0x0000000000d5ed00 in handle_connection (arg=arg@entry=0x10b8e910) at mysql-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:300
#25 0x0000000001223d74 in pfs_spawn_thread (arg=0x10c48f40) at mysql-5.7.22/storage/perfschema/pfs.cc:2190
#26 0x00007fa003eeddc5 in start_thread () from /lib64/libpthread.so.0
#27 0x00007fa002aab74d in clone () from /lib64/libc.so.6

ログイン後にコピー

解决办法

为buf_read_ibuf_merge_pages、buf_read_page_low、fil_io新增一个参数ignore_missing_space。表示忽略正在删除的space，默认为false，当ibuf_merge_pages调用的时候置为true。在fil_io报错处额外判断该参数是否为true，是则不报错，继续其他流程。

或者直接在buf_read_ibuf_merge_pages调用buf_read_page_low时传入IORequest::IGNORE_MISSING参数。

具体代码参考MariaDB commit：8edbb1117a9e1fd81fbd08b8f1d06c72efe38f44

影响版本

察看相关信息，这个问题是修改Bug#19710564时删除表空间版本引入的。

MySQL Community Server 5.7.6引入，版本5.7.22尚未修复，版本8.0.0已修复。
MariaDB Server 10.2受影响。MariaDB Server 10.2.9, 10.3.2已修复

优化建议

可优化一下性能：在buf_read_ibuf_merge_pages中记录下出错的space id，循环的时候判断下一个page的space id，如果space id是相同的，直接删除对应ibuf的记录（当前分配的最大space id记录在系统表空间，space id占4个字节，低于0xFFFFFFF0UL，分配时读取系统表空间保存的值，然后加一，具有唯一性）。

end：对于知识点我就介绍到这里了，写的有点快，可能有不足之处，还望多多交流指正，希望能帮到大家。

mysql1064错误原因及解决办法

MySQL常见问题及解决方案