얼마만한 대용량 데이터베이스인지는 모르겠지만,

지금까지 경험에 의하면, 작업이 느려지는 것이, 정말 PostgreSQL 쪽인지를 먼저 살펴보아야할 것 같습니다.

제가 일하고 있는 곳에서 이런 사태가 몇 달 전에 발생해서 원인을 살펴보니, 전혀 엉뚱한 php의 session 정리 작업 때문이었습니다.

자료 갱신이 너무 빈번히 일어난다면, 일단  매일 vacuum 명령으로 자료 정리를 주기적으로 해 보십시오. 그래도 계속 같은 문제라면, 서버 로그 파일 내용도 좀 보여주십시오.

이런 경우 일반적으로 전혀 엉뚱한 문제 때문에 발생하는 경우도 있거든요.

대충 데이터 껀수는 다해서 1천만껀정도되구요 이제 더 늘어날것으로 보입니다. 그리고 저만 이런현상이 있는게 아니라 인터넷을 뒤져보니까 이 checkpoint process 동작시에 모든 동작이 느려진다는 리포트가 있군요. (IO를 엄청 많이 사용합니다.)

느려지는 시점에 ps -ef 해서 보면 posgresql : checkpoint process

라는 프로세스가 작동하면서 x_log들을 쌓습니다.

바로 이 쌓는 시간이 16M * 3 인 경우 대략 10초정도

걸립니다. 이 시간에는 모든 DB동작이 엄청 느려지죠.

다른 세션이나 여타 작업은 없습니다. 단지 바로 이 DB작업뿐이지요.

쿼리는 이미 다 조사해보았지만 문제있는 쿼리는 없었습니다.

체크포인프 프로세스는 일정한 시간 간격으로 효율적으로 메모리를 쓰기 위해에 있는 커밋이 안된 데이터를 파일에 기록해 주는데 역할을 하고, 이 저장되는 공간이 log file이라고 합니다. 이 로그파일은 파일 갯수만큼 순서대로 이동하여 기록하고 다 쓰면 처음부터 다시 쓰기시작하고요.

체크아웃 프로세스 타임이 길어진다면, 메모리의 데이터 블럭에 커밋이 안된 data block이 많은 것아 로그를 기록하는데 소요되는 시간이 많다는것을 의미합니다.

메모리의 data block 수를 크게 늘이시고, 특히, 쿼리를 살펴 보셔서 트랜잭션에서 빨리빨리 커밋이 일어나게 하시는 것도 중요합니다. 또 로그 파일 수도 늘려서 긴 트랜잭션의 커밋안된 공간이 순환적으로 마치고 check out 프로세스를 기다리게 하면 지연이 생깁니다.

postgresql에는 기본적으로 3개의 세그먼트를 사용하고 300초마다 자동으로 체크포인트가 로그를 기록하거나 파라미터에 정해진 시간만큼이 흘르면 반드시 메모리상의 커밋안된 데이터는 로그파일로 기록하게 되어 있습니다. 한가지 더는 CHECKPOINT 명령으로 강제적으로 로그파일이 로그에 기록하게하여 메모리상에 데이터 블럭을 확보할 수 있습니다.

체크포인트를 자주 발생시키면 예기치 않은 시스템 중단일때 데이터 복구를 최소화 할 수 있으나 디스크 i/o를 많이 일으킬 수 있으니 잘 조절해서 사용하셔야 할것같구.. 메뉴얼에 보며 정말 좋은 이야기들 많이 나와 있습니다. (WAL Configuration)

특히 메모리 공간 잡을때는 일정한 공식이 있는데 기본적으로  (블럭갯수) X (블럭사이즈) 최소값은 16개 X 2 (OS 메모리블럭사이즈) X (최대 접속수)를 계산하여 공유 메모리를 자고, OS 는 shared memory를 제공해 주어야 합니다. 그래저 Postgresql을 돌리는 서버는 예전에 커널 컴파일을 적절히 다시 해 주고 그 위에 깔아서 썼었습니다. (메뉴얼의 Reun-time Configuration참조, 지금은 서버관리는 안하지만. --;;)

