질문/조언질답게시판
Extra Form
PHP PHP 7.2
CMS Rhymix

이미지 1.png

 

nginx + php 7.2 + php-fpm + mariadb 로 구성된 서버입니다.

20기가 메모리에서 14기가를 mysql에 주고 6기가로  php-fpm에 설정했습니다.

기진곰님의 메모리 관리에 관한 글이 도움이 많이 되었습니다.

 

 

요근래 mysql이 cpu 부하가 100%를 넘기더니 이제는 200%에 육박합니다.

몇가지 원인을 찾다가 라이믹스 디버그 기능으로 쿼리를 찾아보는데 결과가 아래와 같습니다.

 

 

Slow Triggers (1)

01. document.getDocumentList.before
  • Target: supercachecontroller.triggerBeforeGetDocumentList
  • Exec Time: 21.3309 sec

 

 

슈퍼캐쉬와 관련이 있는건지 일단 슈퍼캐쉬를 지우거나 끄지는 않았고, 글 작성후에 슈퍼캐쉬를 만져볼려고 합니다.

 

질문은 제가 몇가지 설정을 만지고 이런 결과가 나온거 같은 기억이 나는데 그게 뭘 만졌는지는 정확하게 기억이 안나고 있습니다.

혹시 저와 같은 경험을 해보신 분이 계신지 궁금합니다.

 

 

슬로우 쿼리 2차 증세가 나왔네요. 이건 슈퍼캐쉬를 끄고 나오는 결과입니다.

 

Slow Queries (2)

01. select count(*) as "count" FROM `xe_documents` AS `documents` WHERE `module_srl` IN (?) AND `is_notice` = ? AND `status` IN (?,?)
  • Caller: modules/document/document.model.php:254
    (documentModel->getDocumentList)
  • Connection: master (localhost)
  • Query ID: document.getDocumentList
  • Query Time: 4.9970 sec
  • Result: success
02. SELECT `documents`.`title`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`nick_name`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` AS `documents` WHERE ( `module_srl` IN (?) AND `is_notice` = ? AND `status` IN (?,?) ) AND `documents`.`list_order` <= 2100000000 ORDER BY `documents`.`list_order` asc LIMIT 0, 20
  • Caller: modules/document/document.model.php:254
    (documentModel->getDocumentList)
  • Connection: master (localhost)
  • Query ID: document.getDocumentList
  • Query Time: 11.1768 sec
  • Result: success

 

계속 테스트 해보는데 ducument 모듈에서 슬로우 쿼리가 걸립니다.

 

Slow Queries (2)

01. select count(*) as "count" FROM `xe_documents` AS `documents` WHERE `module_srl` IN (?) AND `is_notice` = ? AND `status` IN (?,?)
  • Caller: modules/document/document.model.php:254
    (documentModel->getDocumentList)
  • Connection: master (localhost)
  • Query ID: document.getDocumentList
  • Query Time: 8.0621 sec
  • Result: success
02. SELECT `documents`.`title`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`nick_name`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` AS `documents` WHERE ( `module_srl` IN (?) AND `is_notice` = ? AND `status` IN (?,?) ) AND `documents`.`list_order` <= 2100000000 ORDER BY `documents`.`list_order` asc LIMIT 0, 20
  • Caller: modules/document/document.model.php:254
    (documentModel->getDocumentList)
  • Connection: master (localhost)
  • Query ID: document.getDocumentList
  • Query Time: 23.9833 sec
  • Result: success

 

그럼, 이걸 어떻게 해석해야 할까요.

 

mysql이 부하가 걸려서 라이믹스에서 슬로우 쿼리가 걸리는 건지..

