데이터베이스를 지탱하는 기술2(7장~9장)

스토리지 기술의 변천과 데이터베이스에 끼치는 영향

하드웨어 성능 개선의 역사

랜덤 엑세스 : 인덱스와 실제 데이터는 물리적으로 인접해 있지 않기 때문에 각각 별도로 액세스할 필요가 있다.(두 번)

액세스하려는 그 데이터가 메모리에 있는가, 아니면 디스크에 있는가에 따라 속도 차이가 크다.

 

HDD로의 액세스를 하게되면 엄청 느려

예전에는 데이터를 수평 분할했다.(샤딩) 서버를 여러 대로 나열하여 처리

메모리의 가격 하락과 64비트 OS의 대두 => 한 대당 탑재 가능한 데이터베이스의 크기가 증가 => 많은 사용자 처리 가능 => 데이터베이스의 서버 대수를 크게 줄임

단일 스레드에서 행하는 타입의 작업에 시간이 너무 오래 걸려(마스터는 병렬화된 처리지만 MYSQL 복제는 단일 스레드라서 갱신 처리가 직렬화(지연)) => SATA SSD(PCI-Express SSE)에 의한 성능 개선(랜덤 엑세스가 매우 빠르다. 병렬성도 높지만, 단일 스레드 성능 매우 높다)

MYSQL과 같은 데이터 베이스에서는

1. 마스터는 HDD, 슬레이브에서는 PCI-Express SSD

2. 마스터도 슬레이브도 PCI-Express SSD

 

데이터베이스 개선의 역사

마스터에서 HDD RAID, 슬레이브에 SSD라는 구성이 보급된 상황에서 데이터베이스쪽에서도 본격적으로 병렬 처리의 성능이 요구되고 있다. MYSQL에서는

1. CPU 확장성 향상

임계영역 : 동시에 하나의 스레드밖에 액세스할 수 없도록 배타 제어를 해야 하는 처리 구간. 을 최소화

2. 디스크 I/O 병렬성의 개선

HDD를 RAID 구성으로 해두면 각각의 디스크가 I/O의 처리 요구를 취급할 수 있기 때문에 디스크가 한 개만인 경우에 비해 병렬성이 크게 향상.(SSD도 포함)

MYSQL 5.1이후에서 I/O 스레드 수를 설정에서 늘리도록 되어서 보다 많은 I/O 처리 가능

3. 백그라운드의 분할/병렬화

MYSQL 5.5부터는 제거 처리를 담당하는 스레드를 나눌 수 있겍 되었다. 제거 및 그 외의 두 개의 스레드가 따로 작업을 수행하여 동시성이 높아졌다. 또한 비동기 I/O도 지원되어 스레드 수가 적어도 I/O 요청을 많이 던질 수 있어 병렬성 높아짐. 아직 복제의 병렬화는 불가능

 

향후 데이터베이스에 요구되는 것

1. 네트워크 및 CPU의 이용 효율이 더 중요하게 된다.

DB 서버의 병목현상이 앞으로는 네트워크나 CPU 쪽으로 이동할 수 있다. NOSQU은 CPU 이용 효율의 문제를 해결하는 것이 커다란 목적 중 하나이다.

2. 성능 이외의 중요성이 높아진다.

다양한 기능, 높은 복원력, 자동화 등이 중요하게 된다.

 

데이터베이스 운용 기술의 급소

문제 예방

잘 알고 있는 기술을 사용

입증된 기술을 사용

아키텍처를 복잡하게 하지 않기

 

문제 인지

모니터링해야 할 항목

1. 응답 시간

2. CPU 사용률

 a. 디스크 I/O

 b. 시스템 공간 사용률

 c. 사용자 공간 사용률

3. 동시접속 수 및 스톨

스톨 : 많은 클라이언트가 작업을 기다리게 되어 전체적으로 시스템이 일시적으로 멈춘 것처럼 보이는 현상

