Aurora MySQL slowquery 슬랙으로 알람 받기

---

How to setting Aurora MySQL slowquery alert to slack

 

 

 

■ 구조

1. RDS Slowquery  발생

2. CloudWatch 로그 그룹에 수집

3. Lambda 에서 트리거

4. Slack 알람

 

 

■ Lambda 환경

- KMS를 통한 Slack_url 암호화

- python 3.8

 

 

■ RDS 설정 확인

- RDS instance 설정에서 slowquery 체크 박스 확인 (CloudWatch로 전송 여부)

- RDS instance 파리미터 설정

    - long_query_time (1)

    - slow_query_log (1)

    - slow_query_log_file (path)

    - log_output (File)

 

■ CloudWatch LogGroups 확인

- slowquery로 검색하여 제대로 수집되고 있는지 확인

 

 

■ RDS 관련 trouble shooting

/* slowquery loggroup이 없을 경우 강제 발생시켜 생성되는지 확인 */
SELECT SLEEP(3);

/* slowquery 관련 파라미터 확인 */
SHOW VARIABLES LIKE '%slow_query%';
SHOW GLOBAL VARIABLES LIKE '%slow%';
SHOW GLOBAL VARIABLES LIKE '%long%';
SHOW GLOBAL VARIABLES LIKE '%out%';
SHOW GLOBAL VARIABLES LIKE '%time%';

/* 로그 출력 방법 확인 table 이냐 file 이냐 
SHOW VARIABLES LIKE '%log_output%';

/* output이 테이블로 되어 있다면 조회가 되어야 함 */
SELECT * FROM mysql.slow_log;

 

 

■ Lambda IAM Role (policy)

