MySQL 교착 상태 및 로그 분석

이 기사에서는 주로 MySQL 교착 상태 및 로그에 대해 설명합니다. 온라인 MySQL 문제를 빠르게 찾고 실제 비즈니스에서 예외를 수정하는 방법은 무엇입니까? 이 글은 두 가지 실제 사례를 바탕으로 관련 경험과 방법을 공유합니다. 관심 있는 친구들이 모두 참고할 수 있기를 바랍니다.

최근 온라인 MySQL에서 여러 가지 데이터 이상이 발생했는데, 모두 이른 아침에 발생했습니다. 비즈니스 시나리오는 일반적인 데이터 웨어하우스 애플리케이션이므로 낮 동안의 부담이 낮아 재현할 수 없습니다. 일부 이상 현상은 심지어 이상하기까지 하며, 결국 근본 원인 분석이 상당히 어렵습니다. 그렇다면 온라인 MySQL 문제를 신속하게 찾고 실제 비즈니스에서 예외를 수정할 수 있는 방법은 무엇일까요? 아래에서는 두 가지 실제 사례를 바탕으로 관련 경험과 방법을 공유하겠습니다.

사례1: 일부 데이터 업데이트에 실패했습니다

어느 날 채널 친구들이 특정 보고서에서 채널 데이터가 0이 거의 없고 대부분의 채널 데이터가 정상이었다고 보고했습니다. 이 데이터는 매일 이른 아침 통계 프로그램에 의해 정기적으로 업데이트됩니다. 모든 것이 정상이거나 모든 것이 실패하는 것은 당연합니다. 그렇다면 일부 개별 데이터가 비정상적인 이유는 무엇일까요?

우선 통계 작업 로그를 보면 가장 먼저 떠오르는데, 통계 프로그램에서 출력한 로그를 보니 SQL 업데이트 실패 등의 비정상적인 설명은 발견되지 않았습니다. 그 당시 데이터베이스에서 무슨 일이 일어났나요? MySQL 서버 로그를 확인하기 전에 습관적으로 데이터베이스 상태를 살펴봤습니다.

이번 업데이트에서 새벽에 교착 상태가 발생했습니다.

공간상의 제약으로 인해 많이 생략했습니다. 이 로그에서 볼 수 있듯이 TRANSACTION 1과 TRANSACTION 2는 각각 특정 수의 행 잠금을 보유한 다음 상대방의 잠금을 기다립니다. 마지막으로 MySQL은 교착 상태를 감지한 다음 TRANSACTION 1을 롤백하도록 선택합니다. : Innodb의 현재 교착 상태 처리 방법은 최소 행 수준 배타적 잠금을 보유한 트랜잭션을 롤백하는 것입니다.

그럼 여기에 세 가지 질문이 있습니다.

1. innodb 행 잠금은 한 행만 잠그지 않나요?

이 테이블은 innodb 엔진에서 가져온 것이므로 InnoDB는 행 잠금과 테이블 잠금을 지원합니다. InnoDB 행 잠금은 인덱스의 인덱스 항목을 잠그는 방식으로 이루어지며 이는 데이터 블록의 해당 데이터 행을 잠그는 방식으로 이루어지는 MySQL 및 Oracle과 다릅니다. InnoDB의 행 잠금 구현 기능은 InnoDB가 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용한다는 것을 의미합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용하고 스캔된 모든 행을 잠급니다. 실제 애플리케이션에서는 InnoDB 행 잠금의 이 기능에 특별한 주의를 기울여야 합니다. 그렇지 않으면 많은 수의 잠금 충돌이 발생하여 동시성 성능에 영향을 미칠 수 있습니다. MySQL의 행 잠금은 레코드에 대한 잠금이 아닌 인덱스에 대한 잠금이므로 서로 다른 행의 레코드에 액세스하더라도 동일한 인덱스 키를 사용하면 잠금 충돌이 발생합니다. 데이터를 검색하고 공유 또는 배타적 잠금을 요청하기 위해 동등 조건 대신 범위 조건을 사용하면 InnoDB는 조건을 충족하는 기존 데이터 레코드의 인덱스 항목을 잠급니다. 또한 간격 잠금은 여러 행도 잠그는 것입니다. 범위 조건은 물론 여러 행도 잠급니다. 잠금 시 간격 잠금을 사용하는 것 외에도 동일한 조건을 사용하여 존재하지 않는 레코드를 잠그도록 요청하면 InnoDB도 간격 잠금을 사용합니다!

이제 우리 비즈니스 테이블의 인덱스 상황을 살펴보겠습니다.

이 테이블의 인덱스가 매우 불합리하다는 것을 알 수 있습니다. 인덱스가 3개 있지만 업데이트가 완전히 이루어지지 않았습니다. 이로 인해 업데이트가 인덱스를 정확하게 사용하지 못하고 다중 행 범위 데이터를 잠가야 하므로 교착 상태가 발생합니다.

