Category: Database

Database 글을 모아 놓은 카테고리

MySQL USING VS ON 차이.

MySQL에서 JOIN 을 사용할때에 USING 이나 ON 을 사용한다. 결과적으로 뽑고자 하는 데이터는 모두 동일한데 과연 이둘의 차이는 무엇일까?

첫째로 사용법에서 차이가 있다. USING 은 두 테이블간 필드이름이 같은 경우에 사용한다.

employees 와 salaries 를 조인(JOIN)하는데 emp_no 를 키가 양쪽 테이블에 모두 있기 때문에 USING 을 사용할 수 있다.
하지만 만일 조인시에 컬럼 이름이 다를 경우에는 ON 을 사용한다. 물론, 컬럼 이름이 같은 것을 기반을 조인을 할때도 ON 을 상용해도 된다.

Oracle 12c 데이터베이스 삭제하기

Oracle 12c 데이터베이스 삭제하기 위해서는 다음과 같이 하면 된다.

 

/dev/shm 공유메모리 설정.

Oracle 12c 에서 메모리의 사용을 어떻게 할 것인가에 따라서 다음 두가지로 나뉜다.

  • AMM
  • ASMM

AMM을 사용할 경우에 init.ora 시작 파일에 MEMORY_TARGET, MEMORY_MAX_TARGET 의 값을 사용하며 ASMM 의 경우에는 SGA_TARGET, SGA_MAX_TARGET 값을 사용한다.

AMM은 SGA, PGA 내의 각종 메모리 구역을 사용하는 목적에 따라서 자동으로 조절해 항상 최대의 성능을 내도록 해준다. 이는 Oracle 11g 에서 소개된 것으로 Oracle 은 이를 적극 사용할 것을 권하고 있다. 하지만 한가지 문제가 있는데, Linux 시스템의 경우에 HugePageSize 를 사용할 수 없다.

ASMM은 SGA 만 자동으로 메모리를 관리해준다. PGA는 자동으로 해주지 않으며 이는 상황에 맞게 normal한 값을 주어야 하며 잘못 설정할 경우에 성능에 심각한 영향을 줄 수 있다. 하지만 Linux 시스템의 경우에 HugePageSize 를 사용할 수 있다.

AMM을 사용할 경우에 초점을 맞춰, 메모리 용량을 설정할 때에 Linux 시스템에 tmpfs 으로 마운트된 /dev/shm 에 Oracle 에서 사용할 메모리양 만큼 마운트를 해줘야 한다.

/dev/shm 는 다음과 같은 모습을 보인다.

/dev/shm 는 기본적으로 마운팅이 되는데, 이는 전체 물리메모리의 절반이 기본으로 된다.

왜 /dev/shm 의 크기가 중요한가?

이유는 간단하다. 이 크기만큼만 Oracle 이 사용할 수 있다. 정확하게는 AMM 기능을 사용할때에만 해당된다. 예를들어, init.ora 에 다음과 같이 /dev/shm 의 값보다 크게 설정했다고 치자.

/dev/shm 크기는 현재 4G 이다. 이 상태에서 Oracle 를 시작하면 다음과 같은 오류가 난다.

그렇다면 /dev/shm 값을 변경해줘야 한다. 어떻게 할까?

먼저, /etc/fstab 을 다음과 같이 변경한다.

그리고 /etc/fstab 를 기반으로 마운트를 전부 재마운트 하도록 한다.

위와같이 조정을 한 후에 init.ora 의 MEMORY_TARGET 를 4G 로 지정해도 정상적으로 동작하게된다.

ps, /etc/fstab 를 조작하지 않고 다음과 같이 수동으로 할 수 있다. 먼저 umount 를 다음과 같이 해준다.

그리고 다음과 같이 크기를 지정해 마운트 해주면 된다.

 

ps, 좀 더 정확하게 말하자면 /dev/shm 은 Virtual Shared Memory Filesystem 이다. Linux 는 공유 메모리를 위한 인프라가 다양한데, 그중에 하나가 파일시스템(Filesystem) 을 이용하는 것이다.

파일시스템에 데이터를 기록하면 여러 프로세스가 액세스를 할 수 있어 손쉽게 공유메모리 기능을 구현할 수 있다. 이것은 POSIX 에서 마련한 공유메모리 기법으로 보통 POSIX 공유메모리 인프라로 불리운다. 그것을 위해서 물리적 메모리의 일부를 Virtual Shared Memory Filesystem 으로 사용하는데, Linux 는 물리적 메모리의 50%를 /dev/shm 에 할당 한다.

Linux 에서 기본은 System V IPC 로 이는 커널파라메터 vm.shmmax, vm.shmall 의 값을 기준으로 공유메모리를 마련한다.

Oracle 11g 의 경우에는 POSIX 규격의 공유 메모리를 사용하기도 하고 System V IPC 규격의 공유 메모리를 설정에 따라서 사용하게 된다. AMM 기능은 POSIX 규격의 공유 메모리를, 그래서 /dev/shm 을 사용하며 이 크기에 따라서 Oracle 의 SGA+PGA 가 결정된다. 만일 AMM 기능을 사용하지 않을 경우에는 System V IPC 를 이용해야하기 때문에 커널 파라메터인 vm.shmmax, vm.shmall 값에 따라서 SGA 크기가 결정된다.