{
    "Statement": [
        {
            "Action": "kms:Decrypt",
            "Effect": "Allow",
            "Resource": [
                "arn:aws:kms:ap-northeast-2:111111111111:key/11111111-1111-1111-1111-111111111111"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "logs:CreateLogGroup",
            "Resource": [
                "arn:aws:logs:ap-northeast-2:111111111111:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": [
                "arn:aws:logs:ap-northeast-2:111111111111:log-group:/aws/lambda/lambda-slowquery-catch:*"
            ]
        }
    ],
    "Version": "2012-10-17"
}

- 07 : kms arn 입력

- 14 : cloudwatch log grop 생성 권한

- 24 : lambda arn 입력

 

 

■ lambda 환경 변수

- kmsEncryptedHookUrl

- slackChannel

 

 

■ lambda code (python 3.8)

import boto3
import json
import logging
import os

import base64
from base64 import b64decode
import gzip

from urllib.request import Request, urlopen
from urllib.error import URLError, HTTPError


# The Slack channel to send a message to stored in the slackChannel environment variable
ENCRYPTED_HOOK_URL = os.environ['kmsEncryptedHookUrl']
SLACK_CHANNEL=os.environ['slackChannel']

HOOK_URL = "https://" + boto3.client('kms').decrypt(
    CiphertextBlob=b64decode(ENCRYPTED_HOOK_URL),
    EncryptionContext={'LambdaFunctionName': os.environ['AWS_LAMBDA_FUNCTION_NAME']}
)['Plaintext'].decode('utf-8')

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def decompress_json(data):
    compressed_payload = base64.b64decode(data)
    return json.loads(gzip.decompress(compressed_payload))

def lambda_handler(event, context):
    logger.info("Event: " + str(event))
    eventData = decompress_json(event['awslogs']['data']);
    logger.info("Message: " + str(eventData))
    
    # <----- 깔끔하게 정렬하여 출력 -----> #
    rds_name=eventData['logStream']
    
    cloudwatch_text=eventData['logEvents'][0]['message']
    
    slack_text=cloudwatch_text.split()
    
    slack_text_excute_query=cloudwatch_text.split(';')

    # Time
    result_time=slack_text[2]
    
    # User
    result_user=slack_text[5].split('[')[1].split(']')[0]
    
    # Host
    result_host=slack_text[7].split('[')[1].split(']')[0]
    
    # id
    result_id=slack_text[9]
    
    # query_time
    result_query_time=slack_text[12]
    
    # lock_time
    result_lock_time=slack_text[14]
    
    # rows_sent
    result_rows_sent=slack_text[16]
    
    # row_examined
    result_rows_examined=slack_text[18]
    
    # database
    #result_database=slack_text[20]  # 나올 때 있고 안나올 때 있어서 봉인
    
    # excute_query
    result_excute_query=slack_text_excute_query[-2]	

    slack_message = {
        'text': "*[slow query detected] %s*\n>>>*Time(UTC):* %s\n*User:* %s\n*Host:* %s\n*Id:* %s\n*Query_time:* %s\n*Lock_time:* %s\n*Rows_sent:* %s\n*Rows_examined:* %s\n*Excuted_query:* %s" % (rds_name, result_time, result_user, result_host, result_id, result_query_time, result_lock_time, result_rows_sent, result_rows_examined, result_excute_query)
    }

    req = Request(HOOK_URL, json.dumps(slack_message).encode('utf-8'))
    try:
        response = urlopen(req)
        response.read()
        logger.info("Message posted to %s", SLACK_CHANNEL)
    except HTTPError as e:
        logger.error("Request failed: %d %s", e.code, e.reason)
    except URLError as e:
        logger.error("Server connection failed: %s", e.reason)

- 수정할 것 없이 그대로 복붙해서 사용해도 알람은 정상적으로 옵니다.

- 내용을 천천히 훑어보면서 직접 튜닝하시는 것을 추천드립니다.

 

 

■ 테스트 코드

{
  "awslogs": {
    "data": "H4sIAAAAAAAACk1QTU/CQBC98ysm5SrNdN3SD2ICiRVJ8KCtFwkhtd2QKu2S7lZEwn93ti3oHia77715O29OAwCrFEqlW5Ec98IKwbqfJbPNUxTHs3lk3RiBPFSiNpTDbrk79vyALh21k9t5LZu9YbVQenl5X9hY1yIt/9E90PKqeVdZXex1IauHYqdFrUi5Igo6fQdaBKwvhtGXqPSf7NRWoorcfCIMu8id1r+zKSifTkszosM5cs4C30PEq6LPb9qHkJA8BIaMjdAboZugF2IQcrR9dDDw32AIr0rU00epdAhpXhbVqq1rmMLKQRttZjOOa1jkxghdpJbnRtTHjZklBNem3xkGsJTZZ49RW3vgRR7URlEIwrqH+E7JXZCZA40SUMoskz/HCcRRAtd0d87YpVQ88NwJfDRKg9nfxKwO4GwWODj/Aik3VCXuAQAA"
  }
}

- Lambda 코드를 작성하고 제대로 작성했는지 바로 test를 진행하기 위한 코드입니다.
- 위의 데이터는 gzip 으로 압축된 형태로, 압축 되기 전의 데이터 형태는 아래와 같습니다.

 

 

■ 테스트 코드 압축 전 형태 (참고용)

{
  "messageType": "DATA_MESSAGE",
  "owner": "123456789123",
  "logGroup": "testLogGroup",
  "logStream": "testLogStream",
  "subscriptionFilters": [
    "testFilter"
  ],
  "logEvents": [
    {
      "id": "eventId1",
      "timestamp": 1440442987000,
      "message": "# Time: 2022-07-05T07:09:40.801098Z # User@Host: admin[admin] @ [10.0.2.240] Id: 20050 # Query_time: 5.000209 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 1 use moccozy; SET timestamp=1657004975; just test;"
    }
  ]
}

- 말 그대로 참고용입니다.

- 테스트 코드를 입맛에 맞게 수정하려면 위의 코드를 수정한 다음 gzip 으로 압축해서 Lambda test 코드에 입력하면 됩니다.

 

■ 테스트 코드 알람 확인

 

 

■ Lambda 트리거 연결

CloudWatch Logs를 선택하고 해당 RDS의 slowquery LogGroups를 검색하여 선택합니다.

필터 이름은 아무거나 입력하면 됩니다.

 

 

■ slowquery 발생 쿼리

/* slowquery alert to slack test (database_name) */
SELECT SLEEP(3);

- Database에 접속하여 다음의 명령어를 실행합니다.

 

 

■ 테스트 쿼리 알람 확인

 

 

■ Reference

	내용	URL
	gzip 압축 변환하는 사이트	https://www.multiutil.com/text-to-gzip-compress/
	lambda 테스트 코드 작성 방법 참고	https://jojoldu.tistory.com/570
	mysql slow query 잡아내기	https://dukkeobi.tistory.com/14
	Amazon Aurora slack 알람	https://enomotodev.hatenablog.com
	Automate RDS Slow Query Log Analysis With Slack Integration	https://blog.shellkode.com

 

 

 

---

by mkdir-chandler

---