라이믹스 2.1.5로 업데이트 하고나서 댓글 목록에 들어가보니 발견한 증상인데요, 그 이전에도 동일한 문제가 있었는지는 확실하지 않은 상태입니다.
관리자 페이지 > 댓글 관리 페이지에 들어가면 거의 30초를 기다려야 페이지가 뜨는 상황인데요
아래와 같이 댓글 개수는 불러오지만 리스트는 불러오지 못하고 있는 상황입니다.
아래와 같이 슬로우 쿼리도 출력되고 있습니다.
쿼리 내용만 보면 크게 문제는 없어보입니다만, 이상하게 속도가 느린 상황입니다.
서버 상황을 보면 30초 이상 걸리는 쿼리는 부하라 판단하고 중단하는 세팅이 되어있는것 같고, 이로 인해 목록이 출력되지 않고 있다고 추측하고 있습니다.
DESCRIBE 결과는 아래와 같습니다.
카운트 쿼리는 단순히 row 숫자만 쿼리하고, 인덱스를 정상적으로 타고 있는데 9초에 가까운 시간이 소요되고 있는것으로 보입니다.
count(*)이 row를 전체 훑는 쿼리라 어느정도 부하는 예상하고 있지만, 2천만건에 가까운 데이터를 쿼리해본건 처음이라 이게 정상인지 판단이 어렵네요..
그리고 실제 내용을 불러오는 두번째 쿼리는 인덱스를 타고있지 않아서 느린 것으로 추측되는데, 원래 서비스를 관리하시던 분께 여쭤보니 원래는 이렇게 느리지 않았다고 하시더라고요
서버 스펙은 위의 대용량 데이터를 처리하기에 부족한 성능은 아닌것으로 보입니다.
이번에 라이믹스 1.9.9.6에서 라이믹스 2.1.4를 거쳐, 라이믹스 2.1.5로 업그레이드를 진행했는데요
제가 이렇게 큰 서비스는 다뤄본적이 없어서 어떻게 접근해야 할지 조언을 구해보고자 질문 올립니다.
1. 이게 라이믹스 문제일까요? 아니면 서버 스펙과 세팅에 대한 문제일까요?
2. 위 슬로우 쿼리 문제를 해결하려면 어느 부분을 의심하고, 분석해보는것이 좋을까요?
3. 1.9.9.6 -> 2.1.4는 서버를 내리고 전체 업로드를 진행했으나, 2.1.4 -> 2.1.5로 업그레이드 할 때는 운영 서버를 멈출 수가 없는 상황이라 변경파일 부분 업로드만 진행을 했습니다.
혹시 2.1.5 전체 파일을 다시 업로드 해보는게 문제 해결에 도움이 될까요? (중간에 퍼미션 이슈가 발생했어서, 권한 설정하고 다시 업로드 진행했습니다. 깔끔하게 업로드가 완료되지 않았나 싶은 의심도 드네요..)
감사합니다.
1. 두 테이블을 조인해서 1900만 개의 row를 카운트하는 데 9초가 걸리는 것은 대략 정상적인 범위입니다. 20년 전에 만들어진 아주 단순한 MyISAM 테이블이 아닌 이상, row 숫자를 어디다 따로 저장해 놓았다가 그냥 가져오는 것이 아니거든요. 현재 트랜잭션에서 볼 수 있어야 하는 row가 몇 개인지 정말로 세어서 반환합니다. 조인이 들어간다면 두 테이블에 공통으로 존재하는 row를 걸러내야 하니 더더욱 그렇고요.
2. 첫 페이지 목록 30개를 불러오는 쿼리는 인덱스를 잘못 타고 있네요. 이렇게 인덱스를 잘못 타서 Using filesort가 뜨면 인덱스를 안 타는 것과 거의 차이가 없을 수도 있습니다.
MySQL이나 MariaDB는 ORDER BY절에 등장하는 컬럼에 걸려 있는 인덱스를 무척 선호하는 경향이 있고, 실제로 그것이 합리적인 선택일 가능성이 높습니다. 이 쿼리는 comments 테이블의 list_order 컬럼에 걸려 있는 idx_list_order 인덱스를 타야겠지요. 그런데 카운트 쿼리는 documents 테이블에서 참조하지도 않는 is_notice 인덱스를 타고 있고, 두 번째 쿼리는 조인에 필요한 인덱스 외에는 거의 쓰지 않고 있습니다.
원인이라면 1) comments 테이블에 있어야 할 인덱스가 누락되었을 가능성 -- 웹이 아닌 다른 분야에서 DB를 배우신 분들이 XE의 DB를 열어보고는 인덱스가 너무 많다며 임의로 지워버리시는 경우가 종종 있습니다;;; 2) documents, comments 두 테이블 모두 ANALYZE TABLE을 한 지 오래 되어서 (또는 백업/복구 후에 ANALYZE TABLE을 한 적이 없어서) 쿼리 옵티마이저가 인덱스의 상태를 제대로 파악하지 못하고 있을 가능성 등이 있습니다.
(filesort를 방지한답시고 sort buffer 크기를 늘리는 것은 해결책이 아닙니다.)
3. 댓글 불러오는 부분과 관련해서 최근에 변경된 것은 없을 텐데요. 저건 DB 튜닝 문제이지 소스 문제가 아닙니다. 댓글 수가 몇 배 더 많은 사이트도 카운트 쿼리만 느릴 뿐, 두 번째 쿼리는 0.001초만에 결과를 반환합니다.
4. 서버 전체의 스펙은 개별적인 쿼리의 소요시간에 큰 영향을 주지 않습니다. 고스펙 서버는 더 많은 쿼리를 동시에 실행할 수 있을 뿐입니다.