MySQL: "테이블 메타데이터 잠금 대기 중" 문제에 대한 영구 솔루션

Question

My MySQL 데이터베이스는 세 가지 웹 애플리케이션의 스토리지 백엔드 역할을 합니다. 그러나 최근 "테이블 메타데이터 잠금을 기다리는 중"이라는 오류가 영구적으로 발생했습니다. 이런 일이 거의 항상 발생하는데 그 이유를 모르겠습니다.

으아악

물론 해당 프로세스를 종료할 수도 있습니다. 그러나 "bookmaker3" 데이터베이스에 대한 테이블 구조를 생성하려는 프로그램을 다시 시작하면 새로 생성된 프로세스가 다시 Metallock에 있습니다.

데이터베이스를 삭제할 수도 없습니다.

으아악

이렇게 하면 금속 자물쇠도 만들어집니다.

이 문제를 해결하는 방법은 무엇입니까?

Answer 1

안타깝게도 허용된 솔루션이 잘못되었습니다. 정말 맞습니다

이것은 확실히(거의 확실히; 아래 참조) 해야 할 일입니다. 그런데 그게 드러났어요,

...그리고 1398은 자물쇠에 대한 연결이 아닙니다. 어떻게 그래? 1398은 잠기기를 기다리는 연결입니다. 이는 아직 잠금을 획득하지 않았으므로 종료해도 아무런 효과가 없음을 의미합니다. 잠금을 보유하고 있는 프로세스는 여전히 잠금을 보유하고 다음 스레드가 무언가를 수행하려고 시도하므로 또한 중지하고 적절한 순서로 "메타데이터 잠금 대기"를 입력합니다.

"메타데이터 잠금 대기 중"(WFML) 프로세스도 차단되지 않을 것이라고 보장할 수는 없지만 WFML 프로세스를 종료하는 것만으로도 완전히 아무 일도 하지 않습니다 .

실제 이유는 다른 프로세스가 잠금을 잡고 있기 때문입니다. 그리고 더 중요한 것은 SHOW FULL PROCESSLIST 어떤 프로세스인지 직접 알려주지 않습니다 .

한 가지 확신할 수 있는 것은 "메타데이터 잠금 대기 중"이라고 표시된 프로세스가 없다는 것입니다. 이 사람들이 피해자라고 할 수 있습니다.

SHOW FULL PROCESSLIST WILL은 프로세스가 무언가를 수행 하는지 알려줍니다. 그렇습니다. 일반적으로 작동합니다. 여기에서 잠금을 유지하는 프로세스는 아무 일도 하지 않으며, 역시 아무 일도 하지 않고 "휴면 중"으로 보고되는 다른 스레드에 숨겨져 있습니다.

SHOW FULL PROCESSLISTDML을 실행하는 프로세스나 "데이터 전송 중" 상태가 표시된다면 그것 이 범인이 거의 확실합니다. 다른 프로세스는 잠금이 해제되기를 기다리고 있습니다(암시적 잠금일 수 있습니다. 프로세스는 실제로 다른 방식으로 잠그는 LOCK TABLE을 전혀 발행할 필요가 없습니다). 그러나 프로세스는 작업을 수행하지 않는 동안 잠금을 유지할 수 있으며 적절하게 "휴면"으로 표시됩니다.

OP의 경우 범인 은 프로세스 1398 이전에 시작된 프로세스 1396이 현재 睡眠 상태이고 46초 동안 지속된 것이 거의 확실합니다. 1396이 필요한 모든 작업을 수행한 이후(지금은 잠자기 상태이며 46초 동안 MySQL에 관한 한) in 스레드가 들어오지 않아 잠금을 유지할 수 있습니다. 보류 전에 절전 모드로 설정합니다(그렇지 않으면 1396도 중지됩니다).

MySQL의 "교착 상태 없는" 잠금 정책으로 인해 어떤 프로세스도 잠금을 보유하고 잠금을 해제한 후 다시 잠금을 복원할 수 없습니다. 따라서 잠금 대기는 항상 잠금을 보유하고 있으며 이전에 보유한 적이 없는 프로세스로 인해 발생합니다. 이는 잠금 "큐"가 순서임을 보장하므로 유용합니다(아래에서 이 사실을 활용하겠습니다).

중요: 제한된 사용자로 MySQL에 연결하면 SHOW FULL PROCESSLIST모든 프로세스가 표시되지 않습니다 . 따라서 사용자가 볼 수 없는 프로세스에 의해 잠금이 유지될 수 있습니다.

