MySQL RDS Slow Query Slack으로 알람 보내기

디프만에서 진행 중인 팀 프로젝트의 런칭을 앞두고, 서비스에서 발생할 수 있는 이슈를 파악하기 위해 문제가 될 수 있는 쿼리를 추적해야 한다는 의견이 나왔습니다.

로그를 일일이 확인하는 대신, 더 효율적으로 문제가 되는 쿼리만을 찾아낼 수 있는 방법을 모색한 결과, AWS Lambda를 이용해 자동으로 Slack으로 알림을 받을 수 있는 방법을 구현하기로 결정했습니다.

해당 글은 MySQL RDS 인스턴스를 미리 생성하였다는 가정 하에 작성되었습니다.

1. RDS 파라미터 그룹 설정

AWS RDS에서 Slow Query 로그 활성화를 하기 위해 파라미터 그룹을 설정 해주어야 합니다.

AWS RDS -> 파라미터 그룹을 선택

파라미터 그룹 생성 클릭

이후 파라미터 그룹을 생성해주고 파라미터 그룹을 수정해주어야 합니다.

이때 수정해줘야 할 파라미터는 아래와 같습니다.

slow_query_log=1 (slow query 발생 시 로그 생성, Default=0)
long_query_time=4 (4초 넘게 실행되는 쿼리 대상으로 로그 생성)
log_output=FILE (Cloud Watch로 확인 시 FILE로 적용)

2. RDS 인스턴스 설정

이제 미리 생성해두었던 RDS 데이터베이스로 돌아가

데이터베이스 수정 -> 추가 구성 -> 데이터베이스 옵션 에서 생성해 두었던 파라미터 그룹을 지정해줍니다

더 내려가 로그 내보내기에서 느린 쿼리 로그를 체크해주고 수정 사항을 저장해줍니다.

그리고 RDS 인스턴스를 재부팅 해줍니다.

RDS 인스턴스가 재부팅 되었다면 테스트를 위해 SELECT SLEEP(5) 쿼리를 실행 시킨 후 Cloud Watch의 로그 그룹으로 가서 확인하시면 Slow Query 가 저장 된 것을 확인할 수 있습니다.

3. Slack Webhook 설정

이제 Slow Query를 슬랙에서 알림 받기 위해서 Slack Webhook URL을 생성해주어야 합니다.

slack webhook 설정에는 2가지 방법이 있습니다.

• 앱 생성 : https://api.slack.com/ -> Your apps 에서 Create New App 버튼 클릭

• 앱 추가 : 해당 채널 -> 앱 추가 -> Incoming WebHooks 검색 후 추가

앱 추가 방식은 만약 생성자가 채널에서 탈퇴를 하는 경우 webHooks도 비활성 되기 때문에 비추천 한다고 합니다.

해당 포스트에서는 앱 생성 방식으로 설명하도록 하겠습니다.

https://api.slack.com/ -> Your apps -> Create New App 버튼 클릭 후 From scratch를 선택합니다.

Create App 클릭 후, 먼저 Settings -> Collaborators에서 동료를 추가해주어야 합니다. (본인이 채널에서 나가더라고 유지가 됩니다)

검색을 통해 추가하고 싶은 동료를 추가해주면 됩니다.

이제 Features -> Incoming Webhooks -> Activate Incoming Webhooks On -> Add New Webhook to Workspace 에서 채널 선택 후 Allow를 누르면 됩니다.

이후 Incoming Webhooks에서 Webhook URL 값을 기억해놓으시면 됩니다.

4. Lambda 생성

Lambda함수를 통해 RDS에서 Slow Query 가 생성되면 이를 Cloud Watch에 저장하고 이를 슬랙으로 보내줄 겁니다.

AWS Lambda -> 함수 생성을 클릭해주고 아래와 같이 작성해줍니다.

Lambda는 Python, Node.js, Ruby, Java 등 여러 언어를 지원해줍니다.

저 같은 경우 Node.js를 선택하였습니다.

Java의 경우 Lambda 실행 시 ColdStart에 걸리는 시간이 다른 언어보다 오래 걸리기 때문에 그 다음으로 잘 하는 언어인 Javascript를 사용하게 되었습니다. (ColdStart는 람다를 처음 시작할 때 컨테이너가 실행되면서 걸리는 지연 시간을 뜻합니다.)

이후 함수 코드를 작성해주시면 됩니다.

import https from 'https';
import zlib from 'zlib';