AMM 기능을 사용하면 HugePage 를 사용할 수 없다라고 하는데, 이는 당연한 것으로 HugePage 는 System V IPC 공유 메모리 인프라로 AMM 은 POSIX 규격의 공유 메모리 인프라를 사용하기 때문에 서로 호환이 될 수가 없는 것이다.

HugePage 는 System V IPC 의 공유 메모리를 구성하는 방법중에 하나로 기존의 4k 크기의 페이지를 2MB 크기의 페이지로 나뉘어진 공유 메모리를 사용하게 된다.

 

[번역] 5.6과 5.7 사이의 MySQL 기본 설정 변경.

이 글은 다음의 글을 번역한 것입니다. 전문 번역자가 아니기에 오류가 있음을 미리 밝힙니다.
https://www.percona.com/blog/2016/09/14/mysql-default-configuration-changes-between-5-6-and-5-7/

이 블로그에서, 우리는 MySQL 5.6 과 5.7 사이에 기본 설정값의 차이에 대해 논의할 것이다.

MySQL 5.7 은 여러분이 기대했던 다양한 새로운 기능이 추가되었다. 하지만, 현재 변수들 또한 알게모르게 변화가 있었다. MySQL 5.7은 5.6으로부터 거의 40개의 기본값이 변경되었다. 어떤 바뀐값들은 여러분의 서버 성능에 아주 크게 영향을 줄 것이고 어떤것은 알지도 모르체 넘어갈 거다. 나는 각각의 변화가 무엇이고 어떤 의미인지를 살펴볼 것이다.

sync_binlog 와 같은 값들은 여러분의 서버에 아주 큰 영향을 줄 수 있다. 내 동료는, Roel Van de Paar, 다른 블로그 글을 통해서 아주 상세하게 sync_binlog의 영향을 깊게 다뤘다. Sync_binlog 는 어떻게 MySQL이 binlog 를 디스크로 플러시(flush)하는지를 제어한다. 새로운 값 1은 MySQL에게 커밋(Commit) 하는 동안 모든 트랜잭션들을 디스크로 쓰도록 강제한다. 이전에는 binlog 를 플러싱하도록 강제하지 못했었고 OS가 binlog를 플러시하는 시점을 결정하는걸 그져 신뢰해야만 했다.(OS 가 binlog 를 플러시하도록 내버려뒀다.)
https://www.percona.com/blog/2016/06/03/binary-logs-make-mysql-5-7-slower-than-5-6/

Variables5.6.295.7.11
sync_binlog01

퍼포먼스 스키마 변수들은 유용하지 않은것처럼 보이지만, 많은 것들이 기본값이 -1 이다. MySQL은 자동적으로 조정되어지는 변수들을 호출하는데 이 표기법을 사용한다. 오직 performance_schema_max_file_classes 만이 (자동으로) 조정되지 않고 바뀐다. 이것은 퍼포먼스 스키마에서 사용되어지는 파일 명령어들의 숫자다. 이것은 당신이 바꿀 필요가 없는 값이다.

Variables5.6.295.7.11
performance_schema_accounts_size100-1
performance_schema_hosts_size100-1
performance_schema_max_cond_instances3504-1
performance_schema_max_file_classes5080
performance_schema_max_file_instances7693-1
performance_schema_max_mutex_instances15906-1
performance_schema_max_rwlock_instances9102-1
performance_schema_max_socket_instances322-1
performance_schema_max_statement_classes168-1
performance_schema_max_table_handles4000-1
performance_schema_max_table_instances12500-1
performance_schema_max_thread_instances402-1
performance_schema_setup_actors_size100-1
performance_schema_setup_objects_size100-1
performance_schema_users_size100-1

optimizer_switchsql_mode 변수들은 각각 활성화될수도 있고 약간 다른 행동을 유도하게할 수도 있다. MySQL 5.7 에서는 민감도와 보안이 향상시키는데 플래그를 두가지 변수값을 활용할 수 있다.(활성화하거나 다른행동을 하게하도록 값을 지정하는 행위) 이것은 추가적으로 옵티마이저에게(Optimizer) 여러분들의 쿼리가 정확하게 해석되도록 결정하는데 좀더 효율성을 제공한다.

optimzer_switch 에 추가된 세개의 플래그들, 모두 MySQL 5.6 에 존재하며, 옵티마이저의 효율성을 높이기위한 목적으로 MySQL 5.7의 기본값으로 설정되었다. duplicateweedout=on, condition_fanout_filter=on 그리고 derived_merge=on. duplicateweedout 은 옵티 마이저의 세미 조인 구체화 전략의 일부입니다. condition_fanout_filter 은 조건 필터링 사용을 제어하고 derived_merge은 derived 테이블의 머지를 제어하고 뷰를 외부쿼리 블럭으로 제어한다.

https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

http://www.chriscalender.com/tag/condition_fanout_filter/

