[DB 오류/MySQL]1038 Out of sort memory, consider increasing server sort buffer size

잘 운영되던 게시판이 갑자기 에러가 난다고 해서 봤더니, sort memory가 충분하지 않아서 생긴 문제였다.

먼저 sorting 동장 방식을 살펴보면.. 

 

🟡MySQL에서 sorting 동작 방식

기본적으로 MySQL에서 자신의 buffer보다 큰 데이터를 대상으로 작업해야할 땐 위 그림과 같은 흐름으로 동작한다 .

 

• 작업용 버퍼에 데이터를 올리고, 그럼에도 작업 대상이 더 남았다면 버퍼에 올린 데이터를 temp file로 보낸다.
• 그 temp file들을 merge하고, merge한 값들 끼리 정렬한 뒤, 결과를 반환한다
• 그리고 buffer 메모리는 sort 완료 후 즉시 시스템으로 반납되는 메모리공간이다

 

위와 같이 동작하는 sorting 행위에서 sort buffer 메모리가 부족하면 나타나는 오류다.

에러메세지에 써있느 것과 같이 sort_buffer_size를 늘려주면 간단하게 해결되는 문제다.

 

기본 사이즈는 262144 바이트 였으나, 256000000 바이트로 셋팅을 바꿔주었다.

(바로 적용이 안돼서 서버 재부팅 필요했음)

그러니까 다시 아무렇지 않게 정상 작동 되었다.

SHOW GLOBAL variables LIKE "sort_buffer_size";
//기본 셋팅 : 262144;

SET GLOBAL sort_buffer_size = 256000000;

 

---

그러나, sort_buffer_size를 늘리면 메모리 부족현상을 겪을 수 있다. 또한 메모리가 부족할시 OS에서는 강제적으로 프로세스를 Kill할 수 있으므로 MySQL의 프로세스가 중지될 수도 있기 때문에 주의해야 한다.

 

다른 방법이 무엇이 있는지 찾아보니,

 

1) 정렬 기준이 되는 컬럼을 인덱스로 지정

2) (가능하다면) json 타입의 컬럼을 text 타입으로 변경

3) select 절에서 json 같은 무거운 컬럼을 제외

4) 쿼리의 order by 부분이 index를 타도록 잘 설계하여 file sorting부분을 아예 없애버리기

 

나는 1-3번은 이미 해결이 되어 있어서 4번 쿼리를 변경하는 방법을 사용해보려했으나, 너무 간단한 SELECT 쿼리여서 현재 쿼리가 최선인 것 같아 sort_buffer_size를 늘려주는 방식을 택했다.

 

이런 에러가 또 난다면 다른 컬럼도 인덱스 지정을 해주고, 꼭 필요한 컬럼들만 조회하는게 좋지 않을까 생각이 든다.

서브쿼리도 더 효율적으로 사용할 수 있는지 확인해봐야겠다.

 

---

🔽참고 블로그

https://kimdubi.github.io/mysql/sort_buffer/

MySQL Out of sort memory 에러 발생 원인 디버깅

 

https://velog.io/@mingle-mongle/%ED%8A%B8%EB%9F%AC%EB%B8%94%EC%8A%88%ED%8C%85

트러블슈팅