데이터 쿼리가 너무 많으면 MySQL이 OOM을 발생시킬지 여부에 대해 이야기해 보겠습니다.

이 기사는 데이터 쿼리가 너무 많은 경우 MySQL이 OOM을 발생시키는지 여부에 대한 관련 지식을 제공합니다.

호스트 메모리가 100G에 불과합니다. 이제 200G 대형 테이블의 전체 테이블을 스캔해야 합니다. DB 호스트의 메모리가 소모됩니까?

논리 백업을 하면 그냥 데이터베이스 전체를 스캔하는거 아닌가요? 이것이 메모리를 모두 소모한다면 오래 전에 논리적 백업이 실패하지 않을까요?

그래서 큰 테이블의 전체 테이블 스캔에는 문제가 없을 것 같습니다. 왜 이런가요?

전체 테이블 스캔이 서버 계층에 미치는 영향

이제 200G InnoDB 테이블 db1.t에서 전체 테이블 스캔을 수행한다고 가정합니다. 물론, 스캔 결과를 클라이언트에 저장하려면 다음과 같은 명령어를 사용하게 됩니다.

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

InnoDB 데이터는 기본 키 인덱스에 저장되므로 전체 테이블 스캔은 실제로 테이블의 기본 키 인덱스를 직접 스캔합니다. 티. 이 쿼리문에는 다른 판단 조건이 없기 때문에 발견된 각 행을 결과 세트에 직접 배치한 후 클라이언트에 반환할 수 있습니다.

그렇다면 이 "결과 세트"는 어디에 존재하나요?

서버는 전체 결과 세트를 저장할 필요가 없습니다. 데이터를 받고 보내는 과정은 다음과 같습니다.

행을 가져와서 "net_buffer"에 씁니다. 이 메모리의 크기는 "net_buffer_length"** 매개변수에 의해 정의되며 기본값은 16k입니다

**"net_buffer"**가 가득 찰 때까지 반복적으로 행을 가져오고 네트워크 인터페이스를 호출하여 전송합니다

전송이 다음과 같은 경우 성공하면 **" net_buffer"를 지우고 계속해서 다음 줄을 가져와 "net_buffer"에 씁니다**

전송 함수가 **"EAGAIN" 또는 "WSAEWOULDBLOCK"**을 반환하면 로컬 네트워크 스택(소켓 전송 버퍼)이 가득 찼습니다. 대기를 입력하세요. 네트워크 스택에 다시 쓸 수 있을 때까지 계속 전송

쿼리 결과 전송 프로세스

표시:

• 쿼리 전송 프로세스 동안 MySQL이 차지하는 최대 내부 메모리는 **"net_buffer_length"**입니다. 이렇게 크면 200G

• 소켓 전송 버퍼에 도달하지 못하고 200G에 도달하는 것이 불가능합니다(기본 정의 /proc/sys/net/core/wmem_default). 소켓 전송 버퍼가 가득 차면 읽는 과정이 진행됩니다. 데이터가 일시 중지됩니다

그래서 MySQL은 실제로 "읽고 보내는" 작업입니다. 이는 클라이언트가 느리게 수신하면 MySQL 서버가 결과를 보낼 수 없게 되어 트랜잭션 실행 시간이 길어진다는 의미입니다.

예를 들어 다음 상태는 클라이언트가 **"소켓 수신 버퍼"**의 내용을 읽지 않았을 때 서버의 show processlist에서 본 결과입니다.

서버 측 전송이 차단되었습니다

상태가 항상 "클라이언트로 전송 중"인 것을 보면 서버 측 네트워크 스택이 가득 찼다는 뜻입니다.

클라이언트가 –quick 매개변수를 사용하는 경우 mysql_use_result 메소드가 사용됩니다. 한 줄을 읽고 한 줄을 처리합니다. 특정 비즈니스의 로직이 상대적으로 복잡하고, 각 데이터 행을 읽은 후 처리해야 하는 로직이 매우 느리면 클라이언트가 다음 데이터 행을 가져오는 데 오랜 시간이 걸리고, 위의 결과가 나타날 수 있습니다.

