MySQL 세마포어 충돌 분석을 기억하세요-MySQL 튜토리얼-php.cn

MySQL 세마포어 충돌 분석을 기억하세요

BA는 InnoDB에 익숙해야 합니다. 서버가 정지된 것처럼 보이기 때문에 세마포 대기가 600초 이상 지속되었습니다. MySQL 백그라운드 스레드 srv_error_monitor_thread는 600초 이상 차단된 래치 잠금이 있음을 발견합니다. 잠금이 10회 연속 감지된 후에도 해제되지 않으면 서비스가 계속 중단되는 것을 방지하기 위해 패닉이 트리거됩니다.

무슨 일이 있었나요

버전 번호: MySQL 5.5.40

# 🎜🎜#로그는 데이터 사전 잠금을 기다리는 스레드를 계속 출력합니다. 위치는 dict0dict.c 라인 305이며 대기 시간이 900초를 초과합니다.

자물쇠를 잡고 있는 스레드는 139998697924352이고 16진수는 7f53fca8a700입니다.

--스레드 139998393616128은 dict0dict.c 라인 305에서 934.00초 동안 기다렸습니다. 세마포:X-lock은 0x105a1b8 파일에서 생성된 dict0dict.c 라인 748a 작성자(스레드)에서 RW-래치에 있습니다. ID 139998697924352)는 독자 수 0, 웨이터 플래그 1, lock_word: 0마지막 읽기가 파일에 잠겼습니다. dict0dict.c 줄 302마지막 쓰기가 /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/ 파일에 잠겼습니다. mysql-5.5.40/mysql-5.5.40/storage/innobase/dict/dict0dict.c line 305

잠긴 스레드의 거래 정보 139998697924352 , 데이터 사전 테이블을 쿼리하는 작업입니다.

---TRANSACTION 0, 테이블 통계 업데이트가 시작되지 않음: MySQL 스레드 ID 14075, OS 스레드 핸들 0x7f53fca8a700, 쿼리 ID 110414021 21.14.5.139 jzjkusrSELECT ROUND(SUM(DATA_LENGTH+ INDEX_LENG TH+DATA_FREE) /1024/1024/1024) AS MY_DB_TOTAL_SIZE FROM information_schema.TABLES

잠금 보유 스레드 139998697924352가 다른 잠금을 기다리고 있는지 확인하세요.

발견된 스레드 139998697924352, 자동 잠금은 btr0sea.c 라인 1134에 있으며, 이 잠금 구조는 AHI와 관련이 있습니다. #### ####-스레드 139986979924352는 Btr0sea.c 라인 178a에서 생성 된 0x1EB06448에서 RW-Latch의 Semaphore : X-Lock (wait_ex)을 934.00 초 동안 BTR0SEA.C Line 1134에서 기다렸다. 작성자(스레드 ID 139998697924352)는 대기 독점 모드에서 이를 예약했습니다. 독자 수 1, 웨이터 플래그 1, lock_word: ffffffffffffffffbtr0sea.c 파일에 잠긴 마지막 읽기 시간 1057/pb2/build/sb_0-13157587-1410170252.03/ 파일에 마지막 쓰기 잠김 rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/innobase/btr/btr0sea.c line 1134

다음으로 두 개의 잠금 장치가 어디에 있는지 살펴 보겠습니다. 구조는 다음과 같습니다. dict0dict.c dict_table_stats_lock 함수 내 305번째 줄

btr0sea.c btr_search_drop_page_hash_index 함수 내 1134번째 줄

# 🎜🎜# what 이 함수들은 어떤 상황에서 호출될까요?

innodb_table_monitor를 활성화하고 dict_table_stats_lock을 호출하여 로그 출력 시 X 잠금을 잠급니다. 이 경우는 활성화되지 않습니다.

innodb_stats_on_metadata가 활성화되면 데이터 사전 테이블을 쿼리하면 통계 정보 업데이트 작업이 트리거되고 dict_table_stats_lock에 대한 X 잠금이 호출됩니다. 이는 잠금을 보유하고 있는 스레드의 트랜잭션 정보와 일치합니다.