이야기가 길어지네요.. 물론 트랜잭션에 따라서 메모리상의 소팅공간도 적절히 확보하여야 할껍니다... 한가지만 더.. log_min_duration_statement()에 밀리세컨드를 설정해 두시면 그 시간이 넘는 SQL문은 로그 기록에 남으니 어떤 SQL문이 말썽을 일으키는지 찾아낼 수 있습니다.  (VACUUM은 정기적으로 하시죠?)

내용은 잘 읽어보았습니다.

궁금한 점이있는데요. .커밋이안된 데이터들을 실제 디스크로

옮기는 과정을 checkpoint process라고 하면

이 커밋이란게 명시적으로 commit; 이라고 넣어주지 않았다는

이야기인지..아니면 내부적(postresql)으로

처리하는 과정인지 궁금합니다.

그리고 데이터블록 사이즈를 30개로 늘려본적이있는데요 그때에 이 모든 check point segements를 작성하기 위해서 엄청나게 시간이 걸렸던 적이있습니다. 대략 1분정도요..  그래서.크게 늘리는걸 조심해했는데..

표현이 모호했는데요,

위에서 말한 commit은 sql 명령어 commit을 이야기하는 것이 아니라, RDBMS에서 자료 insert, update, delete 작업이 완벽히 끝난 상태에서 추가, 변경, 삭제된 자료를 의미합니다.

insert sql 구문을 서버로 보낸다고 해서, 물리적으로 해당 자료가 있어야할 원래의 하드디스크에 파일에 바로 쓰여지지 않거든요. 이것에 대한 동기화 작업이 checkpoint 작업입니다.

이는 트랜잭션의 고유성이라고 하는 이 문제 때문에 그런다고 하네요.

일단 이곳 dsn 에서는 체크포인트 타임의 최대값이 3600초로 해 두었습니다. 그리고, checkpoint_segments 값인 세그먼트 로그파일은 최소값이 1개로 해두었습니다.

이렇게 하면, 체크포인트 작업에 의한 부하를 최소화 할 수 있다고 판단되었기 때문에.

이런 저런 셋팅값을 조절해 보시고, 가장 최적의 값을 찾으셨다면, 이곳에 공개해 주세요.

같은 문제로 고민하는 분들에게 많은 도움이 될 듯싶네요.

지금까지 천만건 이상의 자료가 다루워지는 DB를 만져보지 못해서 DB셋팅에 대한 문제에 그렇게 관심을 가지지를 못했지만, 지금까지 경험에 의하면, DB 속도가 떨어지는 이유의 거의 90% 이상이 깔끔하지 못한 쿼리를 사용하기 때문이었습니다. 이점도 참고하세요.

답변주신것 감사합니다.

오늘도 그 문제때문에 회사에서 고민하다가 왔네요.

그동안 알아낸 현상을 알아내면 다음과 같습니다.

대용량 업데이트가 주기적으로 계속 일어납니다.

이렇게 일어나면 checkpoint_Segements 용량이 금방 차죠.

(지금은 16M * 7 입니다. 대략 25초에 한번씩 찹니다.)

이 checkpoint_segments 가 HDD에 물리적으로 쓰일때

(memory -> hdd로) 갑자기 iowait CPU가 증가하면서

99%까지 혹시 100%까지 증가하면서 모든 DB 관련 동작이 멈춥니다.

OS 조차두요. 그러다가 10초가량 지나면 이 iowait 가 풀리면서 다시 원활하게 되지요.

혹시 쿼리가 이상하나 싶지만 쿼리 하나하나는 이상이 없습니다. 대충 결론낸건 대량 update가 수시로 일어나면서 checkpoint process가 이 io가 일어나면서 생기는 일이라고 결론지었습니다.(mailing list에서도 이와 같은 현상을 겪은 사람들이 있더군요.)

HDD의 셋팅 문제일수도 있는데. 현재 raid 5로 구성되어있는 HDD를 ㅎ사용하고 있습니다. 아직 x_log를 따로 IO분리 안하였는데 한번 해볼까 생각중입니다. 이런 Hardware관련 정보를 좀 주셨으면 좋겠습니다.