감시의 간격을 짧게 해서 높은 정밀도로 스톨의 발생을 감지하자

스톨의 문제의 대부분은 RDBMS의 구현에 기인하고 있다.

4. 초당 SQL 문의 실행 횟수

5. 접속 여부 및 데이터베이스 내부 상태

6. 하드웨어 장애

잘 보고 있어야 할 하드웨어 감시 항목

a. RAID 컨트롤러의 배터리 소멸 상황

b. 디스크의 고장 상태나 블록 오류

c. SSD의 블록 고장 상황

d. 네트워크 카드(NIC)의 고장 상황

7. 빈 공간

빈 공간의 추이는 OS에서 본 빈 공간과 데이터베이스에서 본 테이블의 빈 영역의 두 관점에서 모니터링해야 한다. 전자는 백업 등에서 영향을 받고 있으며, 후자는 평소의 처리에서 영향을 받는다. 짧은 간격으로 체크

 

문제 해결

성능 문제에 대한 대처

애플리케이션 수정

웹 서버 증설

캐시 서버 증설

슬레이브 서버의 증설

보다 스펙이 높은 서버로의 마이그레이션 및 서버 분할

 

돌연사에 대한 대처

일상적인 장애

대수가 적은 상황에서는 그 상황에 따라 대처, 대수가 많은 경우는 자동으로 복구

비정형적인 장애

설정 파일의 기술 실수 등에 의해 일어나는 장애다.

 

MYSQL로 배우는 데이터베이스 관리

MYSQL 도입의 포인트

설정파일 my.cnf

[mysqld]

user=mysql     => 전용 사용자명

basedir=/usr/mysql    => 기본 디렉터리

datadir=/var/lib/mysql     => 데이터 디렉터리

port=3306   => 포트 번호

socket=/tmp/mysql.sock   => 통신 소켓

 

스토리지 엔진

일반적으로 테이블은 파일로 저장된다. 그러나 실제 레코드의 저장 방법으로서는 다양한 구현을 가정할 수 있다. 로컬 파일로 관리하는 것이 일반적으로 생각될 수 있지만 메모리만으로 관리하는 구현도 있을 수 있다. MYSQL에서는 다양한 레코드 처리 구현 방법을 제공하고 있으므로 용도에 맞는 것을 선택할 수 있는 아키텍처를 채용하고 있다. 스토리지 엔진의 선택은 테이블 단위로 할 수 있다. 다른 RDBMS와 비슷한 것은 InnoDB다.

MYSQL 운용에 필요한 파일의 기초 지식

로그 파일 형식

1. 오류 로그 파일 : mysql 본체 프로세스의 기동/정지의 정보 및 오류 정보를 출력하는 파일이다.

2. 슬로우 쿼리 로그 파일 : 실행에 일정 시간(초) 이상을 필요로 하는 쿼리를 로그 파일에 출력하는 기능이다.

3. 일반 로그 파일 : General Query Log. 실행된 모든 sql 문을 로그  파일에 출력하기 위한 기능이다.(개발/테스트 환경)

4. 바이너리 로그 파일 : 애플리케이션에서 발행된 업데이트성의 sql문(insert/update/delete, alter table 등 변경을 수반하는 모든 것)이 기록된다. 분석을 위한 로그 파일이 아니라 복구나 복제와 같이 데이터베이스를 운영하는 데 결정적인 역할을 하는 중요한 파일이다.

 

my.cnf의 설정 항목(innodb 기준)

basedir/datadir

basedir은 mysql 본체를 설치한 디렉터리다.

datadir은 데이터베이스의 데이터(테이블)를 만든 디렉터리로, 데이터 디렉터리라고도 한다. 데이터 디렉터리는 늘어나기 때문에 별도의 파일 시스템에 배치하는 경우가 많다.

port/socket

port는 mysql 서버가 접속을 대기하는 포트 번호이다.

socket에서는 UNIX 소켓 파일에 따른 접속을 위한 파일명을 지정한다. 

