Tagged: database

05/20/2017

[번역] 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/

Variables	5.6.29	5.7.11
sync_binlog	0	1

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

Variables	5.6.29	5.7.11
performance_schema_accounts_size	100	-1
performance_schema_hosts_size	100	-1
performance_schema_max_cond_instances	3504	-1
performance_schema_max_file_classes	50	80
performance_schema_max_file_instances	7693	-1
performance_schema_max_mutex_instances	15906	-1
performance_schema_max_rwlock_instances	9102	-1
performance_schema_max_socket_instances	322	-1
performance_schema_max_statement_classes	168	-1
performance_schema_max_table_handles	4000	-1
performance_schema_max_table_instances	12500	-1
performance_schema_max_thread_instances	402	-1
performance_schema_setup_actors_size	100	-1
performance_schema_setup_objects_size	100	-1
performance_schema_users_size	100	-1

optimizer_switch 와 sql_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 된다.

Variables	5.6.29	5.7.11
optimizer_switch	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 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_mode	NO_ENGINE_SUBSTITUTION	ONLY_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_executed 와 gtid_purged 가 잘못 채워질 수도 있다. 이것은 오직 MySQL 5.7.5 나 그 이후버전에 의해서 새로운 binlog 가 생성되어질때 발생하거나 SET GTID_PURGED 명령문이 5.7.7 보다 이전버전에서 실행될때 발생된다.

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

Variables	5.6.29	5.7.11
binlog_error_action	IGNORE_ERROR	ABORT_SERVER
binlog_format	STATEMENT	ROW
binlog_gtid_simple_recovery	OFF	ON
slave_net_timeout	3600	60

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

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

Variables	5.6.29	5.7.11
innodb_buffer_pool_dump_at_shutdown	OFF	ON
innodb_buffer_pool_load_at_startup	OFF	ON

이제 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 구문이 선택한 페이지 크기에 비해 아주 큰 레코드 때문에 실패하지 않도록 레코드 크기를 체크할 수 있다.

Variables	5.6.29	5.7.11
innodb_checksum_algorithm	innodb	crc32
innodb_file_format	Antelope	Barracuda
innodb_file_format_max	Antelope	Barracuda
innodb_large_prefix	OFF	ON
innodb_log_buffer_size	8388608	16777216
innodb_purge_threads	1	4
innodb_strict_mode	OFF	ON

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)를 필요로 한다. 시스템에서 많은 수의 세션이 캐쉬에 접근하면 성능은 증가 된다.

Variables	5.6.29	5.7.11
eq_range_index_dive_limit	10	200
log_warnings	1	2
table_open_cache_instances	1	16

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

09/24/2015

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초이상 지속된 쿼리들을 차단할 수 있습니다.

=# select pg_terminate_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;
=# select pg_cancel_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;

1 2	=# select pg_terminate_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t; =# select pg_cancel_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;

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

테스트

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

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

테스트 쿼리

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

testdb=> SELECT pg_sleep(10);
 pg_sleep
----------
(1 row)

testdb=> SELECT pg_sleep(10);

pg_sleep

----------

(1 row)

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

datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |              query
-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------
 16386 | testdb  | 14014 |    16385 | test    | psql             |             |                 |          -1 | 2015-09-22 13:18:13.553564+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054514+09 | f       | active | SELECT pg_sleep(100);

-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------

16386 | testdb | 14014 | 16385 | test | psql | | | -1 | 2015-09-22 13:18:13.553564+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054514+09 | f | active | SELECT pg_sleep(100);

Linux 쉘상에서 SIGTERM

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

saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 14014  0.0  0.0 146444  9552 ?        Ss   13:18   0:00 postgres: test testdb [local] SELECT    <--- 쿼리가 실행중입니다.
]# kill 14014

saltmaster ~ # ps aux | grep post

postgres 6129 0.0 0.0 145096 15740 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

postgres 6537 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: checkpointer process

postgres 6538 0.0 0.0 145096 4204 ? Ss 11:01 0:00 postgres: writer process

postgres 6539 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: wal writer process

postgres 6540 0.0 0.0 145916 5800 ? Ss 11:01 0:00 postgres: autovacuum launcher process

postgres 6541 0.0 0.0 104840 3732 ? Ss 11:01 0:00 postgres: stats collector process

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5932 pts/0 S 13:18 0:00 -su

postgres 14003 0.0 0.0 101452 7188 pts/0 S+ 13:18 0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb

postgres 14014 0.0 0.0 146444 9552 ? Ss 13:18 0:00 postgres: test testdb [local] SELECT <--- 쿼리가 실행중입니다.

]# kill 14014

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

testdb=> SELECT pg_sleep(100);
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

testdb=> SELECT pg_sleep(100);

FATAL: terminating connection due to administrator command

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

다음은 PostgreSQL 로그 입니다.

2015-09-22 14:47:45 KST [14014-9] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 14:47:45 KST [14014-10] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 14:47:45 KST [14014-11] test@testdb LOG:  disconnection: session time: 1:29:32.146 user=test database=testdb host=[local]
2015-09-22 14:47:45 KST [19073-1] [unknown]@[unknown] LOG:  connection received: host=[local]

2015-09-22 14:47:45 KST [14014-9] test@testdb FATAL: terminating connection due to administrator command

2015-09-22 14:47:45 KST [14014-10] test@testdb STATEMENT: SELECT pg_sleep(200);

2015-09-22 14:47:45 KST [14014-11] test@testdb LOG: disconnection: session time: 1:29:32.146 user=test database=testdb host=[local]

2015-09-22 14:47:45 KST [19073-1] [unknown]@[unknown] LOG: connection received: host=[local]

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

Linux 쉘상에서 SIGKILL

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

saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 19073  0.0  0.0 146848  8504 ?        Ss   14:47   0:00 postgres: test testdb [local] SELECT
]# kill -9 19073

saltmaster ~ # ps aux | grep post

postgres 6129 0.0 0.0 145096 15740 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

postgres 6537 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: checkpointer process

postgres 6538 0.0 0.0 145096 4204 ? Ss 11:01 0:00 postgres: writer process

postgres 6539 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: wal writer process

postgres 6540 0.0 0.0 145916 5800 ? Ss 11:01 0:00 postgres: autovacuum launcher process

postgres 6541 0.0 0.0 104840 3732 ? Ss 11:01 0:00 postgres: stats collector process

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5932 pts/0 S 13:18 0:00 -su

postgres 14003 0.0 0.0 101452 7188 pts/0 S+ 13:18 0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb

postgres 19073 0.0 0.0 146848 8504 ? Ss 14:47 0:00 postgres: test testdb [local] SELECT

]# kill -9 19073

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

testdb=> SELECT pg_sleep(100);
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>  <----- 쉘모양도 깨졌습니다.

testdb=> SELECT pg_sleep(100);

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

!> <----- 쉘모양도 깨졌습니다.

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

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