(위 설정들을) SQL mode 에 추가해도 직접적으로 성능에 영향을 주지 않지만 성능을 높일수 있는 쿼리 작성법을 개선해준다. 몇몇 주목할만한 변화로 select … group by 문에서 모든 필드를 요구하려면 SUM과 같은 함수를 사용하여 집계하거나 group by 절에 넣어야한다. MySQL은 그룹화해야 한다고 가정하지 않으며, 필드가 없는 경우 오류를 발생시킨다. Strict_trans_tables 는 트랜잭션 테이블과 함께 사용되는지에 따라 다른 효과를 발생시킵니다.

명령문은 그것이 유효하지 않거나 데이터 변경을 위한 명령문에서 값이 누락된다면 트랜잭션 테이블은 롤백되었었다. 트랜잭션 엔진을 사용하지 않는 테이블에서 MySQL은 유효하지 않은 데이터가 발생한 레코드에 의존해 행동한다. 첫번째 row 라면 트랜잭션 엔진의 동작과 똑같이 동작하진다. 만일 그렇지 않다면 유효하지 않은 값은 가장 근사한 유효한 값 혹은 컬럼의 기본값으로 변경되어진다. 경고는 발생되지만 데이터는 insert 된다.

Variables5.6.295.7.11
optimizer_switchindex_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on,mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on, semijoin=on
loosescan=on, firstmatch=on
subquery_materialization_cost_based=on
use_index_extensions=on
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
duplicateweedout=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on
sql_modeNO_ENGINE_SUBSTITUTIONONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

binlog 와 관련해 몇가지 변수가 변경되었다. MySQL 5.7 에 binlog_error_action가 업데이트 됐는데, binlog 를 쓰는중에 오류가 있다면 서버는 중단된다. 이런 일은 흔하지 않지만, 이런일이 발생하면 여러분의 애플리케이션과 리플리케이션에 큰 피해를 발생시키고 그것이 고쳐질때까지 서버는 그 어떤 추가적인 트랜잭션도 실행하지 않는다.

binlog 의 기본 포맷도 이전의 statement 대신에 ROW 로 변경되었다. Statement 는 로그에 적은 데이터를 쓴다. 하지만 많은 명령문들이, update … order by rand()을 포함해, 정확하게 복제되어지지 않았었다. 이러한 비결정적인 명령문은(non-deterministic statements) 마스터와 슬레이브에서 서로 다른 결과셋을 가진다. ROW 포맷으로 변화는 좀 더 많은 데이터를 binlog 에 쓰지만, 정보가 정확하고 올바른 복제를 보장한다.

MySQL은 전통적인 binlog 포지션 방식대신에 GTID 를 사용하는 리플리케이션에 초점을 맞추기 시작했다. MySQL이 시작하거나 재시작할때, 이전에 사용된 GTID 의 목록을 생성해야 한다. 만일 binlog_gtid_simple_recovery가 OFF거나 FALSE 라면 서버는 새로운 binlog 로 시작하고 previous_gtids_log_event 대해서 binlog 파일 검색을 거꾸로 반복한다. 만일 이것이 ON, True 라면 서버는 최신과 가장 오래된 binlog 파일을 검토하고 사용된 gtid 를 계산한다. Binlog_gtid_simple_recovery는 binlogs 를 좀 더 빠르게 파악하게 해주며, 특히 GTID 이벤트가 없는 아주 많은 수의 바이너리 로그를 빠르게 파악해준다. 하지만, 특별한 경우에 이것은 gtid_executedgtid_purged 가 잘못 채워질 수도 있다. 이것은 오직 MySQL 5.7.5 나 그 이후버전에 의해서 새로운 binlog 가 생성되어질때 발생하거나 SET GTID_PURGED 명령문이 5.7.7 보다 이전버전에서 실행될때 발생된다.

5.7에서 업데이트된 또다른 리플리케이션 기반 변수는 slave_net_timeout 이다. 이것은 60s 미만이여야 한다. 이전 버전의 리플리케이션 쓰레드는 적어도 한 시간에 문제가 발생될때까지 마스터로 연결이 깨졌다고 판단하지 않았다. 이 변화는 연결에 문제가 있다는 것을 좀더 빠르게 알려주고 여러분이 이슈를 알기전에 복제가 크게 뒤쳐지지 않도록 해준다.

Variables5.6.295.7.11
binlog_error_actionIGNORE_ERRORABORT_SERVER
binlog_formatSTATEMENTROW
binlog_gtid_simple_recoveryOFFON
slave_net_timeout360060

InnoDB 버퍼 풀 변경은 서버 시작 및 중지 시간에 영향을 준다.

innodb_buffer_pool_dump_at_shutdowninnodb_buffer_pool_load_at_startup 는 서버를 ‘warm up’ 하지 못하도록 함께 쓰인다. 이름처럼, 이것은 셧다운시에 버퍼풀 덤프를 유발하고 시작시에는 로드 된다. 비록 여러분이 100Gb 용량의 버퍼풀을 가지고 있어도, 쓰여진 데이터는 훨씬 작기 때문에 버퍼풀 용량과 똑같은 디스크 용량을 확보할 필요는 없다. 디스크에 쓰여지는 것은 실제 데이터, 테이블공간 과 페이지ID 를 찾는데 필요한 정보만이다.

Variables5.6.295.7.11
innodb_buffer_pool_dump_at_shutdownOFFON
innodb_buffer_pool_load_at_startupOFFON