그래서: SHOW FULL PROCESSLIST가 모든 것을 보여주고 running프로세스를 보여준다면, 그 프로세스는 아마도 책임이 있을 것이고 당신은 그것이 하고 있는 모든 일이 끝날 때까지 기다려야 합니다(또는 당신은 그것을 죽일 수 있습니다 - 당신의 책임하에).

이 답변의 나머지 부분은 프로세스가 뚜렷한 이유 없이 기다리고 있고 아무도 아무것도 하지 않는 것처럼 보이는 혼란스러운 상황을 다룹니다.

더 좋음显示进程列表

으아아아

위는 SLEEP에 있는 프로세스만 표시하도록 조정할 수 있으며 어쨌든 시간 내림차순으로 정렬하므로 정지된 프로세스를 찾는 것이 더 쉽습니다(순서로 인해 일반적으로 메타데이터를 기다리는 중 "직전"임) lock" 잠을 자면 대기 시간보다 항상 더 많은 잠

중 하나입니다.

중요한 사항

"메타데이터 잠금 대기" 프로세스를 별도로 유지하세요.

빠르고 더러운 솔루션, 별로 권장하지는 않지만 빠릅니다.

"메타데이터 잠금 대기 중" 상태인 가장 오래된 스레드보다 오래된 동일한 데이터베이스에서 "휴면" 상태인 모든 프로세스를 제거합니다. Arnaud Amaury

가 할 일은 다음과 같습니다.

• WaitingForMetadataLock에 스레드가 하나 이상 있는 모든 데이터베이스의 경우:
  이 데이터베이스의 WFML에서 가장 오래된 연결은 Z초입니다
  Z보다 오래된 이 데이터베이스의 모든 "휴면" 스레드는 죽어야 합니다. 만약을 대비해 가장 신선한 것부터 시작하세요.
  • 해당 데이터베이스에 오래된 비잠자기 연결이 있다면 아마도 잠금을 유지하고 있는 연결일 것입니다. 하지만 뭔가를 하고 있는 것입니다
  . 물론 종료할 수도 있지만, 특히 업데이트/삽입/삭제인 경우에는 위험을 감수해야 합니다.
  • KILL각 KILL 후에 상황을 재평가하고 그에 따라 프로세스를 다시 시작하세요. 대기 중인 프로세스가 지금 실행 중일 수도 있고 잠시 실행 중이었으며 지금은 휴면 상태일 수도 있습니다. 이제는 새로운 메타데이터 잠금을 보유할 수도 있습니다
  .

100번 중 99번, 삭제될 스레드는 가장 어린잠자고 있는 스레드이고 이전 스레드보다 오래된 스레드입니다. 메타데이터 잠금을 기다리고 있습니다.

으아아아

(*) TIME 순서에는 실제로 밀리초가 있거나 표시되지 않는다고 들었습니다. 따라서 두 프로세스 모두 시간 값이 19인 반면 가장 낮은 프로세스는 더 젊습니다.

좀 더 집중된 수리

SHOW ENGINE INNODB STATUSSHOW ENGINE INNODB STATUS를 실행하고 "TRANSACTION" 섹션을 살펴보세요. 무엇보다도 다음과 같은 것을 찾을 수 있습니다

으아악

이제 SHOW FULL PROCESSLIST를 사용하여 스레드 ID 1396이 트랜잭션 #1701로 무엇을 하고 있는지 확인합니다. "수면" 상태일 가능성이 높습니다. 따라서 활성 잠금이 있는 활성 트랜잭션(#1701)은 실행 취소 로그 항목이 있기 때문에 일부 변경을 수행했지만... 현재는 유휴 상태입니다. This는 당신이 죽여야 할 스레드입니다. 이러한 변경 사항은 손실됩니다.

MySQL에서 아무것도 하지 않는다고 해서 일반적으로 아무것도 하지 않는다는 의미는 아닙니다. MySQL에서 일부 레코드를 가져와 FTP 업로드용 CSV를 작성하는 경우 FTP 업로드 중에 MySQL 연결이 유휴 상태입니다.

실제로 MySQL을 사용하는 프로세스와 MySQL 서버가 Linux를 실행하는 동일한 시스템에 있고 루트 권한이 있는 경우 요청된 연결 잠금을 소유한 프로세스를 알아내는 방법이 있습니다. 이를 통해 프로세스가 strace -ff -p pid실제로 작업을 수행하고 있는지 여부(CPU 사용량 또는 최악의 경우 )를 판단하여 누군가를 죽이는 것이 안전한지 판단하는 데 도움이 됩니다.