const SLOW_TIME_LIMIT = 4; // Slow Query 시간 기준
const SLOW_QUERY_SLACK_URL = `${위에서_만들어놓은_SLACK_WEBHOOK}`;

export const handler = (input, context) => {
    // (1) 
    const payload = Buffer.from(input.awslogs.data, 'base64');
    
    zlib.gunzip(payload, async (err, result) => {
        if (err) {
            return context.fail(err);
        }
    // (2) 
        const resultString = result.toString('utf8');
        let resultJson;

        try {
            resultJson = JSON.parse(resultString);
        } catch (parseErr) {
            console.error(parseErr.message);
            console.error(`[알람발송실패] JSON.parse(result.toString('utf8')) Fail, resultUTF8= ${resultString}`);
            return context.fail(parseErr);
        }

        console.log(`result json = ${resultString}`);
      
        for (const logEvent of resultJson.logEvents) {
            const logJson = toJson(logEvent, resultJson.logStream); // (3)
            try {
                if (logJson.queryTime > SLOW_TIME_LIMIT) {
                    console.log('slow query message');
                    const message = slackMessage(logJson);  // (4)
                    await postSlack(message, SLOW_QUERY_SLACK_URL);   // (5)
                } 
            } catch (slackErr) {
                console.error(slackErr.message);
                console.error(`slack message fail= ${JSON.stringify(logJson)}`);
            }
        }
    });
};

const toJson = (logEvent, logLocation) => {
    const { message, timestamp } = logEvent;

    const splitMessages = message.split('#');
    const userInfos = splitMessages[2].split(' ');
    const queryInfos = splitMessages[3];
    const queryTime = queryInfos.split(' ')[2];
    const currentTime = toYyyymmddhhmmss(timestamp);
    const queryMessages = queryInfos.split(';');
    const queryMessage = queryMessages[queryMessages.length - 2];

    return {
        currentTime,
        logLocation,
        userIp: userInfos[5],
        user: userInfos[2],
        pid: userInfos[8],
        queryTime,
        query: queryMessage,
    };
}

const toYyyymmddhhmmss = (timestamp) => {
    if (!timestamp) {
        return '';
    }

    const pad2 = (n) => (n < 10 ? `0${n}` : n);

    const kstDate = new Date(timestamp + 32400000);
    return `${kstDate.getFullYear()}-${pad2(kstDate.getMonth() + 1)}-${pad2(kstDate.getDate())} ${pad2(kstDate.getHours())}:${pad2(kstDate.getMinutes())}:${pad2(kstDate.getSeconds())}.${pad2(kstDate.getMilliseconds().toPrecision(3))}`;
};


const slackMessage = (messageJson) => ({
    text: 'Slow Query 발생!',
    attachments: [
        {
            color: '#ff7f00',
            title: `${messageJson.currentTime} 발생 Slow Query`,
            fields: [
                {
                    title: 'Query',
                    value: messageJson.query,
                    short: false,
                },
                {
                    title: 'Query Time',
                    value: messageJson.queryTime,
                    short: false,
                },
                {
                    title: 'Log Location',
                    value: messageJson.logLocation,
                    short: false,
                },
                {
                    title: 'Request User',
                    value: messageJson.user,
                    short: false,
                },
                {
                    title: 'Request IP',
                    value: messageJson.userIp,
                    short: false,
                },
            ],
        },
    ],
});

export const postSlack = async (message, slackUrl) => {
    const options = createRequestOptions(slackUrl);
    return request(options, message);
};

export const createRequestOptions = (slackUrl) => {
    const { host, pathname } = new URL(slackUrl);
    return {
        hostname: host,
        path: pathname,
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
        },
    };
};

const request = (options, data) => new Promise((resolve, reject) => {
    const req = https.request(options, (res) => {
        res.setEncoding('utf8');
        let responseBody = '';

        res.on('data', (chunk) => {
            responseBody += chunk;
        });

        res.on('end', () => {
            resolve(responseBody);
        });
    });

    req.on('error', (err) => {
        console.error(err);
        reject(err);
    });

    req.write(JSON.stringify(data));
    req.end();
});

코드가 긴데 하나하나 설명해보자면

(1)

const payload = Buffer.from(input.awslogs.data, 'base64');
    