따라서 일반적인 온라인 비즈니스의 경우 쿼리 결과가 많이 나오지 않는 경우에는 **"mysql_store_result"** 인터페이스를 사용하여 쿼리 결과를 로컬 메모리에 직접 저장하는 것이 좋습니다.

물론, 쿼리가 반환하는 결과가 많지 않다는 것이 전제입니다. 너무 많으면 대용량 쿼리가 실행되므로 클라이언트가 약 20G의 메모리를 차지하게 됩니다. 이 경우 대신 "mysql_use_result" 인터페이스를 사용해야 합니다.

당신이 유지 관리를 담당하고 있는 MySQL의 "클라이언트로 보내기"에 많은 스레드가 표시된다면 이는 비즈니스 개발 학생이 쿼리 결과를 최적화하고 반환된 결과가 그렇게 많은 것이 합리적인지 평가하기를 원한다는 의미입니다.

이 상태에서 스레드 수를 빠르게 줄이고 싶다면 **"net_buffer_length"**를 더 크게 설정하면 됩니다.

인스턴스의 많은 쿼리문 상태가 "데이터 전송 중"인 경우가 있는데, 네트워크 확인 시에는 문제가 없습니다. 데이터 전송에 시간이 오래 걸리는 이유는 무엇인가요?

쿼리 문의 상태 변경은 다음과 같습니다.

• MySQL 쿼리문은 실행 단계에 들어간 후 먼저 상태를 "데이터 전송 중"으로 설정합니다.

• 이후 컬럼 관련 정보를 보냅니다. 실행 결과(메타 데이터)를 클라이언트

• 에 전달한 다음

• 문 실행 프로세스를 계속합니다. 실행이 완료된 후 상태를 빈 문자열로 설정합니다.

즉, "데이터 전송"이 반드시 "데이터 전송"을 의미하는 것은 아니지만 실행자 프로세스의 모든 단계에 있을 수 있습니다. 예를 들어 잠금 대기 시나리오를 구성하고 데이터 전송 상태를 확인할 수 있습니다.

전체 테이블 읽기가 잠김:

Sending data 상태

session2가 잠금을 기다리고 있는 것을 볼 수 있으며 상태는 Sending data로 표시됩니다.

• 스레드가 '클라이언트의 결과 수신을 기다리는 중' 상태일 때만 '클라이언트에게 전송 중'이 표시됩니다.

• '데이터 전송 중'으로 표시되면 '실행 중'이라는 의미일 뿐입니다.

따라서 쿼리 결과가 세그먼트 단위로 클라이언트에 전송되므로 전체 테이블을 스캔하여 쿼리는 메모리를 폭발시키지 않고 많은 양의 데이터를 반환합니다.

위는 서버 레이어의 처리 로직입니다. InnoDB 엔진에서는 어떻게 처리되나요?

InnoDB에 대한 전체 테이블 스캔의 영향

InnoDB 메모리의 기능 중 하나는 업데이트된 결과를 저장하고 리두 로그와 협력하여 임의 디스크 쓰기를 방지하는 것입니다.

메모리의 데이터 페이지는 Buffer Pool(BP라고도 함)에서 관리되며, BP는 WAL에서 업데이트를 가속화하는 역할을 합니다.

BP는 쿼리 속도도 높일 수 있습니다.

WAL로 인해 트랜잭션이 커밋되면 디스크의 데이터 페이지가 오래되었습니다. 데이터 페이지를 즉시 읽어야 하는 쿼리가 있으면 데이터 페이지에 즉시 리두 로그를 적용해야 하나요?

