Tagged: database

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 자체에는 아무런 영향을 주지 않습니다.

Creative Commons License
pg_terminate_backend, pg_cancel_backend by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

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

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

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

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

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

Creative Commons License
PostgreSQL 프로세스를 Kill 하면 발생하는 일 by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

읽기전용 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 서버에만 적용됩니다. 인터넷 상에는 다양한 공식들이존재하지만 이 공식으로 나온 값만으로도 충분히 잘 동작하였습니다.

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

swappiness

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

Maximum number of open files

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

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

IPCS

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

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

kernel version

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

 

PostgreSQL 튜닝.

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

namecontextshor_desc
default_statistics_targetuserSets the default statistics target.
maintenance_work_memuserSets the maximum memory to be used for maintenance operations.
constraint_exclusionuserEnables the planner to use constraints to optimize queries.
checkpoint_completion_targetsighupTime spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_sizeuserSets the planner's assumption about the size of the disk cache
work_memuserSets the maximum memory to be used for query workspaces.
wal_bufferspostmasterSets the number of disk-page buffers in shared memory for WAL.
checkpoint_segmentssighupSets the maximum distance in log segments between automatic WAL checkpoints.
shared_bufferspostmasterSets 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 입니다.

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

/etc/security/limits.conf

postgresql.conf

 

 

 

Creative Commons License
읽기전용 PostgreSQL 튜닝. by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

튜플(Tuple) 과 백쿰(VACUUM)

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

통계정보 확보.

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

샘플 테이블을 만들자.

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

1000 로우를 입력된다.

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

테스트

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

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

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

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

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

데이터를 추가한것이 아닌 그냥 기존의 내용을 업데이트 했을 뿐인데, 총 튜플 수가 증가했다. 거기에 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을 해줘야 한다는 것을 뜻한다.

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

Creative Commons License
튜플(Tuple) 과 백쿰(VACUUM) by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

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배의 용량을 사용할 수 있다는 것이다.

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

8.4 버전에서는.

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

9.0 버전에서는.

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

Creative Commons License
Better VACCUM FULL For PostgreSQL 9.0 by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

