스케일 아웃 적용 이후 스케줄러가 늘어난 서버 수 만큼 돌아가던 문제가 발생

기존 서버에서 스케쥴러를 삭제하고, 스케쥴러를 위한 서버를 새로 만드는 것을 고려

→ 서버가 스케쥴러만을 위해서 대기하고 있기 때문에 그 외에 대기하는 시간이 아깝습니다.

→ AWS에서 제공하는 기능 중 하나인 Lambda와 Amazon EventBridge를 활용해서 특정 시간에 Lambda에 저장된 코드를 실행하도록 구성했습니다.

import redis
import os
import json
import pymysql
import random

# 환경변수 세팅
redis_host = os.environ['ELASTICACHE_HOST']
redis_port = int(os.environ['ELASTICACHE_PORT'])
db_host = os.environ['DB_HOST'] 
db_username = os.environ['DB_USERNAME'] 
db_password = os.environ['DB_PASSWORD'] 
db_name = os.environ['DB_NAME'] 
db_port = int(os.environ['DB_PORT'])

connection = pymysql.connect(
        host = db_host,
        user = db_username,
        passwd = db_password,
        db = db_name,
        port = db_port
    )

def lambda_handler(event, context):
    
    # Redis 연결
    redis_client = redis.StrictRedis(host=redis_host, port=redis_port)
    
    with connection:
        with connection.cursor() as cur:
            if not connection.open:
                connection.ping(reconnect=True) # 연결이 닫혔다면 다시 연결을 시도
            cur.execute("UPDATE stock SET stock = '1000' WHERE id IN (SELECT id FROM (SELECT DISTINCT s1.id FROM study.stock s1 LEFT JOIN study.order_product op ON op.product_id = s1.product_id WHERE stock < 100) AS subquery);")
            connection.commit()
            
    with connection:
        if not connection.open:
            connection.ping(reconnect=True) # 연결이 닫혔다면 다시 연결을 시도
        with connection.cursor() as cur:
            cur.execute("TRUNCATE table event;")
            connection.commit()     
            
    
    if not connection.open:
        connection.ping(reconnect=True) # 연결이 닫혔다면 다시 연결을 시도
    cursor = connection.cursor()
    cursor.execute("SELECT p.product_id, title FROM product p WHERE p.product_id IN (SELECT product_id FROM (SELECT op.product_id, COUNT(op.product_id) AS product_count FROM order_product op GROUP BY op.product_id ORDER BY product_count DESC LIMIT 5) subquery);")
    
    rows = cursor.fetchall()
    
    for index,row in enumerate(rows):
        
        id = row[0]
        title = row[1]
        json_data = make_product_rank_dto(id,title)
        redis_client.set("product:rank:{}".format(index+1),json_data)
    
    
    total = redis_client.get("product:total")
    
    if total == None:
        # product count cache miss
    
        cursor.execute("SELECT count(*) FROM product;")
        total = cursor.fetchall()
        
        redis_client.set("product:total",str(total[0][0]))
        total = total[0][0]
        
    else:
        total = int(total)
        
    random_numbers = set()

    while len(random_numbers) < 20:
        random_number = random.randint(1, total)
        random_numbers.add(random_number)
    
    event_id_list = list(random_numbers)
    
    #save event_id_list 
    result = json.dumps(event_id_list)    
    redis_client.set("product:sale:list",result)
    
    
    # 10 이상 40 이하의 무작위 정수 생성
    
    
    cursor.execute("SELECT * FROM product WHERE product_id IN {}".format(tuple(event_id_list)))
    
    product_list = cursor.fetchall()
    

    sql = "INSERT INTO event (sale_rate, product_id) VALUES ({}, {})"
    
    #stock
    cursor.execute("SELECT s.stock FROM stock s WHERE s.product_id IN {}".format(tuple(event_id_list)))
    
    stock_list = cursor.fetchall()
    print(stock_list)
    for index, product in enumerate(product_list):
        sale_rate = 10 + (int(random.random() * 7) * 5)
        product_id = product[0]
        price = product[4]
        
        product_string = make_product_dto(product)
        print(product_string)
        
        ## stock redis
        redis_client.setex("product:sale:{}:stock".format(product_id),24*60*60,stock_list[index][0])
        redis_client.setex("product:sale:{}:price".format(product_id),24*60*60,price)
        redis_client.setex("product:sale:{}".format(product_id),24*60*60,product_string)
        
        with connection:
            if not connection.open:
                connection.ping(reconnect=True) # 연결이 닫혔다면 다시 연결을 시도
            with connection.cursor() as cur:
                cursor.execute(sql.format(sale_rate, product_id))
                connection.commit()     

    return {
        'statusCode': 200,
        'body': 'Redis 명령 실행 완료'
    }

def make_product_rank_dto(id,title):
    data = {
        "id": id,
        "title": title
    }
    return json.dumps(data)
    

def make_product_dto(product):
    id = product[0]
    category1 = product[1]
    category2 = product[2]
    image = product[3]
    price = product[4]
    stock = product[5]
    title = product[6]
    data = {
        "id": id,
        "category1" : category1,
        "category2" : category2,
        "image" : image,
        "price" : price,
        "stock" : stock,
        "title": title
    }
    return json.dumps(data)