이제 MySQL 은 5.6과 그 이전 버전에 InnoDB 에 구현된 몇가지 옵션들을 기본값으로 만들었다. InnoDB 의 체크섬 알고리즘을 innodb 에서 crc32로 개선했는데, 이는 최근 Intel CPU 의 하드웨어 가속 기능을 활용할 수 있는 장점이 있다.

Barracuda 파일 포맷은 5.5 이후부터 활용되기 시작해 5.6 에서 많은 개선이 이루어졌으며 이제 5.7 에서는 기본값이 되었다. Barracuda 포맷은 압축과 동적 로우 포맷을 사용할 수 있다. 내 동료 Alexey 는 압축된 포맷의 사용과 최적화 때 그가 본 결과에 대해서 글을 썼다. https://www.percona.com/blog/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

innodb_large_prefix 는 기본값이 ‘on’ 이며 Barracuda 파일 포맷과 조합해서 사용할 경우에 좀 더 큰 index key prefix 를(3072 bytes보다 큰) 생성할 수 있다. 이것은 아주 큰 텍스트 필드의 인덱스에서 장점이 된다. 만약 이것이 ‘off’ 라면, 로우 포맷은 동적이거나 압축이 되지 않으며, 767 bytes 보다 큰 index prefix는 자동으로 잘린다. MySQL은 5.7.6에서 더 큰 InnoDB 페이지 크기 (32k 및 64k)를 도입했다.

MySQL 5.7 은 innodb_log_buffer_size 값이 충분히 커졌다. InnoDB 는 바이네리 로그에 디스크로 쓰는 동안에 로그 트랜잭션를 위한 로그 버퍼를 사용한다. 증가된 크기는 로그를 디스크로 플러쉬하는 빈도를 줄이고, I/O를 줄이며, 커밋하기 전에 디스크에 기록 할 필요없이 로그에보다 큰 트랜잭션을 저장할 수 있다.

MySQL 5.7 은 MySQL 5.5 에서 thread contention 을 줄이기 위해서 InnoDB의 퍼지 연산들을 백그라운드 쓰레드로 옮겼다. 최신 버전에서 퍼지 쓰레드는 4개로 기본값이 늘었지만 1에서 32쓰레드까지 언제든지 변경할 수 있다.

MySQL 5.7 은 이제 innodb_strict_mode 가 기본값이 됐고 몇몇 경고들은 오류로 바뀌었다. create table, alter table, create index, optimize table 명령문에서 문법 오류는 에러를 발생시키고 실행전에 사용자가 수정해야만 한다. 또 insert 나 update 구문이 선택한 페이지 크기에 비해 아주 큰 레코드 때문에 실패하지 않도록 레코드 크기를 체크할 수 있다.

Variables5.6.295.7.11
innodb_checksum_algorithminnodbcrc32
innodb_file_formatAntelopeBarracuda
innodb_file_format_maxAntelopeBarracuda
innodb_large_prefixOFFON
innodb_log_buffer_size838860816777216
innodb_purge_threads14
innodb_strict_modeOFFON

MySQL 은 equality 범위를 평가할때 옵티마이저가 index로 넘어가는 횟수를 증가시켰다. 만약 옵티마이져가 eq_range_index_dive_limit 보다 더 많이 index로 넘어가길 원한다면, MySQL 5.7 에서 기본값 200, index 통계를 사용한다. 여러분은 이것을 0부터, 인엑스 다이빙 제한, 4294967295 까지 수정할 수 있다. 이것은 테이블 통계가 무작위 샘플의 카디널리티를 기반으로 하기 때문에 쿼리 성능에 큰 영향을 줄 수 있다. 이로 인해 최적화 프로그램은 인덱스 다이빙보다 검토 할 훨씬 많은 행 집합을 평가하고, 옵티마이 저가 쿼리를 실행하기 위해 선택한 방법을 변경하게 할 수 있다.

MySQL 5.7 에서 log_warnings 은 사라졌다. 대신 log_error_verbosity 를 사용된다. 기본값은 3이고 에러로그에 errors, warnings, notes 가 기록된다. 여러분은 이것을 1(errors 만) 이나 2(errors 와 warnings) 로 변경할 수 있다. 에러 로그를 참조할때, verbosity 는 좋은 것이다. 하지만 이것은 error 로깅을 위해서 디스크 공간과 I/O를 증가 시킨다.

table_open_cache_instances 는 MySQL 5.7.8 부터 변경 되었다. instance 의 숫자는 1에서 16까지 증가된다. 이것은 두가지 장점이 있는데, DML 구문의 contention 을 줄여주고, 캐쉬 접근의 세분화 된다. instance 수를 증가시킴으로써, DML 구문은 전체 캐쉬가 아닌 하나의 instance 만 잠글수 있다. 한가지 중요한 사실은 DDL 구문은 여전히 전체 캐쉬에 잠금(lock)를 필요로 한다. 시스템에서 많은 수의 세션이 캐쉬에 접근하면 성능은 증가 된다.

Variables5.6.295.7.11
eq_range_index_dive_limit10200
log_warnings12
table_open_cache_instances116