하나의 OS에서 여러 개의 mysql 서버를 시작하려면 datadir, port, socket이 서로 중복되지 않도록 지정해야 한다.

default-storage-engine

테이블을 만들 때 기본적으로 선택되는 스토리지 엔진을 지정.

일시적으로 사용하려면 create table 마지막에 'ENGINE=엔진 이름'

log-bin

바이너리 로깅을 활성화하기 위한 피라미터다. 중요. 디폴트은 취득하는 설정

slow-query-log

슬로우 쿼리 로그를 사용하는 피라미터. 디폴트는 취득X

long-query-time

실행에 몇 초 이상 걸린 SQL 문을 슬로우 쿼리 로그에 기록할지 여부를 지정. 디폴트는 10초

max_connections

MYSQL에 접속 가능한 상한의 접속 수를 지정한다. 디폴트는 100

innodb_buffer_pool_size

InnoDB 데이터 영역을 메모리 상에서 관리하기 위한 버퍼 사이즈를 의미한다. 성능을 충분히 활용하려면 어떻게 처리를 메모리 내에서 수행시킬지가 중요하다. 이 사이즈를 크게 함으로써 InnoDB의 데이터 영역이 그만큼 메모리 내에 유지되도록 하기 때문에 성능이 향상된다. 중요!!

innodb_flush_method

InnoDB 데이터 파일과 InnoDB 로그 파일을 읽고 쓰는 방법을 지정한다. =O_DIRECT를 지정하면 OS의 다이렉트 I/O 기능이 활성화된다. 다이렉트 I/O란 InnoDB의 메모리 영역과 디스크 사이의 데이터 교환 동안에 OS 파일 시스템 캐시를 거치지 않은 I/O 방식이다. 메모리 용량이 큰 환경에서 유용

innodb_data_file_path

InnoDB 데이터 파일의 초기 크기와 배치 디렉터리를 지정하는 피라미터다. 자동으로 확장하려면 autoextend

(ex: innodb_data_file_path=ibdata1:100M:autoextend)

innodb_autoextend_increment

위 값에 autoextend를 지정하면 데이터 크기가 꽉 찼을 때 자동으로 확장하는데, 이 확장 단위를 지정한다. 디폴트는 8으로 8mb씩 확장한다. 트래픽이 많은 애플리케이션은 100 등 큰 값을 지정한다.

innodb_log_file_size

innodb 로그 파일의 크기를 지정한다. REDO 로그라고도 불리며, 갑자기 크래쉬가 발생했을 경우에도 제대로 복구할 수 있도록 하는 중요한 역할을 한다. 로그 파일의 크기가 작은 경우에는 복구 속도가 빨라지지만 체크포인트라는 동기식 기록 작업이 빈번하게 발생하기 때문에 갱신 성능이 저하된다. 한편, innodb 로그 파일의 크기가 큰 경우는 체크 포인트의 빈도가 줄어들기 때문에 고속으로 동작하지만 복구에 보다 많은 시간이 걸리는 경향이 있다.

MYSQL 백업의 기초

무엇을 백업할 것인가?

데이터베이스 파일

오류 로그 파일, 슬로 쿼리 로그 파일

바이너리 로그 파일

설정파일 my.cnf

실행 파일

기타 공유 라이브러리, 지원 스크립트, 테스트 툴 등

 

백업 유형

콜드 백업 : mysql의 실행을 멈추고 백업하는 것

온라인 백업 : mysql을 실행하면서 백업 하는 것

논리적 백업 : SQL 문과 같이 인간이 읽을 수 있는 형식으로 백업하는 것(mysqldump)

물리적 백업 : 바이너리 형식으로 그대로 백업하는 것

논리적 백업은 복원 시간이 매우 길어지지만 텍스트(INSERT 문)를 백업하므로 압축 효율이 좋아서 데이터 용량을 이유로 논리적 백업을 채용하고 있는 곳도 많이 있다.

