'RDB'에 해당되는 글 62건

일하다보면 사업부서의 요청으로 통계정보를 추출해달라는 요청을 많이 받는다.
DBA가 없는 회사는 개발자들이 직접 작업하는 경우가 많고 이게 업무에 생각보다 많은 부분을 차지하고 있다.

SAS나 SAP이 있는 회사라면 그걸 쓰면 될텐데.. 이건 너무 비싸고 그렇다고
별도의 데이터 마트나 샌드박스가 구축은 시간이 오래걸리고 이것도 생각보다 비용이 많이 들어간다.
그리고 결정적으로 SAS 도입을 검토한다고 해서 회의에 참여한 적이 있는데 음.. SAS쪽  사업담당자의

시연을 보면서 들던 생각은  저 정도를 쓰려면 MS의 ACCESS 정도는 문제없이 사용할 정도의 숙련도를 가진 사업담당자야할 거 같고, 교육을 받더라도 이해하고 제대로 사용하기에는 많은 시간이 걸릴 거 같다는 생각이 들었다.

일단 여기도 그렇지만 결국 EXCEL이다.
엑셀은 row수가 98만 건이 넘어가면 쉘을 쪼개던지 아니면 파일을 분리해야 한다.
보통은 피벗이나 엑셀함수로 원하는 정보를 만들텐데.. 데이터가 커지면 무한정 루프를 돌면서 업무지연이 발생한다는 것을 알게되었다.

최대한 요구사항에 맞게 데이터를 가공해서 전달하고 Excel에서 쓰는 함수를 최소화할 수 있게 전달해 주길 원했고
초기 포멧의 row seq 정렬에 맞게 추출해서 주기를 원했다.
결국 Excel이나 DB나 순서정렬이 문제였고, 거기에 맞게 가공하기 위한 작업을 하려면
DB도 대용량 데이터의 JOIN과 order by를 사용해야하고 filesort와 데이터 사이즈가 커지면 temparory memory를 쓸 수 밖에 없다.

다른 것보다 filesort를 쓴다는 것은 옵티마이져가 스캔한 것을 다시 한번 메모리에 올리고 재정렬을 해야하므로 부하가 많이 가는 작업이고, 보통은 DB에 부하를 주지 않게하기위해 out file로 처리해서 데이터를 재가공해서 EXCEL로 전달했다. order by 를 사용하지 않고 정렬이 가능한지 여부를 구글링 해보았는데 대부분 답은 정합성을 보장할 수 없다는 얘기였고, 정말 사용할 수 없는지 테스트 해보기로 했다.

1) MyISAM 테이블 엔진 테스트
일단 테이블 생성하고 DB툴을 이용하여 엑셀이나 CSV 파일을 DB에 import 한다.

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
3	500053900210
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

//50만건 정도 올려보았는데 순서대로 insert 되는 것을 확인했다.
//select 역시 순서대로 나온다.

delete from TMP_LOAD_20200619
where SEQ_NO = 3;
commit;
// 시퀀스 3을 삭제해 보았다.

select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
11	500053900228
...

insert into TMP_LOAD_20200619
VALUES (500001,'500053904383');
commit;
// 500001번째 시퀀스를 가진 데이터를 추가로 넣어보았다.

select * from TMP_LOAD_20200619;

1	500053900208
2	500053900209
500001	500053904383
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

MyISAM 엔진의 특성이다.
초기 데이터에서 삭제하면 select하면 보이진 않지만 빈공간은 사라지지 않고 존재하고 새로 입력한 데이터가 거기에 들어간다. 이거를 다시 생각해보면 MyISAM은 한번 입력한 데이터의 정렬은 유지되며 신규로 update나 delete가 일어나지 않는이상 ASC의 정렬은 유지된다고 판단할 수 있다.

다시말하면 초기데이터가 유지된다면 order by 정렬을 사용하지 않아도 된다는 얘기다.

실제로 TMP_LOAD_20200619 테이블을 드라이빙 테이블로 Left Join을 여러개 수행하고 where 조건절로 끊어서 데이터를 가공해서 최종 order by 정렬을 사용한 것과 사용하지 않은 것을 beyond compare로 비교해 보았고
50만건이 100% 일치함을 확인했다.

MyISAM을 사용시 초기데이터가 바뀌지 않는다면 order by 정렬을 빼도 될거 같다.

2) InnoDB 테이블엔진 테스트

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


