전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
Latches
- 래치는 공유된 데이터 구조, 객체, 그리고 파일에 대한 다수 사용자 액세스를 조정하는데 사용되는 경량의 직렬화 장치이다.
- shared pool에서 데이터베이스 블록 버퍼 캐시 또는 라이브어리 캐시처럼 특정 메모리 구조를 보호하는데 사용된다.
래치 spining
- 래치는 락의 일종이고 락은 직렬화 장치며 직렬화 장치는 확장성을 저해한다는 점이다.
- SQL 문을 파싱하는 것은 단순한 행위일지라도 shared pool에서 라이브어리 캐시와 관련된 데이터 구조에 대하여 수백 또는 수천 개의 래치를 획득하고 해제한다.
- 래치를 기다리는 일은 값비싼 작업일 수 있다.
- 프로세스는 래치를 즉시 획득할 수 없다면 프로세스 대기열로 가서 CPU를 포기하고 나중에 CPU 스케줄링 시 자기 차례가 돌아왔을 때 다시 시도하기보다는 CPU 상에 머물면서 지속적으로 시도한다.
- 스핀하면서 꾸준하게 래치를 얻으로고 한 후에도 래치를 획득하는데 실패하면 그때는 프로세스가 sleep 하거나 자신을 CPU로부터 떼어내서 다른 작업을 수행할 것이다.
- 단일 세션이 오랜 시간 동안 래치를 보유해서라기보다는 많은 세션이 동시에 래치를 원하고 또한 각 세션이 짧은 시간 동안 래치를 보유하기 때문에 발생하는 문제이다.
- 래치를 얻기 위한 psuedo 코드는 아래와 같다.
Loop
for i in 1 .. 2000
loop
try to get latch
if got latch, return
if i = 1 then misses = misses+1
end loop
INCREMENT WAIT COUNT
sleep
Add WAIT TIME
End loop;
- 위 로직은 래치 획득을 시도하다가 실패하면 miss 카운트를 증가시킨다.
- 일단 프로세스가 실패하면 계속하여 래치 획득을 시도하면서 정해진 횟수만큼 루프를 돌 것이다.
- 이 중에 한 번이라도 획득 시도가 성공하면 리턴해서 계속해서 작업을 처리할 것이다.
- 만약에 모두 실패하면 그 래치에 대한 sleep 카운트를 증가시킨 후 짧은 시간 동안 sleep할 것이다.
- 상당히 많은 CPU를 사용하게 된다. CPU가 많이 소요되고 있어 시스템은 매우 분주한 듯이 보이지만 그리 많은 일을 하지는 않는다.
공유 자원을 래칭하는 비용 측정하기
- 예제를 이용하여 shared pool을 래칭(래치를 획득하고 해제하는 행위)하는 비용을 알아보자.
- 다중 사용자 환경에서 이들 프로그램을 평가하기 위해 측정값을 수집하기 위해 statspack을 사용할 것이다.
- 시스템의 현 상태를 수집하기 위해 statspack 스냅샷을 실행한다.
- 동일한 프로그램을 N번 실행한다. 모든 프로그램이 같은 테이블에 삽입하면서 발생할 수 있는 경합을 피하기 위해 각 프로그램이 자신만이 보유한 데이터베이스 테이블에 삽입하도록 한다.
- N번째 프로그램이 종료하는 즉시 또 다른 스냅샷을 확보한다.
Note
- 위 분석을 수행하는데 AWR(Automatic Workload Repository)을 사용하지 않는 이유가 있다.
- 누구나 statspack에 엑세스할 수 있기 때문이다. 물론 DBA가 statspack을 설치해야 하지만, 오라클 사용자는 모두 statspack에 접근 가능하다.
- 그래서 모든 사용자가 결과를 재현할 수 있도록 하고 싶기 때문이다.
- 이 테스트는 하이퍼쓰레딩 기능이 활성화된 듀얼 CPU 장비에서 수행했다.
- 한 명의 사용자가 삽입을 처리하기 위해 1개의 CPU를 사용한다면 두 명일 때는 2개의 CPU를 사용할 것이라 짐작할 수 있다.
- SQL 문을 파싱하기 위해 공유 메모리 구조인 shared pool을 래칭할 필요가 있고, 다른 사람이 shared pool을 읽는 동안 변경할 수 없으므로 shared pool이 변경되는 중에는 읽을 수도 없다.
테스트 설정하기
- 테스트를 위해서는 경합이 필요하다.
- 경합을 측정하기 위해 다수의 사용자로 진행할 것이다.
- 사용자당 한 테이블씩 10개의 테이블을 생성하고 테이블명은 T1에서 T10으로 한다.
SQL> begin
2 for i in .. 10
3 loop
4 for x in (select * from user_tables where table_name = 'T'||i )
5 loop
6 execute immediate 'drop table ' || x.table_name;
7 end loop;
8 execute immediate 'create table t' || i || '(x int)';
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
다음 절차대로 테스트를 진행한다.
- statpack.snap을 실행한다.
- 즉시 자바 루틴을 N번 시작한다. 여기서 N은 1부터 10(1명에서 10명의 동시 사용자를 의미)까지 변한다.
- N번 실행이 완료하기를 기다린다.
- statpack.snap을 실행한다.
- 마지막 2개의 statpackID에 대하여 statpack 리포트를 생성한다.
- 다음의 실행에서 제공되는 숫자는 이 절차를 따라 수집되었다.
바인드 변수 없음
- 첫 번째 인스턴스에서 자바 프로그램은 바인드 변수를 사용하지 않고, 데이터를 삽입하기 위해 문자열 연결을 사용할 것이다.
import java.sql.*;
public class instest
{
static public void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@dellpe:1521:ora10gr1",
"scott","tiger");
conn.setAutoCommit( false );
Statement stmt = conn.createStatement();
for( int i = 0; i < 25000; i++ )
{
stmt.execute
("insert into "+ args[0] +
" (x) values(" + i + ")" );
}
conn.commit();
conn.close();
}
}
- 단일 사용자 모드로 테스트를 실행했으며, statpack 리포트는 아래의 정보를 보여준다.
Elapsed: 0.52 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 768M Std Block Size: 8K
Shared Pool Size: 244M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
...
Parses: 810.58 12,564.00
Hard parses: 807.16 12,511.00
....
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 26 55.15
class slave wait 2 10 21.33
Queue Monitor Task Wait 2 10 21.33
log file parallel write 48 1 1.35
control file parallel write 14 0 .51
참고로 SGA 구성 정보를 포함했으며 관련 통계는 아래와 같다.
- Elapsed time of approximately 30 seconds
- 807 hard parses per second
- 26 CPU seconds used
- 아래는 2개의 프로그램 실행하면 하드파싱이 1600회 뛸 것이고 CPU 시간은 아마도 52CPU 초로 2배 정도 뛸 것이다.
Elapsed: 0.78 (mins)
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Parses: 1,066.62 16,710.33
Hard parses: 1,064.28 16,673.67
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 74 97.53
log file parallel write 53 1 1.27
latch: shared pool 406 1 .66
control file parallel write 21 0 .45
log file sync 6 0 .04
- CPU 시간은 세 배로 뛰었다. 오라클의 래치 구현 때문이다.
- 멀티 CPU 장비에서 래치를 즉시 얻을 수 없을 때는 스핀한다. 스핀 행위가 CPU를 소비한다.
- 프로세스 1은 shared pool에서 래치를 획득하기 위한 시도를 많이 한 후에야 프로세스 2가 그 래치를 보유하고 있다는 것을 알게 된다.
- 프로세스 1이나 프로세스 2 모두 래치를 얻기 위해 스핀하는데 많은 CPU를 소비한다.
- 실제 일은 얼마 하지도 못하고 대부분의 처리 시간을 자원을 가용할 때까지 기다리며 보냈다.
- statspack 리포트 페이지를 'Latch Sleep Breakdown' 페이지까지 넘기면 다음 사실을 알 수 있다.
Latch Name Requests Misses Sleeps Sleeps 1->3+
---------------- ------------- ----------- -------- ------------
shared pool 1,126,006 229,537 406 229135/398/4/0
library cache 1,108,039 45,582 7 45575/7/0/0
- 406이란 숫자가 Sleeps 컬럼에 나타난지 알아야 한다.
- Latch Sleep Breakdown 리포트는 래치를 얻기 위해 스핀 루프에서 시도한 횟수와 실패한 횟수를 보여준다.
- 226,537 misses는 Top5에서 나타나지 않는다.
- 2개의 단위 작업을 수행하기 위해서 3개의 단위 CPU가 필요했다.
- 이는 전적으로 공유 자원, 즉 shared pool을 필요로 하고 있다는 사실에 기인한다. 이것이 래칭의 본질이다.
- 추가적으로 스핀에 위해서 시스템이 얼마나 많은 CPU 시간을 결정하는 것은 가능하지 않다.
- 두 명의 사용자가 테스트를 하면서 아는 것이라고는 CPU를 74초 사용했다는 것과 shared pool에 대한 래치를 획득할 기회를 229,537번 놓쳤다는 것이다.
- 기회를 놓칠 때마다 래치를 얻기 위한 시도를 계속하면서 몇 번이나 스핀했는지는 모르기 때문에 스핀하는데 CPU 시간을 얼마나 소비했고 처리하는데 얼마나 소비한지에 대해서는 측정할 방법이 없다.
- 이 테스트에서 단일 사용자의 예를 비교해보면 우리는 자원을 기다리는데 래치가 스핀하는데 22 CPU초를 소비한 것을 알 수 있다.
바인드 변수 사용
- 동일한 테스트지만 현저하게 적은 래치를 이용하는 프로그램을 사용할 것이다.
- 바인드 변수를 사용하도록 코딩할 것이다.
import java.sql.*;
public class instest
{
static public void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@dellpe:1521:ora10gr1",
"scott","tiger");
conn.setAutoCommit( false );
PreparedStatement pstmt =
conn.prepareStatement
("insert into "+ args[0] + " (x) values(?)" );
for( int i = 0; i < 25000; i++ )
{
pstmt.setInt( 1, i );
pstmt.executeUpdate();
}
conn.commit();
conn.close();
}
}
- 단일 리포트는 다음과 같다.
Elapsed: 0.12 (mins)
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
...
Parses: 8.43 29.50
Hard parses: 0.14 0.50
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 4 86.86
log file parallel write 49 0 10.51
control file parallel write 4 0 2.26
log file sync 4 0 .23
control file sequential read 542 0 .14
- 위에 비교는 획기적이다.
- 26CPU 초가 4 CPU초로 줄었다.
- 807 하드 파싱도 0.14로 줄었다.
- elapsed 시간도 45초에서 8초로 줄었다.
- 바인드 변수를 사용할지 않았을 때 CPU 시간을 5/6 사용했다.
- 이것은 전적으로 래치와 관련있지는 않다. 바인드 변수 없이 발생하는 CPU 시간은 SQL 최적화와 파싱하는데 사용된다.
- SQL 파싱은 CPU를 집중적으로 사용하지만 정말 유용하지 않은 일에 5/6을 쓴다는 것은 대가가 크다.
- 사용자 2명으로 테스트 한다.
Elapsed: 0.20 (mins)
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Parses: 6.58 26.33
Hard parses: 0.17 0.67
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 11 89.11
log file parallel write 48 1 9.70
control file parallel write 4 0 .88
log file sync 5 0 .23
log buffer space 2 0 .05
- CPU 시간은 단일 사용자일 때보다 약 2~2.5배 증가했다.
Note
- 반올림 때문에 CPU 시간 4초는 실제로 3.5와 4.49 사이에 그리고 11은 10.5와 11.49 사이의 길이이다.
- 더 나아가서 바인드 변수를 이용하여 두 명의 사용자가 사용한 CPU 양은 바인드 변수를 쓰지 않고 한 명의 사용자가 사용한 CPU 보다 적다.
- statspack 리포트에서 래치 절을 보면 공유 폴과 라이브어리 캐시에 대한 경합이 너무 작아서 리포팅할 가치조차 없다는 것을 알 수 있다.
- 더 깊이 들어가면 shared pool에 대한 래치는 50.367번 그리고 바인드 변수없는 두 명의 사용자 테스트에서는 1,000,000번 이상 요구된 것을 발견할 수 있다.
성능/확장성 비교
- 표6.1은 사용자 수를 증가시키면서 래칭 결과 뿐만 아니라 각 구현마다 CPU 사용량을 요약하고 있다.
뮤텍스
- 뮤텍스는 래치와 비슷한 직렬화 장치이며 상호 배타를 나타낸다.
- 오라클 10g 릴리즈 1에서 도입되어 전통적인 래치 대신 사용되고 있다.
- 래치에 비해서 훨씬 경량이다.
- 뮤텍스 구현을 위한 명령어는 래치 구현에 비해 대략 1/5 정도의 코드로 가능하고 구현에 필요한 메모리도 1/7 정도로 덜 사용한다.
- 경량이라는 점을 제외하고는 다른 면에서는 기능이 더 떨어진다.
- 뮤텍스는 좀 더 가벼운 직렬화 장치이다. 어쩌면 래치보다 확장성이 뛰어나지만 여전히 직렬화 장치에 불과하다.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4407
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.