안녕하세요? 저는 작은 회사에서 IT를 담당하는 직원인대 (특히 Infra) 난대없이 회사내 자체 MYSQL + Apatch application 서버를 맡게되어 머리가 하얗습니다. ㅜㅜ

문제는 얼마전부터 특정 작업에서 엄청느려져서 보니 mysqld CPU 점유가 거의 100% 이더군요. 원인은 아래와 같은 특정 쿼리시 sending data 하면서 20분씩 잡고 있고 이거 하나가 거의 20% 리소스를 먹습니다.

실행되는 Function은 'fn_get_max_batch_number' 이라는 것인데 내용은 제눈에는 간단해 보이는 이겁니다. 물론 NAME_CONST('reqId',34246) 에 맨 뒤에있는 숫자는 계속 변하면서 sending data를 하고 있고요.

-- Connection Id: 19 -- User: root -- Host: localhost:50212 -- DB: bebs -- Command: Query -- Time: 515 -- State: Sending data

SELECT ifnull(MAX(t.batch_number),0) INTO Result FROM cb_ar_claim_detail t WHERE t.req_id= NAME_CONST('reqId',34246)

막상 해당 table을 열어보면 행양은 34000개로 많지만 batch no 최대값 구하는 것같은데 이렇게 시간이 많이 거리는지.., 이부분을 index 처리하면 도움이 될른지... 맨붕입니다.

현재는 무식하게 해당 프로세스 꽉차면 kill 하는 수준이고요.. 정말 미치겠습니다. 아래는 전체 웹사이트 중이 이 function 을 부르는 쿼리 중 대표적인 것입니다. (13개가 있네요.)

<statement name="retrieveClaimApprLine">         
    <![CDATA[ 
    SELECT t.req_id,
           t.approval_seq,
           DATE_FORMAT(t.approval_date, '%d-%m-%Y') approval_date, 
           t.approval_status_code,
           fn_get_approval_status_name('claimApprStatus', t.approval_status_code)
              approval_status_name,
           t.approver_id,
           concat('[',
                  fn_get_group_info2('GN', t.approver_id, ''),
                  ']',
                  '<br>',
                  fn_get_group_info2('GI', t.approver_id, ''))
              appr_info,
           t.approval_reason,
           t.real_appr_id,
           fn_get_emp_info('NAME', t.real_appr_id) real_appr_name,
           fn_get_emp_info('EMAIL', t.real_appr_id) real_appr_mail,
           fn_get_emp_info('DEPTNAME', t.real_appr_id) real_appr_dept
      FROM cb_ar_claim_appr_line t
     WHERE t.req_id = ${reqId} AND t.approval_type = 'G'
     AND t.batch_number = fn_get_max_batch_number(${reqId})
    UNION ALL
      SELECT t.req_id,
             t.approval_seq,
             DATE_FORMAT(t.approval_date, '%d-%m-%Y') approval_date,
             t.approval_status_code,
             fn_get_approval_status_name('claimApprStatus', t.approval_status_code)
                approval_status_name,
             t.approver_id,
             concat(fn_get_emp_info('NAME', t.approver_id),'/',fn_get_emp_info('DEPTNAME', t.approver_id),'/',fn_get_emp_info('EMAIL', t.approver_id)) appr_info,
             t.approval_reason,
             t.real_appr_id,
             fn_get_emp_info('NAME', t.real_appr_id) real_appr_name,
             fn_get_emp_info('EMAIL', t.real_appr_id) real_appr_mail,
             fn_get_emp_info('DEPTNAME', t.real_appr_id) real_appr_dept
        FROM cb_ar_claim_appr_line t
       WHERE t.req_id = ${reqId} AND t.approval_type = 'E'
       AND t.batch_number = fn_get_max_batch_number(${reqId})
    ORDER BY approval_seq
    ]]>
</statement>

고수님들의 많은 지도 편달을 고개숙여 요청드립니다.