어떠한 구성으로 구성을 하면 최적의 구성이다.. 라는 의견을 좀 얻었으면 좋겠습니다.

이번 문제가 잘 해결되면 이에 관련 정보를 공유하도록 하겠습니다. ^^!

관심주셔서 감사합니다!

세그먼트 로그 파일 관련 작업에 대해서 disk io가 심하다는 이야기가 의심스러워 dsn 서버에서 테스트를 해보았습니다.

테스트한 방법은,

약 4만7천 건의 우편번호 자료를 가지고, 우편번호를 일괄적으로 계속 바꾸는 작업이었습니다. - 트랜잭션 작업 때문에, 당연히 begin, end 로 트랜잭션을 만들었습니다. 해당 작업이 완벽히 한 사이클 - 4만7천여건 업데이트가 끝나면, vacuum analyze 명령으로 자료정리를 해 주었으며, 이 전체 작업을 반복해서 하나의 세션에서 10분 작업을 하고,

다른 세션에서는 1초 간격으로 끊임 없이 select 구문으로 특정 우편 번호를 계속 찾고, 물론 이때, index를 사용합니다.

또 다른 사용자는 pg_xlog 파일의 변화를 살펴보았습니다.

먼저 pg_xlog 쪽에서는 정말 20초 간격으로 로그파일이 새롭게 생신 되더군요.

wal 셋팅값으로는 총 3개의 로그파일(내부적으로는 기본 3, 셋팅값 3, 해서 6개의 로그파일이 사용됩니다),

checkpoint time은 5분.

그렇다면, 거의 2분 간격으로 checkpoint 작업이 일어나는 셈입니다.

현만님의 이야기라면, 두번째 세션인 1초 간격으로 select 작업을 하는 놈이 2분 간격으로 속도가 떨어져야함을 의미합니다. 하지만 예상 밖으로 지극히 정상적인 쿼리 속도를 보여주더군요.

그래서, 관리자로 로그인해서, 다른 한 세션에서 열심히 update 작업하고 있음에도 불구하고 강제로 checkpoint 명령으로 자료정리 작업을 시켰습니다. checkpoint 수행 시간이 약 2-3초 정도 걸리더군요. 이 때도 두번째 세션의 select 속도에는 이상이 없었습니다.

다음은 이 글을 쓰고 있는 사이 벌어진 두번째 세션의 1ms 이상 되는 처리 결과만 출력한 결과입니다.

이 사이에도 다섯번의 checkpoint 작업이 있었거든요.

------

02:16:54 0.181254
02:16:55 0.06717
02:17:04 0.043995
02:17:23 0.003009
02:17:33 0.016314
02:17:36 0.063493
02:17:43 0.025473
02:18:13 0.002279
02:18:50 0.008851
02:18:51 0.01537
02:18:52 0.012921
02:19:29 0.017495
02:19:39 0.0044339999999999
02:19:50 0.017392
02:20:03 0.028232
02:20:41 0.185571
02:20:42 0.026285
02:21:14 0.028264
02:21:26 0.013445
02:21:38 0.03375
02:22:31 0.0037280000000001
02:22:33 0.019387
02:23:05 0.007098
02:23:32 0.027139
02:23:56 0.005845
02:24:20 0.002418

--------

이 값을 보면, 충분히 서비스가 가능한 속도입니다. 딱 두개의 아주 큰 값이 있는데, 첫번째는 시작할때의 느린 문제이고, - 이것은 해결 할 수 없는 부분인듯하고요, 가운데, 하나 느린 것은 RDBMS 외적인 문제 같습니다.

아무튼 덕분에 재미난 테스트를 해 보았습니다.

---------

참, 지난번 성철님의 vacuum full 사용하지 않는 일반 vacuum 에서의 데이터 파일 사이즈는 과연 증가하는가?에 대한 테스트도 함께 했었는데,