원칙을 알고 나면 업데이트가 innodb 인덱스를 정확하게 사용할 수 있도록 4필드 결합 인덱스를 신중하게 구축할 수 있습니다. 실제로 인덱스를 업데이트하고 나면 이 교착 상태 문제가 해결됩니다.

참고: innodb는 트랜잭션과 트랜잭션이 보유하고 대기한 잠금뿐만 아니라 레코드 자체도 인쇄합니다. 이는 불행히도 출력 결과를 위해 innodb에 예약된 길이를 초과할 수 있습니다(1M만 가능). 내용이 인쇄되며 최신 교착 상태 정보만 보유할 수 있습니다) 전체 출력을 볼 수 없는 경우 현재 임의의 라이브러리 아래에 innodb_monitor 또는 innodb_lock_monitor 테이블을 생성하면 innodb 상태 정보가 완료되고 오류가 발생합니다. 15초마다 기록됩니다. 예를 들어 innodb_monitor(a int)engine=innodb; 테이블을 생성하고, 오류 로그에 기록할 필요가 없을 때 테이블을 삭제하면 됩니다.

2. 롤백할 때 일부 업데이트 문만 실패하는 이유는 무엇입니까?

롤백하는 경우 전체 트랜잭션의 모든 업데이트가 아닌 일부 업데이트 문만 실패하는 이유는 무엇입니까?

이것은 innodb가 기본적으로 자동으로 제출되기 때문입니다:

여러 개의 업데이트 또는 삽입 문이 있는 경우 각 SQL이 실행된 후 innodb는 즉시 한 번 커밋하여 변경 사항을 유지하고 동시에 잠금을 해제합니다. 이것이 바로 교착 상태 롤백 트랜잭션 후 몇 개의 문만 실패한 이유입니다. 예. 이유.

일반적으로 일부 문이 롤백될 수 있는 또 다른 상황이 있으므로 특별한 주의가 필요하다는 점에 유의해야 합니다. innodb에는 innodb_rollback_on_timeout

공식 매뉴얼에서 다음과 같이 설명하는 매개변수가 있습니다.

MySQL 5.1에서 InnoDB는 기본적으로 트랜잭션 시간 초과 시 마지막 문만 롤백합니다. 트랜잭션 시간 초과로 인해 InnoDB는 전체 트랜잭션을 중단하고 롤백합니다(MySQL 4.1과 동일한 동작). 이 변수는 MySQL 5.1.15에 추가되었습니다.

설명: 이 매개변수가 꺼져 있거나 존재하지 않는 경우 시간 초과가 발생하면 마지막 트랜잭션이 롤백됩니다. 쿼리가 열린 경우 트랜잭션이 시간 초과가 발생하면 전체 트랜잭션이 롤백됩니다.

3. innodb 교착 상태 가능성을 줄이는 방법은 무엇입니까?

행 잠금 및 트랜잭션 시나리오에서 교착 상태를 완전히 제거하기는 어렵지만 다음을 포함하여 테이블 설계 및 SQL 조정을 통해 잠금 충돌 및 교착 상태를 줄일 수 있습니다. gap lock을 피하려면 세션이나 트랜잭션의 트랜잭션 격리 수준을 RC(read commit) 수준으로 변경할 수 있지만 이때 binlog_format을 행 또는 혼합 형식으로 설정해야 합니다

인덱스를 신중하게 설계하고 잠금이 더 정확하도록 인덱스를 사용하여 잠금 충돌 가능성을 줄입니다.

합리적인 트랜잭션 크기를 선택하면 작은 트랜잭션에 대한 잠금 충돌 가능성도 작아집니다. 레코드 세트를 사용하는 경우 한 번에 충분한 수준의 잠금을 요청하는 것이 가장 좋습니다. 예를 들어, 데이터를 수정하려면 먼저 공유 잠금을 적용한 다음 수정 시 독점 잠금을 요청하는 대신 직접 독점 잠금을 적용하는 것이 가장 좋습니다. 이로 인해 다른 프로그램이 액세스할 때 쉽게 교착 상태가 발생할 수 있습니다. 테이블 그룹에서는 동일한 것에 동의하도록 노력해야 합니다. 각 테이블에 순차적으로 액세스하십시오. 테이블의 경우 가능한 한 고정된 순서로 테이블의 행에 액세스하십시오. 이렇게 하면 교착 상태가 발생할 가능성이 크게 줄어듭니다.

동시 삽입에 대한 갭 잠금의 영향을 피하기 위해 동일한 조건을 사용하여 데이터에 액세스하십시오.

