MySQL의 I/O 오류 원인 및 해결 방법(최적화 제안 포함)
- php是最好的语言원래의
- 2018-07-30 16:44:266604검색
이 글은 MySQL이 테이블을 삭제할 때 발생하는 I/O 오류의 원인을 분석한 것입니다. 먼저 문제 현상을 관찰한 후 관련 문제를 조사해야 합니다. 즉, 메인 스레드가 ibuf(space, 페이지) 및 삭제 작업 실행 과정에서 상호 배제를 보장하는 잠금이 없습니다. 비동기 I/O가 완료된 후 병합 작업과 삭제 작업만 상호 배타적입니다. 모든 설명은 이 문서에 자세히 설명되어 있습니다. apache php mysql
begin!
문제 현상
최근 sysbench를 사용하여 MySQL을 테스트했는데 테스트 시간이 길어서 prepare-> 순서로 백그라운드에서 실행되도록 스크립트를 작성했습니다. ;run->cleanup을 사용합니다. 실행 후 로그를 확인해 보니 문제가 발견되었습니다. 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의 코드를 살펴보면 이 오류가 한 위치, 즉 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를 식별자로 사용합니다. 이 기능은 여러 업데이트를 동일한 페이지에 캐시하고, 이를 일회성 업데이트 작업으로 병합하고, IO를 줄이고, 무작위 IO를 순차적 IO로 변환합니다. 이를 통해 무작위 IO로 인한 성능 손실을 방지하고 데이터베이스의 쓰기 성능을 향상시킬 수 있습니다.
관련 삽입 버퍼 병합 로직
버퍼 페이지를 버퍼 풀로 읽어들이면 삽입 버퍼 병합이 수행됩니다. 병합 프로세스가 발생하는 몇 가지 주요 시나리오는 다음과 같습니다.
페이지를 버퍼 풀로 읽을 때 읽기가 완료된 후 ibuf 병합이 먼저 수행된 다음 페이지를 사용할 수 있습니다. 병합 작업은 백그라운드 작업으로 실행됩니다. innodb_io_capacity 매개변수는 InnoDB 백그라운드 작업의 각 병합 프로세스에서 페이지 수의 상한을 설정할 수 있습니다.
충돌 복구 기간 동안 인덱스 페이지를 버퍼 풀로 읽어올 때, 해당 페이지가 실행됩니다.
삽입 버퍼 내구성이 뛰어나며 시스템 충돌로 인해 실패하지 않습니다. 다시 시작하면 삽입 버퍼 병합 작업이 정상으로 돌아갑니다.
서버가 종료되면 —innodb-fast-shutdown = 0을 사용하여 ibuf의 완전한 병합을 강제할 수 있습니다.
이번 우리의 문제는 분명히 두 번째 상황에 속합니다. innodb 메인 스레드(svr_master_thread)는 매초 삽입 버퍼의 병합 작업을 적극적으로 수행합니다. 먼저 지난 1초 동안 서버에 활동이 있었는지 확인합니다(페이지에 튜플 삽입, 테이블에서 행 작업 실행 취소 등). 그렇다면 병합된 페이지의 최대 수는 innodb_io_capacity 설정의 5%입니다. 그렇지 않은 경우 병합할 최대 페이지 수는 innodb_io_capacity에서 설정한 값입니다.
innodb 메인 스레드(svr_master_thread) 병합의 주요 프로세스는 다음과 같습니다.
메인 스레드는 ibuf 트리의 리프 노드에서 페이지 번호와 공간 번호를 읽어서 이진 배열에 기록합니다(잠금 해제됨). ;
메인 스레드는 튜플의 공간이 테이블스페이스 캐시에 있는지 확인하고 그렇지 않은 경우 ibuf에 해당하는 레코드를 삭제함을 의미합니다. 공간 읽기 작업에서 비동기적으로 삭제를 수행할지 여부, 그렇다면 오류를 보고하고 ibuf에 해당하는 레코드를 삭제한 후 프로세스 2로 이동하여 다음 배열 요소를 계속 판단합니다.
如果一切判断正常，主线程发出async io请求，async读取需要被merge的索引页面；
I/O handler 线程，在接受到完成的async I/O之后，进行merge操作；
进行merge的时候调用fil_space_acquire对space->n_pending_ops进行自增。避免删除操作并发；
执行完毕后调用fil_space_release对space->n_pending_ops进行自减。
相关删除表的逻辑
对fil_system->mutex加锁，设置sp->stop_new_ops = true，标记space正在删除，不允许对它进行新操作，然后对fil_system->mutex解锁；
对fil_system->mutex加锁，检测space->n_pending_ops，对fil_system->mutex解锁。如果检测到大于0，意味着还有依赖的操作未完成，睡眠20ms后重试；
对fil_system->mutex加锁，检测space->n_pending_flushes和(*node)->n_pending ，对fil_system->mutex解锁。如果检测到大于0，意味着还有依赖的I/O未完成，睡眠20ms后重试；
此时认为已经没有冲突的操作了，刷出所有脏页面或删除所有给定的表空间的页面；
从表空间缓存删除指定space的记录；
删除对应数据文件。
问题结论
情况很明确了，主线程获取ibuf的（space，page）的过程与删除操作执行的过程并没有锁保证互斥，只有async I/O完成之后的merge操作与删除操作才有互斥。如果后台线程开始ibuf merge并已经执行过了第2步的检测，但还没有执行到第3步检测，此时用户线程开始做删除表的操作，并设置好stop_new_ops标记但还没有执行到第5步删除表空间缓存，就会出现这个错误信息。两线程的交互如下图所示：
 "153293906441041MySQL의 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：对于知识点我就介绍到这里了，写的有点快，可能有不足之处，还望多多交流指正，希望能帮到大家。
相关文章：
相关视频：
위 내용은 MySQL의 I/O 오류 원인 및 해결 방법(최적화 제안 포함)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!