필요없습니다. 이때 메모리 데이터 페이지의 결과는 최신이므로 메모리 페이지를 직접 읽으면 됩니다. 이때 쿼리는 디스크를 읽을 필요가 없으며 결과를 메모리에서 직접 가져오기 때문에 속도가 매우 빠릅니다. 따라서 버퍼 풀은 쿼리 속도를 높일 수 있습니다.

쿼리에 대한 BP의 가속 효과는 중요한 지표, 즉 메모리 적중률에 따라 달라집니다.

쇼 엔진 innodb 상태 결과에서 시스템의 현재 BP 적중률을 확인할 수 있습니다. 일반적으로 응답 시간이 요구 사항을 충족하도록 안정적인 서비스를 제공하는 온라인 시스템의 경우 메모리 적중률이 99% 이상이어야 합니다.

Show Engine innodb 상태를 실행하면 현재 적중률을 표시하는 "버퍼 풀 적중률"이라는 단어를 볼 수 있습니다. 예를 들어 아래 그림의 적중률은 100%입니다.

쿼리에 필요한 모든 데이터 페이지를 메모리에서 직접 얻을 수 있는 경우가 가장 좋으며 해당 적중률은 100%입니다.

InnoDB 버퍼 풀의 크기는 **"innodb_buffer_pool_size"** 매개변수에 의해 결정됩니다. 일반적으로 사용 가능한 물리적 메모리의 60%~80%로 설정하는 것이 좋습니다.

약 10년 전만 해도 단일 머신의 데이터 볼륨은 수백 기가바이트였으며, 물리적 메모리는 수 기가바이트였습니다. 이제는 많은 서버가 128G 이상의 메모리를 가질 수 있지만 단일 머신의 데이터 볼륨은 T 수준에 도달했습니다. .

그래서 **"innodb_buffer_pool_size"**가 디스크 데이터 크기보다 작은 것이 일반적입니다. 버퍼 풀이 가득 차고 디스크에서 데이터 페이지를 읽어야 하는 경우 이전 데이터 페이지를 제거해야 합니다.

InnoDB 메모리 관리

LRU(Least Recent Used) 알고리즘을 사용하여 가장 오랫동안 사용되지 않은 데이터를 제거합니다.

기본 LRU 알고리즘

InnoDB의 BP 관리용 LRU 알고리즘은 연결 목록

• 연결 목록의 헤드가 P1입니다. 이는 P1이 최근에 액세스한 데이터 페이지

• At임을 의미합니다. 이번에는 읽기 요청이 P3에 접근하므로 상태 2가 되고, P3가 앞으로 이동한다

• 상태 3은 이번에 접근한 데이터 페이지가 연결리스트에 존재하지 않는다는 뜻이므로 새로운 데이터 페이지 Px BP에 신청하고 Linked List의 Header에 추가해야 합니다. 하지만 메모리가 가득 차서 새로운 메모리를 요청할 수 없습니다. 그래서 연결리스트 끝에 있는 Pm 데이터 페이지의 메모리는 지워지고, Px의 내용은 저장되어 연결리스트의 선두에 놓이게 됩니다

결국, 존재하지 않은 데이터 페이지 Pm은 가장 오랫동안 액세스한 항목은 제거됩니다.

이때 전체 테이블 스캔을 하게 되면 어떻게 될까요? 200G 테이블을 스캔하려는 경우 이 테이블은 기록 데이터 테이블이며 일반적으로 어떤 기업도 이에 액세스하지 않습니다.

그런 다음 이 알고리즘에 따라 스캔하면 현재 BP의 모든 데이터가 제거되고 스캔 프로세스 중에 액세스된 데이터 페이지의 내용이 저장됩니다. 즉, BP는 주로 이 과거 데이터 테이블의 데이터를 저장합니다.

비즈니스 서비스를 제공하는 도서관의 경우 이는 불가능합니다. BP 메모리 적중률이 급격히 떨어지고 디스크 압력이 증가하며 SQL 문 응답이 느려지는 것을 볼 수 있습니다.