MYSQL에서 온라인으로 물리 백업을 하려면 LVM 및 일부 스토리지 제품이 지닌 스냅샷 기능 또는 외부 mxtrabackup을 사용하길 권장

 

복구 방법

장애가 발생한 경우에는 백업을 취한 때부터 상당한 시간이 경과되었을 수 있다. 그 사이에 행해진 갱신 정보가 모두 손실되는 것을 막기 위한 기능이 바이너리 로그에 있다.

이러한 바이너리 로그를 도입하여 복구를 하는 작업을 포인트 인 타임 리커버리(PIRT) 또는 롤 포워드 리커버리라고 부른다. (기본 설정 취득X)

MYSQL에서의 백업/복구

콜드 백업의 절차

MYSQL을 중지하고 백업한다. 매우 전통적인 백업 방식이다. OS 명령으로 백업 시 압축 여부는 선택할 수 있다.

// 1 mysql의 정지
mysqladmin shutdown -uroot
// 2 데이터 디렉터리를 /backup에 백업
cp -rp /var/lib/mysql /backup/
// mysql의 기동
mysqld_safe &

 

바이너리 로그에 의한 포인트 인 타임 복구

1. 바이너리 로그 활성화

바이너리 로그는 디폴트 상태에서는 검색되지 않는다. 가져오려면 my.cnf 내에서 log-bin이라고 한 줄을 추가한다.

mysql을 시작할 때 '호스트이름.000001이라는 파일이 생성된다. 이 파일이 바이너리 로그이고 갱신 정보는 이 파일에 기록되어 간다. 또한 mysql의 기동/정지 등에 따라 숫자가 증가한다.

2. 바이너리 로그에 의한 복구

바이너리 로그를 사용하여 복구하려면 먼저 SQL 문으로 변환하고 그것을 실행해 나가는 접근 방식을 취한다. SQL 문으로의 변환에는 mysqlbinlog 명령어를 사용한다.

mysqlbinlog hostname.000011 > recover.sql
mysql -uroot < recover.sql

3. 바이너리 로그의 포맷

바이너리 로그의 내용은 업데이트성 SQL 문의 내용이 그대로의 형태로 기록되는 '문장 기반'과 각 레코드 갱신 전과 갱신 후의 바이너리 이미지가 기록되는 '행 기반'의 두 종류가 있다.

mysqlbinlog로 출력한 텍스트 파일은 고쳐 쓸 수 있다는 것이 장점이다.

// 1. uuid() 함수를 사용하여 값을 투입(비추천)
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(36));
INSERT INTO t1 VALUES(1, UUID());
// 2. 내용을 확인
SELECT * FROM t1;
// 3. 바이너리 로그의 내용을 확인
mysqlbinlog hostname.000001

 

4. 바이너리 로그 배치 장소

바이너리 로그는 애플리케이션에 의해서 지속적으로 업데이트되므로 하루와 일주일 단위의 백업에 의해 취해지는 형식과 다르다. 바이너리 로그는 롤 포워드 리커버리의 핵심이 되는 파일이다. 바이너리 로그는 RAID1 상에 두어서 디스크가 망가져도 손실되지 않도록 하는 중복화를 권장한다.

 

mysqldump에 의한 온라인 백업

mysqldump는 MYSQL 본체의 bin 디렉터리에 기본적으로 설치되는 툴로, 테이블 데이터의 내용을 SQL 문으로 덤프할 수 있다. 온라인 백업이라는 이름 그대로 MYSQL을 기동한 상태에서 조작한다.

// 1 전체에 잠금을 걸고서 온라인 백업
mysqldump -uroot --lock-all-tables --master-data=2 > dump.sql
// 2 내용을 보면 바이너리 로그의 위치 정보가 기록되어 있음(발췌)
more dump.sql
~~~
// -- CHANGE MASTER TO MASTER_LOG_FILE='hostname.000042', MASTER_LOG_POS=98;
// 3 mysqldump에서 취득한 백업의 복구
mysql -uroot < dump.sql
// 4 바이너리 로그의 내용을 적용해 나감
mysqlbinlog --position=98 hostname.000042 > binlog.sql
mysql -uroot < binlog.sql