명시적 락킹(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 폼에 의해서 획득됩니다.

SHARE

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.

Creative Commons License
명시적 락킹(Explicit Locking) by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

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

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

pg_hba.conf 파일

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

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

postgresql.conf 파일

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

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

iptables 설정

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

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

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

Creative Commons License
외부에서 Postgresql 접속할 수 있도록 설정하기 by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

PostgreSQL 과 문자셋

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

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

문제는 저러한 문제셋 설정이 과연 향후 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

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

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

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

문제는 이 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

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

 

Creative Commons License
PostgreSQL 과 문자셋 by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

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

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

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

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

Redis Master-Slave

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

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

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

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

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

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

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

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

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

Creative Commons License
[번역]레디스 슬레이브에서 “놓쳐버린” 키들. by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

Redis 운영에 필요한 잡지식들.

Redis 는 In memory 기반으로 동작하기 때문에 메모리 관리가 매우 중요한데, 먼저 리눅스에 메모리 관리를 Redis 운영에 적합하도록 설정하는 것이 좋다.

먼저 Redis 는 Swap 을 사용하지 않고 물리 메모리 내에서만 운영하는 환경이다.

가상 메모리 overcommit 에 대해서 2 로 설정하는 것이 좋다. 2로 설정하게 되면 리눅스가 사용가능한 메모리는 다음과 같이 계산한다.

예를들어 32GB 물리 메모리를 가지고 있고 overcommit_ratio 가 50%라면  16GB 물리 메모리만 사용하게 된다. 그래서 overcommit_ratio 를 99% 로 설정을하면 모든 메모리를 사용하게 된다.

swappiness 는 0 ~ 100 사이에 값을 가진다. 0에 가까우면 메모리의 dirty page 가 있다고 하더라도 최대한 swap 을 하지 않지만 100에 가까우면 조금만 dirty page 가 있으면 swap 을 한다. Redis 를 물리 메모리만 사용하게끔 하고 싶다면 swappiness 를 0 으로 하는게 좋다.

Redis 의 save 옵션&&maxmemory-policy

Redis 는 메모리의 내용을 디스크로 보관하도록 하는 옵션이 여럿 있다. 그중에 save 옵션이 존재하는데, 다음과 같다.

위 두 조건중에 하나만 만족되도 Redis 는 메모리에 내용을 디스크에 쓰게된다. 만약, Redis 를 운영하는동안 위 조건을 하나도 만족하지 못한다면 디스크로 전혀 쓰는 행위가 발생하지 않게되고 maxmemory 에 닫게 되는 상황에서 maxmemory-policy 가 volatie-lru 로 되어 있다면 다음과 같은 상황에 직면하게 된다.

위 오류는 랜덤으로 key-value 를 생성해서 무한 set 을 돌린 결과 이다. maxmemory 는 512MB 였는데, expire 되는 key 가 하나도 없어 메모리는 항상 차게되어 있고 결국에는 더 이상 사용할 메모리가 없자 오류가 난 것이다.

이는 프로그램 운영 상으로도 매우 심각한 문제가 된다. Redis 는 ‘evicted_keys’ 가 존재하는데 보통은 메모리 부족현상으로 발생되는 거라고 생각한다. 그래서 메모리가 부족하면 evicted_key 만 나오고 프로그램은 예외가 발생하지 않을 거라고 생각한다.

evicted_keys 도 증가했지만 예외상황을 맞을수도 있다.

그렇다면 maxmemory-plicy 가 allkeys-lru 되어 있다면 어떻게 될까? 결론부터말하면 프로그램은 예외를 발생할 확률이 줄어든다. 예외를 발생시킬 수도 있지만 안될 확률이 높다. 프로그램은 잘동작하는 것처럼 보이지만 Redis 는 evicted 를 발생시키고 있다. 따라서 evicted_key 가 증가하는 현상이 보인다면 maxmemory 값을 늘려주는게 좋다.

bgsave 는 slave 와 접속을 차단한다. replication 은 SYNC 는 BGSAVE 를 발생시킨다.

먼저, 구분되어야 할게 있다. Master<->Slave 간의 replication 상태에서 데이터는 전송되지 않는다. 무슨 말이냐하면 Master 발생되는 명령어들을 그대로 Slave 에 던지는 방법이다. “set myhello ‘Hello'” 라는 명령어를 Master 에서 했다면 Slave 도 똑같은 명령어가 전달되지 데이터가 전달되지 않는다.

용어의 차이일 수 있는데, SYNC 시에는  slave 에게 데이터를 전송하기에 앞서 disk 에 rdb  파일을 생성한다. replication.c 에 다음과 같은 내용을 볼 수 있다.

rdb 파일은 백그라운드 프로세스를 생성시키고 rdb_filename 파일에 저장한다. 문제는 이러한 일이 벌어지는 동안에 접속은 차단된다.

closeListeningSockets 함수는 TCP/IP 접속 소켓을 닫게 된다.

결국 BGSAVE 가 발생하는동안 slave 접속은 차단된다. 그리고 다시 접속이 이루어지면 Full Sync 가 발생된다. 정리를하면 현재 Replication 이 된 상태라면 Sync 를 위해서 rdb  덤프는 발생되지 않는다. 하지만 새롭게 Replication 이 맺어지면 그때에는 Master 가 rdb 덤프를 하고 그리고 나서 데이터를 전송하게 된다. 실제로 새로운 Replication 이 맺어지면 파일 덤프가 다음과 같이 발생한다.

temp-5594.rdb 임시파일을 생성하고 나서 rdb_filename 으로 변경한다. Replication 상태에서 rdb 검프는 SYNC 에만 발생한다.

그런데, Redis 는 slave 가 잠시동안 접속이 차단될때에 Full Sync 가 발생하지 않도록 repl-backlog-size 에 복제를 위한 데이터를 저장해둔다. 주의해야할 것은 이 크기는 메모리 크기를 말하는 것으로 디스크에 데이터를 저장하지 않는다.

여기서 또 짚고 넘어가야할 것은 repl-backlog-size 가 작아서 runtime 으로 크기를 변경해주면 기존의 데이터는 다 지워지고 새로운 크기의 backlog 가 할당되는 방식을 취한다.

 

Redis 를 운영할때에 Master-Slave 로 운영한다면 모든 것을 메모리로만 운영할 수는 없다. Slave 로 Replication 을 할때마다 BGSAVE 가 발생하고 이는 일시적으로 Slave 와의 접속을 차단하기 때문이다. Slave 와의 접속이 차단된 후에 다시 연결이 되었을 경우에 Full Sync 가 발생할 수 있다.

diskless 를 통한 slave sync

이는 사실상 트릭같다. disk sync 은 Master 가 bgsave 로 메모리에 데이터를 점프한 후에 그것을 slave 로 전송하는 방법이다. bgsave 가 작동하게 되면 slave 와의 연결은 잠정적으로 끊어지게 된다.

하지만 disklee sync 는 bgsave 가 동작하지 않으며 Master 가 아닌 Slave 가 Master 로부터 메모리의 내용을 전송받아 덤프 파일을 생성한 후에 이것을 메모리에 올리는 방식이다. Master 로부터 Slave 가 데이터를 전송받을때, Master 는 “redis-rdb-to-slaves *:6379” 프로세스가 생성돼 전송을 담당한다. Slave 에서는 전송된 데이터를 파일로 저장하기위해서 별도의 프로세스는 작동하지 않는다.

Master의 메모리 내용을 Slave 가 덤프 받는 방식이 diskless sync 이며 수십기가의 메모리 내용을 전송해야하기 때문에 네트워크 상태가 좋아야 한다.

Creative Commons License
Redis 운영에 필요한 잡지식들. by Voyager of Linux is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.