당연히, 예상된 결과겠지만, vacuum 명령만으로도 충분히 빈 공간을 표시하고, 그 곳에 새 자료를 저장합니다. 즉, vacuum full 처럼 완벽한 하드디스크 정리는 하지않지만, 그래도 치명적인 disk full 사태까지는 가지 않을 것 같네요. 주기적인 vacuum 명령만으로도 말이지요.

이렇게 신경을 써주시다니. 감사드릴뿐입니다.. T.T

문제가 되는건 동시에 대략 한 50개의 connection에서

동시에 같은 table에 update를 합니다. update하는 row는 전부 틀리구요. 이 순간에 checkpoint process가 돌게 되면 iowait가 증가하면서

위의 모든 update문장이 쌓이게 됩니다. 이 순간 업데이트 문장이 잘 되던것이 순간 5초 가량으로 시간이 증가하게 됩니다.

하지만 트래픽이 낮은 순간에는 위와 같은 현상이 나타나지 않습니다. 저도 pgbench 를 이용하여 테스트를 해보았지만 위와 같은 현상은 나타나지 않았습니다.

지금 데이터가 대략 15G가 정도 쌓였는데 (오픈한지 1주일 좀 넘었습니다.) 아직 vacuum full은 하지 않았습니다. 하지만 auto vacuum은 돌리고 있는 중입니다.

vacuum full을 한번 해보고 다시 한번 볼까 합니다.

postgresql 8.0에서는 위의 checkpoint 과정이 많이 튜닝이 되었다고 하는데 postgresql 8.0을 사용해볼까하는 생각도 가지고 있습니다.(최후의 방법입니다.)

이 골치아픈 문제가 어서 해결되었으면 하는 바램입니다. 항상 사용자가 가장 많을때 문제가 생기니까 걱정만 되는군요.

50개의 connection이 동시에 update를 하는 경우라면 당연히 무리가 될 수 있을 것 같네요.

저도 300개의 thread가 동시에 insert와 update를 하는 프로그램을 짰는데 updaet 구문 하나당 1분 넘게 처리가 지연되는 현상이 생기더군요. 그래서 update하는 함수를 synchronize 하게 작동하게 했습니다. 동시에 한 sql만 처리하도록 한거죠. 그랬더니 처리 시간도 그렇게 많이 안걸리면서 무난하게 작동하더군요.

지금은 해당 함수의 lock control을 변경해서 10개정도의 동시성을 허용하려고 하고 있습니다.

제 생각에는 checkpoint 자체의 문제라기 보다는 지금 I/O의 대역폭이 50개의 connection에서 발생하는 update구문과 checkpoint를 동시에 처리할 수 없는 상황인 것이 문제 아닌가 생각합니다. 아무리 raid라고 해도 한계는 있는법이니까요.

보통 DB가 동시에 발생하는 요청의 수에 비해서 처리 시간이 선형으로 증가하다가 일정 갯수 이상의 요청이 동시에 들어오면 급격하게 효율이 떨어집니다.

지금 어떤 언어로 작성하셨는지 모르겠지만 동시에 처리하는 숫자를 제한해보시는 것이 좋을 것 같습니다.

그리고 지금 사용하는 raid system이 pgsql의 data 저장만을 위해서 독립되었는지 알고 싶네요. 가능하면 DB만을 위해서 사용되도록 하는 것이 좋을겁니다. 위에서 OS에 영향을 준다고 하셔서 말씀 드리는 건데요. raid에 OS까지 같이 깔려있다면 분리하도록 하세요. 단순히 파티션을 분리하는 수준이 아닌 전혀 다른 디스크로... 그리고 만약 디스크가 다른데도 영향을 준다면 이건 Disk Controller의 I/O 대역폭을 다 쓴다는 말로 들리니 Controller를 새로 추가하셔야 할 것 같습니다.

아 글구 그 많큼의 컨텍션이 발생하여 업데이트하는 테이블에 Foreign Key Constraint가 있는지도 체크를 해 보셔야 할껍니다. PostgreSql은 무결성을 잘 체크해주는 디비기 때문에 FK가 있다면, 부모의 키값 또한 트랜잭션이 일어날때 선점하려 들지도 모르고, FK가 있다면 해당 FK에 꼭 인덱스를 쓰는 습관도 성능향상에 매우 유리하죠. (절대적이지요.)

