검색결과 리스트
글
DBMS 메모리 구조
링크 :
- 1. 데이터 파일의 구조
- 2. DBMS 메모리 구조
- 3. 인덱스 기본
- 4. 정렬을 제거하는 방법
- 5. 프로시져 캐시 경합 확인하기
- 6. Loop 쿼리 제거하기
- 7. 튜닝 결과 샘플 보기
MS SQL의 시스템 공유 메모리 영역을 Memory Pool 이라고 한다.
여러 쓰레드가 동시에 액세스 할 수 있는 메모리 영역이다.
캐시 영역은 매우 다양한데 모든 DBMS가 공통적으로 사용하는 캐시 영역은 위와 같다.
메모리를 공유하기 위해 액세스 직렬화 메커니즘이 사용된다.
MSSQL은 쓰레드 기반 아키텍쳐이기 때문에 Worker Thread 전용 메모리 영역이 없다. 쓰레드는 부모 프로세스의 메모리 영역을 사용한다.
DB 버퍼 캐시
데이터 파일로부터 읽어 들인 데이터 Page를 담는 영역을 DB 버퍼 캐시라고 한다.
인스턴스에 접속한 모든 사용자 스레드는 Worker Thread를 통해 DB 버퍼 캐시의 버퍼 블록을 동시(내부적으로 버퍼 LOCK을 통한 직렬화)에 액세스할 수 있다.
Direct Path Read 메커니즘이 작동하는 경우를 제외하면, 모든 페이지 읽기는 버퍼 캐시를 통해 이루어진다.
즉, 읽고자 하는 페이지를 먼저 버퍼 캐시에서 찾아본 후 없으면 디스크에서 읽는다. 디스크에서 읽을 때에도 먼저 버퍼 캐시에 적재한 후에 읽는다.
데이터 변경도 버퍼 캐시에 적재된 페이지를 통해 이루어지며, 변경된 블록을 주기적으로 데이터 파일에 기록하는 작업은 Lazy writer 가 한다.
버퍼 블록의 상태
- Free 버퍼: 비어있거나 (Clean 버퍼), 데이터가 있지만 파일과 동기화되어 덮어써도 무방
- Dirty 버퍼: 캐시 된 후 변경이 발생했지만 동기화가 되지 않은 상태
- Pinned 버퍼: 읽기 or 쓰기 작업이 진행 중인 버퍼 블록
데이터 파일 (디스크) -> 로드 -> Free 버퍼 확보 -> 변경 -> Dirty로 상태 변경 -> 동기화 -> Free
LRU 알고리즘 (Least recently used)
캐시는 유한 자원, 사용 빈도가 높은 데이터 블록 위주로 캐시를 구성하는 알고리즘
모든 버퍼 블록 헤더를 LRU 체인에 연결 후 사용 빈도 순으로 정렬된 상태를 유지
FREE 버퍼가 필요한 순간에 액세스 빈도가 낮은 순으로 데이터 블록을 캐시에서 밀어낸다.
프로시저 캐시
LRU 알고리즘 사용
캐싱된 SQL과 생성계획의 재사용성을 높이는게 중요하다.
테이블, 인덱스, 파일그룹, 데이터 파일, Heap/Index 구조 오브젝트, 익스텐트, 사용자, 제약에 관한 메타 정보를 저장한다.
SQL 파싱 순서
실행 계획을 메모리 풀에 캐싱해 두는 이유는 하드 파싱 비용이 비싸기 때문이다.
실행 계획 경우의 수를 계산하는 방법은 테이블이 n개일 때 조인 경우의 수는
해당 결과에 조인 종류, 스캔 경우의 수, 테이블 스캔 or 인덱스 스캔 여부 확인, 인덱스를 탄다면 어떤 인덱스를 탈지... 모든 경우의 수를 따지면 하드 파싱은 매우 비싸진다.
때문에 한번 만들어둔 실행 계획을 다시 사용하는 것을 소프트 파싱이라고 한다.
소프트 파싱의 키 값은 SQL 문장 그 자체 (문자열이 이름이다.) 이며 별도의의 SQL ID를 부여하는 DBMS의 경우도 SQL ID와 SQL 문자이 1:1로 대응한다.
소프트 파싱을 잘 활용하기 위해서는 실행 계획을 공유하지 못하는 경우를 이해할 필요가 있다.
- 공백 문자 또는 줄바꿈
SELECT * FROM USERDB;
SELECT *
FROM USERDB;
- 대소문자 구분
SELECT * FROM USERDB;
SELECT * FROM userdb;
- 주석
SELECT * FROM USERDB;
SELECT * /* 모든 칼럼*/ FROM USERDB;
- 스키마 명시
SELECT * FROM USERDB;
SELECT * FROM dbo.USERDB;
- 조건절 비교값
SELECT * FROM dbo.USERDB WITH(NOLOCK) WHERE ID = '철수';
SELECT * FROM dbo.USERDB WITH(NOLOCK) WHERE ID = '영희';
위의 예시들 중 가장 큰 문제가 되는 경우는 마지막 경우이다. 만약 로그인 쿼리나 아이템 페이지 목록을 얻는 쿼리 등을 작성한다고 생각해보면 프로시저 캐시는 다른 이름의 동일한 기능을 하는 실행계획으로 가득찰 것이다. 위와 같은 쿼리를 Literal SQL 이라고 한다. Literal SQL은 프로시저 캐시 효율과 밀접한 관계가 있다.
http://databaser.net/moniwiki/pds/OracleServer/Oracle_Hard_Parsing.pdf
바인드 변수 사용하기
- 실행 계획을 캐시에 저장 후 실행 시점에 값을 바인딩
사용 시 파싱 소요 시간과 메모리 사용량이 감소한다. (CPU, 메모리 사용률)
리터럴 변수 변수 사용은 라이브러리 캐시 경합을 발생시켜 시스템 성능을 저하시킨다.
바인드 변수 사용 시 주의 사항
변수 바인딩 시점이 쿼리 최적화 후, 옵티마이저는 조건절 칼럼의 데이터 분포가 균일하다고 가정한다.
칼럼에 대한 히스토그램 정보가 프로시저 캐시에 있지만 사용할 수 없다. 때문에 균일하지 않을 때 성능이 다르게 나타날 수 있다.
바인드 변수 부작용 극복 노력
Parameter Sniffing - 첫 수행 시 바인드 값을 훔쳐보고 칼럼 분포를 이용해 실행 계획 결정하는 방법
첫 실행 값에 따라 성능이 달라지는 등의 문제가 있다. 보통 해당 기능은 사용하지 않는다.
https://technet.microsoft.com/ko-kr/library/ms191006(v=sql.105).aspx
RECENT COMMENT