5.7 에서 기본값에 많은 변경이 있다. 하지만 이렇게 많은 옵션들은 오랫동안 존재해왔고 사용자에게 친숙해야 한다. 많은 사람들이 이 변수들을 사용했고 그들은 MySQL을 앞으로 나가게 하는 가장 좋은 방법이다. 하지만 기억해야 할 것은, 여러분은 여전히 이러한 변수들을 편집할 수 있고 서버가 여러분의 데이터를 위해 최고 동작하도록 그들을 설정할 수 있다.

Postgresql 9.x Replication – Streaming Log

Streaming log replication 은 Postgresql 9.0 부터 도입된 기능이다.  이 기능은 Primary 에서 Standby 서버로 직접 전송함으로서 replication delay 를 줄여준다. 따라서 pg_xlog 파일전송이 필요가 없다. 또 xlog 를 Streaming 으로 받기 위해서 Primary 서버에 REPLICATION 권한의 접속 계정이 필요하다.

Replication 권한 사용자 생성.

Primary 서버에서 REPLICATION 권한의 사용자를 다음과 같이 생성해준다.

그리고 Standby 서버에서 Primary 접속을 위해서 pg_hba.conf 파일을 Standby 서버 접속을 허용해 줍니다.

중요한 것은 DATABASE 에 반드시 ‘replication’ 이여야 한다.

Streaming Replication 을 위한 postgresql.conf 를 다음과 같이 설정한다.

데이터백업/복구.

PostgreSQL 9.3 이후부터 pg_basebackup 명령어를 지원한다. 이 명령어는 REPLICATION 권한을 가진 사용자로 접속을해 Primary 의 데이터 디렉토리를 원격에서 로컬로 복제를 해준다.

Standby 서버를 추가할때에 Primary 서버의 데이터 디렉토리를 복제해야 한다. 앞에서 생성한 계정을 이용하면 된다.

정상적으로 실행이 되면 위와같이 나온다.

Streaming logging replication

pg_basebackup 을 실행해 Primary 데이터 디렉토리가 복제했고 이로 인해서 postgresql.conf 파일도 Primary 것일 것이다. 이것을 최초의 파일로 교체해 준다.

그리고 recovery.conf 파일을 다음과 같이 작성해 준다.

그리고 Standby 서버를 시작해 준다.

로그파일을 보면 다음과 같이 나온다.

그리고 프로세스를 살펴보면 다음과 같다.

Primary 서버에서 Streaming Replication 상태를 다음과 같이 조회해 볼수 있다.

혹은 pg_stat_replication 을 조회해도 된다.

하지만 위 방법은 Standby 서버에는 접속할 수 없다. 접속을 시도하면 다음과 같은 에러를 낸다.

Hot Standby 세팅.

Hot Standby 는 Standby 서버에 접속해 읽기전용의 SQL 연산을 수행할 수 있도록 해준다. 이를 위해서는 먼저 Primary 서버에서 다음과 같이 postgresql.conf 파일에 Hot Standby 설정을 해줘야 한다.

Standby 서버도 다음과 같이 설정을 변경해 준다.

이제 서버들을 재시작해줘야 하는데 순서가 중요하다. 다음과 같은 순서로 재시작을 해준다.

  1. Standby 서버 Shutdown
  2. Primary 서버 재시작.
  3. Standby 서버 시작.

Standby 서버의 log 파일을 살펴보면 다음과 같다.

프로세스는 다음과 같이 나온다.

테스트.

Primary 서버에 테이블을 하나 만들고 데이터를 입력해보고 이것이 Standby 서버로 잘 가는지를 살펴본다.

그리고 Standby 서버에서 guestbook 테이블이 존재하고 데이터가 존재하는지 살펴본다.

잘 보이면 정상이다.

PostgreSQL Replication – Log Shipping

PostgreSQL 리플리케이션은 장애를 대비해 가용성을 높이는 최소한의 방법입니다. 현재 버전의 PostgreSQL 다양한 리플리케이션을 지원하는데, 이 문서는 가장 오래되고 기초적인 리플리케이션인 Log Shipping Replication 에 대해서 다룹니다.

File based replcation in PostgreSQL

이 리플리케이션은 Warm Standby 이라고 합니다. 이에 대한 설명은 PostgreSQL 문서에 다음과 같이 잘 나와 있습니다.

운영 서버에서 만드는 트랜잭션 로그 조각을 정기적으로 대기 서버로 옮기고, 그것을 적용시켜, 운영 서버가 장애로 멈추게 되면, 대기 서버를 운영해서, 가용성을 향상할 수 있다. 이 기능을 warm standby, 또는 log shipping 기능이라고 한다.

이 복제 방식은 먼저, 운영 서버와 대기 서버가 모두 실행 중이어야한다. 하지만, 두 서버 쌍방간의 연결 상태는 다른 복제 방식보다 나빠도 괜찮다. 운영 서버는 아카이브 모드로 운영 되어야하며, 운영 중에 생기는 다 쓴 WAL 세그먼트 파일(스위칭된 트랜잭션 로그 파일)을 차례대로 대기 서버로 보내고, 대기 서버는 복구 모드 전용(복구가 끝나도 다음 복구 파일이 있으면 계속 복구 작업을 하는 상태)으로 실행된다. 이 방식을 이용하면, 데이터베이스 테이블들을 수정해야할 필요가 없다. 또한 다른 복제 방식에 비해 관리 작업 비용도 적으며, 복제 작업이 운영 서버 쪽으로 끼치는 영향도 다른 복제 방식보다 적다.