zlib.gunzip(payload, async (err, result) => { ... }

Cloud Watch에서 전송되는 코드는 Base64로 인코딩 되어 있고, gzip 형식으로 압축되어 있습니다. 이를 압축 해제하고 Base64 디코딩을 해야 합니다.

(2)

const resultString = result.toString('utf8');
        let resultJson;

try {
    resultJson = JSON.parse(resultString);
} catch (parseErr) {
    console.error(parseErr.message);
    console.error(`[알람발송실패] JSON.parse(result.toString('utf8')) Fail, resultString= ${resultString}`);
    return context.fail(parseErr);
}

(1)에서 생성한 Buffer 객체를 String 문자열로 변환해주고, JSON으로 직렬화 하고 있습니다.
result.toString('utf8') 로 인코딩을 해주고 있는데 만약 'ascii'로 인코딩을 해주면 한글이 깨질 수 있습니다.

(3)

const logJson = toJson(logEvent, resultJson.logStream); // (3)

...

const toJson = (logEvent, logLocation) => {
    const { message, timestamp } = logEvent;

    const splitMessages = message.split('#');
    const userInfos = splitMessages[2].split(' ');
    const queryInfos = splitMessages[3];
    const queryTime = queryInfos.split(' ')[2];
    const currentTime = toYyyymmddhhmmss(timestamp);
    const queryMessages = queryInfos.split(';');
    const queryMessage = queryMessages[queryMessages.length - 2];

    return {
        currentTime,
        logLocation,
        userIp: userInfos[5],
        user: userInfos[2],
        pid: userInfos[8],
        queryTime,
        query: queryMessage,
    };
}

json 형식의 데이터를 슬랙 메시지로 보내기 쉽게 가공하는 과정입니다.

toYyyymmddhhmmss(timestamp)는 UTC 시간 데이터를 식별 가능한 KST로 변환해줍니다.

(4)

const message = slackMessage(logJson);

toJson을 통해 받은 JSON 데이터를 슬랙 메시지로 변환합니다.

(5)

await postSlack(message, SLOW_QUERY_SLACK_URL);  

슬랙 URL로 가공한 메시지를 전달합니다. 이때 비동기로 전송하기 위해 Promise 객체로 만들어줍니다.

이제 테스트를 해볼 차례입니다.

AWS Lambda > 테스트로 들어가면 람다로 테스트 메시지를 보낼 수 있는데

메시지 형식은 아래와 같이 보내야 합니다.

{
  "awslogs": {
    "data": "gzip로 압축된 데이터"
  }
}

여기서 data 내부의 내용에 MySQL 쿼리 로그를 입력해야 하는데 이때 gzip로 압축을 해줘야 합니다.

압축 전 데이터는 아래와 같습니다.

{
  "messageType": "DATA_MESSAGE",
  "owner": "123456789123",
  "logGroup": "testLogGroup",
  "logStream": "testLogStream",
  "subscriptionFilters": [
    "testFilter"
  ],
  "logEvents": [
    {
      "id": "eventId1",
      "timestamp": 1440442987000,
      "message": "# Time: 2024-08-17T02:56:39.392215Z
# User@Host: user[user] @  [123.456.789.10]  Id: 11111 # Query_time: 5.000313  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1 use dbname;
SET timestamp=1723863394; select sleep(5) LIMIT 0, 1000;"
    }
  ]
}

이 JSON 데이터를 압축 사이트 에서 압축하시면 테스트 데이터를 얻을 수 있습니다.

이제 테스트를 해보시면 슬랙 메시지가 오는 것을 확인할 수 있습니다.

5. CloudWatch & Lambda 연동

Cloud Watch 로 들어가 해당 로그 그룹 선택 -> 작업 -> 구독필터 -> Lambda 구독 필터 생성을 차례로 선택합니다.

구독 필터 생성 화면에 들어가면 생성한 람다 함수를 등록합니다.

아래로 내려가보시면 로그 형식 및 필터 구성이 있습니다.

여기서 구독 필터 이름을 지정해주시고 구독 필터 패턴 같은 경우 필요한 형태에 따라 적어주시면 됩니다. (ex : 조회만 필터링 하고 싶다면 SELECT 를 적어 놓으시면 됩니다.)

아래로 내려가면 필터링 테스트를 할 수 있습니다.

스트리밍 시작을 누르면 더이상 수정이 어려우니 여기서 테스트를 하는 것을 추천합니다.

이제 모든 설정이 끝났으므로 본인의 DB 에 테스트를 해보면

SELECT SLEEP(5);

그럼 아래와 같이 슬랙 알림이 오는 것을 확인할 수 있습니다.