1	500053900208
2	500053900209
3	500053900210
21	500053900211
22	500053900212
23	500053900213
7	500053900214
8	500053900215
9	500053900216
10	500053900217
..
// 원하는 순서가 아니다.. 뭔가 다르다.

InnoDB는 데이터 정렬하는 방법이 다르다.
테이블의 데이터를 PK값으로 정렬되어 저장하고 pk의 값의 정렬은

실제 데이터에서 보듯이 내가 생각하는 seq_no 순서대로의 순서가 아니고 clustered index로 저장하여 정렬한다.

#클러스터링 인덱스(clustered index)란?
여러개의 인덱스를 하나로 묶는다는 뜻으로 테이블의 PK에 대해서만 적용되는 내용이다.

즉 PK값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
중요한 것은 PK값에 의해 레코드의 저장 위치가 결정된다는 것이고, 또한 프라이머리 키값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

#결론

innodb의 PK정렬이 order by 비용을 줄여 빠르다고 하는데, 실제 테스트해 본바로는 select는 대용량 일수록 MyISAM의 순서정렬을 사용하지 않은 쿼리가 훨씬 빨랐다.

데이터 추출작업이 필요할 때 order by를 뺄수 있는 MyISAM 엔진을 적극활용하자.

끝~

#사용방법

alter table 테이블이름 modify 현재컬럼이름 int(20) NOT NULL;
alter table 테이블이름 change 현재컬럼이름 변경할컬럼이름 int(20) NOT NULL;

MODIFY는 컬럼 타입을 변경하기 위해 쓰고 CHANGE 는 컬럼이름을 바꾸기 위해 쓴다.
근데 CHANGE로도 컬럼 타입만을 바꾸기도 한다.(이름은 똑같이 쓰고..)

ex)
alter table 테이블이름 change 현재컬럼이름 현재컬럼이름 int(30) NOT NULL;

그래서 성능테스트 해봤는데 큰 차이는 없는 듯하다.
데이터 타입이 다를경우 어떤 차이가 발생하는지 테스트해 보았지만 별차이 없었다.

다만 테스트 중 성능이슈를 보려고 pk의 컬럼사이즈를 왔다갔다 테스트해봤는데
상용 운영DB에서는 int(1) -> int(10) 가능하지만 int(10) -> int(1) 은 곤란하다.