이 방식의 구현 방법은 간단하다. 운영 서버에서 다 쓴 WAL 파일을 다른 서버로 운송(shipping) 하는 것 뿐이다. PostgreSQL에서는 그 로그 옮기는 작업은 한 번에 하나의 로그 파일을 옮길 수 있도록 구현되어 있다. WAL 파일(16MB)을 옮기는 작업은 데이터베이스 서버 밖에서 관리자가 정의한 방식으로 진행 되기 때문에, 같은 사이트 내로 옮겨도 되고, 전혀 다른 시스템 쪽으로 보내도 되고, 여러 시스템으로 한꺼번에 보내도 된다. 이 부분은 전적으로 관리자에게 맡긴다. 단지 고려해야할 사항은 파일이 전송될 때의 전송량 때문에 운영 서버에 영향을 줄 수도 있다. 이 부분이 염려되면, 전송 속도를 제한 할 수 있는 방법도 고려해야할 것이다. 아니면, 레코드 기반 로그 전달 방식 (스트리밍 복제)을 고려할 수 도 있다. (25.2.5절 참조)

로그 전달 방식은 비동기식임을 기억해야 한다. 다시 말하면, 전송하는 WAL 내용은 이미 커밋된 자료이기 때문에, 그 자료가 대기 서버로 미쳐 전달 되기전에 운영 서버가 멈춰버리면, 그 자료는 손실 된다. 자료 손실량을 줄이는 방법으로 archive_timeout 환경설정값을 몇 초 정도로 짧게 지정해 자주 사용하는 WAL 파일을 바꾸고, 그것을 전송하면, 되겠지만, 그 파일의 크기가 16MB이기 때문에, 잦은 WAL 파일 전송 작업으로 네트워크 사용량이 증가할 것이다. 스트리밍 복제 방식(25.2.5절 참조)을 이용하면, 이 손실 되는 자료량을 최소화 할 수 있다.

(물론 위에서 언급한 한계점이 있기는 하지만,) 대기 서버로 넘어 오는 WAL 파일이 제때에 잘 넘어 온다면, 운영 서버가 중지 되어 대기 서버가 그 역할을 맡기까지 서비스가 중지되는 시각은 극히 짧다. 이렇게 가용성을 향상 시킨다. 베이스 백업 자료를 준비하고, 지금까지 보관해둔 WAL 파일을 가지고, 서버를 복구 하는 방법은 이 방식보다 꽤 많은 서비스 중지 시간이 필요할 것이다. 서버가 복구 되는 기술적인 방식은 동일하지만, 가용성 입장에서는 차이가 난다. warm standby 방식으로 구현되면, 대기 서버 쪽에서는 어떤 쿼리도 사용할 수 없다. 대기 서버 쪽에서 읽기 전용 쿼리를 사용하려면, hot standby 방식으로 구축해야한다. 이 부분은 25.5절에서 자세히 설명한다.

 

시작하기에 앞서

WAL 파일

대부분의 데이터베이스 시스템은 데이터베이스에 변화와 관련된 모든 작업에 대해서 파일로 기록을 해 둡니다. 정확하게는 데이터를 조작하기에 앞서 어떤 SQL 를 명령했는지에 대해서 로깅을 한 후에 실제 데이터를 조작 합니다. 이는 갑자기 장애가 발생했을시에 이 로그 파일을 이용해 데이터를 복구할 수 있게 됩니다. 이를 WAL (Write Ahead Log) 파일이라고 합니다.

PostgreSQL 도 같은 방법으로 동작합니다. PostgreSQL 에서 WAL 파일은 pg_xlog 디렉토리에 생성되며 특징은 16MB 크기단위로 파일이 쪼개져 저장됩니다.

 

CheckPoint

모든 데이터베이스 시스템은 메모리 기반으로 동작합니다. 메모리에 자주 쓰이는 데이터를 캐쉬해기도 하고 트랜잭션시에도 메모리에 저장됩니다. 특정 시점이 되면 PostgreSQL 은 메모리에 있는 것을 디스크에 쓰기를 함으로써 실제 데이터가 영구적으로 저장이 됩니다.

그런데, 이러한 작업을 수동으로 해야할 때가 있습니다. 대표적으로 PITR(Point In Time Recovery) 백업을 하기 위해서는 모든 메모리에 내용을 강제로 디스크에 쓰여져야 합니다. 이때 PostgreSQL 가 하는 작업이 바로 CheckPoint 입니다.

다시말해, CheckPoint 는 메모리에 있는 내용을 디스크에 강제로 쓰기하는 작업을 말합니다.

 

리플리케이션 설정하기

설정하는 순서는 다음과 같습니다.

  1. Master 서버가 standby 서버에 postgres 계정으로 ssh 패스워드 인증없이 바로 접속할 수 있도록 설정을 합니다.
  2. Master 서버의 postgresql.conf 파일을 수정하고 서버를 재시작 합니다.
  3. Master 서버에서 PITR 백업을 합니다.
  4. 백업본을 standby 서버로 전송합니다.
  5. standby 서버에서 전송받은 백업 압축파일을 해제 합니다.
  6. standby recovery.conf 파일을 작성하고 pg_xlog 디렉토리를 생성하고 소유권을 postgres 로 바꿉니다.
  7. standby 서버를 기동 합니다.