2015-09-22 14:53:28 KST [6129-6] LOG:  server process (PID 19073) was terminated by signal 9: Killed
2015-09-22 14:53:28 KST [6129-7] DETAIL:  Failed process was running: SELECT pg_sleep(200);
2015-09-22 14:53:28 KST [6129-8] LOG:  terminating any other active server processes
2015-09-22 14:53:28 KST [6540-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 14:53:28 KST [6540-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 14:53:28 KST [6540-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 14:53:28 KST [19452-1] [unknown]@[unknown] LOG:  connection received: host=[local]
2015-09-22 14:53:28 KST [19452-2] test@testdb FATAL:  the database system is in recovery mode
2015-09-22 14:53:28 KST [6129-9] LOG:  all server processes terminated; reinitializing
2015-09-22 14:53:28 KST [19453-1] LOG:  database system was interrupted; last known up at 2015-09-22 11:01:35 KST
2015-09-22 14:53:28 KST [19453-2] DEBUG:  checkpoint record is at 0/176D970
2015-09-22 14:53:28 KST [19453-3] DEBUG:  redo record is at 0/176D970; shutdown TRUE
2015-09-22 14:53:28 KST [19453-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 14:53:28 KST [19453-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 14:53:28 KST [19453-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 14:53:28 KST [19453-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 14:53:28 KST [19453-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 14:53:28 KST [19453-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 14:53:28 KST [19453-10] LOG:  record with zero length at 0/176D9D0
2015-09-22 14:53:28 KST [19453-11] LOG:  redo is not required
2015-09-22 14:53:28 KST [19453-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 14:53:28 KST [19453-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 14:53:28 KST [19453-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 14:53:28 KST [19457-1] LOG:  autovacuum launcher started
2015-09-22 14:53:28 KST [6129-10] LOG:  database system is ready to accept connections

2015-09-22 14:53:28 KST [6129-6] LOG: server process (PID 19073) was terminated by signal 9: Killed

2015-09-22 14:53:28 KST [6129-7] DETAIL: Failed process was running: SELECT pg_sleep(200);

2015-09-22 14:53:28 KST [6129-8] LOG: terminating any other active server processes

2015-09-22 14:53:28 KST [6540-2] WARNING: terminating connection because of crash of another server process

2015-09-22 14:53:28 KST [6540-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2015-09-22 14:53:28 KST [6540-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.

2015-09-22 14:53:28 KST [19452-1] [unknown]@[unknown] LOG: connection received: host=[local]

2015-09-22 14:53:28 KST [19452-2] test@testdb FATAL: the database system is in recovery mode

2015-09-22 14:53:28 KST [6129-9] LOG: all server processes terminated; reinitializing

2015-09-22 14:53:28 KST [19453-1] LOG: database system was interrupted; last known up at 2015-09-22 11:01:35 KST

2015-09-22 14:53:28 KST [19453-2] DEBUG: checkpoint record is at 0/176D970

2015-09-22 14:53:28 KST [19453-3] DEBUG: redo record is at 0/176D970; shutdown TRUE

2015-09-22 14:53:28 KST [19453-4] DEBUG: next transaction ID: 0/725; next OID: 16387

2015-09-22 14:53:28 KST [19453-5] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0

2015-09-22 14:53:28 KST [19453-6] DEBUG: oldest unfrozen transaction ID: 711, in database 1

2015-09-22 14:53:28 KST [19453-7] DEBUG: transaction ID wrap limit is 2147484358, limited by database with OID 1

2015-09-22 14:53:28 KST [19453-8] LOG: database system was not properly shut down; automatic recovery in progress

2015-09-22 14:53:28 KST [19453-9] DEBUG: resetting unlogged relations: cleanup 1 init 0

2015-09-22 14:53:28 KST [19453-10] LOG: record with zero length at 0/176D9D0

2015-09-22 14:53:28 KST [19453-11] LOG: redo is not required

2015-09-22 14:53:28 KST [19453-12] DEBUG: resetting unlogged relations: cleanup 0 init 1

2015-09-22 14:53:28 KST [19453-13] LOG: checkpoint starting: end-of-recovery immediate

2015-09-22 14:53:28 KST [19453-14] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s

2015-09-22 14:53:28 KST [19457-1] LOG: autovacuum launcher started

2015-09-22 14:53:28 KST [6129-10] LOG: database system is ready to accept connections

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

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

postgres@saltmaster ~/9.2/main/pg_log $ ps aux | grep post
postgres  6129  0.0  0.0 145096 15704 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5936 pts/0    S+   13:18   0:00 -su
postgres 19454  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: checkpointer process
postgres 19455  0.0  0.0 145096  4204 ?        Ss   14:53   0:00 postgres: writer process
postgres 19456  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: wal writer process
postgres 19457  0.0  0.0 145784  5664 ?        Ss   14:53   0:00 postgres: autovacuum launcher process
postgres 19458  0.0  0.0 104840  3564 ?        Ss   14:53   0:00 postgres: stats collector process
                                                 >프로세스시작시간<

postgres@saltmaster ~/9.2/main/pg_log $ ps aux | grep post

postgres 6129 0.0 0.0 145096 15704 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5936 pts/0 S+ 13:18 0:00 -su

postgres 19454 0.0 0.0 145096 3488 ? Ss 14:53 0:00 postgres: checkpointer process

postgres 19455 0.0 0.0 145096 4204 ? Ss 14:53 0:00 postgres: writer process

postgres 19456 0.0 0.0 145096 3488 ? Ss 14:53 0:00 postgres: wal writer process

postgres 19457 0.0 0.0 145784 5664 ? Ss 14:53 0:00 postgres: autovacuum launcher process

postgres 19458 0.0 0.0 104840 3564 ? Ss 14:53 0:00 postgres: stats collector process

>프로세스시작시간<

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

PostgreSQL 쉘 상에서 SIGTERM

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

postgres=# select pg_terminate_backend(19954);
 pg_terminate_backend
----------------------
 t
(1 row)

postgres=# select pg_terminate_backend(19954);

pg_terminate_backend

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

(1 row)

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

testdb=> SELECT pg_sleep(200);
LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

testdb=> SELECT pg_sleep(200);

LOG: disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

FATAL: terminating connection due to administrator command

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

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

2015-09-22 15:03:55 KST [20142-4] postgres@postgres LOG:  duration: 2.154 ms  statement: select pg_terminate_backend(19954);
2015-09-22 15:03:55 KST [19954-3] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 15:03:55 KST [19954-4] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 15:03:55 KST [19954-5] test@testdb LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

2015-09-22 15:03:55 KST [20142-4] postgres@postgres LOG: duration: 2.154 ms statement: select pg_terminate_backend(19954);

2015-09-22 15:03:55 KST [19954-3] test@testdb FATAL: terminating connection due to administrator command

2015-09-22 15:03:55 KST [19954-4] test@testdb STATEMENT: SELECT pg_sleep(200);

2015-09-22 15:03:55 KST [19954-5] test@testdb LOG: disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

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

PostgreSQL 쉘 상에서 SIGINT

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

postgres=# select pg_cancel_backend(20197);
 pg_cancel_backend
-------------------
 t
(1 row)

postgres=# select pg_cancel_backend(20197);

pg_cancel_backend

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

(1 row)

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

testdb=> SELECT pg_sleep(200);
ERROR:  canceling statement due to user request

1 2	testdb=> SELECT pg_sleep(200); ERROR: canceling statement due to user request

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

2015-09-22 15:11:37 KST [20142-6] postgres@postgres LOG:  duration: 1.750 ms  statement: select pg_cancel_backend(20197);
2015-09-22 15:11:37 KST [20197-3] test@testdb ERROR:  canceling statement due to user request
2015-09-22 15:11:37 KST [20197-4] test@testdb STATEMENT:  SELECT pg_sleep(200);

2015-09-22 15:11:37 KST [20142-6] postgres@postgres LOG: duration: 1.750 ms statement: select pg_cancel_backend(20197);

2015-09-22 15:11:37 KST [20197-3] test@testdb ERROR: canceling statement due to user request

2015-09-22 15:11:37 KST [20197-4] test@testdb STATEMENT: SELECT pg_sleep(200);

결론

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

09/22/2015

PostgreSQL 프로세스를 Kill 하면 발생하는 일

PostgreSQL 은 프로세스 복제 모델로 동작합니다. 접속을 하면 Postmaster 라 불리는 부모프로세스를 복제해서 접속을 하고 쿼리를 처리하도록 합니다. 그런데 데이터베이스를 운영하다보면 불필요한 쿼리를 없앤다고 운영체제에서 kill 명령어를 이용하는 경우가 있습니다.

결론부터 말하면 절대로 해서는 안되는 일 입니다. 이렇게하게되면 PostgreSQL은 다음과같이 모든 프로세스를 죽이고 서버를 재시작합니다.

2015-09-22 11:01:35 KST [6129-2] LOG:  server process (PID 6345) was terminated by signal 9: Killed
2015-09-22 11:01:35 KST [6129-3] LOG:  terminating any other active server processes
2015-09-22 11:01:35 KST [6135-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 11:01:35 KST [6135-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 11:01:35 KST [6135-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 11:01:35 KST [6129-4] LOG:  all server processes terminated; reinitializing
2015-09-22 11:01:35 KST [6536-1] LOG:  database system was interrupted; last known up at 2015-09-22 10:57:06 KST
2015-09-22 11:01:35 KST [6536-2] DEBUG:  checkpoint record is at 0/176D910
2015-09-22 11:01:35 KST [6536-3] DEBUG:  redo record is at 0/176D910; shutdown TRUE
2015-09-22 11:01:35 KST [6536-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 11:01:35 KST [6536-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 11:01:35 KST [6536-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 11:01:35 KST [6536-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 11:01:35 KST [6536-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 11:01:35 KST [6536-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 11:01:35 KST [6536-10] LOG:  record with zero length at 0/176D970
2015-09-22 11:01:35 KST [6536-11] LOG:  redo is not required
2015-09-22 11:01:35 KST [6536-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 11:01:35 KST [6536-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 11:01:35 KST [6536-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 11:01:35 KST [6540-1] LOG:  autovacuum launcher started
2015-09-22 11:01:35 KST [6129-5] LOG:  database system is ready to accept connections

2015-09-22 11:01:35 KST [6129-2] LOG: server process (PID 6345) was terminated by signal 9: Killed

2015-09-22 11:01:35 KST [6129-3] LOG: terminating any other active server processes

2015-09-22 11:01:35 KST [6135-2] WARNING: terminating connection because of crash of another server process

2015-09-22 11:01:35 KST [6135-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2015-09-22 11:01:35 KST [6135-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.

2015-09-22 11:01:35 KST [6129-4] LOG: all server processes terminated; reinitializing

2015-09-22 11:01:35 KST [6536-1] LOG: database system was interrupted; last known up at 2015-09-22 10:57:06 KST

2015-09-22 11:01:35 KST [6536-2] DEBUG: checkpoint record is at 0/176D910

2015-09-22 11:01:35 KST [6536-3] DEBUG: redo record is at 0/176D910; shutdown TRUE

2015-09-22 11:01:35 KST [6536-4] DEBUG: next transaction ID: 0/725; next OID: 16387

2015-09-22 11:01:35 KST [6536-5] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0

2015-09-22 11:01:35 KST [6536-6] DEBUG: oldest unfrozen transaction ID: 711, in database 1

2015-09-22 11:01:35 KST [6536-7] DEBUG: transaction ID wrap limit is 2147484358, limited by database with OID 1

2015-09-22 11:01:35 KST [6536-8] LOG: database system was not properly shut down; automatic recovery in progress

2015-09-22 11:01:35 KST [6536-9] DEBUG: resetting unlogged relations: cleanup 1 init 0

2015-09-22 11:01:35 KST [6536-10] LOG: record with zero length at 0/176D970

2015-09-22 11:01:35 KST [6536-11] LOG: redo is not required

2015-09-22 11:01:35 KST [6536-12] DEBUG: resetting unlogged relations: cleanup 0 init 1

2015-09-22 11:01:35 KST [6536-13] LOG: checkpoint starting: end-of-recovery immediate

2015-09-22 11:01:35 KST [6536-14] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s

2015-09-22 11:01:35 KST [6540-1] LOG: autovacuum launcher started

2015-09-22 11:01:35 KST [6129-5] LOG: database system is ready to accept connections

클라이언트와 접속중인 프로세스라서 kill -9를 해도 상관이 없다고 하겠지만 전혀 그렇지 않습니다. 위의 경우에는 데이터가 아무것도 없어서 금방 재시작이 되었지만 아주 바쁜 서버의 경우에, 다시 말해서 트랜잭션이 많은 서버에서는 재시작하는데도 상당히 오랜시간이 걸릴 수 있습니다.

만약 꼭 쿼리를 죽여야겠다고 한다면 psql 명령어로 PostgreSQL에 접속한 후에 pg_terminate_pid 함수를 이용해서 해야 합니다. 이 함수로 인해서 프로세스가 죽더라도 서버가 재시작되는 일은 거의 없습니다.

08/29/2015

읽기전용 PostgreSQL 튜닝.

이 문서는 읽기전용 PostgreSQL 서버를 대상으로 하는 튜닝 경험에 대해서 정리한 것입니다.

환경

테스트된 서버 환경은 다음과 같습니다.

CPU: Inter(R) Xeon(R) CPU E5606@2.13GHz * 2
RAM: 32GB
HDD: SAS 450GB
OS: Linux
PostgreSQL: 9.2

이 서버는 하루에 한번 덤프된 PosgreSQL 데이터를 전송받아서 Restore 하고 읽기전용, 그러니까 SELECT 쿼리만 사용 합니다.

Linux 튜닝

리눅스 튜닝에 핵심은 공유메모리, 최대 열수 있는 파일수, 메모리관련 커널 파라메터들 입니다. 이는 최대 물리 메모리에 영향을 받습니다.

공유메모리

리눅스의 최대 공유메모리는 다음과 같은 계산으로 세팅이 가능합니다. 단, 다음의 공식은 절대적인 기준은 아니며 PostgreSQL 서버에만 적용됩니다. 인터넷 상에는 다양한 공식들이존재하지만 이 공식으로 나온 값만으로도 충분히 잘 동작하였습니다.

_SHMALL=$(expr $(getconf _PHYS_PAGES) \* 30 / 100)
_SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)

1 2	_SHMALL=$(expr $(getconf _PHYS_PAGES) \* 30 / 100) _SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)

32GB 물리 메모리를 가진 서버의 경우에 다음과 같이 리눅스 커널의 최대 공유메모리를 설정할 수 있습니다.

kernel.shmall = 2470023
kernel.shmmax = 10117214208

1 2	kernel.shmall = 2470023 kernel.shmmax = 10117214208

swappiness

데이터베이스 시스템은 고속으로 동작되어야 함으로 스왑 파티션을 사용을 자제해야 합니다. 아예 사용을 하지 않도록 리눅스를 설정합니다.

vm.swappiness = 0

1	vm.swappiness = 0

Maximum number of open files

최대 열기 파일 설정은 기본적인 튜닝입니다. 대부분 다음과 같이 설정을 하면 충분합니다.이는 메모리가 크면 클수록 높여줄 수 있습니다.

postgres        soft      nofile        65535
postgres        hard      nofile        65535

1 2	postgres soft nofile 65535 postgres hard nofile 65535

리눅스 커널은 다음과 같이 설정할 수 있습니다.

fs.file-max = 65535

1	fs.file-max = 65535

IPCS

ipcs 명령어는 PIC(Process Internal Communication) 에 정보를 볼 수 있습니다. 여기서 중요한 것은 세마포어가 중요합니다. 세마포어는 동시 프로세스를 제어하는데 중요한 요소 입니다.

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

------ Semaphore Limits --------

max number of arrays = 128

max semaphores per array = 250

max semaphores system wide = 32000

max ops per semop call = 32

semaphore max value = 32767

기본 설정으로도 좋습니다.

kernel version

커널은 3.13 이상을 사용하는 것이 좋습니다. 왜냐하면 3.13 버전에서 아주 크게 Disk I/O 가 향상되었기 때문입니다. PostgreSQL 의 데이터도 결국에는 하드디스크에 저장이 됩니다. 더우기 SELECT 전용으로 사용한다면 데이터를 하드디스크에서 자주 읽어들일겁니다. 따라서 커널 버전을 3.13 이상을 사용해야 합니다.

PostgreSQL 튜닝.

PostgreSQL 튜닝의 핵심은 접속자와 메모리 입니다. 주요 핵심 파라메터는 다음과 같습니다.

name	context	shor_desc
default_statistics_target	user	Sets the default statistics target.
maintenance_work_mem	user	Sets the maximum memory to be used for maintenance operations.
constraint_exclusion	user	Enables the planner to use constraints to optimize queries.
checkpoint_completion_target	sighup	Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_size	user	Sets the planner's assumption about the size of the disk cache
work_mem	user	Sets the maximum memory to be used for query workspaces.
wal_buffers	postmaster	Sets the number of disk-page buffers in shared memory for WAL.
checkpoint_segments	sighup	Sets the maximum distance in log segments between automatic WAL checkpoints.
shared_buffers	postmaster	Sets the number of shared memory buffers used by the server.

context 부분을 보면 그 값을 적용하기 위해서 무엇을 해야하는지 알려줍니다. user 부분은 PostgreSQL 를 재시작할 필요가 없습니다. 값을 변경하면 바로 그 값이 적용이 되며 오직 사용자 세션에게만 적용됩니다. 다른 사용자 세션에는 적용되지 않습니다. sighup 은 PostgreSQL 서버를 reload 해주거나 HUP Kill 시그널을 주면 적용됩니다. postmaster 는 반드시 PostgreSQL 서버를 재시작해줘야 적용이 됩니다.

default_statistics_target

이것은 통계와 관련이 있습니다. PostgreSQL 쿼리 플래너는 개별 테이블에 통계를 필요로 합니다. 만약 이 값이 적다면, 쿼리 플래너의 결과가 달라집니다. 대신 이 값이 너무 높으면 PostgreSQL 는 개별테이블의 통계를 수집하는데 많은 시간을 사용하게 됩니다.

만약 ‘LIKE’ 쿼리를 자주 사용한다면, 이값을 증가시켜줄 필요가 있습니다. 하지만 기본적인 값으로도 괜찮습니다.

maintenance_work_mem

maintenance 연산은 ‘CREATE INDEX, ALTER TABLE ADD FOREIGN KEY’ 그리고 ‘VACUUM’ 와 깊이 연관이 있습니다. SELECT 전용으로만 사용한다면 VACUUM은 필요가 없습니다. 하지만 덤프 파일을 Restore 할때에 인덱스를 생성하거나 ALTER TABLE 를 많이 사용되게 됩니다.

따라서 만약, 덤프 파일을 가지고 빠른 Restore 를 하고 싶다면 이 값을 조정해줘야 합니다. 단, 이 값은 work_mem 보다 항상 높아야 합니다. 그리고 이 값은 autovacuum_max_workers 값과 관련이 높습니다.

통상적으로 이값은 전체 메모리의 5% 정도로 잡습니다. 만약 32GB 라면 이값은 1600MB 입니다.

maintenance_work_em = 1600MB

1	maintenance_work_em = 1600MB

constraint_exclusion

이 값은 파티션 테이블과 관련이 깊습니다. 만약 파티션 테이블을 사용한다면, 반드신 설정을 해줘야 합니다. 성능향상을 위해서 상속이나 파티션된 테이블을 자주 사용됩니다.

checkpoint_completion_target

0.9 가 기본값인데, 대부분 괜찮습니다.

effective_cache_size

이 크기는 리눅스 시스템에서 모든 애플리케이션을 끈 상태에서 cache 메모리 크기를 봐야 합니다. 이 크기만큼 보통 할당을 합니다. 예를들어 리눅스 캐쉬 사용량이 22GB 라고한다면 이값을 22GB 정도 주면 됩니다.

이렇게 값을 준다고해서 실제로 메모리가 할당이 되지 않습니다. 쿼리 플래너가 작업을 할때에 잠깐사용 합니다. 만약 이를 값이 적다면 리눅스는 파일 시스템을 사용하게 되어서 성능이 저하됩니다.

work_mem

이 메모리는 복잡한 sort가 많이 사용되거나 큰 sort 가 필요하다면 이 값을 증가 시켜줄 필요가 있습니다. 한가지 조심해야 할 것은 이 값은 각 사용자 세션별로 할당이 됩니다. 따라서 이 값을 정할때에 PostgreSQL 의 최대 접속자를 고려해야 합니다.

이 값을 대략 다음과 같이 계산을 합니다.

( OS cache memory / connections ) * 0.5

만약 OS cache memory 사용량이 28GB 이거나 접속량이 20 이라면 다음과 같습니다.

(28/20)*0.5 = 700MB

shared_buffers

전체 메모리의 25% 정도가 적당합니다. 만일 32GB 메모리를 사용한다면 대략 8GB 정도면 충분합니다.

결론

정리를 한다면 다음과 같습니다.

/etc/sysctl.conf

kernel.shmall = 2470023
kernel.shmmax = 10117214208
vm.swappiness = 0
kernel.sched_migration_cost_ns = 5000000
fs.file-max = 65535

kernel.shmall = 2470023

kernel.shmmax = 10117214208

vm.swappiness = 0

kernel.sched_migration_cost_ns = 5000000

fs.file-max = 65535

/etc/security/limits.conf

postgres        soft    nofile           65535
postgres        hard    nofile           65535

1 2	postgres soft nofile 65535 postgres hard nofile 65535

postgresql.conf

default_statistics_target = 100 
maintenance_work_mem = 4GB 
constraint_exclusion = on 
checkpoint_completion_target = 0.9 
effective_cache_size = 16GB 
work_mem = 700MB 
wal_buffers = 8MB 
checkpoint_segments = 16 
shared_buffers = 8GB

default_statistics_target = 100

maintenance_work_mem = 4GB

constraint_exclusion = on

checkpoint_completion_target = 0.9

effective_cache_size = 16GB

work_mem = 700MB

wal_buffers = 8MB

checkpoint_segments = 16

shared_buffers = 8GB

07/12/2015

튜플(Tuple) 과 백쿰(VACUUM)

PostgreSQL 은 다른 데이터베이스 시스템은 테이블(Table)에 데이터를 레코드(Record) 혹은 로우(Row)라고 한다. 그런데, PostgreSQL 에서는 튜플(Tuple)이라는 것이 존재합니다. 이 튜플은 PostgreSQL 에서 매우 중요한 요소 입니다. 이에 대해서 간략하게 알아 보도록 하겠습니다.

통계정보 확보.

먼저 이 튜플을 알기 위해서는 PostgreSQL 이 제공하는 통계정보를 알아 볼 필요가 있다. 이 통계정보에는 로우 갯수와 튜플 갯수등을 볼 수 있고 로우에 변화에 따라서 이들이 어떻게 변하는지를 추적할 수 있으며 이를 토대로 튜플이 과연 무엇인지를 알 수 있게 된다.

샘플 테이블을 만들자.

testdb2=> CREATE TABLE tuple_test (
id NUMERIC NOT NULL,
rand_num NUMERIC NOT NULL,
birth DATE,
CONSTRAINT id_pk PRIMARY KEY (id)
);

testdb2=> CREATE TABLE tuple_test (

id NUMERIC NOT NULL,

rand_num NUMERIC NOT NULL,

birth DATE,

CONSTRAINT id_pk PRIMARY KEY (id)

);

그리고 샘플 데이터를 입력한다.

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1, 1000);

1	testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()9000 + 10000)::int, CURRENT_DATE - (random() 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1, 1000);

1000 로우를 입력된다.

통계정보를 통해서 튜플의 상태를 확인해보자.

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1000 |       1000 |          0 |          100.00 |            0.00 | 120 kB         | 48 kB
(1 row)

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1000 | 1000 | 0 | 100.00 | 0.00 | 120 kB | 48 kB

(1 row)

테스트

튜플의 개수는 로우 개수와 같다. 데이터 입력이 들어오면 튜플의 개수는 입력되어 들어온 만큼 증가 한다.

그럼 튜플이 뭔지를 보여주는, 특성을 볼수 있는 실험을 해보자. 대부분의 RDBMS 에서는 트랜잭션을 지원한다. 이 트랜잭션의 결과는 COMMIT 아니면 ROLLBACK 이다. COMMIT이 되면 트랜잭션에서 했던 작업이 확정되어 그로 인해서 발생된 결과물은 실제 데이터로 고정된다. ROLLBACK 은 트랜잭션에서 했던 작업일 모두 취소하는 것으로 실제 데이터로 고정되지 않고 삭제처리된다. PostgreSQL 도 트랜잭션을 지원하기 때문에 동작방법은 동일하다.

testdb2=> BEGIN;
testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1001, 1100);
testdb2=> COMMIT;
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1100 |       1100 |          0 |          100.00 |            0.00 | 120 kB         | 48 kB
(1 row)

testdb2=> BEGIN;

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1001, 1100);

testdb2=> COMMIT;

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 120 kB | 48 kB

(1 row)

COMMIT 후에는 확인결과 100개의 로우가 INSERT 되었고 튜플도 그만큼 늘었다. 그럼 ROLLBACK 도 테스트 해보자.

testdb2=> BEGIN;
BEGIN
testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1101, 1200);
INSERT 0 100
testdb2=> ROLLBACK;
ROLLBACK
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1200 |       1100 |        100 |           91.00 |            8.00 | 136 kB         | 56 kB
(1 row)

testdb2=> BEGIN;

BEGIN

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1101, 1200);

INSERT 0 100

testdb2=> ROLLBACK;

ROLLBACK

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1200 | 1100 | 100 | 91.00 | 8.00 | 136 kB | 56 kB

(1 row)

총 튜플 수가 증가했다. 그런데, dead_tup 에 100 이 있고 live_tup 에는 1100 으로 로우 개수와 동일하다. 또, 이전과 비교해서 테이블의 용량이 모두 증가했다. 트랜잭션을 롤백해서 실제 데이터가 반영이 안되었음에도 불구하고 어째서 테이블 용량이 증가하고 총 튜플수가 증가하는 걸까?

이번에는 다음과 같이 간단하게 UPDATE 문을 실행해보자.

testdb2=> UPDATE tuple_test SET rand_num = rand_num + 1;
UPDATE 1100
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  2300 |       1100 |       1200 |           47.00 |           52.00 | 232 kB         | 104 kB
(1 row)

testdb2=> UPDATE tuple_test SET rand_num = rand_num + 1;

UPDATE 1100

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 2300 | 1100 | 1200 | 47.00 | 52.00 | 232 kB | 104 kB

(1 row)

데이터를 추가한것이 아닌 그냥 기존의 내용을 업데이트 했을 뿐인데, 총 튜플 수가 증가했다. 거기에 dead_tup 의 값도 증가했다. live_tup 만이 실제 데이터의 양이 바뀌지 않았음을 말해주고 있다. 데이터 용량을 말해주는 rel_size 는 저장되는 데이터의 증가와도 관련있어서 증가할 수도 있지만 그 증감이 너무나 크다.

도대체 왜 이러는 걸까? 왜 UPDATE, 트랜잭션의 ROLLBACK 시에 튜플이 증가하고 데이터 저장용량도 증가하는 걸까?

Dead Tuple 과 VACUUM FULL

결론부터 말하자면 MVCC 때문이다. 데이터베이스가 동시접속자 환경에서 데이터 일관성을 유지하기 위한 기법으로 ‘다중버전동시성제어’ 를 사용한다. PostgreSQL에서 트랜잭션이 발생하면 PostgreSQL 은 지금 있는 로우와 똑같은 것을 한번더 복제한다. 내부적으로는 테이블의 row 가 두가지가 생기는것이고 이는 두가지 버전이라고 생각하면 쉽다. 기존의 row 는 아직 COMMIT되기 이전에 사용자들이 보는 용도로, 복제된 또다른 버전의 row 는 트랜잭션 처리용으로 사용되어지게된다. 마치 스냅샷의 여러 버전들처럼 PostgreSQL은 row 에 다야한 버전을 생성하는데, 이것이 바로 튜플이다.

그럼 UPDATE 할때는 어떻게 될까? 위 예제에서는 데이터가 얼마 없어서 순식간에 UPDATE 끝났지만 아주 큰 데이터가 있을경우에는 시간이 걸린다. 그렇다고 SELECT 사용자에게 기다리고 할 순 없어서 PostgreSQL은 row 를 또 다른 버전을 만들어 UPDATE를 실행하고 완료되면 이전 버전은 삭제처리하게 된다.

그래서 UPDATE 시에 보면 dead_tup 의 갯수가 이전 양인 100 에서 전체 row 갯수만큼 1100이 증가된 것이다. PostgreSQL에서 튜플은 바로 이런 것이다. 그런데 여기서 문제가 있다.

UPDATE, ROLLBACK 이 완료되면 이전 버전의 row 들은 실제 활용하는 데이터가 아니기 때문에 하드디스크에 남아있으면 안된다. 하지만 PostgreSQL은 실제 삭제처리를 하지않고 사용자들에게 보이지 않도록만 한다. 튜플에 dead 라고 마킹(marking)만 하고 끝내는 것이다. DELETE 시에도 이렇게 실제데이터는 지우지 않고 Dead Tuple 로 처리된다. 그런데 SELECT를 할때는 어떻게 할까? PostgreSQL 엔진이 데이터 파일에서 데이터를 읽어들일때 이것이 dead 튜플인지 live 튜플인지 분간할 수 없다. 그냥 데이터 크기만큼 읽어들인다. 쉽게 말해서 Dead tuple 도 읽는다는 거다. 이거는 대단히 불합리하고 SELECT 성능에 영향을 미칠 수 밖에 없다.

튜플중에서 Dead Tuple 이 많으면 SELECT 성능이 떨어진다. Row 가 1개 인데, rel_size 용량이 수십GB 일수도 있다. 그렇게되면 PostgreSQL이걸 다 읽어보게되고 실행시간이 길어진다.

그렇다면 Dead Tuple 을 없앨려면 어떻게 해야할까? VACUUM FULL 을 한번 돌려주면 된다. VACUUM은 Dead Tuple 을 삭제처리 해준다. 이는 UPDATE, DELETE, ROLLBACK 이 많은 서버에 경우 반드시 VACUUM FULL을 해줘야 한다는 것을 뜻한다.

testdb2=> vacuum FULL verbose tuple_test;
INFO:  vacuuming "public.tuple_test"
INFO:  "tuple_test": found 1200 removable, 1100 nonremovable row versions in 12 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
VACUUM
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1100 |       1100 |          0 |          100.00 |            0.00 | 136 kB         | 48 kB
(1 row)

testdb2=> vacuum FULL verbose tuple_test;

INFO: vacuuming "public.tuple_test"

INFO: "tuple_test": found 1200 removable, 1100 nonremovable row versions in 12 pages

DETAIL: 0 dead row versions cannot be removed yet.

CPU 0.00s/0.00u sec elapsed 0.03 sec.

VACUUM

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 136 kB | 48 kB

(1 row)

“tuple_test”: found 1200 removable 이 문장이 Dead Tuple 삭제가 가능하다는 것을 말하는 것이다. 실제 데이터 양(rel_size) 도 줄었다.

07/12/2015

Better VACCUM FULL For PostgreSQL 9.0

원문:https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.0#Better_VACUUM_FULL

지금까지 VACUUM FULL 은 매우 느렸다. 이 구문(Statement)는 테이블로부터 빈공간을 확보하고 그것의 크기(Size)를 줄였지만 VACUUM 은 만족할만큼 빠르게 동작하지 않았다.

이것이 느린 이유는 동작 방법에 문제가 있었기 때문이다. 레코드를 그들의 소스블럭으로부터 테이블에 시작점에 가까운 블럭으로 하나하나씩 읽어서 옮겼었다. 그리고 테이블에 끝이 빈공간이면 그 부분을 지웠다.

전략적으로 이러한 방법은 매우 비효율적(Inefficient) 이다. 레코드를 하나하나씩 옮기는 것은 랜덤 I/O를 발생시킨다. 게다가, 인덱스(Index)를 재구성하는 동안에 그것을 유지하고, 더 많은 비용이 들어가는, 인덱스는 파편화된다. 이럴바에는 VACUUM FULL이 다 끝나고 나서 재인덱스(reindex)를 하는 것이 낫다.

Version 9.0 에서 VACUUM FULL은 테이블과 동일한 또 하나의 테이블을 만들고 거거에 모든 레코드를 순차적이게 복사한다. 모든 레코드가 복사되고 나면, 인덱스를 재생성하고 예전 테이블은 삭제되고 새로운 테이블로 대체된다.

이것은 VACUUM 을 보다 빠르게 한다. 하지만 VACUUM FULL은 동작하는 동안에 AccessExclusiveLock 을 필요로 한다. 이전 방법과 비교했을때 이 방법의 결점은 VACUUM FULL을 했을 경우 새로운 버전의 테이블을 만들기 위해서 디스크에 테이블 크기의 2배의 용량을 사용할 수 있다는 것이다.

이제, 두가지 방법에 대해서 실시간으로 비교를 해보자. 먼저 아래와 같은 방법으로 테스트 데이터를 준비 했다.

marc=# CREATE TABLE test (a int); 
CREATE TABLE 
marc=# CREATE INDEX idxtsta on test (a); 
CREATE INDEX 
marc=# INSERT INTO test SELECT generate_series(1,1000000); 
INSERT 0 1000000 
marc=# DELETE FROM test where a%3=0; -- making holes everywhere 
DELETE 333333 
marc=# VACUUM test; 
VACUUM

marc=# CREATE TABLE test (a int);

CREATE TABLE

marc=# CREATE INDEX idxtsta on test (a);

CREATE INDEX

marc=# INSERT INTO test SELECT generate_series(1,1000000);

INSERT 0 1000000

marc=# DELETE FROM test where a%3=0; -- making holes everywhere

DELETE 333333

marc=# VACUUM test;

VACUUM

8.4 버전에서는.

marc=# \timing 
Timing is on. 
marc=# VACUUM FULL test; 
VACUUM 
Time: 6306,603 ms 
marc=# REINDEX TABLE test; 
REINDEX 
Time: 1799,998 ms

marc=# \timing

Timing is on.

marc=# VACUUM FULL test;

VACUUM

Time: 6306,603 ms

marc=# REINDEX TABLE test;

REINDEX

Time: 1799,998 ms

다 합해 대충 9초 정도 걸렸다.

9.0 버전에서는.

marc=# \timing 
Timing is on. 
marc=# VACUUM FULL test; 
VACUUM 
Time: 2563,467 ms

marc=# \timing

Timing is on.

marc=# VACUUM FULL test;

VACUUM

Time: 2563,467 ms

아직 이것은 제품에서(아마 서비스중에라는 뜻인듯..) VACUUM FULL 이 좋은 아이디어라는 뜻은 아니다. 만약 이것이 필요하다면, 아마도 VACUUM 정책이 올바르지 않기 때문일 것이다. (응?)

07/12/2015

명시적 락킹(Explicit Locking)

이 문서는 PostgreSQL 9.1.2 문서중에 13.3 챕터를 해석한 것입니다. 전문 번역가도 아니고 중학교 영어만으로 해석한 것이어서 의미전달이 제대로 되지 않을 수도 있음을 미리 밝힙니다.

원문: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

13.3 명시적 락킹(Explicit Locking)

PostgreSQL 은 테이블의 데이터에 동시적 접근(concurrent access)을 제어하기 위해서 다양한 락 모드(Lock mode)를 제공합니다. 이러한 모드들은 MVCC(다중 버전닝 동시성 제어)가 바라던 해동을 제공해주지 않을때에 애플리케이션 제어 락킹을 하려고 할때에 사용되어질수 있습니다. 또 대부분의 PostgreSQL 명령어들(commands)은 명령어가 실행되는 동안에 비호환적인 방법으로 참조하는 테이블이 변경되거나 버려지진(Dropped)않도록 하기위해서 자동적으로 적절한 락을 획득합니다. (예를들어, TRUNCATE는 같은 테이블에서 다른 연산들을 가지고 안전하게 동시에 실행되어지지 않을 수 있습니다. 그래서 안전성을 획득하기 위해서 강정적으로 테이블에 배타적 락(Exclusive Lock)을 얻얼 수 있습니다.)

13.3.1 테이블 수준의 락들(Table-Level Locks)

아래의 리스트는 PostgreSQL에 의해서 자동적으로 사용되어지는 사용가능한 락 모드와 문맥(contexts)들을 보여줍니다. 당신은 LOCK 이라는 명령어를 통해서 명시적으로 이러한 락들을 획득할 수 있습니다. 한가지 주목할 것은, 비록 이름에 “row”라고 되어있어도 테이블 수준의 락들(Table-Level Locks)이라는 것입니다. 락 모드의 이름들은 역사적입니다.

몇몇 확장된(extent) 이름들은 각 락 모드마다 전통적인 사용법과 반대적입니다. 하지만 의미론적으로는 모두 같습니다. 하나의 락 모드와 다른 락과의 차이에 대한 한가지 사실은 각각 상충되는 락 모드가 설정된다는 것입니다. 두개의 트랜잭션(transaction)은 같은 시점에 같은 테이블에서 상충적인 락 모드(locks of conflicting, 서로 모순되는 락 모드) 를 가질 수 없습니다. (하지만, 하나의 트랙잭션은 그 자체적으로 출돌이 나지 않습니다. 예를 들면, 같은 테이블에서 ACCESS EXCLUSIVE LOCK을 획득하고 후에 ACCESS SHARE LOCK를 획득할 수 있습니다. ) 상충적이지 않은 락 모드는 많은 트랜잭션에 의해서 동시적으로 가질 수 있습니다. 주목할 것은 특정 상황에서 어떤 락 모드는 자체 상충적(self-conflicting)이 됩니다. (예를들어, ACCESS EXCLUSIVE LOCK는 같은 시점에서 하나의 트랜잭션보다 더 많이 가질 수 없습니다.)

Table-level Lock Modes

ACCESS SHARE

오직 ACCESS EXCLUSIVE 락 모드와 상충적입니다.

SELECT 명령어는 참조하는 테이블에 이 모드의 락을 획득합니다. 일반적으로, 테이블에서 수정은 하지 않고 오직 읽기만을 하는 모든 쿼리들은 이 락 모드를 획득합니다.

ROW SHARE

이것은 EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

SELECT FOR UPDATE 와 SELECT FOR SHARE 명령어들은 대상 테이블에서 이 모드의 락을 획득합니다. (추가적으로 ACCESS SHARE 는 selected FOR UPDATE/FOR SHARE 가 아닌 참조되어지는 다른 모든 테이블을 락 합니다.

ROW EXCLUSIVE

이것은 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

UPDATE, DELETE, INSERT 명령어는 대상 테이블에서 이 락 모드를 획득합니다. (추가적으로 ACCESS SHARE 는 다른 참조되어지는 모든 테이블을 락 합니다. 일반적으로, 이 모드는 테이블에서 데이터를 수정하는 모든 명령어들에 의해서 이 모드는 획득되어 집니다.

SHARE UPDATE EXCLUSIVE

SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 VACUUM 실행과 동시적 스키마 변화에 대해서 테이블을 보호합니다.

VACUUM( FULL 제외), ANALYZE, CREATE INDEX CONCURRENTLY, 그리고 특정한 ALTER TABLE 폼에 의해서 획득됩니다.

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE 그리고 ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 동시적으로 데이터를 변경하는 것에 대해 테이블을 보호 합니다.

CREATE INDEX(CONCURRENTLY 제외) 에 의해서 획득되어 집니다.

SHARE ROW EXCLUSIVE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 동시적 데이터 변경과 특정시점에서 한 세션이 그것을 가지고 있기위해서 자체 exclusive(self-exclusive) 한 것에 대해서 테이블을 보호 합니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 자동적으로 획득되어지지 않습니다.

EXCLUSIVE

ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 오직 동시적 ACCESS SHARE 락만을 허용합니다. 예를들어, 테이블로부터 읽을때에 한 트랜잭션이 이 락 모드로 홀딩되면 다중으로 실행을 할수 있습니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 테이블에 자동적으로 획득되어지지 않습니다.

ACCESS EXCLUSIVE

모든 모드의 락과 상충됩니다. 이 모드는 어떤 방법으로든 트랜잭션이 테이블에 접근하는 것을 보장합니다.

ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 그리고 VACUUM FULL 명령어에 의해서 획득되어 집니다. 또, 이 모드는 LOCK TABLE 구문에 대한 기본적인 락 모드 입니다.

Tip: 오직 ACCESS EXCLUSIVE 는 SELECT 구문을 블럭하는 락입니다.

한번 획득되어진 락은 트랜잭션이 끝날때가지 들고 있습니다. 하지만 만약에 저장시점이 설립된 이후에 락이 획득되어진 것이라면, 저장시점이 롤백되는 즉시 해제 됩니다. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

07/12/2015

외부에서 Postgresql 접속할 수 있도록 설정하기

Postgresql 설치를 하게되면 외부에서 접속을 기본적으로 할 수 없게 되어 있다. 호스트 기반 인증 파일인 pg_hba.conf 파일만 고친다고 되지 않는다. 외부에서 접속하는 서버의 IP를 192.168.0.18 이라고 가정한다.

pg_hba.conf 파일

Postgresql 은 Host Based Authorization 기반으로 외부 접속을 제어 한다. 이는 pg_hba.conf 파일을 다음과 같이 편집함으로써 가능하다.

host    all             all             192.168.0.18/32           md5

1	host all all 192.168.0.18/32 md5

위 설정은 모든 데이터베이스에 대해서 모든 사용자에 대해서 192.168.0.18 에서 접속을 허용한다는 내용이다.

postgresql.conf 파일

이 파일도 반드시 설정을 바꿔줘야 외부에서 접속을 할 수 있다. 바꿔야 할 설정은 다음과 같다.

listen_addresses = '*'                  # what IP address(es) to listen on;

1	listen_addresses = '*' # what IP address(es) to listen on;

이걸하지 않하면, 외부에서 접속을 할 수 없다. 그리고 이 설정은 반드시 서버를 재시작 해줘야 한다. 따라서 보통 Postgresql 을 설치를 하면 기본적으로 이 설정을 해주고, pg_hba.conf 파일에서 모두 외부접속을 제어한다.

iptables 설정

보통 많은 사람들이 iptables 를 내려놓고 서버를 운영하는데, 매우 위험한 짓이다. 이를 내려놓고 하는 이유는 귀찮기 때문이다. 뭔가를 하는데 자꾸 않되고 하다가 찾다보면 결국 iptables 때문인걸 알게 되면 매우 화가나고 다음부터 이를 피하기위해서 iptables 를 꺼놓는다. 바보같은 짓거리에 서버 관리자로서 자질도 의심되는 행동이다.

Postgresql 을 위한 iptables 설정은 다음과 같다. 참고로 배포판에 rpm 으로 설치된 iptables 를 이용하는 방법이다.

]# vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW,ESTABLISHED -m tcp -p tcp --dport 5432 -j ACCEPT

1 2	]# vi /etc/sysconfig/iptables -A INPUT -m state --state NEW,ESTABLISHED -m tcp -p tcp --dport 5432 -j ACCEPT

이렇게 한 후에 iptables 를 재시작 해주면 된다.

07/12/2015

PostgreSQL 과 문자셋

PostgreSQL 도 문자셋에 관해서 많은 옵션들을 제공한다. 그런데, 대부분은 이에 대해서 잘 모르는 듯해서 여기서 정리해 본다.

PostgreSQL 에서 문자셋 지정을 처음 하는 부분은 바로 설치를 마친후에 initdb 명령어를 사용하면서 부터다 대충 다음과 같이 사용한다.

su -l postgres -c "/usr/local/pgsql9.1/bin/initdb -E UTF8 --locale=ko_KR.UTF-8 --pgdata='$PGDATA' "

1	su -l postgres -c "/usr/local/pgsql9.1/bin/initdb -E UTF8 --locale=ko_KR.UTF-8 --pgdata='$PGDATA' "

문제는 저러한 문제셋 설정이 과연 향후 PostgreSQL 을 사용하는데 있어 어떤 영향을 주는가 하는 것이다. 먼저 PostgreSQL 은 ISO C 와 POSIX 등의 언어표현에 관해 지원 한다.

Locale

보통 initdb –locale=ko_KR.UTF-8 로 사용되어지는 것으로 운영체제에 종속적이다. 운영체제에서 지원하는 locale 만 사용할 수 있는데, 리눅스의 경우에는 ‘locale -a’ 명령어로 확인 가능하다.

이는 운영체제의 사용자에게 보여주는 메시지 문자를 지정한다. 만일 영어로 운영체제를 사용하고 싶다면 바로 이 locale 를 변경하면 된다. 그런데, locale 의 설정은 ‘ko_KR.UTF-8’ 처럼 나오는데 이는 language_territory.codeset 형태이다.

language 는 인간의 사용하는 언어이고 territory 는 ‘한 국가가 다스리는 영토, 지역’을 뜻한다. 예를들어서 fr_CA 도 있는데 이는 캐나다에서 사용하는 프랑스어라는 뜻이 된다. codeset 은 이러한 언어를 컴퓨터 언어로 표현하는 문자 셋이다.

이러한 형태가 나온 이유는 한 국가(영토, 지역)에서 두가지 이상의 언어를 사용할 경우를 대비한 것으로 풀이된다.

LC_COLLATE

이는 매우 중요한 것으로 다음과 같은 것에 영향을 미친다.

대소문자를 구분하는 기능.
문자열 정렬
‘like’ 문에서 인덱스를 사용여부 결정

이는 데이터베이스를 생성할때에도 지정할 수가 있는데, 지정하는 방법은 locale 과 같이 language_territory.codeset 형식이지만 codeset 은 생략하는 경우가 있다. 다음과 같은 명령어로 확인 가능하다.

=# show LC_COLLATE;

1	=# show LC_COLLATE;

문제는 이 LC_COLLATE 는 initdb 시에 한번 결정이 되면 바꿀 수 없고 하지만, Template 을 1번이 아닌 0번으로 할 경우에는 가능하다. Template1 은 initdb 때 생성되는 일종의 Master DB라 보면되고 모든 데이터베이스는 이 Template1 을 기반으로 생성되어진다.

LC_CTYPE

이는 문자의 범주를 정하는 것으로 각 언어마다 가지고 있는 고유한 특성을 타나내기도 한다. 예를들면 단어가 무엇인지, 대문자와 소문자,one byte 문자인지 multi byte 문자인지에 대한 정의등에 대한 것이다. 이는 initdb 실에 결정되면 바꿀 수 없다.

대부분 Locale 을 따라 지정하는게 낫다.

LC_MESSAGE

이는 접속한 클라이언트에게 어떤 언어로 보여줄지를 결정한다.

LC_MONETARY

이는 접속한 클라이언트에게 어떤 화폐단위로 보여줄 것인지를 결정한다.

LC_NUMERIC

이는 접속한 클라이언트에게 어떤 숫자단위로 보여줄 것인지를 결정한다.

LC_TIME

이는 접속한 클라이언트에게 날짜와 시간에 대한 포맷을 결정한다.

03/04/2015

[번역]레디스 슬레이브에서 “놓쳐버린” 키들.

이 문서는 다음의 글을 번역한 것입니다.

“Missing” Keys on Redis Slaves

레디스 슬레이브에서 “놓쳐버린” 키들.

만약 당신이 레디스(Redis)에서 만료중인 키를(expiring key)(“임시적인 키”로 알려진) 사용하고 있다면, 당신은 레디스 마스터에서 새로운 슬레이브(Slave)를 붙였을때에 놀랄 수도 있습니다. 슬레이브에서 키 갯수(key count)가 마스터에서 키 갯수보다 약 25%정도 낮을 것입니다. 이것은 임시적인 키(volatile key)가 아주 많다면 특별하게도 정상적인 것입니다.

레디스 슬레이브가 키를 놓쳐버린 걸까? 단순하게 데이터를 잃어버린 걸까? 결론부터 말하자면 “아니오” 입니다. 그러나, 왜그런지 이해하길 희망하면서, 레디스 슬레이브가 어떠한 데이터도 잃어버리지도 않았는데도 더 적은 키 갯수를 보고합니다. 이것은 두가지 세세한 구현으로부터 발생됩니다: 어떻게 레디스는 키를 만료시키고 어떻게 레디스 마스터는 새로운 슬레이브에게 데이터셋(dataset)을 보내는지하는 것

어떻게 레디스는 임시적인 키들을 만료시킬까?

레디스는 임시적인 키들(volatile keys)을 만료로 지정한 순간에 메모리로부터 삭제를 하지 않습니다. 대신에, 그들은 두가지 방법중에 하나로 삭제를 합니다.

레디스 클라이언트가 키에 읽고 쓰기 연산을 수행할때에, 레디스 서버는 첫번재로 키가 존재하고 만료 시간을 가지고 있는지를 체크 합니다. 만약 키가 존재하고 만료시간이 지난 키라면 레디스는 즉시 명령어를 처리하기 전에 메모리로부터 키를 삭제합니다.
접근이 더 이상 없는 키의 경우 메모리에 임시적인 키가 영구적으로 남아있는 것을 방지하기 위해서 레디스는 만료 키를 위해서 단순한 패시브 알고리즘을 사용합니다: 매 10ms, 100개의 랜덤으로 임시적인 키들을 추출하고 즉각 만료가 지난 모든 키들을 삭제처리합니다. 만약 25% 혹은 그 이상의 키들이 삭제되었다면, 레디스는 즉각 다른 100개의 키를 추출하고 같은 일을 반복합니다.

위의 두번째 방법은 매우 중요한데, 키의 25% 이상이 이미 만료된 임시적인 키(메모리에서 아직 삭제가 되지 않은) 될 수 있다는 것을 의미한다. 레디스는 삭제되때까지 INFO 에 “keys”와 “expires”에 이러한 키들을 계속 포함시킬 것입니다.

어떻게 레디스는 새로운 슬레이브에 데이터셋을 보낼까?

레디스 슬레이브가 레디스 마스터에 붙었을때에, 마스터 서버는 그들의 데이터셋의 RDB 스냅샷을 생성하고 그것을 슬레이브로 보냅니다. 이때 레디스는 RDB 스냅샷을 생성할때에 아직 메모리에 남아있다 할지라도 이미 만료시간이 지난 키들은 포함하지 않습니다.

그래서, 왜 나의 슬레이브의 키 갯수는 마스터의 키 갯수보다 적을까?

슬레이브가 마스터 인스턴스에 붙으면, 슬레이브는 메모리에서 삭제되지는 않았지만 만료된 임시적인 키들을 포함하지 않은 데이터셋을 받습니다. 그리고 25% 이상의 키는 메모리에서 아직 삭제되지 않았지만 만료된 키일 수 있기 때문에 슬레이브는 마스터의 25% 이상 더 적은 키를 보여줄 것입니다. 덧붙여서, 이것은 모든 키가 없어져서 RDB 백업으로 레디스 서버를 복구할때에도 똑같은 일이 벌어집니다.