CREATE TABLE `TMP_LOAD_20200511` (
  `SEQ_NO` bigint(20) unsigned SEQ_NOT NULL,
  `DENT_NM` varchar(12) SEQ_NOT NULL COMMENT '가입번호',
  PRIMARY KEY (`SEQ_NO`),
  KEY `IDX_LOAD_20200511_01` (`DENT_NM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;
--> 10만건 데이터 insert 


ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL; 
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(40) unsigned NOT NULL;
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL;
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(1) unsigned NOT NULL;
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO varchar(10) NOT NULL; 
<-- 2.1초 bigint에서 varchar는 시간이 좀 걸린다.
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(1) unsigned NOT NULL; 
<-- 2.4초 varchar는 bigint 시간이 좀 걸린다.
ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL; 
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO varchar(10) NOT NULL;
<-- 2.1초 bigint에서 varchar는 시간이 좀 걸린다.


ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO varchar(1) NOT NULL;
Error occurred during SQL query execution
이유:
 SQL Error [1265] [01000]: Data truncated for column 'SEQ_NO' at row 2
 

 [admin@db ~]$ perror 1265
MySQL error code 1265 (WARN_DATA_TRUNCATED): Data truncated for column '%s' at row %lu
//에러한번 내주고 테스트 끝~

#결론

두개 성능차이 없고 컬럼 타입변경에는 MODIFY든 CHANGE 아무거나 쓰면 된다.

#PK가 다른 테이블의 FK일 떄 컬럼 변경 테스트(MODIFY, CHANGE 둘다 동일함... 그냥 착각이었음)

ex) TABLE A (pk)-> TABLE B (A pk를 FK로 갖음) 일때!

TABLE A의 PK의 컬럼타입 사이즈를 늘리는 것은 가능하지만 줄이면 아래와 같은 에러가 난다.

//TESTDB.TABLE_A 에 PK가 VARCHAR(20) 이고 TESTDB.TABLE_B 에서 A의 PK를 FK로 걸어놓았을 때!
ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(30) NOT NULL COMMENT '대분류코드';
--> pk 컬럼 사이즈 늘리기 가능

ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(10) NOT NULL COMMENT '대분류코드';
--> 에러
Error occurred during SQL query execution
이유:
 SQL Error [1833] [HY000]: Cannot change column 'TEST_CD': used in a foreign key constraint 'TABLE_B_ibfk_1' of table 'TESTDB.TABLE_B'
 --> TABLE_B의 FK는 아직 VARCHAR(20)이라서 발생
 
 //해결방법
 //체크 포인트를 끄고 반영후 -> 다시 켬
 set FOREIGN_KEY_CHECKS = 0;
 ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(10) NOT NULL COMMENT '대분류코드';
 --> 가능
 set FOREIGN_KEY_CHECKS = 1;
 
 
 
 

반영중 뜻밖의 에러를 만났다.

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

//스키마 생성안되고 에러남
SQL Error [1067] [42000]: Invalid default value for 'VALUE10'

mysql 5.7에서 개발하던 테이블 스키마를 mysql 5.5에 넣던 중에 발생햇는데..

음 왜 5.7을 5.5에 넣어야만 했는지는 사정이있고.. 여하튼 이게 안되네;

하위버전으로 마이그레이션 한 경우는 별로 없어서 케이스를 찾기 힘들었고

버전 올려라, 걍 datetime 써라 라는 얘기 밖에..

그러던 중에 이런 얘기를 찾을 수 있었다.

--------------------------------------------------------------------------------------

datetime 컬럼에 기본값이 now() 적용은 안되고 current_timestamp 를 사용해야함
mysql 5.5 버전 이하에서는 한 테이블당 1개의 컬럼만 current_timestamp를 사용할 수 있다.

mysql 5.6부터 이 제한이 해제되었다.

---------------------------------------------------------------------------------------

설마라고 생각하고 테스트를 진행했다.

일단 datetime은 timestamp로 바꾸고 not null 보다는 default로 넣으라는 얘기가 많아서 그대로 수정함

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 timestamp DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

SQL Error [1293] [HY000]: Incorrect table definition; 
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
// only one...설마

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 timestamp DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 datetime NOT NULL,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

// 된다..헐;;

진짜 mysql 5.5는 CURRENT_TIMESTAMP 는 컬럼에 하나만 지원한다. ㅎㅎ

하위버전으로 마이그레이션하는 것은 어렵다는 것을 한번 더 확인했다.

끝~

이번에 stored function 관련 full scan 튜닝이 끝나면 innodb status를 보고 좀더 개선포인트가 있는지 본격적으로 my.cnf 메모리 튜닝을 검토해볼 생각이다.

튜닝 가이드 중에서 잘 정리된 내용(SK Cloud Z Support Portal > Cloud Z DB >이승철님)이 있어 정리해놓는다.

Mariadb의 메모리는 크게 두 가지로 분류가 됩니다.

-> 글로벌 메모리영역은 공유하고 세션메모리영역(connection당)은 공유하지 않는다.

이것에 대한 하나 예를 들면 오라클과 postgres를 mysql로 마이그레이션을 진행한다면 다른 것보다 function과 procedure의 사용이 많은지 확인해봐야하고 어느부분에서는 Dynamic sql로 변경을 해야할 수도 있다.

오라클, postgres는 function과 procedure를 글로벌메모리영역에서 사용하지만 mysql은 일단 성능도 그들의 1/2배이며, global 메모리 영역에서 쓰지 않고 session 메모리 영역에서만 사용하기 때문에 성능이슈가 발생할 가능성이 더 크다. 인덱스스캔이 제대로 된다면 별문제 없지만.. 이후 function이나 procedure가 바뀌고 기존의 실행계획이 틀어진다면 바로 풀스캔 및 디스크I/O로 이어져 성능이슈가 발생할 가능성이 있다......mysql 잇점이 있다면 경험상 postgres는 procedure가 뭔짓을 하는지 모르다가 out of memory 떨구고 DB가 죽었지만, mysql은 session(connection) 메모리가 full이어서 disk로 넘어가게 되면 이미 성능이슈로 서버의 응답시간이 지연되는 것을 이미 다른 SM이 알기 때문에 DB 장애로 넘어가기 전에 미리 확인이 가능했다.

1. Global 메모리 영역
: DB가 최초 기동되었을 때에는 메모리를 최소한만 사용하다가 설정된 값 까지 증가하며 증가한 이후에는 "메모리를 반환하지 않고" 설정 된 값 이내에서 계속 사용됩니다.
(오라클의 경우 DB기동시 설정된 값 만큼 메모리를 할당 받고 올라가는 반면 Mariadb 는 기동시 설정된 메모리 값만큼 할당 받는것이 아닌 설정된 값 만큼 서서히 증가하게 됩니다)
•Innodb_buffer_pool_size
•Key_buffer_size
•innodb_log_buffer_size
•tmp_table_size
→ Global 메모리 = Innodb_buffer_pool_size + Key_buffer_size + innodb_log_buffer_size + tmp_table_size

--> 대용량 테이블을 union all을 사용하는 sql호출이 많아지면서 성능이슈가 있었고 tmp_table_size을 사이즈를 512M -> 2G로 늘려서 DB 성능을 개선한 적이 있다. 해당 옵션을 튜닝하려면 max_heap_table_size도 같이 늘려줘야한다.

임시테이블 튜닝 가이드참조: https://sungwookkang.com/m/1229

2. Session 메모리 영역
Mariadb session 별로 사용되어지는 메모리 공간으로 Max connection 설정 값과 관련이 있습니다.
(커넥션을 맺고만 있을 경우에는 커넥션에 필요한 최소한의 메모리만 사용되어지며 조인, 정렬 등이 필요할 경우에는 아래 설정된 값만큼을 메모리에서 사용하게 됩니다.
때문에 정확한 예측값은 동시에 Active한 세션 수 이지만 기본적으로는 Max connection 수 만큼 동시 사용 세션이 수행될 수 있기에 아래 공식으로 계산되어 집니다.)
•sort_buffer_size
•read_buffer_size
•read_rnd_buffer_size
•join_buffer_size
•thread_stack
•binlog_cache_size
•Max connection
→ Session 메모리 = (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size) x Max connection

*예시
만약 Mariadb의 설정값이 다음과 같다면...
Global 메모리
•Innodb buffer pool size: 2048 MB
+ Key buffer size: 16MB
+ innodb log buffer: 10MB
+ tmp table size: 64MB
= 약 2.1 GB

Session 메모리
•( sort_buffer_size: 2MB
+ read_buffer_size: 1MB
+ read_rnd_buffer_size: 1MB
+ join_buffer_size: 0.128 MB
+ thread_stack: 0.128 MB
+ binlog_cache_size: 1MB)
x Max connection 300
= 약 1.6 GB
이 됩니다.

따라서 global 메모리 영역 (2.1GB) + Session 메모리 영역(1.6GB) 에 추가로 Mariadb 기본 기동(350MB) + performance_schema data(150MB) + OS / 파일 버퍼링 공간(전체 메모리의 약 10%) 을 고려하여
위 설정된 값을 기준으로 전체 메모리의 적정 수치는 4,5GB ~ 5GB 정도가 적당해 보입니다.
위의 설명 및 예시를 참고하시어 MariaDB의 메모리를 설정해주시면 됩니다.

참조사이트:

support.cloudz.co.kr/support/solutions/articles/42000064656--cloud-z-db-mariadb의-메모리-설정을-어떻게-해야-하나요-

How number of columns affects performance?

-> 팀이동후 DBA로 업무분석하다 처음으로 정리한 내용이었다. 당시 mysql 5.5에 MyISAM이라 시도때도 없이 Lock이 잡히고 새벽에 장애나고 ; 가장 큰 문제는 납작두꺼비처럼 옆으로 퍼져버린 테이블이 문제였는데.. 테이블 정규화를 해야하는데 소스수정범위가 너무 크다보니 개발쪽에 일정잡기가 어려워 결론적으로 현재는 옆으로 더 퍼져있고;;.. 인덱스만 추가하고 function/procedure에서 full scan 안타게 조심해서 쓰고 있다.

한번 선언한 테이블의 정규화는 많은 시간과 비용이 든다는 것을 다시 한번 느끼게 된다.

1) innoDB engine에서 테이블에 최대생성 가능한 컬럼과 인덱스

- 컬럼 1017개

- 인덱스 64개

-> 적정 컬럼 수는 40개 내외라고 한다.

2) 컬럼을 늘릴 때 발생할 수 있는 이슈

- 테이블에 인덱스 검색을 한다고 하더라도 DB 내부 Logic은 인덱스조건에 해당하는 row의 전체를 읽기 때문에 한번에 읽을 데이터의 값이 커지고 io에 부담을 준다.

DB컬럼은 여러 개의 Linked List로 구성되어있고 인덱스를 사용하더라도 해당열의 전체 값을 가져오기 때문에 컬럼이 늘어날수록 데이터 조회 비용은 늘어날 수밖에 없다.

#아래는 (프로게이밍연구실)에서 발췌한 내용임

DB에서 연결리스트를 가장 많이 사용하는 부분이 바로 테이블

간단하게 예를 들어 만든 테이블 구조입니다. 위 구조라면 한 행의 크기는 68 BYTE의 작은 테이블이지요.

온라인 게임에서 사용되는 테이블의 크기는 보통 이렇게 작은 데이터들로 이루어지기 때문에 별 문제가 발생하지 않습니다

이렇게 쿼리를 날린다면 가져오는 데이터는 총 62 BYTE의 데이터를 가져옵니다.

하지만 데이터베이스 엔진의 내부 알고리즘의 움직임에 조금 관심을 가져본다면 ‘어떤 과정을 통해 데이터를 가져오는 것일까?’라는 생각을 해보신다면 재미있는 것을 알 수 있습니다.

바로 ‘연결된 리스트’라는 것이 그 재미의 핵심입니다.

아무리 인덱스를 사용하여 데이터를 빠르게 가져온다고 해도 연결된 리스트라는 녀석은 내부적으로 한 행의 모든 데이터내용을 싹 읽어버립니다. 게임과 같이 작은 데이터를 다룬다면 큰 문제가 없겠지만 아래처럼 게시판일 경우에는? 얘기가 완전 달라지는 것이죠.

위에 보이는 것처럼 큰 데이터 열이 있다면 연결된 리스트로 데이터를 읽을 때에 어마어마한 비용이 발생하겠지요?

그런데 위에서 보이는 것과 같이 큰 크기의 열이 있으면 그만큼 많은 페이지를 읽어야 하고 어마어마한 데이터를 ‘사용하지 않더라도 읽어야 하는 것’이죠.

인덱스를 사용하면 빠르게 데이터를 조회할 수 있지만 만약 게시판에 정말 많은 량의 내용을 담은 게시물이 있을 경우 성능을 보장할 수 없습니다.

그렇다면 ‘어떻게 하면 이런 재앙을 막을 수 있을까?’에 대한 연구해봅시다.

개인적으로 저는 저렇게 큰 데이터 열을 별도의 테이블로 분리시켜야 한다고 생각합니다.

‘게시물을 가져올 때에 JOIN을 하게 되니 성능이 더 나빠지지 않느냐!?’ 라고 반박하시는 분께서 계실지 모르겠지만, 인덱스를 사용해서 정확하게 SEEK한 데이터는 무조건 0초 만에 수행됩니다.

게시물 본문과 글 번호를 별도의 테이블로 분리한 후 본문을 조회할 때에만 조인하여 가져오는 방식이 제가 생각할 수 있는 최고의 성능을 보장할 수 있는 최선의 방법입니다.

혹시라도 이런 고민을 해보신 적 있으시다면 같이 연구해보면 정말 좋을 것 같습니다.

3) 성능 테스트 (ex 컬럼count 100개)

- 결과 char, int 컬럼의 경우 100개까지 늘려도 성능에 큰 무리가 없지만

varchar 컬럼의 경우 io가 slowdown되어 성능에 큰 영향을 줌

참조사이트:

https://www.percona.com/blog/2009/09/28/how-number-of-columns-affects-performance/

1. 목적

상용서비스에 슬로우 쿼리 이슈가 있어 확인해보니 select ~ from 안에 function을 사용했는데

이게 문제가 되어 응답시간이 느려지고 서비스 지연이 발생하였다.

근데 이게 원래는 슬로우 쿼리가 아니었는데 왜 갑자기 튀어나왔는지 보다보니

지난주에 대용량 테이블에 인덱스 추가 3개를 하고 나서 사이드 이펙트로 튀어나왔다. ㅠ

인덱스 추가하고 나서 아무래도 fucntion의 로컬 메모리(connection 당) 사용이 어느부분에서 MAX를 넘어 디스크를 사용하게 되었고 이게 Disk I/O를 치는 이유로 보인다.

--> mysql은 오라클과 달리 function이나 procedure가 global memory 영역을 사용하지 못하고 connection당 local 메모리가 my.cnf에 정의되어 있고 그 이상 사용할 경우 Disk로 넘어가 스토리지 엔진의 핸들러가 바뻐지게 되고 이게 Disk I/O를 발생시킨다.

슬로우쿼리 설정값은 2초이고 인덱스 3개 추가 후 현재는 14초 이상이 소요되고 있다.

대용량 참조하는 fucntion의 로직을 개선하려고 보니 쓰는 곳이 너무 많아서 수정하게 되면 테스트할 곳이 많다고

개발, 기획파트에서 얘기가 많다.ㅠ

고민중 fucntion에 캐시를 쓸 수 있는 DETERMINISTIC 옵션이 있는 것을 확인하고 테스트를 진행하였다.

mysql function에 DETERMINISTIC, NOT DETERMINISTIC 이라는 설정이 있다.

#장비사항

DB version : mysql 5.7.19

MEM : 16G ( innodb buffer pool :16G =테스트 용도로 Max로 잡음)

2.옵션 정의

characteristic
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
COMMENT 'string'

1) DETERMINISTIC
- 입력값이 같은면 결과는 늘 같다.

- 쿼리가 실행되면 그때 한번 호출하고 그 쿼리 안에서 상수값을 돌려줌
다시 쿼리가 호출되면 다시 실행됨

2) NOT DETERMINISTIC
- 입력값이 같아도 시간에 따라 결과는 다르다.

- 디펄트가 늘 변함 임으로 속도가 느려질수 있다 꼭 구분해서 사용하자

--> 따로 설정하지 않으면 NOT DETERDETERMINISTIC 이다

3. 추가 설명

DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해
항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면,
디폴트는 NOT DETERMINISTIC이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는
DETERMINISTIC를 확실하게 지정해 주어야한다.

NOW() 함수(또는 동일 기능함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게
만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은
올바르게 포함한다. 또한 RAND()도 함수내에서 일단 한번 호출되어 지면 정확하게 복사본을 만들게 된다.
(함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는
동일한 암시적 입력(implicit input)으로 간주할 수 있다.)

--> function 안에 now() 있는데 넣다 뺐다 둘다 테스트 해봐야할 거 같다.

contains sql, no sql, reads sql data, modifies sql data
특성은 함수가 데이터를 읽거나 또는 스는 정보를 제공한다. NO SQL 또는 Read sql data는 함수가 데이터를
변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이
되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야한다

--> 이렇게 얘기하긴 하는데 찾아보니 별로 중요하지 않다는 얘기가 더 많다.

1) CONTAINS SQL : 데이터를 읽거나 쓰는 명령문을 갖지 않음.
2) NO SQL : 아무런 SQL명령문이 없음.
3) READS SQL DATA : 데이터를 읽는 명령문.
4) MODIFIES SQL DATA : 데이터를 쓰는 있는 명령문
5) CONTAINS SQL : DEFAULT

6) SQL SECURITY : 사용자가 생성/수정 권한인지, 호출 권한인지 명시 

4. 테스트

4-1) 쿼리 캐시 OFF 일떄

DEFAULT 설정

-> 수행시간 13초

DETERMINISTIC  설정
-> 수행시간 13초 (똑같네;;)

4-1) 쿼리 캐시 ON 일떄

mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

#my.cnf 설정하고 재시작한다.
mysql> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

#쿼리캐시 설정 옵션설명

1) Query_Cache_Type = ON
– 예외사항을 제외한 모든 SQL결과를 메모리에 캐싱하겠다.
2) Query_Cache_Type = OFF
– Query Cache를 사용하지 않겠다.
3) Query_Cache_Type = DEMAND

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (더 느리다;;)

캐시를 쓰는지 확인해본다.

mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 18       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

--> Qcache_hits  0 캐시 안타네;; now() 때문에 그런가 now() 빼고 테스트해본다.

4-1) 쿼리 캐시 ON + function안에 now() 제거

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (똑같다;;)

mysql> show global status like 'qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 54       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

--> 역시나 Qcache_hits 0 캐시 안쓴다.

음 mysql 매뉴얼을 좀 더보니 쿼리캐시를 사용하지 못하는 경우라는 게 있다.;

[쿼리 캐시를 사용하지 못하는 경우]

1) 임시 테이블에 대한 쿼리
2) 사용자 변수의 사용(프리페어 스테이트먼트와 동일하게 작용)
3) 칼럼 기반의 권한 설정
4) LOCK IN SHARE MODE 힌트
5) FOR UPDATE 힌트
6) UDF(User Define Function)사용
7) 독립적인 SELECT문장이 아닌 일부분의 서브 쿼리
8) 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리 --> 아....
9) SQL_NO_CACHE 힌트

5.결론

쿼리캐시 사용하고 function 에 DETERMINISTIC 을 설정하더라도
스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리는 캐시를 쓰지 않는다.
--> function 튜닝은 결국 한 세션에 로컬 메모리를 적게 사용(table cache를 줄임) 옵티마이져 cost를 개선하는 방법 말곤 없는 거 같다.

function 로직 개선을 위해 개발, 기획파트랑 다시 얘기좀 해봐야겠다.

쿼리 튜닝하고 장애 대처하다보니 mysql dba로 몇 년이 지났다.

궁금할 때나 장애로 날을 샐 때마다 내 스승은 구글링이며 몇가지 책등 일뿐..

어느 순간부터 기본부터 다시 공부하고자 하는생각이 들었다. 일단 쿼리 수행 구조부터 다시 시작한다.

real mysql 에 나온 것을 바탕으로 구글링해서 괜찮은 이미지를 차용해서 정리해 보았다.

장애때 중요한 부분은 옵티마이져 - 실행계획, 쿼리실행엔진 - 핸들러 (Disk I/O와 관련) 으로 보인다.

그래도 정리를 한번 해놓고 나니 깔끔하긴 하다. 

이제 메모리 사용과 접목해본다.

참조 : https://rrhh234cm.tistory.com/140

하루 단위 풀백업이 1.4T가 넘어가면서 xtrabackup에 압축이슈가 발생하였다.
xtrabackup은 2시간내외로 끝나는데 tar 압축은 10시간이 넘어가다보니 좀더 나은 방법을 검토 중에
좋은 참조 문서를 발견하였다.

일단 증분백업을 검토해봤지만.. 아무래도 관리하기 귀찮다. 어쩔 수 없이 증분백업데이터를 한번은 merge 해야하고, 관리이슈가 크기 때문에 풀백업을 유지하고
압축시간을 줄일 수 있는 방법을 찾던 중에 pigz라는 것을 찾게 되었다.

오라클의 parallel 힌트같이 압축중에 데이터 용량이 큰 파일을 만났을 시 process를 늘려서 한번에 처리하는 똑똑한 압축툴이다. tar과 같이 쓸 수 있어 기존로직을 많이 변경하지 않아도 되서 테스트를 진행하였다.

###parallel 압축 gz 방식 검토 ###
#서버에 pigz가 설치되어있는지 확인

rpm -qa | grep pigz

#pigz-2.3.4-1.el6.x86_64.rpm 설치

[root@ temp]# rpm -Uvh pigz-2.3.4-1.el6.x86_64.rpm
경고: pigz-2.3.4-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
준비 중...               ########################################### [100%]
   1:pigz                   ########################################### [100%]

#1) 기존 압축방식과 비교
1-1) tar.gz
tar -cvzf test.tar.gz backup
#시작 17:10분

#종료 17:52분

--> 42분

1-2) tar pigz
tar 명령어에 외부 압축프로그램 바로 지정하는 방식을 사용한다면,
tar -I pigz -cvf blah.tar.gz blah blah 압축하기
tar -I pigz -xvf blah blah blah 압축풀기

#바로테스트진행
tar -I pigz -cvf test2.tar.gz backup

#top 확인

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 

5454 root 20 0 1813m 17m 584 S 1247.8 0.1 12:29.46 pigz
5453 root 20 0 113m 1272 1044 D 18.8 0.0 0:12.01 tar
358 root 20 0 0 0 0 S 2.3 0.0 6:35.84 kswapd1
357 root 20 0 0 0 0 S 1.7 0.0 11:00.23 kswapd0
5490 webadmin 20 0 15424 1660 912 R 0.7 0.0 0:00.44 top
--> cpu 1247%...ㄷㄷㄷ 상용서비스에서 쓸 수 있을까;

#시작 17:54분 

#종료 18:04분

--> 10분

#1-3) Process 설정

-p, --processes n Allow up to n compression threads (default is the
number of online processors, or 8 if unknown)

-->cpu의 Core 수를 조절할 수 있게 설정된다. 정확하게는 하이퍼스레딩 개수이다.

예를들어 물리 core가 8 core라면 top으로보면 1600% 까지 쓸 수 있다고 보면된다.

하지만 실제로 확인해보면 오버클럭되서 max는 1880% 정도까지 나온다.

Compress a directory using tar:
tar cf - path/to/directory | pigz > filename.tar.gz

#1-4) pigz 압축률 테스트
tar -cvf - backup | pigz -9 -p 4 > test2.tar.gz

#top 확인

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
591 root 20 0 322m 4152 592 S 404.9 0.0 29:37.29 pigz
590 root 20 0 113m 1284 1052 S 3.3 0.0 0:10.82 tar
357 root 20 0 0 0 0 S 0.7 0.0 11:18.11 kswapd0
229 root 20 0 0 0 0 S 0.3 0.0 20:33.63 kblockd/3
-->압축률 (9: 성능이 좋은것, 설정은 12까지 있는데 이건 잘 사용 안하는 거 같다)
압축해서 데이터 사이즈는 줄어들었지만 너무느리다.

# 압축률 default 로 테스트
tar -cvf - backup | pigz -6 -p 6 > test2.tar.gz

--> 압축률 default , process 6
--> 수행시간 10분

데이터 사이즈는 tar.gz로 압축할 떄와 비슷하지만 수행시간을 1/4로 줄였다.
core도 서버와 DB가 사용할 부분을 남겨놓고 설정했고
가장 원하는 방식으로 테스트를 마쳤다.

#반영

개발DB의 실제 반영은 xtrabackup에서 지원하는 --stream =  tar을 사용하여 굳이 백업 디렉토리를 만들지 않고

압축파일로만 관리하도록 반영하였다.

-> 이후 10.4로 DB upgrade를 하면서 한번 더 확인했는데

mariabackup에서는 --stream = tar은  지원하지않는다. 대신 xbstream 이라는 압축포멧을 default로 지원한다.

참조 : mariadb.com/kb/en/mariabackup-options/#-stream

/usr/bin/innobackupex \
--host=127.0.0.1 \
--user=root \
--password='비번' \
--ibbackup=xtrabackup \
--no-lock \
--stream=tar ./ | pigz -p 6 > backup.tar.gz

상용DB는 위와 같은 방식이 아니라 장애시 바로 점검이 가능하도록 백업디렉토리와 압축파일을 분리하여 관리하고 있다.

실제 상용에 적용할 때는 --no-lock 옵션을 사용하기 때문에 바로 압축하는 방식을 사용하지 못하였다. apply log를 합친 다음에 압축을 하여야 하기 때문이다.

1) xtrabackup -> 2) applay_log merge -> 3) pigz 압축 -> 4) transfer

# pigz 적용스크립트

tar -cf - 압축디렉토리 | pigz -p 6 > 압축파일이름.tar.gz
// -cvf로 하면 압축파일을 볼수있는데 압축파일이 TB 단위다 보니 이거 넣으면 확실히 더 느리다 걍 뺀다.

#pigz Man page
pigz --version (return code: 0)
pigz 2.4

tar --use-compress-program="pigz -p 4" -cvf mvno-test.tar.gz backup

pigz --help (return code: 0)
Usage: pigz [options] [files ...]
will compress files in place, adding the suffix '.gz'. If no files are
specified, stdin will be compressed to stdout. pigz does what gzip does,
but spreads the work over multiple processors and cores when compressing.

Options:
-0 to -9, -11 Compression level (level 11, zopfli, is much slower)
--fast, --best Compression levels 1 and 9 respectively
-b, --blocksize mmm Set compression block size to mmmK (default 128K)
-c, --stdout Write all processed output to stdout (won't delete)
-d, --decompress Decompress the compressed input
-f, --force Force overwrite, compress .gz, links, and to terminal
-F --first Do iterations first, before block split for -11
-h, --help Display a help screen and quit
-i, --independent Compress blocks independently for damage recovery
-I, --iterations n Number of iterations for -11 optimization
-J, --maxsplits n Maximum number of split blocks for -11
-k, --keep Do not delete original file after processing
-K, --zip Compress to PKWare zip (.zip) single entry format
-l, --list List the contents of the compressed input
-L, --license Display the pigz license and quit
-m, --no-time Do not store or restore mod time
-M, --time Store or restore mod time
-n, --no-name Do not store or restore file name or mod time
-N, --name Store or restore file name and mod time
-O --oneblock Do not split into smaller blocks for -11
-p, --processes n Allow up to n compression threads (default is the
number of online processors, or 8 if unknown)
-q, --quiet Print no messages, even on error
-r, --recursive Process the contents of all subdirectories
-R, --rsyncable Input-determined block locations for rsync
-S, --suffix .sss Use suffix .sss instead of .gz (for compression)
-t, --test Test the integrity of the compressed input
-v, --verbose Provide more verbose output
-V --version Show the version of pigz
-Y --synchronous Force output file write to permanent storage
-z, --zlib Compress to zlib (.zz) instead of gzip format