따라서 InnoDB는 원시 LRU를 직접 사용할 수 없습니다. InnoDB는 이를 최적화합니다.

향상된 LRU 알고리즘

InnoDB는 Linked List를 New 영역과 Old 영역을 5:3의 비율로 나눕니다. 그림에서 LRU_old는 전체 연결리스트의 5/8인 Old 영역의 첫 번째 위치를 가리킨다. 즉, 연결리스트의 선두 부근의 5/8이 New 영역이고, 연결리스트의 끝 부근의 3/8이 Old 영역이다.

개선된 LRU 알고리즘 실행 프로세스:

State 1, P3에 액세스해야 합니다. P3은 New 영역에 있으므로 최적화 전 LRU와 동일하므로 연결 목록의 선두로 이동 => State 2 이후

, 새로운 것에 접근해야 합니다. 현재 연결 리스트에 존재하지 않는 데이터 페이지의 경우 데이터 페이지 Pm은 이때 여전히 제거되지만 새로 삽입된 데이터 페이지 Px는 **"LRU_old"*에 배치됩니다. *

기존 영역의 데이터 페이지는 매번 삭제된다. 접속 시 다음과 같은 판단을 해야 한다.

LRU 링크드 리스트에 데이터 페이지가 1초 이상 존재하는 경우 링크드 리스트의 선두로 이동한다.

LRU 연결 리스트에 데이터 페이지가 1초 미만 동안 존재하면 위치는 변경되지 않습니다. 1s는 "innodb_old_blocks_time" 매개변수에 의해 제어되며 기본값은 1000, 단위 ms입니다.

이 전략은 전체 테이블 스캔과 같은 작업을 처리하도록 맞춤화되었습니다. 또는 200G 기록 데이터 테이블을 스캔합니다.

4. 스캔 과정에서 새로 삽입해야 하는 데이터 페이지는 이전 영역에 배치됩니다.

5. 데이터 페이지에는 여러 레코드가 있습니다. 여러 번 액세스할 수 있지만 순차적 스캔으로 인해 이 데이터 페이지의 첫 번째 액세스와 마지막 액세스 사이의 시간 간격은 1초를 초과하지 않으므로 이전 영역에 계속 유지됩니다

6. 후속 데이터, 이전 데이터 페이지는 다시는 접근할 수 없으므로 링크드 리스트(새 영역)의 선두로 이동할 기회가 절대 없으며 곧 제거될 예정입니다.

이 전략의 가장 큰 장점은 이 큰 테이블을 스캔하는 과정에서 BP도 사용하지만 Young 영역에는 전혀 영향을 미치지 않아 버퍼의 쿼리 적중률을 보장한다는 점이라고 볼 수 있습니다. 정상적인 업무에 대응하는 풀.

요약

MySQL은 계산과 전송을 동시에 하는 로직을 사용하기 때문에, 데이터 양이 많은 쿼리 결과의 경우 전체 결과 집합이 서버 측에 저장되지 않습니다. 따라서 클라이언트가 결과를 제때 읽지 못하면 MySQL 쿼리 프로세스가 차단되지만 메모리가 소모되지는 않습니다.

InnoDB 엔진의 경우 제거 전략으로 인해 대규모 쿼리로 인해 메모리 폭발이 발생하지 않습니다. 또한 InnoDB는 LRU 알고리즘을 개선했기 때문에 콜드 데이터의 전체 테이블 스캔이 버퍼 풀에 미치는 영향도 제어할 수 있습니다.

전체 테이블 스캔은 여전히 ​​IO 리소스를 소비하므로 업무량이 많은 기간에는 메인 데이터베이스에서 온라인으로 전체 테이블 스캔을 직접 수행할 수 없습니다.

추천 학습: mysql 비디오 튜토리얼

위 내용은 데이터 쿼리가 너무 많으면 MySQL이 OOM을 발생시킬지 여부에 대해 이야기해 보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!