'Database/Mysql'에 해당하는 글 26건

RDS cpu 100%

Database/Mysql 2017. 8. 9. 23:39

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

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

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


/rdsdbbin/mysql/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
cs


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

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)





WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

Insert Emoji

Database/Mysql 2017. 2. 21. 22:52

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

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

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

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


### Error updating database.  

Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83 \xF0...' for column 'comment' at row 1



해결 방법은 어렵지 않다. 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


connect.url=jdbc:mysql://db_domains.rds.amazonaws.com:3306/test?useUnicode=true&characterEncoding=utf8
cs


소스에서 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 를 적용했다.


ALTER TABLE `oops4u`.`article` 
CHANGE COLUMN `title` `title` VARCHAR(255CHARACTER SET 'utf8mb4' NOT NULL COMMENT '제목 ' ,
CHANGE COLUMN `content` `content` VARCHAR(255CHARACTER SET 'utf8mb4' NOT NULL COMMENT '내용' ;
...
cs



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...)

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


ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


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




WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

order by case when

Database/Mysql 2016. 10. 27. 15:57

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


이벤트가 있다.

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

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

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


order by 

case when event_edate >= NOW() then event_edate end asc,

case when event_edate < NOW() then event_edate end desc


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


10/24 ~ 20, 10/25 ~ 30


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


order by 

case when event_edate < NOW() then event_edate end desc,

case when event_edate >= NOW() then event_edate end asc


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

아래처럼 해결...


order by

case when event_edate >= NOW() then 1 else 2 end,

case when event_edate >= NOW() then event_edate + 0 end asc,

case when event_edate < NOW() then event_edate + 0 end desc



WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

mysql if

Database/Mysql 2016. 1. 30. 20:40

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

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

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


* Subquery


1
2
3
4
5
select 
    (select count(*from order where ds_pay_method = 'VBank' and no_pay_amt > 0) as vbank_cnt,
    (select sum(no_pay_amt) from order where ds_pay_method = 'VBank' and no_pay_amt > 0) as vbank_sum,
    (select count(*from order where ds_pay_method = 'Card' and no_pay_amt > 0) as card_cnt,
    (select sum(no_pay_amt) from order where ds_pay_method = 'VBank' and no_pay_amt > 0) as card_sum
cs



* if condition


1
2
3
4
5
6
7
select
    sum( if(ds_pay_method = 'VBank',1,0) ) as vbank_cnt,
    sum( if(ds_pay_method = 'VBank',no_pay_amt,0 ) ) as vbank_sum,
    sum( if(ds_pay_method = 'Card',1,0) ) as card_cnt,
    sum( if(ds_pay_method = 'Card',no_pay_amt,0 ) ) as card_sum
from order
where no_pay_amt > 0;
cs




WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

Row size too large

Database/Mysql 2015. 10. 29. 10:54

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline

 

Mysql 에서 글을 겁나 길게 썼더니 발생한 오류이다.
InnoDB 는 한 레코드 삽입에 있어서 8000 byte 의 크기 제한이 있다. 그 크기를 넘어섰다고 경고를 하는 것이다.
해당 컬럼 타입을 TEXT 나 BLOB 으로 변경하거나 ROW_FORMAT 을 DYNAMIC 이나 COMPRESSED 로 변경하란다.
이미 BLOB 으로 되어 있고 COMPRESSED 로 포맷을 변경하였는데도 동일한 에러가 발생하였다.

 

여러 삽질 끝에 테이블 row_format을 compressed 로 변경하기 위해서는,
my.inf 나 AWS 의 Parameter Groups 에서 innodb_file_format 을 Barracuda 로 설정해야 한다는 것을 알았다.
Barracuda 는 DYNAMIC 과 COMPRESSED 을 지원하는 InnoDB 파일 형식이다.

 

 

먼저 file_format 을 바꾸고,

 

innodb_file_format = Barracuda
( > SET GLOBAL innodb_file_format = barracuda; )

 

row_format 을 바꾼다.

 

ALTER TABLE mytable
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED
    KEY_BLOCK_SIZE=8;

 

 

그리고 정상적으로 변경이 되었는지 확인한다.

 

> show variables like "%innodb_file%";
innodb_file_format        Barracuda
innodb_file_format_check  ON       
innodb_file_format_max    Barracuda
innodb_file_per_table     ON       

 

> show table status like 'mytable';
Row_format : Compressed

 

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  2개가 달렸습니다.
  1. 이 포스트 덕분에 문제를 해결했네요. 감사합니다. ;)
secret