MySQL 튜닝의 기록 (22.11~12 HospitAI Talk 프로젝트)

AWS RDS 의 parameter group 설정

 

설정1 (이후 “ warning get packets out of order …” 와 “too many connection error” 가 사라짐)

• innodb_dedicated_server : true
• innodb_thread_concurrency : 2 (일단 cpu 코어 수에 맞춤)
• innodb_thread_sleep_delay: 1000 마이크로초 (default 10000)
• max_connections 300
• wait_timeout 180
• interactive_timeout 180

 

설정2 : Cache Miss Rate가 엄청나게 높아서, thread cache size를 올려봄.

• thread_cache_size = 32

 

설정3: Connection Miss Rate는 낮고, Cache Miss는 좀처럼 떨어지지 않으며, Connection Usage는 증가하므로.

• max_connections : 500
• thread_cache_size : 48
• wait_timeout 60
• interactive_timeout 60
• + nginx 로드밸런싱을 least_conn 으로.

 

설정4: Cache Miss Rate 가 점차 관리되는 모습을 보이고 있지만 Aborted_connects가 다시 증가했기 때문에, timeout을 다시 늘림.

• wait_timeout 180
• interactive_timeout 180

 

---

위 설정에 따른 종속변수들의 변화

Cache Miss Rate(%) =  (Threads_created / Connections) * 100

• default : 3431/11771*100 = 약 29%
• 설정1 이후 : 239/297*100 = 약 80%
• 설정2 이후 : 262/339*100 = 약 77%
• 설정3 이후 : 277/498*100 = 약 55%
  • 통합된 부하테스트 : 523/1270*100 = 약 41%
  • 몇 번 artillery로 초당100개씩 돌린 후의 수치이다.
  • 그러나 threads_created는 좀처럼 늘지 않았다.

 

Connection Miss Rate(%) = (Aborted_connects / Connections) * 100

• default : 3062/11771*100 = 약 26%
• 설정 1 이후 : 0/297 = 0%
• 설정2 이후 : 0/339 = 0%
• 설정3 이후 : 4/498 = 약 0.8%
  • 통합된 부하테스트 : 180/1270*100 = 약 14%

 

Connection Usage(%) = (Threads_connected / max_connections) * 100

• default : 8/63*100 = 약13%
• 설정1 이후 : 239/300*100 = 약 80%
• 설정2 이후 : 262/300*100 = 약 87%
• 설정3 이후 : 2/500*100 =0.4% (커넥션이 잘 release 되고 있다는 뜻)
  • 통합된 부하테스트 : 2/500

---

위 설정이후의 부하테스트 및 속도측정

구슬픈 수기 기록

• https://docs.google.com/spreadsheets/d/1ILjAfIfdlNYAs_gVN2UUdRkce3DTqxV3_mVMqAYyZV8/edit?usp=sharing

Artillery 기록

• https://docs.google.com/presentation/d/1JxPWE2DrFOKO5EByrlw73IcA6A_ryyNMwjpNwhFoUIA/edit#slide=id.p

 

프로젝트 기간 중 쿼리와 데이터셋이 수차례 바뀌었다.

가장 응답속도가 빨랐던 때는 아래와 같다.

당시의 응답속도는 10ms 보다도 빨랐다.

• 주소를 full-text index로 검색 + 병원의 오픈시간을 substring으로 검색하던 쿼리
• 테이블을 서울시 25개 자치구별 리스트 파티션으로 나누고, 그 파티션 내에서 다중칼럼 index를 사용하던 쿼리

 

그러나 서비스의 질을 위해 데이터를 교체하고 좌표 계산 및 거리 정렬을 추가했다.

• 이후 좌표값을 범위 검색을 해야 했고, 인덱스의 활용은 제한될 수밖에 없었다.
• 한 칼럼을 인덱스로 범위 검색을 하고나면 이후의 칼럼은 인덱스를 타지 않기 때문.
• 검색조건에서의 부하가 가중된 상황에서 (서비스의 질과 속도를 trade off한 셈이다.) 취할 수 있는 최선의 조치를 모색해야 해야했다. 여러모로 다른 대안의 가능성을 타진해 봤지만, 최종적으로 선택한 것은 다소 평범하다.
  • 쿼리 실행 계획에서 Using Filesort를 제거하고 (좌표 및 거리계산 함수를 따로 모듈화하여 뺐다.)
  • Covering Index를 활용해 쿼리 실행계획의 extra 항목에 Using Index를 띄워 Disk I/O를 최소화 

 

그럼에도 응답속도는 이전에 비해 끔찍한 수준이었다. 물론 이는 서비스의 질을 위해 속도를 trade off했으므로, 수인해야 하는 결과이기는 했다.

• 하지만 한개의 쿼리로 수십개의 병원을 8~9ms의 속도로 찾아오다가, 50ms를 상회하는 속도를 지켜보자니 도무지 견딜 수 없었다...
• 결국 AWS RDS의 파라미터 값들을 직접 건드리며 MySQL 튜닝을 해보기로 결정했다.
• 앞서 적은 설정값들이 그 내역이다.
• 아래는 튜닝 이후의 부하테스트 및 속도 기록이다
  • 초당 100건씩 10초 부하 중위값 : 기존 응답시간의 약 1/5 (21.8%)

  

  

  • 초당 100건씩 10초 부하 p95 : 기존 응답시간의 약 1/7 (14%)

 

• 중위값은 안정적이지만, p95 세자리수는 눈에 너무나 거슬린다...
• 여전히 튜닝은 진행 중이며, 기록은 업데이트 될 것이다.

 

 

---

하기의 내용은 책을 인용한 것이아니라면, 프로젝트 중 여러 블로그를 참조한 것으로서 정확하지 못하다. 

Connection Usage가 100% 라면 max_connections 증가시켜야.

• 이 경우 wait_timeout을 최대한 작게. 10~20정도 추천.

Connection Miss Rate 가 1% 이상 된다면 wait_timeout을 좀 더 길게 잡는 것이 좋다.

 

Cache Miss Rate 가 높다면 thread_cache_size를 기본값 8보다 높게.

• default가 8. 통상적으로 적어도 16이상. → 현재 16으로 변경.
• thread_cahce_size

이 변수(thread_cache_size)를 설정해도 즉각적인 효과는 없습니다. 다음 번에 연결이 닫힐 때 효과가 나타납니다. 이 때 … 캐시에 스레드를 저장할 공간이 있는지 확인 … 다른 연결에서 재사용할 수 있도록 스레드를 캐시 … 그렇지 않으면 스레드를 캐시하는 대신 스레드를 종료합니다. 이 경우 캐시의 스레드 수와 스레드 캐시가 사용하는 메모리 양이 즉시 감소하지는 않습니다. 새 연결이 이것을 사용하기 위해 캐시에서 스레드를 제거하는 경우에만 감소합니다.(MySQL은 연결이 닫힐 때만 캐시에 스레드를 추가하고 새 연결이 생성될 때만 캐시에서 스레드를 제거합니다.) - <MYSQL 성능최적화>, 119쪽