--locak-all table는 백업 중 전체 테이블에 공유 잠금을 위해 사용한다. 공유 잠금이란 참조는 허용하지만 갱신은 허용하지 않는다는 유형의 잠금 방식이다.

--master-data=2 옵션은 백업 시점의 바이너리 로그의 위치 정보를 덤프 파일에 기록하는 것이다.

문자 인코딩을 위해서는 원래 그대로의 인코딩으로 저장하는 것이 안전하다.

mysqldump 인수 '--default-character-set=cp932'등'--default-character-set=<인코딩 이름>'을 지정하면 된다.

 

작업을 걸지 않는 백업

백업 시간이 길어지면 그 사이 계속 업데이트할 수 없다는 것이 아쉽다. MYSQL에서는 스토리지 엔진이 InnoDB의 경우에만 잠금을 걸지 않은 채로 백업을 할 수 있는 방법을 제공하고 있다. InnoDb에서는 어느 한 시점의 스냅샷을 취하는 기능이 있다. 스냅샷을 취했을 경우 같은 트랜잭션 내에서라면 그 이후의 SELECT 결과가 스냅샷 취득 시점의 것이 된다. 비록 그 테이블의 내용이 갱신되었다 해도 스냅샷 취득 시점의 테이블 내용을 반환한다.

mysqldump에서는 이 기능을 다음과 같이 응용하여 백업받을 수 있다.

º백업 시작 직후에 전체를 잠금

º전체 스냅샷을 취득

º바이너리 로그 위치 정보를 기록

º잠금을 해제

º각 테이블의 내용을 SELECT 결과는 스냅샷 취득 시점의 것이 됨

mysqldump -uroot --single-transaction --master-data=2 > dump.sql

슬레이브에서 취한 백업을 복원한 후 마스터의 바이너리 로그를 어디서부터 적용해 가면 좋은가 하는 정보를 기록해야 할 필요가 있다. --dump-slave라는 인수를 mysqldump에 지정함으로써 백업 당시의 마스터의 바이너리 로그의 위치 정보를 기록해 주므로 이를 활용하자

 

바이너리 로그 삭제

전체 백업과 그 이후의 바이너리 로그가 있으면 복구할 수 있다. 따라서 그 이전의 바이너리 로그는 삭제해도 괜찮다.

바이너리 로그는 PURGE MASTER LOGS TO 라는 명령어로 삭제한다.

hostname.000042가 있다면

PURGE MASTER LOGS TO 'hostname.000042'

 

LVM 스냅샷 기능을 통해 온라인 백업

Linux 등이 갖고 있는 LVM(Logical Volume Manager)에는 스냅냣 취득 기능이 있어, 그 시점에서 디스크에 쓰여져 있는 정보의 스냅샷을 기록할 수 있다. 그 내용을 테이프에 복사함으로써 그 시점에서의 백업을 유지할 수 있다.

// 1 전체 데이터베이스를 공유 잠금
FLUSH TABLES WITH READ LOCK;
// 2 바이너리 로그의 위치 정보를 알아냄
SHOW MASTER STATUS;
// 3 디스크에 기록되지 않은 정보를 기록
sync
// 4 스냅샷을 취득
lvm이라면 lvcreate 커맨드로 취득
// 5 공유 잠금을 해제
UNLOCK TABLES;
// 6 취득한 스냅샷을 테이프 등에 대피(cp와 tar등)

 

복원 절차는 콜드 백업 때와 마찬가지. 테이프에 저장된 백업 데이터를 데이터 디렉터리에 복사하여 복원

복구 단계는 mysqldump 때와 동일