안녕하세요.

라이믹스 1.9x버전에 snow 스킨을 사용중인데 

하나의 글을 여러명이 동시에 조회하거나 댓글을 달때 랙이 엄청 심하게 발생하네요

아무래도 동시에 조회하면서 DB부하가 발생하는거 같은데

어느부분을 체크하는게 좋을까요? 

서버 사양은 접속자수 대비 충분한데 서버 사용률 체크해보면 mysql이 점유율이 항상 높게 나오네요

 

이와 관련있는지 모르겠지만 동시에 댓글을 달다보면 댓글 카운트수도 맞지 않고 그러네요.

  • profile

    DB 체크해보시고 myisam으로 되어 있다면 innodb로 변경처리해보세요. myisam의 경우 테이블 단위로 락이 걸려서 동접수가 많아지면 기하급수적으로 느려집니다.(글하나 읽을때마다 글+댓글 테이블이 잠기는 셈이니 느릴수밖에 없겠죠?)

    이미 innodb인 경우 DB서버 설정의 문제일수도 있습니다.

    p.s.) 라이믹스 1.9 버전대 지원은 이미 종료되었습니다. 라이믹스 2.0 이상으로 업데이트해주세요.

  • profile ?

    답변감사합니다. 
    우선 mysql 은 innodb 이고 서버 스팩은 16코어 32기가로 충분한데 mysql cpu 점유율을 보니 
    100%대가 몇개 있고 하나는 300~500% 까지 치솟내요.

     

    접속자수가 엄청 많은건 아닌데 DB 부하가 너무 심한게 하나의 글을 동시에 조회하거나 댓글을 달경우 아래처럼 딜레이 시간이 엄청 오래걸리네요.

     

    이런건 mysql 서버 설정의 문제인지 아님 라이믹스나 스킨쪽의 문제인지 어디를 봐야할지 감이 잡히질 않네요

     

    dblock2.png

  • ? ?

    라이믹스 시스템 설정 밑에 디버그 설정 들어가서 디버그 정보 출력을 켜시고
    출력된 디버그 정보들을 한번 좌악 올려주시면 보다 쉽게 분석될겁니다.

  • ? profile
    해당 로그 조회 방법이 어떻게 되나요? 저는 phpmyadmin 사용중인데 해당 기록을 조회하는 다른 프로그램이 있나요?
  • profile ?

    라이믹스 설정화면에 들어간 다음에 거기서 시스템 설정 그 다음에 다시 디버그 설정 들어가시면 됩니다.
    로그 출력 방법은 3가지 선택하실 수 있는데 주석 출력 (HTML 주석 형태로 포함됩니다) 파일 출력 화면 출력인데 중복 선택 가능합니다. 화면 출력 선택하시면 나중에 브라우저 화면 왼쪽 하단에 DEBUG라고 조그만 버튼이 표시됩니다. 그 버튼 누르면 로그가 보이죠. 디버그 정보는 디폴트로는 관리자에게만 나옵니다.

  • ? ?

    이런걸로 확인이 가능할지 모르겠는데 디버그 키고 나온 내용중 시간이 오래걸리는 

    댓글 등록과 댓글 등록후 새로고침 될때의 디버그 내용으로 

    익명게시판 중 하나의 글을 여러명이 동시에 볼때 생기는 현상입니다.

     

    내용상으로는 댓글 등록시 익명으로 글을 쓸때 딜레이가 심한거 같고,

    댓글 등록 완료 후 페이지 새로고침될때 조회수 증가하는 부분에서 lock 이 걸리면서 대기를 오래하는거 같네요.

    혹시 조회수 증가부분을 조금 부정확해도 상관없으니 lock 대기를 줄일 수 있는 방법이 없을까요?

     

     

     

     

     

    ---------- 댓글 등록시 -------------

     

    Database Queries
    ================
    01. SELECT *  FROM `rx_member` AS `member`   WHERE `member_srl` = ?     
        - Caller:      modules/member/member.model.php line 345 (memberModel->getMemberInfoByMemberSrl)
        - Connection:  master (localhost)
        - Query ID:    member.getMemberInfoByMemberSrl
        - Query Time:  0.0005 sec
        - Result:      success
    02. SELECT `modules`.*  FROM `rx_modules` AS `modules` , `rx_documents` AS `documents`   WHERE `documents`.`document_srl` = ? AND `modules`.`module_srl` = `documents`.`module_srl`     
        - Caller:      modules/module/module.model.php line 172 (moduleModel->getModuleInfoByDocumentSrl)
        - Connection:  master (localhost)
        - Query ID:    module.getModuleInfoByDocument
        - Query Time:  0.0005 sec
        - Result:      success
    03. SELECT *  FROM `rx_layouts` AS `layouts`   WHERE `layout_srl` = ?     
        - Caller:      modules/layout/layout.model.php line 282 (layoutModel->getLayout)
        - Connection:  master (localhost)
        - Query ID:    layout.getLayout
        - Query Time:  0.0002 sec
        - Result:      success
    04. SELECT `bulk_message`.*, `bulk_message_target`.`target_member_srl`  FROM (SELECT *  FROM `rx_bulk_message_target` AS `bulk_message_target`   WHERE `target_member_srl` = ?     )  as `bulk_message_target` RIGHT JOIN `rx_bulk_message` AS `bulk_message` ON  `bulk_message`.`document_srl` = `bulk_message_target`.`document_srl`    WHERE `bulk_message`.`is_stop` = 'N' AND `bulk_message_target`.`target_member_srl` IS NULL  AND ( `bulk_message`.`regdate` >= ? OR `bulk_message`.`target_range` = 'A' )   GROUP BY `bulk_message`.`document_srl`   
        - Caller:      modules/bulkmsg/bulkmsg.controller.php line 21 (bulkmsgController->triggerModuleHandlerInit)
        - Connection:  master (localhost)
        - Query ID:    bulkmsg.getUserNotReceivedMessages
        - Query Time:  0.0004 sec
        - Result:      success
    05. SHOW TABLES LIKE 'rx_pointhistory'
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    bulkmsg.getUserNotReceivedMessages
        - Query Time:  0.0004 sec
        - Result:      success
    06. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'itemshop'     
        - Caller:      modules/itemshop/itemshop.model.php line 19 (itemshopModel->getItemshopInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getItemshop
        - Query Time:  0.0003 sec
        - Result:      success
    07. SELECT *  FROM `rx_itemshop_transaction` AS `itemshop_transaction`   WHERE `member_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 163 (itemshopModel->getPossessInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getTransaction
        - Query Time:  0.0002 sec
        - Result:      success
    08. SELECT *  FROM `rx_itemshop_product` AS `itemshop_product`   WHERE `product_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 56 (itemshopModel->getProductInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getProduct
        - Query Time:  0.0002 sec
        - Result:      success
    09. SELECT *  FROM `rx_documents` AS `documents`   WHERE `document_srl` = ?     
        - Caller:      modules/document/document.item.php line 113 (documentItem->_loadFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocument
        - Query Time:  0.0002 sec
        - Result:      success
    10. SELECT *  FROM `rx_document_extra_vars` AS `extra_vars`   WHERE `extra_vars`.`module_srl` >= -1 AND `extra_vars`.`document_srl` IN (?) AND `extra_vars`.`var_idx` >= -2     
        - Caller:      modules/document/document.model.php line 42 (documentModel->getDocumentExtraVarsFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentExtraVars
        - Query Time:  0.0003 sec
        - Result:      success
    11. INSERT INTO `rx_sequence` (seq) VALUES ('0')
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentExtraVars
        - Query Time:  0.0007 sec
        - Result:      success
    12. SELECT count(*) AS `count`  FROM `rx_files` AS `files`   WHERE `upload_target_srl` = ?     
        - Caller:      modules/file/file.model.php line 246 (fileModel->getFilesCount)
        - Connection:  master (localhost)
        - Query ID:    file.getFilesCount
        - Query Time:  0.0002 sec
        - Result:      success
    13. SELECT *  FROM `rx_star_target` AS `star_target`   WHERE `module_srl` = ?     
        - Connection:  master (localhost)
        - Query Time:  0.0002 sec
        - Result:      success
    14. SELECT *  FROM `rx_spamfilter_denied_ip` AS `spamfilter_denied_ip`       ORDER BY `regdate` DESC 
        - Caller:      modules/spamfilter/spamfilter.model.php line 35 (spamfilterModel->getDeniedIPList)
        - Connection:  master (localhost)
        - Query ID:    spamfilter.getDeniedIPList
        - Query Time:  0.0004 sec
        - Result:      success
    15. SELECT *  FROM `rx_spamfilter_denied_word` AS `spamfilter_denied_word`       ORDER BY `hit` DESC 
        - Caller:      modules/spamfilter/spamfilter.model.php line 76 (spamfilterModel->getDeniedWordList)
        - Connection:  master (localhost)
        - Query ID:    spamfilter.getDeniedWordList
        - Query Time:  0.0002 sec
        - Result:      success
    16. INSERT INTO `rx_sequence` (seq) VALUES ('0')
        - Caller:      modules/spamfilter/spamfilter.controller.php line 242 (spamfilterController->insertLog)
        - Connection:  master (localhost)
        - Query ID:    spamfilter.insertLog
        - Query Time:  0.0004 sec
        - Result:      success
    17. INSERT  INTO `rx_spamfilter_log` 
     (`spamfilter_log_srl`, `ipaddress`, `regdate`) 
     VALUES (?, ?, ?)
        - Caller:      modules/spamfilter/spamfilter.controller.php line 242 (spamfilterController->insertLog)
        - Connection:  master (localhost)
        - Query ID:    spamfilter.insertLog
        - Query Time:  0.0003 sec
        - Result:      success
    18. INSERT INTO `rx_sequence` (seq) VALUES ('0')
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    spamfilter.insertLog
        - Query Time:  0.0002 sec
        - Result:      success
    19. START TRANSACTION
        - Caller:       line 0 ()
        - Connection:  
        - Query ID:    
        - Query Time:  0.0000 sec
        - Result:      error 0 
    20. INSERT  INTO `rx_comments_list` 
     (`comment_srl`, `document_srl`, `head`, `arrange`, `module_srl`, `regdate`, `depth`) 
     VALUES (?, ?, ?, ?, ?, ?, ?)
        - Caller:      modules/comment/comment.controller.php line 598 (commentController->insertComment)
        - Connection:  master (localhost)
        - Query ID:    comment.insertCommentList
        - Query Time:  0.0002 sec
        - Result:      success
    21. INSERT  INTO `rx_comments` 
     (`comment_srl`, `module_srl`, `parent_srl`, `document_srl`, `is_secret`, `notify_message`, `content`, `voted_count`, `blamed_count`, `nick_name`, `user_id`, `user_name`, `member_srl`, `email_address`, `homepage`, `uploaded_count`, `regdate`, `last_update`, `ipaddress`, `list_order`, `status`) 
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        - Caller:      modules/comment/comment.controller.php line 605 (commentController->insertComment)
        - Connection:  master (localhost)
        - Query ID:    comment.insertComment
        - Query Time:  0.0003 sec
        - Result:      success
    22. SELECT `modules`.*  FROM `rx_modules` AS `modules` , `rx_documents` AS `documents`   WHERE `documents`.`document_srl` = ? AND `modules`.`module_srl` = `documents`.`module_srl`     
        - Caller:      modules/module/module.model.php line 172 (moduleModel->getModuleInfoByDocumentSrl)
        - Connection:  master (localhost)
        - Query ID:    module.getModuleInfoByDocument
        - Query Time:  0.0002 sec
        - Result:      success
    23. SELECT count(*) AS `count`  FROM `rx_comments` AS `comments`   WHERE `document_srl` = ?     
        - Caller:      modules/comment/comment.model.php line 261 (commentModel->getCommentCount)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentCount
        - Query Time:  0.0002 sec
        - Result:      success
    24. INSERT INTO `rx_sequence` (seq) VALUES ('0')
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentCount
        - Query Time:  0.0001 sec
        - Result:      success
    25. UPDATE  `rx_documents` AS `documents`  SET `comment_count` = ?, `update_order` = ?, `last_update` = ?, `last_updater` = ?  WHERE `document_srl` = ?
        - Caller:      modules/document/document.controller.php line 1939 (documentController->updateCommentCount)
        - Connection:  master (localhost)
        - Query ID:    document.updateCommentCount
        - Query Time:  1.9889 sec
        - Result:      success
    26. SELECT *  FROM `rx_itemshop_transaction` AS `itemshop_transaction`   WHERE `member_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 163 (itemshopModel->getPossessInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getTransaction
        - Query Time:  0.0004 sec
        - Result:      success
    27. SELECT *  FROM `rx_mission_list` AS `mission_list`   WHERE `mission_type` LIKE ?     
        - Caller:      modules/mission/mission.model.php line 85 (MissionModel->getMissionsByMissionType)
        - Connection:  master (localhost)
        - Query ID:    mission.getMissionsByMissionType
        - Query Time:  0.0003 sec
        - Result:      success
    28. SELECT *  FROM `rx_mission_user` AS `mission_user`   WHERE `member_srl` = ?     
        - Caller:      modules/mission/mission.model.php line 55 (MissionModel->getUserMissionData)
        - Connection:  master (localhost)
        - Query ID:    mission.getUserMissionDataByMemberSrl
        - Query Time:  0.0001 sec
        - Result:      success
    29. UPDATE  `rx_mission_user` AS `mission_user`  SET `static_achieve_data` = ?, `static_activity_data` = ?, `daily_achieve_data` = ?, `daily_activity_data` = ?  WHERE `member_srl` = ?
        - Caller:      modules/mission/mission.model.php line 132 (MissionModel->saveUserMissionData)
        - Connection:  master (localhost)
        - Query ID:    mission.updateUserMissionData
        - Query Time:  0.0001 sec
        - Result:      success
    30. SELECT `modules`.*  FROM `rx_modules` AS `modules` , `rx_documents` AS `documents`   WHERE `documents`.`document_srl` = ? AND `modules`.`module_srl` = `documents`.`module_srl`     
        - Caller:      modules/module/module.model.php line 172 (moduleModel->getModuleInfoByDocumentSrl)
        - Connection:  master (localhost)
        - Query ID:    module.getModuleInfoByDocument
        - Query Time:  0.0002 sec
        - Result:      success
    31. SELECT `member_srl`  FROM `rx_member` AS `member`   WHERE `is_admin` = ?     
        - Caller:      modules/ncenterlite/ncenterlite.model.php line 361 (ncenterliteModel->getMemberAdmins)
        - Connection:  master (localhost)
        - Query ID:    ncenterlite.getMemberAdmins
        - Query Time:  0.0002 sec
        - Result:      success
    32. SELECT *  FROM `rx_ncenterlite_unsubscribe` AS `ncenterlite_unsubscribe`   WHERE `target_srl` = ? AND `member_srl` = ?     
        - Caller:      modules/ncenterlite/ncenterlite.model.php line 692 (ncenterliteModel->getUserUnsubscribeConfigByTargetSrl)
        - Connection:  master (localhost)
        - Query ID:    ncenterlite.getUserUnsubscribeConfigByTargetSrl
        - Query Time:  0.0001 sec
        - Result:      success
    33. INSERT  INTO `rx_ncenterlite_notify` 
     (`notify`, `srl`, `target_srl`, `member_srl`, `target_member_srl`, `target_nick_name`, `target_user_id`, `target_email_address`, `type`, `target_type`, `target_summary`, `target_browser`, `target_url`, `regdate`, `target_p_srl`) 
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        - Caller:      modules/ncenterlite/ncenterlite.controller.php line 1285 (ncenterliteController->_insertNotify)
        - Connection:  master (localhost)
        - Query ID:    ncenterlite.insertNotify
        - Query Time:  0.0003 sec
        - Result:      success
    34. SAVEPOINT SP1
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    ncenterlite.insertNotify
        - Query Time:  0.0001 sec
        - Result:      error 1295 This command is not supported in the prepared statement protocol yet
    35. UPDATE  `rx_point` AS `point`  SET `point` = ?  WHERE `member_srl` = ?
        - Caller:      modules/point/point.controller.php line 706 (pointController->setPoint)
        - Connection:  master (localhost)
        - Query ID:    point.updatePoint
        - Query Time:  0.0001 sec
        - Result:      success
    36. SELECT *  FROM `rx_anonymous_sequence` AS `anonymous_sequence`   WHERE `document_srl` = ? AND `member_srl` = ?     
        - Connection:  master (localhost)
        - Query Time:  1.9391 sec
        - Result:      success
    37. SELECT max(`seq`) AS `max_seq`  FROM `rx_anonymous_sequence` AS `anonymous_sequence`   WHERE `document_srl` = ?     
        - Connection:  master (localhost)
        - Query Time:  2.0203 sec
        - Result:      success
    38. INSERT  INTO `rx_anonymous_sequence` 
     (`member_srl`, `document_srl`, `module_srl`, `seq`) 
     VALUES (?, ?, ?, ?)
        - Connection:  master (localhost)
        - Query Time:  0.0002 sec
        - Result:      success
    39. UPDATE LOW_PRIORITY `rx_comments` AS `comments`  SET `nick_name` = ?, `user_name` = ?  WHERE `comment_srl` = ?
        - Connection:  master (localhost)
        - Query Time:  0.0003 sec
        - Result:      success
    40. UPDATE  `rx_ncenterlite_notify` AS `ncenterlite_notify`  SET `target_nick_name` = ?  WHERE `target_srl` = ?
        - Connection:  master (localhost)
        - Query Time:  0.0002 sec
        - Result:      success
    41. SELECT *  FROM `rx_star_target` AS `star_target`   WHERE `module_srl` = ? AND `star_only` = ?     
        - Connection:  master (localhost)
        - Query Time:  0.0002 sec
        - Result:      success
    42. COMMIT
        - Caller:       line 0 ()
        - Connection:  
        - Query ID:    
        - Query Time:  0.0000 sec
        - Result:      error 0 
    43. SELECT *  FROM `rx_member` AS `member`   WHERE `member_srl` = ?     
        - Caller:      modules/member/member.model.php line 345 (memberModel->getMemberInfoByMemberSrl)
        - Connection:  master (localhost)
        - Query ID:    member.getMemberInfoByMemberSrl
        - Query Time:  0.0002 sec
        - Result:      success
    44. SELECT `modules`.*  FROM `rx_modules` AS `modules` , `rx_documents` AS `documents`   WHERE `documents`.`document_srl` = ? AND `modules`.`module_srl` = `documents`.`module_srl`     
        - Caller:      modules/module/module.model.php line 172 (moduleModel->getModuleInfoByDocumentSrl)
        - Connection:  master (localhost)
        - Query ID:    module.getModuleInfoByDocument
        - Query Time:  0.0002 sec
        - Result:      success
    45. SELECT *  FROM `rx_comments` AS `comments`   WHERE `comment_srl` = ?     
        - Caller:      modules/comment/comment.item.php line 63 (commentItem->_loadFromDB)
        - Connection:  master (localhost)
        - Query ID:    comment.getComment
        - Query Time:  0.0002 sec
        - Result:      success
    46. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'nowconnect'     
        - Caller:      modules/nowconnect/nowconnect.model.php line 17 (nowconnectModel->getNowconnectInfo)
        - Connection:  master (localhost)
        - Query ID:    nowconnect.getNowconnect
        - Query Time:  0.0002 sec
        - Result:      success
    47. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'itemshop'     
        - Caller:      modules/itemshop/itemshop.model.php line 19 (itemshopModel->getItemshopInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getItemshop
        - Query Time:  0.0002 sec
        - Result:      success
    48. SELECT *  FROM `rx_itemshop_transaction` AS `itemshop_transaction`   WHERE `member_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 163 (itemshopModel->getPossessInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getTransaction
        - Query Time:  0.0002 sec
        - Result:      success

    Slow Queries
    ============
    None

    Slow Triggers
    =============
    01. comment.insertComment.after
        - Target:     anonymouscontroller.triggerCommentAnonymous
        - Exec Time:  3.9613 sec
     

     

     

     

     

     

     

     

     

    ---------- 등록 완료 후 새로고침 되면서 페이지 불러올때 -------------

     

    Database Queries
    ================
    01. SELECT *  FROM `rx_member` AS `member`   WHERE `member_srl` = ?     
        - Caller:      modules/member/member.model.php line 345 (memberModel->getMemberInfoByMemberSrl)
        - Connection:  master (localhost)
        - Query ID:    member.getMemberInfoByMemberSrl
        - Query Time:  0.0002 sec
        - Result:      success
    02. SELECT `a`.`title` AS `title`, `a`.`group_srl` AS `group_srl`  FROM `rx_member_group` AS `a` , `rx_member_group_member` AS `b`   WHERE ( `b`.`member_srl` = ? AND `b`.`group_srl` = `a`.`group_srl` AND `a`.`site_srl` = ? ) AND `a`.`list_order` <= 2100000000     ORDER BY `a`.`list_order` ASC 
        - Caller:      modules/member/member.model.php line 526 (memberModel->getMemberGroups)
        - Connection:  master (localhost)
        - Query ID:    member.getMemberGroups
        - Query Time:  0.0003 sec
        - Result:      success
    03. SELECT `modules`.*  FROM `rx_modules` AS `modules` , `rx_documents` AS `documents`   WHERE `documents`.`document_srl` = ? AND `modules`.`module_srl` = `documents`.`module_srl`     
        - Caller:      modules/module/module.model.php line 172 (moduleModel->getModuleInfoByDocumentSrl)
        - Connection:  master (localhost)
        - Query ID:    module.getModuleInfoByDocument
        - Query Time:  0.0003 sec
        - Result:      success
    04. SELECT *  FROM `rx_documents` AS `documents`   WHERE `document_srl` = ?     
        - Caller:      modules/document/document.item.php line 113 (documentItem->_loadFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocument
        - Query Time:  0.0002 sec
        - Result:      success
    05. SELECT *  FROM `rx_document_extra_vars` AS `extra_vars`   WHERE `extra_vars`.`module_srl` >= -1 AND `extra_vars`.`document_srl` IN (?) AND `extra_vars`.`var_idx` >= -2     
        - Caller:      modules/document/document.model.php line 42 (documentModel->getDocumentExtraVarsFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentExtraVars
        - Query Time:  0.0002 sec
        - Result:      success
    06. SELECT *  FROM `rx_layouts` AS `layouts`   WHERE `layout_srl` = ?     
        - Caller:      modules/layout/layout.model.php line 282 (layoutModel->getLayout)
        - Connection:  master (localhost)
        - Query ID:    layout.getLayout
        - Query Time:  0.0002 sec
        - Result:      success
    07. SELECT `bulk_message`.*, `bulk_message_target`.`target_member_srl`  FROM (SELECT *  FROM `rx_bulk_message_target` AS `bulk_message_target`   WHERE `target_member_srl` = ?     )  as `bulk_message_target` RIGHT JOIN `rx_bulk_message` AS `bulk_message` ON  `bulk_message`.`document_srl` = `bulk_message_target`.`document_srl`    WHERE `bulk_message`.`is_stop` = 'N' AND `bulk_message_target`.`target_member_srl` IS NULL  AND ( `bulk_message`.`regdate` >= ? OR `bulk_message`.`target_range` = 'A' )   GROUP BY `bulk_message`.`document_srl`   
        - Caller:      modules/bulkmsg/bulkmsg.controller.php line 21 (bulkmsgController->triggerModuleHandlerInit)
        - Connection:  master (localhost)
        - Query ID:    bulkmsg.getUserNotReceivedMessages
        - Query Time:  0.0003 sec
        - Result:      success
    08. SHOW TABLES LIKE 'rx_pointhistory'
        - Caller:       line 0 ()
        - Connection:  master (localhost)
        - Query ID:    bulkmsg.getUserNotReceivedMessages
        - Query Time:  0.0004 sec
        - Result:      success
    09. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'itemshop'     
        - Caller:      modules/itemshop/itemshop.model.php line 19 (itemshopModel->getItemshopInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getItemshop
        - Query Time:  0.0003 sec
        - Result:      success
    10. SELECT *  FROM `rx_itemshop_transaction` AS `itemshop_transaction`   WHERE `member_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 163 (itemshopModel->getPossessInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getTransaction
        - Query Time:  0.0002 sec
        - Result:      success
    11. SELECT *  FROM `rx_itemshop_product` AS `itemshop_product`   WHERE `product_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 56 (itemshopModel->getProductInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getProduct
        - Query Time:  0.0002 sec
        - Result:      success
    12. START TRANSACTION
        - Caller:       line 0 ()
        - Connection:  
        - Query ID:    
        - Query Time:  0.0000 sec
        - Result:      error 0 
    13. UPDATE  `rx_documents` AS `documents`  SET `readed_count` = `readed_count` + ?  WHERE `document_srl` = ?
        - Caller:      modules/document/document.controller.php line 1340 (documentController->updateReadedCount)
        - Connection:  master (localhost)
        - Query ID:    document.updateReadedCount
        - Query Time:  7.6425 sec
        - Result:      success
    14. COMMIT
        - Caller:       line 0 ()
        - Connection:  
        - Query ID:    
        - Query Time:  0.0000 sec
        - Result:      error 0 
    15. SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`voted_count`, `documents`.`blamed_count`, `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 `rx_documents` AS `documents`   WHERE ( `module_srl` IN (?) AND `is_notice` = 'Y' ) AND `list_order` <= 2100000000     ORDER BY `list_order` ASC 
        - Caller:      modules/document/document.model.php line 304 (documentModel->getNoticeList)
        - Connection:  master (localhost)
        - Query ID:    document.getNoticeList
        - Query Time:  0.0004 sec
        - Result:      success
    16. SELECT *  FROM `rx_document_extra_vars` AS `extra_vars`   WHERE `extra_vars`.`module_srl` >= -1 AND `extra_vars`.`document_srl` IN (?) AND `extra_vars`.`var_idx` >= -2     
        - Caller:      modules/document/document.model.php line 42 (documentModel->getDocumentExtraVarsFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentExtraVars
        - Query Time:  0.0002 sec
        - Result:      success
    17. SELECT count(`document_srl`) AS `count`  FROM `rx_documents` AS `documents`   WHERE `module_srl` IN (?) AND `status` IN (?,?) AND ( `list_order` <= ? )     
        - Caller:      modules/document/document.model.php line 639 (documentModel->getDocumentPage)
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentListPage
        - Query Time:  0.0006 sec
        - Result:      success
    18. SELECT *  FROM `rx_documents` AS `documents`   WHERE ( `module_srl` = ? AND `is_notice` = ? AND `status` IN (?,?) ) AND `documents`.`list_order` <= 2100000000     ORDER BY `documents`.`list_order` asc  LIMIT 20
        - Caller:      modules/supercache/supercache.model.php line 421 (SuperCacheModel->getDocumentList)
        - Connection:  master (localhost)
        - Query ID:    supercache.getDocumentList
        - Query Time:  0.0010 sec
        - Result:      success
    19. SELECT *  FROM `rx_document_extra_vars` AS `extra_vars`   WHERE `extra_vars`.`module_srl` >= -1 AND `extra_vars`.`document_srl` IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND `extra_vars`.`var_idx` >= -2     
        - Caller:      modules/document/document.model.php line 42 (documentModel->getDocumentExtraVarsFromDB)
        - Connection:  master (localhost)
        - Query ID:    document.getDocumentExtraVars
        - Query Time:  0.0004 sec
        - Result:      success
    20. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'nowconnect'     
        - Caller:      modules/nowconnect/nowconnect.model.php line 17 (nowconnectModel->getNowconnectInfo)
        - Connection:  master (localhost)
        - Query ID:    nowconnect.getNowconnect
        - Query Time:  0.0003 sec
        - Result:      success
    21. select count(*) as "count" FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0005 sec
        - Result:      success
    22. SELECT `comments`.*, `comments_list`.`depth` AS `depth`  FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0   ORDER BY `comments_list`.`head` ASC, `comments_list`.`arrange` ASC  LIMIT 0, 50
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0003 sec
        - Result:      success
    23. SELECT `comment_srl`, `point`  FROM `rx_comment_voted_log` AS `comment_voted_log`   WHERE `comment_srl` IN (?,?,?,?,?,?,?) AND `member_srl` = ?     
        - Caller:      modules/document/document.item.php line 1006 (documentItem->getComments)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentVotedLogMulti
        - Query Time:  0.0003 sec
        - Result:      success
    24. select count(*) as "count" FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0004 sec
        - Result:      success
    25. SELECT `comments`.*, `comments_list`.`depth` AS `depth`  FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0   ORDER BY `comments_list`.`head` ASC, `comments_list`.`arrange` ASC  LIMIT 0, 50
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0004 sec
        - Result:      success
    26. SELECT *  FROM `rx_comment_declared` AS `comment_declared`   WHERE `comment_srl` IN (?,?,?,?,?,?,?) AND `declared_count` >= ?     
        - Connection:  master (localhost)
        - Query Time:  0.0003 sec
        - Result:      success
    27. UPDATE  `rx_ncenterlite_notify` AS `ncenterlite_notify`  SET `readed` = 'Y'  WHERE `member_srl` = ? AND `srl` = ?
        - Caller:      modules/ncenterlite/ncenterlite.controller.php line 865 (ncenterliteController->triggerAfterModuleHandlerProc)
        - Connection:  master (localhost)
        - Query ID:    ncenterlite.updateNotifyReadedBySrl
        - Query Time:  0.0026 sec
        - Result:      success
    28. SELECT *  FROM `rx_files` AS `files`   WHERE `upload_target_srl` = ? AND `isvalid` = ?     ORDER BY `file_srl` ASC 
        - Caller:      modules/file/file.model.php line 383 (fileModel->getFiles)
        - Connection:  master (localhost)
        - Query ID:    file.getFiles
        - Query Time:  0.0014 sec
        - Result:      success
    29. select count(*) as "count" FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0018 sec
        - Result:      success
    30. SELECT `comments`.*, `comments_list`.`depth` AS `depth`  FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0   ORDER BY `comments_list`.`head` ASC, `comments_list`.`arrange` ASC  LIMIT 0, 50
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0036 sec
        - Result:      success
    31. select count(*) as "count" FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0005 sec
        - Result:      success
    32. SELECT `comments`.*, `comments_list`.`depth` AS `depth`  FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0   ORDER BY `comments_list`.`head` ASC, `comments_list`.`arrange` ASC  LIMIT 0, 50
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0005 sec
        - Result:      success
    33. select count(*) as "count" FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0016 sec
        - Result:      success
    34. SELECT `comments`.*, `comments_list`.`depth` AS `depth`  FROM `rx_comments` AS `comments` , `rx_comments_list` AS `comments_list`   WHERE `comments_list`.`document_srl` = ? AND `comments_list`.`comment_srl` = `comments`.`comment_srl` AND `comments_list`.`head` >= 0 AND `comments_list`.`arrange` >= 0   ORDER BY `comments_list`.`head` ASC, `comments_list`.`arrange` ASC  LIMIT 0, 50
        - Caller:      modules/comment/comment.model.php line 537 (commentModel->getCommentList)
        - Connection:  master (localhost)
        - Query ID:    comment.getCommentPageList
        - Query Time:  0.0035 sec
        - Result:      success
    35. SELECT *  FROM `rx_menu_item` AS `menu_item`   WHERE `url` = ?     
        - Connection:  master (localhost)
        - Query Time:  0.0030 sec
        - Result:      success
    36. SELECT *  FROM `rx_member_menu` AS `member_menu`   WHERE `member_menu` LIKE ? AND `member_srl` = ?     
        - Connection:  master (localhost)
        - Query Time:  0.0023 sec
        - Result:      success
    37. SELECT *  FROM `rx_files` AS `files`   WHERE `upload_target_srl` = ? AND `isvalid` = ?     ORDER BY `file_srl` ASC 
        - Caller:      modules/file/file.model.php line 383 (fileModel->getFiles)
        - Connection:  master (localhost)
        - Query ID:    file.getFiles
        - Query Time:  0.0004 sec
        - Result:      success
    38. select count(*) as "count" FROM `rx_member_message` AS `message` LEFT JOIN `rx_member` AS `member` ON  `member`.`member_srl` = `message`.`sender_srl`    WHERE `message`.`receiver_srl` = ? AND `message`.`readed` = ? AND `message`.`message_type` = ?
        - Caller:      layouts/slow/components/config/config-message.html line 11 (include)
        - Connection:  master (localhost)
        - Query ID:    communication.getNewMessage
        - Query Time:  0.0008 sec
        - Result:      success
    39. SELECT *  FROM `rx_member_message` AS `message` LEFT JOIN `rx_member` AS `member` ON  `member`.`member_srl` = `message`.`sender_srl`    WHERE ( `message`.`receiver_srl` = ? AND `message`.`readed` = ? AND `message`.`message_type` = ? ) AND `message`.`list_order` <= 2100000000   ORDER BY `message`.`list_order` DESC  LIMIT 0, 5
        - Caller:      layouts/slow/components/config/config-message.html line 11 (include)
        - Connection:  master (localhost)
        - Query ID:    communication.getNewMessage
        - Query Time:  0.0009 sec
        - Result:      success
    40. SELECT *  FROM `rx_modules` AS `modules`   WHERE `module` = 'itemshop'     
        - Caller:      modules/itemshop/itemshop.model.php line 19 (itemshopModel->getItemshopInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getItemshop
        - Query Time:  0.0004 sec
        - Result:      success
    41. SELECT *  FROM `rx_itemshop_transaction` AS `itemshop_transaction`   WHERE `member_srl` = ?     
        - Caller:      modules/itemshop/itemshop.model.php line 163 (itemshopModel->getPossessInfo)
        - Connection:  master (localhost)
        - Query ID:    itemshop.getTransaction
        - Query Time:  0.0019 sec
        - Result:      success
    42. SELECT *  FROM `rx_menu_item` AS `MI`   WHERE MI.`url` = ? AND MI.`is_shortcut` = ? AND MI.`menu_srl` IN (SELECT `menu_srl`  FROM `rx_menu` AS `M`   WHERE M.`site_srl` = 0     )     
        - Caller:      modules/seopro/tool/seotag/seotag.php line 492 (SEOtag->getDescription)
        - Connection:  master (localhost)
        - Query ID:    menu.getMenuItemByUrl
        - Query Time:  0.0005 sec
        - Result:      success
    43. SELECT *  FROM `rx_menu_item` AS `MI`   WHERE MI.`url` = ? AND MI.`is_shortcut` = ? AND MI.`menu_srl` IN (SELECT `menu_srl`  FROM `rx_menu` AS `M`   WHERE M.`site_srl` = 0     )     
        - Caller:      modules/seopro/tool/seotag/seotag.php line 501 (SEOtag->getDescription)
        - Connection:  master (localhost)
        - Query ID:    menu.getMenuItemByUrl
        - Query Time:  0.0005 sec
        - Result:      success

    Slow Queries
    ============
    01. UPDATE  `rx_documents` AS `documents`  SET `readed_count` = `readed_count` + ?  WHERE `document_srl` = ?
        - Caller:      modules/document/document.controller.php line 1340 (documentController->updateReadedCount)
        - Connection:  master (localhost)
        - Query ID:    document.updateReadedCount
        - Query Time:  7.6425 sec
        - Result:      success

  • ? ?

    rx_anonymous_sequence
    이게 의심스러운데요... 이름만 봐서는 꼭 라이믹스 기본 테이블같은데 제 사이트에는 없네요.
    뭔가의 모듈이 만든건데 뭘하는지는 몰라도 혼자서 4초 동안 삽질하고 있습니다.
    이놈이 전체적인 락을 유발한건지도 모른다고 생각합니다만...
    이게 어느 모듈에서 만든 테이블인지 확인해야 할 것 같습니다.
    그래서 그 모듈을 잠시 사용안해보시는걸 추천합니다.

    사이트 소스에 대고 텍스트 전체 검색을 한번 때려보세요 

    rx_anonymous_sequence나 anonymous_sequence로...

     

  • ? ?
    네 다른분이 만든 익명 표시 모듈인데
    저이후에 페이지 새로고침되면서 조회수 증가하는 부분인 아래 쿼리가 락이 걸리는거라 보면 될까요?
    UPDATE `rx_documents` AS `documents` SET `readed_count` = `readed_count` + ? WHERE `document_srl` =
    시간상으로는 락걸릴때 위 부분에서 가장 많이 시간을 잡고 있더라고요.
  • ? ?
    아마 테이블에 레코드 개수가 엄청 많을겁니다 인덱스를 안걸어놓은걸로 추측되는군요
  • ? ?
    안녕하세요. 덕분에 문제점을 찾아서 해결했네요.
    말씀하신 커스텀 모듈 부분에 해당하는 db에 인덱싱이 빠져있어서 쿼리가 느렸던거네요.
    정말 감사합니다!