데이터베이스 질문과 답변
index에대해 알것 같기도 하고
모를 듯한 궁금증이 심해져서 질문 드립니다.
특정 데이터 값들을 저장하는 테이블이 있습니다.
pk 값은 특정 id 값으로 지정되어 있고, 해당 테이블의 데이터는 아주 아주 빈번하게 갱신됩니다.
- 전체 테이블은 약 60만 데이터를 가지고 있으며, 초분시 빈번하게(약 시간당 5~15만 데이터) 업데이트
이때, 갱신이 이루어질때마다
최근 갱신 시간을 해당 테이블의 컬럼(최근 갱신 일시)에 업데이트 하게 되는데.
이 컬럼(최근 갱신 일시)에 index를 지정해도 문제가 없는지,
index를 지정하는게 맞는지 영향도는 어떻게 되는지 궁금합니다. ???
참고로, select 유형은
pk 값 참조 조회 또는 만족하는 시간 구간(최근 갱신 일시)를 포함하는 조회가 주를 이룹니다.
추가 질문 1)
이와 같이 index 컬럼의 데이터가 빈번하게 갱신되는 경우,
index 에 따른 단편화현상이나 highwater mark 는 어떻게 되는 궁금합니다.
추가 질문 2)
데이터가 갱신 될 경우,
기존 데이터의 index는 사라지고 갱신된 데이터에 맞게 새로 index가 형성되는지요,?
추가 질문 3)
해당 테이블에 index를 추가하려고 했더니,
"자원이 사용중.."이라하여, 추가 할 수 없는데, 서비스를 중지하고 index 추가하는 방법 뿐인가요.?
- 최적 주기에 대한 index 재구성의 필요성도 있을 듯 한데, 같은 영향을 받을 듯 합니다...
마지막으로,
index 동작 및 형성에 대한 internal 원리 또는 인덱스 관리에 대한 좋은 자료 있으면
추천 부탁드립니다.
아.. 대충 답변할려고 했는데 이넘의 성격은 ㅠ
1) 인덱스 단편화 현상은 발생합니다.
- 해결 방안 : 해당 테이블에 트랜잭션이 최저일때 시간을 알아본다 ( 패턴분석)
- 수동 리빌드 아니면 배치 리빌드 추가
- 50만건이라도 트랜잭션이 빈번하게 일어나면 나중에 완전 겁데기 리프블록을 스캔이 발생하여
아주 극심한 비효율이 발생합니다( 여기 사이트가 그래요, 주기적인 리빌드가 필요함 )
- 50만건정도의 인덱스 분할 부하( 인덱스 컨탠션 )정도는 부하정도에 희박해 보이지만...
추후 단편화로 인해 겁데기 리프블록 스캔으로 인한 부하 발생 소지가 다분함.
2) 네
- 9/1 : 맨 왼쪽이나 오른쪽( 양끝 ) 리프블록에서 분할이 발생하면 9/1로 분할이 일어납니다.
- 5/5 : 위 경우가 아닐때 5/5로 분할이 발생합니다.
3) 온라인 옵션을 주고 한번 실행해보세요.
4) 구글에 찾아보면 겁나 많음.
전 경험한 것으로 판단을 해서...
경험이 적어 오류가 많지만... ㅋㅅㅋ)ㆀ
(그래서 배우기 위해 막막 개념 없이 글을 쓰고 있습니다. 커뮤니티 특성상 그런거니 이해 바랍니다~~ ^.^)
저런 경우도 인덱스 단편화 현상이 발생하나요?
Deadlock 걸려서 속도가 현저히 떨어지는게 아닌가용? >ㅅ<)ㆀ
그리고 60만건에서 1/4 데이타가 빈번하게 업데이트 되는데
이런 경우 잡으로 인덱스 리빌드는 무리가 아닐까용? @.@)?
(역시 배워볼려고 막 찔려보는...)
그리고 빈번하게 발생한다면 highwater mark가 고려 대상이 되나요?
@.@)? 헤헷.. 답변이 아닌 질문만 하네요.
추가 질문이 될 수 있겠지만... ㅋㅅㅋ)ㆀ
자원 사용중이라는 것은 트렌젝션 처리가 완료되지 않은 것인데 이때 인덱스 추가는 물론
관련 처리중 항목 업데이트도 안되는데 관련 트렌젝션이나 세션을 강제 종료하지 않는 이상
자료 무결성 원칙에 따라 냅두는 것이 바람직한 것이 아닌가영... ㅋㅅㅋ)/ 후다닥 ==3==3
1) 다편화
- 한 공간에 전체 공간은 8K에서 빈번한 업데이트로 인해 사용량은 2K 빈공간 6K 이런 상황에서
인덱스 스캔시 ( 레인지 ) 어쩔수 없이 비효율이 발생함
- 날짜 컬럼에 인덱스가 걸려있다고 하면 해당 날짜가 최근날짜로 업데이트가 발생하면
맨 오른쪽 리프블록에 인서트 원 리프블록 삭제 처리 마킹함 추후 블록클린( 패스트 블록클린 발생 )
2) 인덱스 리빌드
- 엄청나게 인덱스 단편화가 발생했다면 오랜시간이 소요되겟지만 매일 일정한 리빌드는 다편화 부분이적어 빠른시간안에 끝나고, 리빌드는 온라인으로 가능한걸로 아는데 아닌가요?
3) 오라클에서 데드락이 발생하는 경우 무한 웨이팅 현상이 발생하기 때문에 데드락을 유발하는 세션을 킬을 하고 데드락 상황을 배제함. alert.log에 데드락 발생이라 표시하고 덤프파일 표시.
4) 하이 워터 마크
- 아주 극심함 입력, 수정, 삭제가 발생하는 경우 테이블 풀 스캔시 30만건 읽어오는데 몇백만 블록 스캔 이 발생하는 경우가 있음. 그러므로 해당 테이블도 리로그 작업이 필요함( 주기적 )
물론 적정한 입력 수정 삭제 라면 익스텐트을 적절이 재사용을 함( 완전 재사용은 아님 )
5) 인덱스을 온라인 옵션으로 생성한다고 해서 데이타 무결성 이상현상은 발생하지 않습니다.
^.^;
인덱스 조각나는거 아닌가요?
인덱스가 되는데 전체적으로 봤을 때 연결이 안되는 단편이죠.
데드락이야... 교착 상태로 처리 하기 위한 시간 지연 생기지 않나요?
트렌젝션이 빈번하게 발생하면 어쩔 수 없는 현상 아닌가용? @.@)ㆀ
이재현 // 음.. 교착 상태는 잘못 이해한 것 같습니다. 말씀 감사합니다.
음.. A가 트렌젝션 잡고 있을 때 B가 같은 영역 업데이트 요청이 들어 왔을 때
A가 또 같은 영역 업데이트 들어와서 교착 상태에 빠지는데 풀릴 수 있는 부분이라 생각했는데
아니군요. 음.. 근데 업데이트가 빈번하게 발생하면 위 상황에서 대기 상태로 가는 대기(데드락 아닌 락 상태) 상태가 발생하니 속도 지연 발생하는 것은 아닌가요?
인덱스 시작 시점과 종료 시점에 TM S 모드로 락을 획득함
50만건 테이블에서 하루에 1번 그나마 트랜잭션이 없는 새벽시간때 이정도 리빌드는 금방 치고 빠지니
별 문제없다고 판단됩니다.
11g
완벽한 온라인 리빌디 제공
전 개념이 없어서... 또 글 막막 남깁니다.
배움을 주시는 모든 분들께 진심으로 감사드립니다.
보다 구체적으로 상황 예를 들면,
전 개발자로 대국민 서비스하는 시스템을 여러개 만든 경험이 있었는데요,
대부분 하루에 업데이트가 10만건을 넘습니다.
최근 만든 시스템도 영업사원이 3000명 정도되서 일 업데이트가 50만건을 넘고요,
근데 굳이 인덱스 재구성 안해도 속도를 어느 정도 보장합니다.
혹 인덱스 종류에 따라 영향이 있을 수 있는지요?
그렇다면 대국민 서비스가 아니더라도 업데이트가 많으면 잡으로 재구성해야 한다는 말이 되는데..
뭔가 제가 이해하고 있는 것이 분명 오류가 있는 것 같은데,
이해할 수 있는 방향으로 말씀 부탁드려도 될까용.. ㅋ ^.^; (날로 먹기..ㅋ)
업데이트가 발생하는 컬럼에 인덱스 존재 유무 부터 확인해야 할거같습니다.
그리고 어떤 패턴으로 해당 컬럼에 업데이타가 되지는 업데이트가 되는 시점에
인덱스 분할이 발생하는지 아님 해당 블록에서 업데이트가 되는지?
아래 같은 현상을 인덱스 단편화에 의한 형상입니다.
해당 테이블은 x 처러하겠습니다.
-- xxxx
SQL> SELECT TO_CHAR(xxxx) FROM xxx.xxxWHERE xxxx> 0 AND
2 ROWNUM <= 1;
선택된 레코드가 없습니다.
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.16 | 33672 |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.16 | 33672 |
|* 2 | INDEX RANGE SCAN| xxxx_IDX02 | 1 | 1 | 0 |00:00:00.16 | 33672 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
2 - access("xxxx">0 AND "xxxx" IS NOT NULL)
고려사항
1) 우선 업데이트가 발생하는 컬럼에 인덱스가 있는지 팍악한다.
2) 해당 테이블에 전체 로우가 몇건이고 증가추이는 얼느정도인지 파악한다.
3) 인덱스 단편화로 인해 어느정도의 비효율이 발생하는지 파악한다.
- 해당 SQL이 TOP SQL으로 발견되는지...
- 우리는 서버가 하이엔드급이라 무시한다..
4) 인덱스 리빌드을 하면 어느 정도 시간이 걸리는지..
5) 해당 테이블이에 트랜잭션 패턴은 어떤건지.. 어느시간에 트랜잭션 발생 빈도가 저조한지..
6) 오라클 사용버전
- 10G 시작 종료때 TM S 모드로 획득
- 11G 락 회득없음
무조건 인덱스 리빌드로 처리한다는건 아닙니다.
질문자님 이재현님 진심으로 감사합니다.
이따 한가해지면 답변드릴게요..
늘 프로젝트 때 DBA가 같이 다녀서 인덱스 재구성에 대한 이해가 적은데요,
인덱스 재구성은 자주 쓰나요?
개발자 입장에서 초기 시스템 빅데이타 재구성 작업 할 때 인덱스 효율이 급격히 떨어져서
그 때 써보고 안써봐서요, 자주 쓴다면 재구성을 하는 기준과 작업 시점이 있는지요.
위에서도 썼지만 지금도 운영하고 있는 시스템을 많은 사용자가 쓰고 있는데
업데이트가 자주 일어나는 시스템은 효율적인 성능을 위해 정책을 두고 정기적으로
인덱스 리빌드를 해주는 것이 좋은가요?
DBMS 마다 인덱스 종류가 차이가 있겠지만 인덱스 마다 이런 기준점이 달라질 수 있는지요?
얘기하다 보니 저도 질문자와 같은 질문을 하고 있는 것 같네요.. ^.^;
조금 방대한 것 같고,
저도 결론은 인덱스 관리에 관한 자료를 찾을 수 있는 방법을 알려주시는 것이 좋아 보입니다.... ! ^.^;
1) 인덱스 재구성은 자주 쓰나요?
아뇨, 님의 말씀 처럼 인덱스 효율이 급격히 떨어졌을때만 사용합니다.
2) 재구성을 하는 기준과 작업 시점이 있는지요?
님이 말씀 처럼 인덱스 효율이 급격히 떨어졌을때만 사용하는데,
해당 SQL이 비번하게 발생하여 불필요한 많은 로지칼 리드가 발생하는 테이블이나 인덱스들을 재구성을 고려해
볼수 있습니다.
작업 시점은 해당 SQL들이( 입력, 수정, 삭제 ) 발생이 미비한 시점 ( 트랜잭션이 미비한 시점 하루중에 )에 작업하는게 좋다고 생각합니다.
3) 업데이트가 자주 일어나는 시스템은 효율적인 성능을 위해 정책을 두고 정기적으로 인덱스 리빌드을 해주는것이 좋은가요?
업데이트가 자주 일어난다는 말씀은 해당 컬럼에 인덱스가 존재하고 업데이트가 자주 발생하는 경우라고 생각하고 말씀드리겠습니다. 자주 발생한다고해도 성능적인 이슈가 없다면( TOP SQL ) 불 필요하고 생각됩니다.
4) DBMS 마다 인덱스 종류가 차이가 있겠지만 인덱스 마다 이런 기준점이 달라질 수 있는지요?
벨런스 트리 인덱스는 항상 소트가 되어있기 때문에 중간에 있던 데이터가 맨 오른쪽으로 밀리면 어쩔수없이
인덱스 분할이 발생합니다. 물론 한상 발생하는것이 아니고 맨 오른쪽 ( 왼쪽 )의 리프블록이 공간이 부족시..
밸런스 트리 인덱스로 생성할때는 항상 이런 점을 염두해 두셔야 합니다.
5) 저도 결론은 인덱스 관리에 관한 자료를 찾을 수 있는 방법을 알려주시는 것이 좋아 보입니다
여기 사이트에 지식창고에 가면 널리고 널렸습니다.
알찬 답변 감사드립니다.
지식창고는 이해가 될지 모르겠지만, 공부하고 싶은 욕구가 막 생기네요.
대용량 데이터베이스 솔루션1 책이 도움 될 것 같은데... 다행히 파네요.
잘생긴 부쉬맨님 회사에서 출판했네요. ㅎㅎ
사는 김에 2까지 사서 열공을...... !!
사실 제가 답답해서 질문을 올린건,
중요, 주요 사용하는 테이블인데 성능에 문제가 있어서
해당 date 컬럼에 인덱스를 구성할려는 찰라에 궁금증이 생겼던 것입니다.
- 1시간 구간 데이터를 모두 select 획득하는데 1~2시간 소요/ㅠ,
"과연 인덱스 데이터에 대해 빈번한 갱신이 발생하는 상황에서 인덱스 성능이 언제까지 지켜질지,
과연 리빌드 상황까지 발생하는지, 얼마나 자주 리빌드를 해야하는지" 등의 궁금증 이었습니다.
용이하게 인덱스 구성과 리빌드가 가능했으면 직접 해 보고 몸으로 느끼는 체질이지만
테이블에 대한 서비스를 멈출 수는 없는 시스템이어서
그럴 수가 없었네요...ㅎㅎ
여기 많은 자료들을 보고, 많이 배우고 있는데
좀 더 찾아보고 더 배워야겠네요.
) 버튼을 클릭하여 작성 하시면 됩니다.