'2020/07'에 해당되는 글 2건

#mysql DB에서 swap 메모리를 쓴다는 것은?

innodb buffer pool에 할당된 메모리를 다쓰고 메모리가 부족할 때 일시적으로 swap 메모리(disk)를 사용한다.

#리눅스에서 swap 메모리설정

vi /etc/sysctl.conf
---------------------------------------------------
## swap setting
vm.swappiness=0

vm.swappiness=0 으로 하면 스왑메모리를 사용하지 않겠다는 것이지만 실제로 0으로 설정해도 mysql DB는 최소한의 스왑메모리를 사용한다. 메모리가 부족하면 DB가 죽을 수 있으므로 swappiness에 0으로 할당되어있다 하더라도 DB OS 자체를 보호하기위해 최소한의 swap 메모리를 사용하는 것으로 보인다.

#Swap 메모리 초기화 방법

리눅스에서 swap 메모리를 초기화하는 것은 아주 간단하다.

일시적 메모리 사용의 증가로 swap을 사용하게될 경우 메모리에 여유가 생겨도 swap 메모리는 자동으로 초기화되지 않는다.  이것을 수동으로 초기화하려면 아래의 명령을 입력하면 된다. 당연히 root 권한에서 실행해야 한다.
swapoff -a && swapon -a

swapoff 처리에 시간이 조금 오래 걸릴 수 있는데 서버가 멈춘 것은 아니므로 걱정하지 않아도 된다.

swap 메모리에서 필요한 부분을 물리 메모리로 옮기는 처리중인 것이다.

#실제 초기화 작업

개발DB는 32G로 되어있고 mysql 인스턴스가 2개 떠있다.
개발장비가 부족하여 어쩔수 없이 멀티 인스턴스로 띄워놓았고, 각각 innodb_buffer_pool 은 16G 할당해놓았다.
당연히 50~80%가 MAX인데 그 이상(100%)을 설정해놓았으니 스왑메모리를 사용하였고 요즘 들어 너무 느려서 스왑메모리 사용을 초기화하고 관련 내용을 정리한다.

[root@admin]# free -m
             total       used       free     shared    buffers     cached
Mem:         32079      28264       3815          1        392       6806
-/+ buffers/cache:      21065      11014
Swap:        15999       5048      10951
//스왑메모리를 5G정도 사용하고 있다.

[root@admin]# swapoff -a && swapon -a
swapoff: /dev/sda3: swapoff failed: 메모리를 할당할 수 없습니다

DB가 메모리를 쓰고 있어서 남는 메모리가 없어 메모리 할당을 할수 없다는 메시지다.
DB 인스턴스 하나를 죽이고 버퍼캐시를 초기화해서 메모리를 비운다.

service mysqld_multi stop 1 --password=xxxxxxxx
echo 3 > /proc/sys/vm/drop_caches

[root@admin]# swapoff -a && swapon -a
//오 된다~

#top 확인

top - 18:30:39 up 564 days,  2:46,  2 users,  load average: 2.28, 1.89, 1.48
Tasks: 618 total,   2 running, 616 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  4.1%sy,  0.0%ni, 95.8%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32849832k total, 21902092k used, 10947740k free,     8956k buffers
Swap:  4177092k total,  4177092k used,        0k free,    78480k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                     
30537 root      20   0  102m  628  544 D 91.6  0.0   5:12.30 swapoff                                                                                                      
 5525 root      20   0     0    0    0 S  1.0  0.0  80:24.23 kondemand/3                                                                                                  
 5528 root      20   0     0    0    0 S  1.0  0.0 195:01.54 kondemand/6                                                                                                  
   15 root      RT   0     0    0    0 S  0.7  0.0   0:20.01 migration/3                                                                                                  
 5540 root      20   0     0    0    0 S  0.7  0.0 195:24.81 kondemand/18
//음 뭔가 작업을 하고 있다. 프로세스는 100%까지 쓰는 것으로 보아 cpu는 1core를 사용하는 것을 알 수 있다.
//나머지 DB인스턴스에 영향을 미칠정도는 아닌 것을 알 수 있다.

#메모리 확인

...
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      22070      10009          1         12        117
-/+ buffers/cache:      21940      10139
Swap:         3030       3030          0
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      22089       9990          1         12        117
-/+ buffers/cache:      21959      10120
Swap:         3000       3000          0
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      22091       9987          1         12        117
-/+ buffers/cache:      21962      10117
Swap:         2998       2998          0
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      22093       9985          1         12        116
-/+ buffers/cache:      21964      10115
Swap:         2997       2997          0
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      22228       9851          1         13        124
-/+ buffers/cache:      22090       9989
Swap:         2803       2803          0

// 5047M에서 swap메모리 사용을 계속 줄여서 0에 수렴하게 만든 후 다시 스왑메모리를 사용하도록 할당하는 작업을 하고 있다.
//근데 이게 생각보다 많이 느리다. SATA2에 5G정도사용했는데.. 30분이상 걸리고 있다.

...
..
.
[root@admin]$ free -m
             total       used       free     shared    buffers     cached
Mem:         32079      28125       3953          1        165       3658
-/+ buffers/cache:      24301       7778
Swap:        15999          0      15999

//완료된 후에 위처럼 스왑메모리가 초기화 되었고 free가 15999(16G)로 초기화 되어 있는 것을 알 수 있다.

다시 DB 인스턴스를 띄운다.

service mysqld_multi start 1