SSH no password 접속 설정.

이는 RSA 키를 교환함으로써 가능 합니다. PostgreSQL 은 postgres 계정으로 동작하기 때문에 postgres 계정에서 Standby postgres 계정으로 RSA 키 접속을 설정해 줍니다.

먼저, Master  서버의 postgres 계정에서 다음과 같이 키를 작성해 줍니다.

이렇게하면 postgres 계정에 “.ssh/id_rsa, .ssh/id_rsa.pub” 두개의 파일이 생성이 됩니다. id_rsa 는 private key 이고 id_rsa.pub 는 public key 입니다. 다음과 같이  publickey 를 Standby 의 postgres 계정을 복사해 줍니다.

이제 그냥 “ssh ‘postgres@192.168.96.26′” 을 입력하면 패스워드 입력 없이 접속이 가능해 집니다.

Master 서버에 postgresql.conf 파일 설정

다음과 같이 postgresql.conf 파일을 설정해 줍니다.

archive_command 에 설정한대로 Standby 서버에 archive 디렉토리를 만들어주고 postgres 소유권으로 바꿔 줍니다.

서버를 재시작 해줍니다.

Standby 를 위한 데이터 백업하기 

리플리케이션의 시작은 슬레이브 데이터베이스시스템을 위해 데이터베이스를 옮겨주는 것입니다. PostgreSQL 뿐만아니라 MySQL 도 그렇지만, 데이터베이스의 파일들을 압축해서 옮겨줘야 하는데, 이를 위해서는

  1. Master 서버의 작업 중단
  2. 모든 메모리에 있는 내용을 디스크에 쓰기(CheckPoint)

뒤 두가지가 필요합니다. PostgreSQL 은 이 두가지를 명령어를 통해서 한번에 되도록 지원하는데 두가지 방법이 있습니다. 이 문서에서는 PITR 백업 방법을 설명합니다.

먼저, PostgreSQL  서버에 다음과 같이 해줍니다.

이제 백업 완료된 파일을 Slave 서버로 옮겨 줍니다.

백업본을 Standby  서버로 옮겨주기

다음으로 Slave 서버에서 전송되어진 압축파일을 다음과 같이 압축해제해 줍니다.

Standby 서버에서 전송받은 백업 압축파일을 해제

이제 recovery.conf 을 작성해 줍니다.

recovery.conf 파일 작성

다음과 같이 recovery.conf 파일을 작성하기 위해서 다음과 같이 data 디렉토리로 이동해 줍니다.

recovery.conf 는 다음과 같이 작성해 줍니다.

이렇게 파일을 작성하고 역시나 소유권을 postgres 로 변경해줍니다. 그리고 서버를 시작해줍니다.

체크하기

이 렇게 해놓으면 Master 서버에서는 archive 해야할 로그파일을 Standby 서버로 전송하고 Standby 서버는 이것을 replay 하게 됩니다. 그러니까 Standby 서버는 replay 로만 동작하게 됩니다. 이는 상태를 보면 알 수 있습니다. Standby 서버의 프로세스 상태는 다음과 같습니다.

38번째 파일을 기다리고 있다고 나옵니다. . ‘/opt/pgsql-9.5.0/archive’ 에는 Master 에서 전송한 파일을 받고 받자마자 바로 replay를 합니다. 그리고 3개가 넘어가면 pg_archivecleanup 프로그램이 recovery.conf 에 명시된대로 삭제됩니다. 이는 ‘/opt/pgsql-9.5.0/archive’ 디렉토리를 모니터링 해보면 알 수 있습니다. 없던 파일이 나타나고 사라지는것을 확인할 수 있습니다.

로그파일을 보면 현재 상태를 확인할 수 있습니다.

이 Replication 의 특징는 Standby 를 활용할 수 없습니다. Select 전용으로라도 활용할 수 없습니다. 왜냐하면 replay로 동작중이기 때문에 psql 를 이용해서 접속을 하면 다음과 같은 에러메시지를 만나게 됩니다.

만일 Master 서버에 여러 Standby 서버가 필요하다면 Master 서버에 postgresql.conf 파일에 ‘archive_command’ 에 스크립트를 넣어줍니다.

단점

이 Log Shipping Replication 은 자세한 Replication 상태를 모니터링 할 수가 없습니다. 파일 전송은 PostgreSQL 이 담당하는게 아닌 리눅스 시스템이 담당합니다. 거기다 Standby 서버에는 접속조차 할 수 없어서 Standby 서버에서 제대로 Recovering 이 되고 있는지를 알 수가 없습니다.

동작 방법이 16MB WAL 파일 작성이 완료되어지면 전송이 되어지는데, 그래서 WAL 파일이 자주 생성된다면 파일을 전송하는데 시스템 자원 소모가 커질 우려도 있습니다.

pg_terminate_backend, pg_cancel_backend

PostgreSQL 에서 쿼리를 취소시키위한 함수로 pg_terminate_backend, pg_cancel_backend 를 제공 합니다. 공식문서에는 다음과 같이 나와 있습니다.

