오라클 데이터베이스 레코드 입력속도 향상
글쓴이: gilsion / 작성시간: 목, 2005/01/20 - 10:19오전
안녕하세요. 제목에 대한 고민을 하고 있는 중입니다.
현재 상황은 이러합니다. 20만개에서 70만개 정도의 레코드가 분류별로 바이너리 파일에 있습니다. 이것을 아스키파일로 분해해서 파일로 만든다음 sqlldr 을 이용하여 DB 에 저장하고 있습니다.
대략 분류가 80개 정도되는데 대충 레코드를 다 집어넣으면 4000만개 정도 됩니다.
오라클의 엄청난 성능으로 원하는 사항에 대한 select 쿼리는 만족할만 합니다만 저 자료를 다 집어넣는데 시간이 좀 걸립니다. 대략 6~7시간은 걸리는것 같습니다.
아래는 16번 분류까지 실행하는데 걸리는 시간을 측정한결과입니다.
파일파싱 하는 시간도 포함이긴 하지만 과정을 눈으로 볼때 파싱시간은 엄청 짧으므로 SQLLDR을 이용한 입력시간이 대부분이라 생각됩니다.
분류 레코드수 소요시간 01 775,038 00::05::09 02 704,378 00::05::07 03 650,994 00::05::09 04 610,902 00::05::51 05 109,554 00::01::58 06 568,322 00::05::26 07 574,137 00::05::30 08 527,638 00::04::58 09 606,733 00::05::32 10 296,314 00::03::47 11 384,178 00::03::53 12 353,837 00::04::14 13 329,741 00::04::21 14 337,265 00::04::25 15 326,020 00::04::14 16 328,140 00::04::27
위에는 표시되어 있지 않지만 분가 80번에 다다르면 레코드가 10만개 정도로 작아지는데도 소요시간은 4~5분대를 지속적으로 유지합니다. 레코드가 작아도 시간은 계속 오래걸리는군요.
상황이 이러할땐 어떤방법으로 데이터입력 시간을 줄일수 있을까요.
앗. 프로그래밍 Qna로 썻군요. 이동바랍니다.
Forums:
경험에 비추어 보면
사이즈는 틀리지만 한 800만개 넣는데 10분 내외로 걸렸던것 같은데...
네트웍을 고려하셔서 로칼에서 하시면 좋을것 같고 트랜잭션과 로그파일에 대한 고려 버퍼를 메뉴얼을 보시고 고려하시면 쾌청한 속도가 나올것 같습니다.
테이블 만들 때 인덱스를 먼저 걸어 놓으신게 아닌지요?대량 레코드
테이블 만들 때 인덱스를 먼저 걸어 놓으신게 아닌지요?
대량 레코드를 집어넣을 때는 인서트 후에 인덱스를 거는게 퍼포먼스가 낫다고 알고 있는데요.
노루가 사냥꾼의 손에서 벗어나는 것 같이, 새가 그물치는 자의 손에서 벗어나는 것 같이 스스로 구원하라 -잠언 6:5
일반적인 ETL 작업에서는
ETL(혹은 ETT)작업을 하시는듯 하군요.
일반적으로 대량레코드를 넣을때 제 경험에 비추어 말씀드리겠습니다.
(아마 다 아시는 내용을 구구절절 설명드리는게 아닌가 ... ㅡ.ㅡ;; )
0. 병렬작업
어떤 플랫폼에서 작업하시는지는 모르겠지만 시스템자원을 최대한 쓰신게 아니라면 동시에 여러작업을 병렬로 돌리심이 좋습니다. (프로세서의 iowait점유율이 급격히 증가하지 않을정도까지)
1. SAM파일 생성과정
혹시 이 부분도 고민이시라면 프로그래밍 QnA에 올린게 맞습니다. :)
2. SQL Loader 적재과정
direct path, parallel, streamsize, multithreading, skip_index 같은 옵션들을 최대한 활용해야 합니다. 기본적으로 direct는 적용하셨나요?
결과를 보면 100만건도 안되는데 5분이 넘는걸보니 웬지 컨벤셔널로 돌리셨던가 하드웨어가 보통PC에 IDE저장장치일지도 모른다는 생각이 드는군요. (혹시 저게 금융권 DB서버에서 돌린건가요?) 어떤사양의 어떤플랫폼에서 나온 결과인지를 말씀해주시면 좀더 구체적인 참고가 될텐데요.
homeless
Re: 일반적인 ETL 작업에서는
네. 주어진 데이터를 가지고 Analysis를 하고있습니다.
또한 예상하신 바와 같이 일반 워크스테이션 PC를 사용하고 있습니다.
0. 오라클에 대해 무지해서 그럽니다만. 병렬작업이라 함이 SQLLDR 을 한번에 여러개 돌려라 라는 의미인가요?
1. SAM파일 생성과정이 미치는 영향에 대해 알지못해 이해하지 못했습니다.
2. 나름대로 옵션을 기입하긴 했습니다.
sqlldr80 userid=user/passwd, direct=y ,control='C:\TEST\TEST.CTL', log='C:\TEST\TEST', bad='C:\TEST\TEST', errors=10000
가능하시면 언급하신 옵션에 대해 설명좀 부탁드립니다. 기본적으로 제공되는 sqlldr80 help만보면 이해하기가 좀 힘들더군요.
첫질문에 있는 속도결과는 파일파싱 하는 부분까지 포함되어있어서 현재 다시 속도측정중에 있습니다.조금은 더 작게 나오겠죠^^
저 또한 무지합니다.그런데 문득 생각이 나서..sam 파일을 쪼개
저 또한 무지합니다.
그런데 문득 생각이 나서..
sam 파일을 쪼개서 pipe를 이용한 parallel로 실행하시면....
스토리지가 ide가 맞다면 그정도가 나오는것이 당연할지도 모르겠습니다.
스토리지가 ide가 맞다면 그정도가 나오는것이 당연할지도 모르겠습니다.
병렬작업이라 함은 한번에 여러개수행을 말씀드린 것이 맞습니다.
sqlldr1.sh & sqlldr2.sh & sqlldr3.sh & ...
와 같은 식으로요. 동시수행갯수는 iostat로 모니터링했을때 iowait가 심하게 증가하지 않을정도로만 하시는것이 좋습니다.oci에 익숙한 프로그래머라면 sam파일 생성과 sqlldr적재작업을 분리시키지 않고 바로 버퍼의 내용을 direct path loading을 이용하여 적재가 가능합니다. 중간생성되는 sam파일에 소모되는 io read/write 시간을 없앨수 있습니다. 관심있으시면 오라클웹사이트에서 oci프로그래밍문서를 참조하시기 바랍니다.
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10779/toc.htm
sqlldr의 옵션에 관한 설명은 헬프화면정도가 아니라 오라클의 공식문서를 참조하셔야 합니다. 아래의 utilities문서에서 sql*loader관련부를 찾으세요. case study가 도움이 될것입니다.
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/toc.htm
정보통신부의 우편번호테이블(엑셀파일로 제공되지요) 정도의 scheme에 건수는 100만건쯤되는 테이블로 작업한다고 가정할때,PC사양이 펜4 2GHz, U320scsi, 커널2.4, Oracle9i쯤되는데서 적재한다면 30초 (넉넉잡아) 안에는 끝나야합니다.
펜3 500, ide 같은채널의 같은 스토리지파티션에 오라클테이블스페이스와 sam파일이 모두 존재하며(최악의상황을 가정했습니다), 오라클이고 시스템이고 죄다 튜닝이 안되어있다면 gilsion님과 같은 결과가 나올수도 있을거라 생각합니다. :)
homeless
감사합니다. 오라클/데이터베이스 지식을 넓히는데 많은 도움이 될 것 같습
감사합니다. 오라클/데이터베이스 지식을 넓히는데 많은 도움이 될 것 같습니다.
조금 깊이 있게 공부를 하면 개선된 결과를 얻을수 있을것 같습니다.
아 그리고 위에 제시된 결과는 믿을게 되지 못하는군요.
바이너리파일 파싱만 해보니 예상보다 시간이 너무 많이 걸렸었습니다.
아래가 sqlldr 테스트 결과입니다.
시스템 : 2.8G Xeon/1G mem/SCSI 하드
뭔가가 속도에 대한 개선이 있으면 댓글로 결과를 첨부하도록 하겠습니다.
고민한 결과 어쨋든 속도향상의 결과가 있었습니다.데이터 입력의 상
고민한 결과 어쨋든 속도향상의 결과가 있었습니다.
데이터 입력의 상황은 이러합니다. 많으면 2백만개, 적으면 50만개의 레코드를필요마다 삽입해야합니다. 컬럼도 제법됩니다. NUMBER 형으로 12개쯤 되는군요.
데이터가 계속 누적되어 현재까지 입력되어있는 데이터의 갯수를 보면 대략 4000만개 정도 됩니다.
상황이 이러다보니 다시 50만건 정도의 데이터를 누적저장 시키면 5분 이상이 소요되었습니다.
kihlle님께서 알려주신데로 OCI 프로그래밍도 해보았지만(sample 코드는 대충 sqlloader 소스코드 이더군요) 크게 이득을 보지 못했었습니다.
결론적으로는 테이블생성시 데이터파일을 조건별로 분할시켜 저장했습니다.
데이터파일을 10개정도로 나누고 조건별로 저장을 하여 골고루 분산시켜보니 총 입력시간으로 본다면 거의 1/10 정도로 해결이 되는군요.
문제도 대충 해결한것 같고 지식도 넓힌것 같습니다. :)
댓글 달기