AHI(적응형 해시 인덱스)는 InnoDB에서 인덱스 페이지 검색 속도를 높이기 위해 사용하는 해시 테이블 구조입니다. 페이지 방문 횟수가 특정 조건을 충족하면 해당 페이지의 주소가 해시 테이블에 저장되어 B-트리 쿼리 비용을 절감합니다.

MySQL 5.5 버전 AHI는 해시 테이블 수정의 일관성을 유지하기 위해 전역 잠금 btr_search_latch에 의해 유지됩니다.

InnoDB 버퍼 풀 상태는 여유 버퍼가 기본적으로 0 유휴 상태로 유지되는 것을 보여줍니다. InnoDB 버퍼 풀은 데이터 페이지를 제거할 때 btr_search_drop_page_hash_index 함수를 호출하여 AHI에서 데이터 페이지를 지웁니다.

-------------------버퍼 풀 및 메모리-- --- -------------할당된 총 메모리 17582522368;

추가 풀 할당 0#🎜🎜 #

사전 메모리 할당 4289681

버퍼 풀 크기 1048576

#🎜 🎜## 🎜 🎜 #사용 가능한 버퍼 0

데이터베이스 페이지 1040831

이전 데이터베이스 페이지 384193#🎜 🎜## 🎜 🎜##🎜 🎜#수정된 DB 페이지 0

🎜🎜##🎜🎜 ##🎜 🎜#AHI 전반적인 상황 btr_search_latch 잠금은 종종 성능에 영향을 미치는 경쟁의 핫스팟입니다. 버전 5.7 이후 개선되었으며 InnoDB 버퍼처럼 여러 인스턴스로 분할되었습니다. 이 경우 Innodb_stats_on_metadata 매개변수를 켜면 메타데이터 정보를 쿼리할 때 통계 정보가 업데이트되고 데이터 사전이 잠기므로 많은 비즈니스 작업이 차단됩니다. 또한 버퍼 풀 공간이 부족하여 테이블이 제거됩니다. 이전 페이지를 삭제하고 AHI의 btr_search_latch 잠금 경쟁을 유발하여 궁극적으로 세마포어 시간 초과 및 충돌이 발생합니다.

>> 부활절 달걀 <<

Analyze Semaphore crash in 수 메가바이트 로그, 잠금, 스레드, 트랜잭션 찾기 이들의 관계는 상당히 답답하다. sed, awk 및 grep이라는 세 가지 마법 무기의 도움으로 효율성이 향상되었지만 여전히 충분히 효율적이지 않습니다.

DBA가 이러한 관계를 빠르게 정리할 수 있도록 게으른 마음으로 작은 프로그램을 작성했습니다.

사용법은 다음과 같습니다:

hongbin@MBP ~> mysqldba doctor -f /Users/hongbin/workbench/mysqld_safe.log#🎜🎜 #

대상 버전, 코드 확인 시 해당 버전을 찾으세요.

MySQL 서버 버전: '5.7.16-log'

로그에 나타나는 세마포어 충돌 횟수와 mysql 시작 횟수. 시작 횟수가 충돌 횟수보다 크면 정상적인 시작 또는 기타 충돌이 발생할 수 있습니다.

************ MySQL 서비스 시작 횟수 ********* ***

MySQL 세마포 충돌 -> 3회 ["2018-08 -13 23:12:18" "2018-08-14 12:13:43" "2018-08-16 13:42: 36"]

MySQL 서비스 시작 -> 3회 ["2018-08 -13 23:12:59" "2018-08-14 12:15:20" "2018-08-16 13:46: 37"]

잠금 위치, 발생 횟수, 스레드 ID(발생 횟수), 발생 횟수가 더 많은 스레드에 초점을 포함하여 주로 대기 중인 스레드인 RW-래치:

****** ****** 잠금을 기다린 스레드 ************row0purge.cc: 861 -> 58 140477266503424:(57) 140617703745280:(1)gi.cc:14791 -> 140477035656960:(1)trx0undo.ic:171 -> 1 1406176 82765568:(1)ha_innodb.cc:14791 -> 620 140617389913856:(58) 140202719565568:(58) 140202716903168:(57) 140477029533440:(56) 140617407219456 :(55) 14047703565 6960:(52) 140477035124480:(29) 140477108467456:(29) 140477025539840:(26) 140477031130880:( 25) 1404770276 69760:(22) 140617634944768:(21) 140617634146048:(21) 140477019948800:(21) 140477026604800:(20) 1404770220787 20:(18) 140477018883840:(16) 140477038585600:(15) 140477028734720:(10) 140477022877440: (9) 140477034325760:(1) 140477031663360:(1)srv0srv.cc:1968 -> 140477276993280:(185) 140617714235136:(23)ha_innodb.cc:5510 -> 601 140617398167296:(57) 140617409615616:(55) 140617392043776:( 53) 140477110597376:(52) 140617395771136:(50) 140617636275968:(45) 140617632548608 :(40) 140617634146048:( 33) 140617639675648:(32) 140617397102336:(28) 140617639409408:(23) 140617635743488:(21) 14061763781 1968:(18) 140617638610688:(16) 140617399232256:(12) 140617638344448:(10) 140617638078208 :(10) 140477033793280:( 10) 140477029267200:(10) 140617397368576:(9) 140617635211008:(6) 140617393641216:(5) 14061763754572 8:(3) 140617402693376:(2) 140477037254400:(1)dict0dict.cc:1239 - > 136 140477122623232:(50) 140617392842496:(35) 140202726487808:(26) 140477123688192:(12) 140477038851840:(5) 1404770300 65920 :(4) 140617634412288:(4)row0trunc.cc:1835 -> 1 140477109798656:( 1)

어느 쓰기 스레드가 ************

140616681907968 -> 1 trx0undo.ic:171에서 ******** 어느 작가 스레드 블록을 보유하고 있나요? (1)140477173069568 -> 243 srv0srv.cc: 1968:(185) row0purge.cc:861:(57) row0trunc.cc:1835:(1)140617682765568 -> 29 srv0srv.cc:1968:(23) ha_innodb .cc:5510:(5) row0purge.cc: 861:(1)

쓰기 스레드에 해당하는 거래 정보, 거래 정보를 출력하지 않는 로그 기록도 있을 수 있습니다:

***** ***** 이 기록기 스레드 trx 상태 ******* ***MySQL 스레드 ID 83874, OS 스레드 핸들 140477173069568, 쿼리 ID 13139674 10.0.1.146 aml 참조 테이블에서 삭제

S 잠금 통계 쓰기 스레드:

****이 작가 스레드는 마지막으로 읽혀졌습니다. ****

140477173069568 -> 243 row0purge.cc:861:(243)140617682765568 -> :(24)140616681907968 -> 1 trx0undo.ic:190:(1 )

140617682765568을 보유한 스레드 작성에 대한 통계 -> 24 dict0stats.cc:2366:(24)140616681907968 -> 1 buf0flu.cc:1198 :(1)

사후 이벤트 로그 분석을 통해 스레드의 트랜잭션 정보가 로그에 출력되지 않을 가능성이 있으므로 트랜잭션이 수행하는 특정 작업을 알 수 없습니다. 이런 상황에 대응해 미니프로그램에는 거래 중 거래정보를 수집하는 기능이 추가됐다.

사용법은 다음과 같습니다:

hongbin@MBP ~> mysqldba -uxxx -pxxx doctor -w

"작성자(스레드 ID 140616681907968)가 예약하면 대상 mysql의 오류 로그를 모니터링합니다. in mode" 키워드를 사용하여 ps에서 거래 정보를 조회하고 저장합니다.

위 내용은 DBA를 위한 미니 프로그램의 사용법일 뿐입니다. 이외에도 다양한 기능이 있습니다. 여러분의 생각을 저와 자유롭게 공유해 주세요.

SQL과 관련된 더 많은 기술 기사를 보려면