특정 기간의 통계를 화면에 출력해야 할 경우가 있다. 일련의 날짜들이 DB 테이블에 들어 있다면 별 문제가 없겠지만, 굳이 별도의 통계 테이블이 필요하지 않다면 쿼리로 일련의 날짜들에 관련된 임시 테이블을 생성해야 한다. 우선 필요한 날짜 기간을 파악하고, 날짜 테이블을 만들어 본다.

curdate() - interval 1 day
curdate() - interval 2 day
curdate() - interval 3 day

이런 식이라면 현재부터 원하는 이전날짜까지의 결과셋을 만들 수 있다.

아래는 0000 부터 9999까지 각 자리마다 컬럼을 구성하는 쿼리이다.

select *
from 
(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join 
(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join 
(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join 
(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d

0 | 0 | 0 | 0 부터
1 | 0 | 0 | 0
...
9 | 9 | 9 | 8
9 | 9 | 9 | 9 까지가 출력된다.

그리고 아래와 같이 select 문을 변경하면 원하는 일자 테이블을 출력할 수 있다.

select curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) day as date

2021-10-07
2021-10-06
2021-10-05
...

그리고 여기에 기간도 설정하고 다른 테이블과 조인하여 통계에도 사용할 수 있다.

select a.date as daily, sum(b.cost) as dailycost
from (
    select curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) day as date
    from 
        (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join 
        (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join 
        (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
        cross join 
        (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    ) a left outer join `sales` b on a.date = Date(b.createDatetime)
where 1=1
and a.gDate between '2021-09-01' and '2021-09-31' 
group by daily
order by daily asc;

위 쿼리로 2021-09-01 부터 2021-09-31 까지의 일별 매출 통계를 출력할 수 있다.

약간 무식해 보이긴 하지만 필요하다면 ^^

결론부터 말하자면 RDS 의 cpu 가 예상과 다르게 100% 를 찍는다면 빨리 slow query 를 찾아보세요.

며칠 동안 불규칙 적으로 발생한 5분 정도 짜리의 cpu 과부하를 찾아내는 것은 쉽지 않았습니다.

RDS 에서 생성된 slow_query.log 파일에는 로그 flush 메시지 밖에 없었으니까요.

특별히 높지 않은 커넥션 수...

lock 이 걸렸는지, slow query 가 걸렸는지 5분 정도만에 cpu 점유율이 다시 안정을 되찾는...

밤이고 낮이고 심심할 때 가끔씩 찾아오는 과부하라...

그 시간대에 발생된 query 를 5 대가 넘는 서버에서 모두 뽑기엔, 아니 뽑는다 해도 이 중 무엇이 문제인지 알 수가 없다는...

람다에서 주기적으로 유입되는 데이터를 의심했지만, 시간대가 맞기도 하고 안맞기도 하고...

processlist query 를 1분마다 날리면서 lock 걸린 query 를 찾아야 하는 것인가...

일단 slow query 를 좀 봐야겠다는 생각에 20초 짜리 query 를 날렸지만, 이마저 slow_query.log 파일은 응답하지 않았습니다.

결국 고객센터에 징징거리기 시작했더니, 해결책을 알려줬습니다.

log_output 이 default 로 table 로 되어 있어서 slow query 가 테이블에 들어가 있다는...

EC~! 근데 왜 slow_query 파일이 생성되서 사람 민망하게 만드는 거냐고!!

화는 잠시 접어두고 결국 slow query 를 찾아 잘못된 인덱스를 수정하고 cpu 를 잠재웠습니다.

하필 중요한 검색 쪽이 ㅡㅡ;;

그럼 그렇지. 이 많은 query 들이 slow query 하나 없이 잘 돌아가고 있다고 생각한 나는 용자.

참고로 알아두면 좋을 Parameter Groups 를 적어 봅니다.

• slow_query_log : 1 (slow query 사용)
• long_query_time : 2 (slow query 제한, default 10 초)
• log_queries_not_using_indexes : 1 (인덱스 타지 않는 query 기록)
• log_output : table (로그 출력 table / file)

웹서비스를 구축할 때 데이터베이스는 주로 utf-8 을 사용해 왔다.

요즘은 웹과 모바일 앱을 동시에 사용하다 보니, 입력란에 모바일 이모지들을 함부로(?) 입력하는 유저들 덕분에 앱이 종료되거나 오류가 발생하는 일이 생긴다.

이것은 모바일 내장 이모지들이 4byte 문자열이고, DB collation 이 3byte 집합인 UTF8 을 사용하기 때문이다.

그로 인해 아래와 흡사한 오류들을 마주할 수 있다.

해결 방법은 어렵지 않다. DB 옵션의 character_set / collation 과 테이블을 UTF8 에서 UTF8MB4 로 변경하면 된다.

Mysql 에서는 2010년 MYSQL 5.5.3 버전에 4byte 집합인 utf8mb4 charset 을 추가하였다.

기존 utf8 시스템을 utf8mb4 로 바꾸어도 값의 손실은 없으며, DB 용량이 조금 늘어날 것이다.

난 아래의 순서대로 기존 UTF8 코드를 UTF8MB4 로 변경했다.

(환경 : java / RDS mysql)

1. JDBC

소스에서 DB 연결에 jdbc characterEncoding 을 지정하는데 이것을 utf8 에서 utf8mb4 로 바꾸면 되는줄 알았지만 MySQL Connector/J 문서에 보면 utf8mb4 는 지원하지 않는다.

그냥 characterEncoding 파라미터를 삭제해 버리면 DB 의 기본 인코딩대로 작동한다. 이거 수정하고 그냥 배포!

요고 안하면 조~기 맨 위에 오류가 발생한다.

2. Parameter Groups

RDS mysql 의 Parameter Groups 은 my.ini 파일과 같다. 각종 옵션들을 수정할 수 있다. 여기서 인코딩을 전부 바꿔준다.

(참고로 character_set_server 만 바꿔주고, 나머지는 default 로 내비둬도 utf8mb4 적용되었음.)

3. Table Column

이모지를 저장할 테이블의 컬럼들을 utf8mb4 로 수정한다.

table 전체를 바꿔도 무방하지만 난 이모지가 저장될 컬럼에만 utf8mb4 를 적용했다.

4. DB reboot

Parameter groups 의 인코딩 관련한 옵션들은 바로 적용이 되지 않는다. 재부팅이 필요하다.

Parameter groups 가 적용되지 않으면 테이블 collation 변경 덕분에 오류 없이 데이터는 들어오지만 이모지 대신 ??? 문자로 도배될 것이다.

재부팅하고 자알~ 되는지 확인한다.

위 이미지는 ios 에서 입력한 이모지를 android 와 web 에서 확인한 것이다.

http://getemoji.com/ 의 이모지 리스트처럼 웹에서 ㅁ형태로 깨져보이는 이모지도 꽤 있지만 뭐...

그건 내 힘으로 해결할 수 있는건 아닌듯 하고...

유의사항

Mysql 에서 한 컬럼의 최대 key 사이즈는 767 byte 이다.

그래서 index 가 문자열일 경우 utf8 에서는 255 까지 지정이 가능했다 (767 / 3 byte = 255...)

만약 utf8mb4 로 변경한 컬럼이 문자열 index 로 사용되고 있다면, 그 컬럼의 자료형은 varchar(191) 로 수정되어야 한다. (767 / 4 byte = 191...)

그렇지 않으면 아마도 이런 오류를 만나게 될 것이다.

혹은 innodb_large_prefix=1 로 시스템 변수를 설정하면 key 길이가 3072 byte 까지 확장되므로 위 오류를 막을 수 있다.

테이블의 특정 조건의 데이터는 오름차순, 특정 조건의 데이터는 내림차순이 가능할까...

이벤트가 있다.

이벤트 종료일이 10/20 부터 10/30 일 까지가 있다.

오늘이 10/25 일 이라면 이벤트 종료 임박 순서로 나열하고자 때,

10/25 ~ 30 일까지 오름차순, 그 뒤에 10/24 ~ 20 일까지가 내림차순으로 나열되어야 한다.

단순하게 생각하고 쿼리를 날렸더니 다음과 같은 결과가 나왔다.

10/24 ~ 20, 10/25 ~ 30

음... 순서가 지맘대로 나왔군. 저 두 순서만 바꾸면 되겠구나 해서...

했더니만 결과는 같았다 ㅡㅡ

아래처럼 해결...

하나의 결제 테이블에 결제 수단과 결제한 금액이 들어 있다.

결제 수단별 결제 횟수와 결제 금액을 출력하는 sql.

컬럼별 subquery 를 돌리지 않고 if 문을 사용하여 조금이나마 서버를 도와주기.

* Subquery

* if condition