기분탓인가? 툴에서 쿼리 반응속도가 아까보단 좀 낫다.

장비하나 사주면 좋을텐데.. innodb 버퍼풀을 줄이고 싶지만 조인 20개이상 걸린 해비 쿼리가 많아서 줄이면
성능테스트를 할 수가 없다..;
당분간 이걸로 버텨야할 듯하다.
끝~

테이블의 데이터가 쌓이고 컬럼이 많거나 파티션 테이블의 경우는 한번 생성한 스키마에 인덱스를 걸거나 컬럼을 추가할 이슈가 생기면 상당히 까다로운 작업을 해야한다. 

--> 컬럼이 많은 것과 파티션 테이블 중 어느게 더 반영이슈가 있나 테스트 해본적이 있는데 컬럼 30개, 1억건 rows 파티션 테이블보다 컬럼 200개짜리 1000만 rows 테이블의 online DDL이 반영이 더 오래 걸린다.

간단한 DDL문이라고 생각하면 대규모 장애를 만날 수도 있다.
일단 수행시간이 오래걸리고 alter table ALGORITHM=INPLACE, LOCK=NONE; 을 쓸 수없기 때문에
지금은 모르겠지만 암튼 default인 mysql 5.7.19까지는 ALGORITHM=COPY로만 수행이 가능한 것이라.. 작업이 쉽지 않았다. 거기다 이 테이블은 트리거도 걸려있다...;;; 머리가 아프다.

상용서비스 반영 전에 적용방식을 테스트해본다.

1) Online DDL

mysql> alter table TESTDB.TB_xxxx
    -> add COLUMN xxxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'xxxxxx',
    -> add COLUMN xxx_xxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'zzzzzz'
    -> ;
Query OK, 0 rows affected (31 min 31.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

Alter table add column 테스트 해보았는데 30분이상 걸렸다.

이대로 online 반영은 어려울 거 같다.

2) TABLE 교체

2-1) 컬럼추가할 테이블A와 동일한 테이블B를 하나 더 만든다 스키마 생성할 때 컬럼을 추가해서 만드는 것이다.
2-2) 이전 테이블A의 데이터를 테이블B에 insert 한 후 테이블을 교체한다.

use TESTDB;
Create table TB_xxxx_TMP
(
...
xxxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'xxxxxx',
xxx_xxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'zzzzzz',
  PRIMARY KEY (`xxx_NO`,`xxx_SEQ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xxxx이력'
;

INSERT INTO TESTDB.TB_xxxx_TMP
(xxxx_NO, xxx_xxx_NO 컬럼 2개를 제외한 이전 테이블A의 컬럼을 순서대로...)
select * from TESTDB.TB_xxxx;
commit;

Query OK, 8833879 rows affected (14 min 45.61 sec)
Records: 8833879  Duplicates: 0  Warnings: 0
// 테스트기는 약900만 상용서비스 DB는 1200만 정도 있음

rename table TB_xxxx_TMP to TB_xxxx_TMP2, TB_xxxx_TMP to TB_xxxx, TB_xxxx_TMP2 to TB_xxxx_TMP;
//테이블교체는 A->C, B->A, C->B 로 진행한다. 바로 교체보다 안전한 방식임

그래도 10분이상 걸린다...
테스트 해본바로는 insert into select ~ 는 select .. into values ~과는 달리 meta Lock이 걸리지 않았고
작업중에도 TB_xxxx에 insert와 select가 가능했다.
하지만 트랜젝션 격리수준이 default인 repeatable-read라서 안정성 측면에서 트리거를 끊고 작업하는 게 속편할 거 같다.  트리거에 덴적이 있어서 더 조심스러울 수도 있지만;;
사업이랑 협의해서 insert는 반영후 hist에 업데이트하고 update는 무시하기로 협의하였다.
고객트래픽이 몰리는 시간에 반영하긴 어렵고 9시 전에 작업하기로 하였다.

#최종작업계획 

2-1) insert update 트리거 중단
2-2) 컬럼추가할 테이블A와 동일한 테이블B를 하나 더 만든다 스키마 생성할 때 컬럼을 추가해서 만드는 것이다.
2-3) 이전 테이블A의 데이터를 테이블B에 insert 한 후 테이블을 교체한다.
2-4) insert update 트리거 재생성
2-5) TB_xxxx 가 트리거를 건 테이블의 pk 비교하여 추가된 부분을 insert함

#실제 반영리뷰
테스트기에서 작업은 14분정도 소요되었지만 실제 상용반영은 5분 29초였고
차이는 리눅스 OS와 mysql 버전은 5.7.19로 같은데, DISK가 테스트기는 SATA2이고 상용기는 SSD이다.
SSD가 3배정도 빠른 것을 알 수 있다..ㅎㅎ

2-5) pk 비교는 Left outer join 으로 하여 TB_xxxx가 null 인 것을 찾는 방식으로 작업하였고
수행시간은 30초정도 걸렸다 1000만건이 넘더라도 Pk의 b-tree 인덱스 성능은 강력하다는 것을 알 수 있다.
작업 끝~

#Thereafter

mysql 8.0.12 버전부터(MairDB 10.3.7 부터) Game changer가 등장했다.

상용버전을 mysql 8(=5.8) 로 올려야할 강력한 이유가 생겼다.

그것은 algorithm = instant 인데 Online DDL 을 지원한다.

DBA에게 축복과도 같은 기능으로 추후 Data dictionary 좀 더 공부하고 정리해서 올릴 예정..