좀 허무할수 있는 결과를 알려드리겠습니당.ㅡㅡ;

오늘 vacuum full로해서 데이터 사이즈를 9G정도로 줄여놨습니다.

그러면서..운영을 했는데 조금은 hanging 걸리는 시간이 줄어들기는 했지만(x_log작성시 즉 checkpoint process가 돌고 있을때

쿼리는 대충 2~10초 사이.. 평소에 이 쿼리는 수백ms에서 끝납니다.)여전히 문제점은 보였습니다.

방법이 없는가..하는 마음으로 머리를 싸메고 있는데 하나 이상한 점을 발견했습니다.

저희 운영시스템은 비슷한 데이터량의 DB를 3대를 사용하여 운영하는데 이들 2대는 같은 기종 같은 머쉰이고 다른 1대는 앞의 것과 다른 장비였습니다. 그런데 앞의 2대에서는 위의 문제가 checkpoint process발생중에 항상 나타났고, 다른 1대는 checkpoint process 시에 전혀 문제없이 도는것이었습니다.

hardware문제라고 확신하고싶지는 않지만 일단 장비를 교체해서 다시 테스트 해보려고 합니다. 제가 pgbench 로 테스트해서 문제없다고 생각한것도 다른 1대의 spec을 가지고 있었기때문이죠.

오늘 장비요청해서 내일오전에 새장비가 도착한다는군요. 그래서 다시 2대를 새장비에 포팅해서 현상을 볼까합니다.

이게 사실이라면 좀 허무할수 있겠네요.

다시 결과를 올리도록하겠습니다.

뭐.. 허무할 것까지는 없을 것 같고요.

일단 IO wait가 걸렸다는 것은 쿼리가 문제건 H/W가 문제건 최대 대역폭을 넘어서는 I/O가 발생했다는 뜻이니 돈을 들여 H/W를 보충하거나 시간을 들여 쿼리를 최적화 또는 쿼리 방식의 변경을 생각해 보셔야 할겁니다.

그리고 외래키에 대해서 말씀하셨는데 트리거도 이런 문제를 발생시키는 주범 중에 하나입니다. 이것도 점검해 보세요.

해결된듯 싶습니다..

일단 장비 교체를 하였구요. 이전 장비의 scsi driver가 좀 오래된

버젼인데 그걸 바꿀까 했지만 새로운 다른 장비로 교체해서 운영하였습니다..

이번에는 check process동작시에 update가 느려지는 현상은

많이 사라졌습니다. 여전히 check process동작시에 조금 느려지는

update 문장이 있긴했지만 2초정도는 애교로 봐줄수 있을것 같더군요.

참고로 이번 셋팅에서 좀 많이 실험을 했는데요.

fsync=off 로 하였고 checkpoint_process를 굉장히 크게 주었습니다.

이 크기에 따라서 업데이트를 많이 하는 경우에는 check point가 일어나는 시점에서 전반적인 performance 저하가 나타나기때문에

application 의 pool에서 job이 쌓이는 현상이 나타나면서

전반적인 시스템의 속도 저하를 가지고 오더군요.

만일 DB쪽의 시스템이 나가거나 전원이 나가거나 하면 자료 손실의 위험이 있겠지만 장비쪽보다는 우선 시스템의 속도문제가 커서 checkpoint_segments 값을 굉장히 크게 주었습니다.

(이 값은 pgbench로 테스트해본 결과 update하는 속도와 시간을 가지고 적당한 값을 맞춰야겠더군요.)

또한 wal_buffer로 늘려주었습니다.

이렇게 셋팅함으로써 오늘 아무런 문제없이 속도 빠르게 잘 운영하고있습니다. 아~ 참고로.. vacuum은 full 말고 하루에 한번정도 해줘야 속도가 유지되더군요.

좋은 정보 감사합니다.

하드웨어 사양도 공개 될 수 있다면, 도움이 될 것 같네요.