왜 이런 일이 발생하나요?

저는 "지속적" 또는 "풀링된" MySQL 연결을 사용하는 웹 애플리케이션에서 이런 일이 발생하는 것을 보았습니다. 이제 일반적으로 시간 절약이 거의 없습니다. 웹 애플리케이션 인스턴스는 종료되지만 연결은 아니므로 아직 잠겨 있습니다. 살아있고...다른 사람을 차단하세요.

제가 발견한 또 다른 흥미로운 접근 방식 은 위의 가설에서 일부 행을 반환하는 쿼리를 실행하고 그 중 일부만 검색하는 것입니다 . 쿼리가 "자동 정리"로 설정되지 않은 경우(그러나 기본 DBA는 설정함) 연결을 열린 상태로 유지하고 테이블에 대한 전체 잠금을 방지합니다. 나는 행을 선택하고 오류가 있는지(존재하지 않음) 또는 존재하지 않는지(존재해야 함) 확인하여 행이 존재하는지 확인하는 코드 조각을 사용하여 이 문제를 실행했지만 실제로 행을 검색하지 않고.

PHP 및 PDO

PDO에는 지속적인 연결 기능이 있습니다. 이것이 PDO가 연결을 풀링하지 않고 각 연결을 닫지 않도록 하는 방법입니다. 매우 지저분합니다.

열 때 옵션을 설정하세요(네 번째 옵션은 새 PDO()입니다):

으아악

연결이 끊어졌을 때:

으아악

데이터베이스에 물어보세요

최신 MySQL을 사용하는 경우 너무 새롭지는 않지만 더 이상 사용되지 않을 예정이므로 범인을 찾는 또 다른 방법은 (다시 권한 정보 모드가 필요함)

으아악

실제 솔루션에는 시간과 노력이 필요합니다

다음 아키텍처로 인해 문제가 자주 발생합니다.

웹 애플리케이션이 종료되거나 웹 애플리케이션 경량 스레드 인스턴스가 종료되면 컨테이너/연결 풀이 되지 않을 수 있습니다. 연결을 열어두는 것은 컨테이너이므로 분명히 연결은 닫히지 않습니다. 예상대로 MySQL은 작업이 완료된 것으로 간주하지 않습니다.

웹 앱이 자체적으로 정리되지 않으면(거래 없음 ROLLBACK或COMMIT，没有UNLOCK TABLES 등) 웹 앱이 시작한 모든 작업이 여전히 존재하며 다른 모든 사람을 차단할 수 있습니다.

그럼 해결책은 두 가지입니다. 더 나쁜 것은 유휴 시간 제한을 줄이는 것입니다. 하지만 쿼리 사이에 너무 오래 기다리면 어떻게 되는지 추측해 보세요(문자 그대로: "MySQL 서버가 사라졌습니다"). 그런 다음 사용 가능한 경우 mysql_ping을 사용할 수 있습니다(곧 더 이상 사용되지 않을 예정입니다. PDO에는 해결 방법이 있습니다 . 대안으로 에서 오류를 확인하고 오류가 발생하면 연결을 다시 열 수 있습니다(이것은 Python 방식입니다). 적은 공연비 - 가능합니다

.

더 나은, 더 스마트한 솔루션은 구현이 그렇게 간단하지 않습니다. 스크립트가 자체적으로 정리되도록 하고, 모든 행을 검색하거나 모든 쿼리 리소스를 해제하고, 모든 예외를 포착하여 올바르게 처리하거나, 가능하다면 지속 연결을 완전히 건너뛰세요. 각 인스턴스가 자체 연결을 생성하도록 하거나 스마트 풀 드라이버를 사용(PHP PDO에서는 PDO::ATTR_PERSISTENT 显式设置为 false 사용)하세요.

또는(PHP와 마찬가지로) 소멸자와 예외 처리기가 트랜잭션을 커밋하거나 롤백하여 연결을 강제로 정리하도록 할 수 있으며(충분해야 함) 명시적인 테이블 잠금 해제 및 RELEASE_ALL_LOCKS()를 실행하거나 제출할 수도 있습니다. 좋은 결과를 위해 연결 자살(KILL CONNECTION_ID()).

기존 결과 집합 리소스를 쿼리하여 해제하는 방법을 모르겠습니다. 유일한 방법은 이러한 리소스를 개인 배열에 저장하는 것입니다.