– pg_cancel_backend(pidint) : Cancel a backend’s current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.
– pg_terminate_backend(pidint) : Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

이 함수를 이용해서 다음과 같이 30초이상 지속된 쿼리들을 차단할 수 있습니다.

프로세스에 시그널(Signal)을 보내는거는 똑같은데, 하나는 SIGINT을 다른 하나는 SIGTERM을 받습니다.
SIGINT는 인터럽트(Interrupt) 시그널을 말합니다. 가장 흔하게 볼 수 있는게 어떤 프로그램을 포그라운드(Foreground)로 실행중에 Ctr+C 를 누르는 것이 바로 SIGINT 입니다. 이 시그널을 받은 프로그램은 별도의 행동을 정의하지 않았다면 프로그램을 종료 됩니다.
SIGTERM 은 Kill 시그널로 프로그램 종료 시그널 입니다. 이 시그널을 받은 프로그램은 이 신호를 무시하거나 접속을 차단하고 프로그램 종료를 위한 프로세스를 진행합니다. 한마디로 말해서 안전한 종료 입니다.
우리가 흔히 쓰는 ‘kill -9 pid’ 는 SIGKILL인데, 이것은 시그널을 받은 프로그램의 후속 행동과는 상관없이 즉시 프로그램을 강제 종료시키도록 합니다.

테스트

테스트는 PostgreSQL 의 프로세스에게 SIGTERM, SIGKILL, SIGINT를 보내 이에 어떤 반응을 보이는지를 보는 것이 주 목적입니다. 이를 위해 Linux 쉘 상에서 SIGTERM, SIGKILL을 주는 방법과 PostgreSQL 쉘상에서 위에서 소개했던 두개의 함수를 사용하는 방법을 채택했습니다.

모든 테스트는 화면과 PostgreSQL이 기록하는 로그를 기반으로 평가되었습니다.

테스트 쿼리

테스트를 위해서 실행했던 테스트 쿼리는 다음과 같습니다.

위와같이 pg_sleep 함수를 사용한 상태에서 pg_stat_activity 를 보면 다음과 같이 나옵니다.

Linux 쉘상에서 SIGTERM

먼저 Linux 쉘상에서 SIGTERM 을 테스트 입니다.

SIGTERM 을 Linux 쉘에서 보내자 PostgreSQL 쉘에서도 다음과 같은 반응 했습니다.

다음은 PostgreSQL 로그 입니다.

PostgreSQL은 쿼리를 취소한게 아니라 Connection 을 차단하고 있음을 보여주고 있습니다.

Linux 쉘상에서 SIGKILL

이번에는 Linux 쉘상에서 SIGKILL에 대해 테스트 입니다.

‘kill -9 ‘ 는 SIGKILL 을 의미합니다. PostgreSQL 쉘은 다음과 같이 되었습니다.

SIGTERM과는 달리 “FATAL: terminating connection due to administrator command” 메시지도 없이 바로 접속이 차단되었습니다. 그리고 쉘 모양도 깨졌습니다.

PostgreSQL 에서의 로그는 다음과 같습니다.

SIGTERM  때와는 달리 서버가 재시작되었습니다. Recovery Mode 로 전환해 트랜잭션을 복구하고 checkpoint 를 날려 메모리를 리셋했습니다. checkpoint 는 PostgreSQL의 shared_buffer 메모리에 있는 모든 데이터를 디스크에 Flush 하도록하는 것으로 메모리 초기화로 부르기도 합니다. 이렇게 되면 메모리 그래프상으로는 갑자기 메모리 사용량이 줄어드는 현상을 보입니다.

어쨌든 SIGKILL은 PostgreSQL을 재시작하는 결과를 가지고 옵니다. 이는 프로세스의 시작시간이 바뀌것으로도 판단할 수 있습니다.

프로세스 시작시간이 14:53분으로 이전과 달라졌습니다.

PostgreSQL 쉘 상에서 SIGTERM

PostgreSQL 쉘 상에서 SIGTERM은 pg_terminate_backend 함수를 사용함으로서 이루어집니다. pg_stat_activity 테이블에서 pid를 알아낸 다음 다음과 같이 쿼리를 했습니다.

쿼리를 수행중이던 다른 PostgreSQL 쉘에서는 다음과 같이 메시지가 나왔습니다.

그리고 PostgreSQL 의 로그는 다음과 같습니다.

이는 Linux 쉘 상에서 SIGTERM을 보낸것과 같은 결과 입니다.

PostgreSQL 쉘 상에서 SIGINT

이번에는 SIGINT에 관한 것입니다.

PostgreSQL 쉘에서 쿼리를 보냈던 화면에는 다음과 같이 나왔습니다.

위 내용은 PostgreSQL 로그에도 동일 했습니다.

결론

SIGINT 은 SIGTERM과 중요한 차이점을 보여주고 있습니다. SIGTERM은 connection 자체를 차단하게 합니다. 따라서 쿼리를 하기위해서는 반드시 PostgreSQL로 재 접속이 이루어져야 합니다. 반면에, SIGINT은 쿼리문(statement) 자체를 취소(cancel)하게 함으로써 connection 자체에는 아무런 영향을 주지 않습니다.