트러블슈팅
DB Buffer Memory 문제
{
"error": "Error: target: chatting.-.primary: vttablet: rpc error: code = ResourceExhausted desc = Out of sort memory, consider increasing server sort buffer size (errno 1038)
}SELECT * FROM data ORDER BY created DESC LIMIT 20 OFFSET 0;
를 사용했을 시 sort buffer size가 부족하다는 문제가 발생했다.
MySQL에서는 데이터를 정렬(ORBER BY)하기 위해 별도의 메모리 공간을 할당하는데 이때 사용되는 메모리가 Sort buffer이다.
이 메모리는 정렬이 필요할 경우에만 할당되며, 쿼리 실행이 완료되면 시스템으로 즉시 반납된다.
Soft buffer의 크기는 시스템 설정 변수인 sort_buffer_size로 조정 할 수 있다.
sort_buffer_size는 byte단위로 표시된다.
data의 컬럼 중 image 컬럼의 사이즈가 문제였다.
기본 sort buffer size는 262144로 262KB였다.
프론트에서 이미지 크기를 최대 50KB로 넣을 수 있게끔 줄였기 때문에 25장 이상의 이미지를 sotring 하면 오류가 발생했다.
sort buffer size를 늘리는 방법으로도 문제를 해결할 수 있었지만, buffer size를 늘리는 방법은 CPU의 사용을 늘리는 방법이기 때문에 느려질 수 있다.
그래서 이 방법은 좋은 방법이라고 생각되지 않았다.
sort buffer size를 늘리는 방법으로도 문제를 해결할 수 있었지만, sort_buffer_size를 늘리면 메모리 부족현상을 겪을 수 있다.
또한 메모리가 부족할시 OS에서는 강제적으로 프로세스를 Kill할 수 있으므로 MySQL의 프로세스가 중지될 수도 있기 때문에 주의해야 한다.
///
Sort Buffer : MySQL이 정렬을 수행하기 위한 별도 메모리 공간. 정렬에만 이용, 가변적으로 증가하지만 최대 사이즈는 soft_buffer_size 시스템 변수, 그리고소트 완료후 즉시 시스템으로 반납되는 메모리공간이다. (이 사이즈를 넘길 경우 이제 문제가 된다)
읽고->SortBuffer에서정렬->Disk에 임시저장 -> 다음 읽고 -> 정렬 -> 임시저장 (Multi- Merge방식) 512K가 적당(P334)
SOrtBuffer를 크게한다고 해서 빨리 지지는 않지만, DISK I/O의 횟수를 줄일수 있다.
///
그래서 sort buffer size를 늘리지 않고 해결할 수 있는 방법을 찾기 위해 노력했다.
처음에는 서브쿼리의 임시테이블을 활용해 sort buffer memory 문제가 나지 않게끔 하는 방법을 생각했다.
SELECT
*
FROM (
SELECT *
FROM data
ORDER BY msg_id DESC
) AS Data
LIMIT 20
OFFSET 0;위 방법은 서브쿼리를 사용한 방법으로 해결을 한 방법이였다.
서브쿼리(임시 테이블)을 활용해 해결한 방법이였지만, 똑같이 ORDER BY를 사용한 sorting이기 때문에 추후에 메모리 문제가 날 것이라고 생각했다.
그래서 인덱스를 사용하기로 했다.
mysql 8.0 이상부터는 descending index가 적용된다. 연산이 더 빨리 될 수 있도록 unix time(int) 컬럼에 Descending Index를 만들어 정렬하게끔 시도했다.
SELECT
BIN_TO_UUID(msg_id, 1) AS msg_id,
content,
type,
time,
image,
created,
updated,
user,
version
FROM
bufferTest
WHERE
time >= (
SELECT
time
FROM bufferTest
LIMIT 1
OFFSET "subOffset추가"
)
LIMIT 20
OFFSET "mainOffset추가";
subOffset = page x limit(20);
mainOffset = (page - 1) x limit(20);Descending Index의 장점은 Forward Index Scan 방식이기 때문에 Backward Index Scan보다 속도가 훨씬 빠르다.
커서를 time 기준으로 최신순 정렬한 데이터에서 n번째로 잡았기 때문에 그 기준점보다 크거나 같은 데이터를 가져온다.
그렇기 때문에 속도적으로 조금 더 빠른 페이지네이션이 가능하다.
ASC로 정렬해서 커서값을 찾아야할 경우
전체 row 값을 받아오는 쿼리를 요청 후
다시 DB에 LIMIT과 OFFSET, 총 2번 쿼리요청을 해야했기 때문에 시간이 평균적으로 180~220 ms가 나왔다.
DESC 정렬 후 page 요청값 기준으로 검색하는 방법은 평균 80~100 ms로 훨씬 더 빠른 결과물이 나왔다.
그럼 왜 PK(msg_id)를 인덱스로 사용하지 않았는지?
-> Primary Key는 기본적으로 생성될 때 ASC 정렬이 되게끔 생성된다.
그래서 제가 추가적으로 DESC 정렬 인덱스를 msgid에 걸어봤는데 DESC 정렬이 되지 않았다.
그래서 쿼리에 "ORDER BY DESC"를 붙일 경우, Backward Index Scan이 들어가게 되는데
이 경우에는 Forward Index Scan 보다 느리게 검색된다.
https://tech.kakao.com/2018/06/19/mysql-ascending-index-vs-descending-index/
카카오 테크 블로그에서 테스트해본 결과 28.9% 정도 느려진다고 한다.