필요하지 않은 한 실제 요구 사항을 초과하는 잠금 수준을 적용하지 마십시오. , 쿼리 시 잠금을 표시하지 않습니다.

일부 특정 트랜잭션의 경우 테이블 잠금을 사용하여 처리 속도를 높이거나 교착 상태 가능성을 줄일 수 있습니다.

Case2:

이상한 잠금 대기 시간 초과

며칠 동안 데이터 로컬 infile을 로드할 때 작업이 실패했습니다. 잠금 대기 시간 초과가 보고되었습니다. 트랜잭션 innodb를 다시 시작해 보세요. 비정상. 플랫폼에서 친구들과 소통한 후 이것이 우리 비즈니스 데이터베이스의 잠금 시간이 너무 짧거나 잠금 충돌이 있는 문제라는 것을 알게 되었습니다. 하지만 다시 생각해 보면 그렇지 않습니까? 이건 항상 좋지 않은가? 또한 기본적으로 단일 형식의 작업이므로 여러 사람의 갈등이 없습니다. 누구의 문제이든 먼저 데이터베이스에 문제가 있는지 확인해 보겠습니다.

기본 잠금 시간 제한은 50초인데 실제로 조정해도 소용이 없습니다. 정말 죽었습니다. 말 의사처럼 시도해 보았지만 효과가 없었습니다. . .

그리고 이번에는 SHOW ENGINE INNODB STATUSG가 교착 상태 정보를 표시하지 않았습니다. 그런 다음 로그에서 그 순간 전후에 데이터가 어떤 작업을 수행했는지 확인하기 위해 MySQL 서버 로그에 주의를 돌렸습니다. 다음은 MySQL 로그 파일 시스템 구성에 대한 간략한 소개입니다.

(a) 오류 로그: mysqld를 시작, 실행 또는 중지할 때 발생하는 문제를 기록합니다. 기본적으로 활성화되어 있습니다.

(b) 일반 로그: 모든 명령문과 명령을 기록하는 일반 쿼리 로그입니다. 데이터베이스를 열면 약 5% 정도의 성능 손실이 발생합니다.

(c) binlog 로그: 바이너리 형식으로, 데이터를 변경하는 모든 명령문을 기록하며 주로 슬레이브 복제 및 데이터 복구에 사용됩니다.

(d) 느린 로그: 실행하는 데 long_query_time 초 이상 걸리는 모든 쿼리 또는 인덱스를 사용하지 않는 쿼리를 모두 기록합니다. 기본적으로 꺼져 있습니다.

(e) Innodb 로그: innodb redo 로그, 실행 취소 로그, 데이터 복원 및 실행 취소 작업에 사용됩니다.

위 소개에서 볼 수 있듯이 이 문제의 현재 로그는 d와 b에 있을 수 있습니다. d에 로그인이 없으면 b만 활성화할 수 있지만 b는 성능에 어느 정도 손실이 발생합니다. 데이터베이스가 꽉 찼기 때문에 로그의 양이 매우 방대하므로 열 때 주의해야 합니다.

저는 매일 문제 발생 전후 30분 동안만 전체 로그를 엽니다. 우리 비즈니스 데이터베이스에 대한 MySQL 클라이언트 요청을 찾을 수 없습니다! 로그 형식은 다음과 같으며 모든 연결과 명령을 기록합니다.

문제는 기본적으로 확인되었습니다. 클라이언트 요청이 우리 측에 도달하기 전에 위 예외가 발생했습니다. 플랫폼과 반복적인 통신 및 확인을 거친 후 플랫폼은 이를 수행하기 전에 SQL 작업 테이블에서 이를 검색해야 했기 때문에 최종적으로 확인했습니다. SQL 및 업데이트 작업 상태 결과적으로 이 테이블에는 시간당 삽입 및 업데이트 동시성이 많아 잠금을 기다리는 동안 일부 SQL이 시간 초과됩니다. . .

MySQL 로그 분석 스크립트

이른 아침은 데이터 웨어하우스의 업무 피크이기 때문에 이 시간대에는 많은 문제가 발생하는데, 이 마을을 지나면서 이상한 문제들이 종종 사라지고 낮에는 매장이 재현되지 않습니다. 문제를 신속하게 찾기 위해 관심 있는 로그를 캡처하는 방법이 최우선입니다. 여기서는 crontab 배포라는 작은 스크립트를 작성했습니다. 열 시간 범위를 선택하고 매분 로그를 샘플링할 수 있으며 설명해야 할 내용은 다음과 같습니다. 일반 로그 쉽게 켜지 마십시오. 그렇지 않으면 데이터베이스 성능에 큰 손상을 줄 수 있습니다. ㅋㅋㅋ 공유 MySQL 교착상태 문제 해결 방법

위 내용은 MySQL 교착 상태 및 로그 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!