아니면 라이믹스에서 슬로우 쿼리가 걸려서 mysql cpu 부하가 걸리는 건지..

 

 

  • profile

    DB에 문제가 있어서 쿼리를 실행하는 데 8~23초나 걸리니까 라이믹스에 슬로우 쿼리라고 뜨는 거죠. 라이믹스는 슬로우 쿼리를 유발하지 않습니다. 슬로우 쿼리가 발생하면 곧이곧대로 보고할 뿐입니다.

     

    슈퍼캐시는 느린 쿼리 결과를 캐싱하여, 느린 쿼리가 자주 발생하지 않도록 해줄 뿐입니다. 슈퍼캐시를 끄면 본색이 드러나지요. 게시판에 글 수가 많다면 getDocumentList 쿼리에 긴 시간이 걸리는 것은 무척 흔한 일입니다.

     

    1. 목록에서 공지글을 제외하는 옵션을 꺼 보세요. 무척 간단한 옵션인데 의외로 이 옵션이 성능에 큰 영향을 줍니다. 글이 많이 쌓여 있는 사이트라면 무조건 끄고 (즉, 공지글이 목록에 중복 노출되는 상태로) 운영하시기를 권장합니다. 쿼리문을 자세히 보면 아시겠지만 WHERE 조건이 2개에서 3개로 늘어나거든요. 2개까지는 적절한 복합인덱스를 탈 수 있는데, 3개가 되면 DB가 난감해합니다.

     

    2. DB에서 직접 ANALYZE TABLE xe_documents; 쿼리를 실행해 보세요. 그동안 누적된 데이터 분량이 많고 최근에 DB 튜닝값이 변경되었다면 주요 테이블에서 이걸 한 번씩 실행해 줘야 쿼리 최적화가 제대로 됩니다.

  • profile profile
    답변 감사합니다.
    평소 라이믹스로 사이트 운영하며 관리만하다가 문제가 생겨서 직접 해결하려고 하는 건 익숙치가 않네요. 말씀하신 슈퍼캐쉬나 라이믹스 구조에서 쿼리 최적화는 이번에 직접 몇가지 테스트해보니 정말 잘 짜여있다는 걸 알 수 있었습니다.

    최근에 100만개 정도의 도큐먼트 파일을 입력하였는데 아마 이때부터 그런 문제가 생긴 것 같기도 하네요. 말씀해주신 방법으로 진행을 해보도록 하겠습니다. 너무 감사드립니다.
  • profile profile
    100만개 ㄷㄷㄷ
    샤딩이든 파티셔닝이든 하셔야할듯
  • profile profile
    DB 튜닝이 잘 되어 있고 쿼리에 불필요한 조건이 들어가지 않는다면 고작 100만개 정도로 파티셔닝이 필요하지는 않습니다. 현존하는 대부분의 DB는 억 단위의 데이터도 효율적으로 처리할 수 있도록 설계되어 있으니까요.^^
  • profile profile

    공지 제외 옵션이 부하을 많이 주는거군요 이 댓글에서 알게 되었네요. 무심코 다 클릭해서 쓰고 있었는데 모두 해제했습니다.

  • ?

    is_notice가 인덱스가 안걸려있나보군요...
    게다가 xe는 모든 게시판의 게시물을 단일 테이블에 몰아서 저장하다보니 이런 류의 문제가 더더욱 크게 나타나는듯... 인덱스 안걸리면 쥐약...

    사실 공지같은거에 인덱스 걸자니 메모리가 아깝고 안걸자니 애매한데.. 

    공지를 별도의 테이블로 분리시키는 것도 방법일 것 같아요. xe에선 불가능한 구현이겠지만...

    그누처럼 테이블을 쪼개면 검색이 매우 힘들어지고.. (내용검색이 아니더라도 누가 쓴 글을 모두 찾기 이런건 의외로 필요..)

  • ? profile

    is_notice 컬럼에 인덱스는 걸려 있습니다. 검색 조건으로 사용할 만한 컬럼에는 모두 인덱스가 걸려 있습니다. (이런 면에서는 그누보드보다 훨씬 낫습니다! 그누보드는 게시물 추출 조건을 조금만 커스터마이징해도 인덱스를 전혀 못 탑니다ㅠ) MySQL 특성상 쿼리 옵티마이저가 종종 엉뚱한 인덱스를 타는 것이 문제입니다.

    단순히 module_srl과 status만으로 검색하면 module_srl+status 또는 module_srl+list_order 복합인덱스를 한 번에 탈 수 있기 때문에 여러 게시판의 글을 한 테이블에 모두 저장한다고 불이익을 보는 것은 전혀 없습니다. 실제로 1000만 개 가량의 게시물이 쌓여 있는 대형 사이트에서도 불필요한 카운트 쿼리를 제외하면 성능상 문제를 겪지 않고 있고요, 대부분의 커뮤니티는 자유게시판 등 특정 게시판에 글이 집중되기 때문에 게시판별로 테이블을 구분한다고 큰 효과가 나지도 않습니다. XE는 테이블이 1개니까 문서 관리와 통합검색 등에서 얻는 이득이 훨씬 크지요.

    다만 거기에 is_notice 조건이 추가되면 MySQL이 복합인덱스를 안 타고 카디널리티가 낮은 is_notice 인덱스를 타거나, 각 컬럼의 인덱스를 직접 조합하려고 뻘짓을 할 수도 있습니다. 단시간에 게시물 분량이 크게 늘어나거나 DB 튜닝값이 변경되면 어느 인덱스를 타는 것이 더 효율적인지 쿼리 옵티마이저가 제대로 판단하지 못하기 때문에 일단 ANALYZE TABLE부터 한 번 실행해 보라고 말씀드린 것입니다. 단시간이 아니라도 꾸준히 글 리젠율이 높은 사이트라면 정기적으로 ANALYZE TABLE을 해주는 것이 좋습니다.