제 4 절 데이터베이스 I/O 원리1. 블록 단위 I/OOracle은 I/O 단위를 Block이란 용어를 쓰고, Altibase, DB2, MSSQL은 Page 란 용어를 쓴다. # 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽기 때문에, SQL 성능을 좌우하는 가장 중요한 성능 지표는 Access하는 블록 개수 # 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 Access해야 할 블록 개수 블록 단위 I/O 종류
2. 메모리 I/O vs 디스크 I/O가. I/O 효율화 튜닝의 중요성물리적인 Disk I/O가 필요할 때면, 서버 프로세스는 시스템에 I/O Call을 하고 잠시 대기 상태에 빠진다. 디스크 I/O 경합이 심할 수록 대기 시간도 길어짐. 그러므로, Oracle과 같은 Disk DBMS는 Disk I/O를 최소화 해야 한다. 만일 물리적인 Disk I/O를 피할 수 없다면, 높은 스펙의 Storage 장비를 이용해 SAN 환경을 구축하여 DB 성능을 높이곤 한다. MMDB인 Altibase 또한 Disk I/O를 고려해야 한다. checkpoint 시 대량의 I/O sync가 발생하면, DB 자체에 부하가 발생하기 때문에 어느 정도 Disk I/O 성능이 받쳐줘야 한다. 나. 버퍼 캐시 히트율 (Buffer cache Hit ratio)
Buffer cache Hit ratio : 물리적 디스크 읽기를 수반하지 않고, 곧바로 메모리에서 블록을 찾은 비율
BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) x 100
= ( (논리적 블록 읽기 - 물리적 블록 읽기) / 논리적 블록 읽기 ) x 100
= ( 1 - (물리적 블록 읽기) / (논리적 블록 읽기) ) x 100
논리적 블록 읽기 = 총읽은 블록 수
캐시에서 곧바로 찾은 블록 수 = 논리적 블록 읽기 - 물리적 블록 읽기
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 70 1168 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.03 70 1170 0 1
# 항목 설명
Disk : 물리적인 Disk를 경유한 블록 수
Query + current : 버퍼 캐시에서 읽은 블록 수
BCHR = (1 - (Disk / (Query + Current))) x 100
= (1 - (70 / (1170 + 0 ))) * 100
= 94 %
# I/O 효율화 튜닝의 핵심 원리
1. 논리적인 Block 요청 횟수를 줄인다.
2. 물리적으로 디스크에서 읽어야 할 블록 수를 줄인다.
위 2가지 원리를 SQL 튜닝으로 해결해야 한다.
# BCHR이 성능 지표로서 갖는 한계점
- 같은 블록을 반복적으로 access 하는 형태의 application이라면 논리적인 I/O 요청이 비효율적으로 많이 발생하는데도 BCHR은 매우 높게 나타남.
- 같은 블록을 여러 세션이 동시에 access함으로 인해 latch 경합과 Buffer lock 경합까지 발생한다면, 메모리 I/O 비용이 오히려 디스크 I/O이상으로 커질 수 있음.
다. 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향
Filesystem cache를 최소화해야 함. 데이터베이스 자체적으로 cache 역활을 하는 Memory 영역이 할당되어 있기 때문
각 DBMS 벤더별로 설치 매뉴얼의 OS recomment setting엔
File cache 영역을 최소화하도록 권고하고 있음.
알티베이스 기술문서의 각 OS 별 설정 가이드 참조
그러나 가장 근본적인 해결책은 논리적인 블록 요청 횟수 최소화
3, Sequential I/O vs Random I/O# Sequential I/O
- record 간 논리적 또는 물리적 순서를 따라 차례대로 읽어 나가는 방식, 그림에서 5번
- Table scan시에도 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 sequential access 방식임
# Random I/O
- record간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 Block 씩 접근하는 방식
- 그림에서 1번, 2번, 3번, 4번 6번
- 1번 2번 3번 acess는 인덱스 깊이에 따라 1 ~3 block 정도 읽는 것으므로, 대개 성능에 영향을 미치지 않음.
- 주로 4번, 6번 access가 성능 저하를 읽으킨다. => 인덱스에 포함된 row id를 가지고 테이블을 찾아가야 하기 때문에 random I/O 가 크다면 부하 발생
# I/O 튜닝 핵심 원리
# sequential access 선택도 높이기create table tas select * from all_objects order by dbms_random.value; select count(*) from t; COUNT(*) ---------- 77435 select count(*) from t where owner like 'SYS%'; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 1104 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 1105 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1104 pr=0 pw=0 time=0 us) 34952 TABLE ACCESS FULL T (cr=1104 pr=0 pw=0 time=26659 us cost=307 size=612612 card=36036) 선택도 = (34952 / 77435) * 100 = 45.13 % 읽은 블록 수는 1104 개 select count(*) from t where owner like 'SYS%' and object_name='ALL_OBJECTS' select count(*) from t where owner like 'SYS%' and object_name='ALL_OBJECTS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.07 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 1104 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.07 0 1105 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1104 pr=0 pw=0 time=0 us) 1 TABLE ACCESS FULL T (cr=1104 pr=0 pw=0 time=0 us cost=307 size=1360 card=40) 선택도 = ( 1 / 77435 ) * 100 = 0.001 % create index t_idx on t(owner, object_name); select /*+ index(t t_idx) */ count(*) from t where owner like 'SYS%' and object_name = 'ALL_OBJECTS'; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 105 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 105 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=105 pr=0 pw=0 time=0 us) 1 INDEX RANGE SCAN T_IDX (cr=105 pr=0 pw=0 time=0 us cost=6 size=1564 card=46)(object id 119277) Index range scan을 하더라도, 105 블록을 읽고 1 건의 record를 얻었다. 1개 레코드를 얻으려고 실제 스캔한 레코드 수 조회 select /*+ index(t t_idx) */ count(*) from t where owner like 'SYS%' and ((owner='SYS' and object_name >= 'ALL_OBJECTS') or (owner > 'SYS')); COUNT(*) ---------- 19663 선택도 = (1 / 19663) * 100 = 0.005% 인덱스 스캔 효율은 조건절에서 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받음. 인덱스 컬럼 순서 변경후 다시 실행 drop index t_idx; create index t_idx on t( object_name, owner); select /*+ index(t t_idx) */ count(*) from t where owner like 'SYS%' and object_name = 'ALL_OBJECTS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us) 1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=0 us cost=3 size=34 card=1)(object id 119278) 단 3건의 블록만 읽음. 루트 -> 브랜치 -> 리프 노드 순으로.... # Random access 발생량 줄이기drop index t_idx; create index t_idx on t( owner); select /*+ index(t t_idx) */ object_id from t where owner ='SYS' and object_name = 'ALL_OBJECTS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.02 0.02 70 1168 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.02 0.03 70 1170 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID T (cr=1168 pr=70 pw=0 time=0 us cost=5009 size=376 card=8) 30956 INDEX RANGE SCAN T_IDX (cr=68 pr=70 pw=0 time=11000 us cost=81 size=0 card=32554)(object id 119281) 1100개의 블록을 random access 했음. 테이블을 30956번 방문했지만 인덱스에서 테이블로 Random access 횟수가 1100(1168-68)번 발생했다. 이것은 buffer pinning 효과로 인한 I/O 감소 효과 => buffer pinning 효과는 시즌 2에서... drop index t_idx; create index t_idx on t(owner, object_name); select /*+ index(t t_idx) */ object_id from t where owner ='SYS' and object_name = 'ALL_OBJECTS'; select /*+ index(t t_idx) */ object_id from t where owner ='SYS' and object_name = 'ALL_OBJECTS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 5 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 5 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 84 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=0 us cost=4 size=47 card=1) 1 INDEX RANGE SCAN T_IDX (cr=4 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 119280) 테이블 1번 방문함. random access 횟수는 1번 (5 -4 ) 위 사항들이 이해 안되면 인덱스 튜닝 원리를 읽고 와라 4. Single Block I/O vs MultiBlock I/O# Single Block I/O - 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 것, - 인덱스를 통해 테이블을 access 할때는 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용함 - 인덱스의 논리적인 순서 때문에 single block I/O가 적합. - Index fast full scan시 논리적인 순서를 무시하고, 물리적인 순서에 따라 Multiblock I/O를 수행함. # Multiblock I/O - I/O Call이 필요한 시점에 인접한 블록(한 익스텐트 내)들을 같이 읽어 메모리에 적재하는 것, - OS 단에서는 보통 1MB 단위로 I/O 수행, 이건 OS마다 다름. (OS 명령 찾아볼 필요 있음) - Multiblock I/O로 읽더라도 익스텐트 범위를 넘지 못한다. - Multiblock I/O 단위는 db_file_multiblock_read_count 파라미터에 의해 결정됨 - Multiblock I/O 수행시 db_file_multiblock_read_count 파라미터 수치만큼 Buffer cache에 적재함. - OS level에서 허용하는 I/O 단위가 1 MB이면 db_block_size가 8192 일 때, 최대 설정할 수 있는 db_file_multiblock_read_count 파라미터 수치는 128이 된다. # 관련 시스템 이벤트 db file sequential read 대기 이벤트 : Single Block I/O 방식으로 I/O를 요청할 때 발생(알티베이스 경우 db file page read) db file scattered read 대기 이벤트 : Multiblock I/O 방식으로 I/O를 요청할 때 발생(알티베이스 경우 db file multi page read) # db file sequential read 대기 이벤트create table t as select * from all_objects;alter table t add constraint t_pk primary key(object_id); select /*+ index(t) */ count(*) from t where object_id > 0 call count cpu elapsed disk query current rows ----- ---- ---- ------ ---- ---- ----- ---- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.26 0.25 64 65 0 1 ----- ---- ---- ------ ---- ---- ----- ---- total 4 0.26 0.25 64 65 0 1 Rows Row Source Operation ---- ------------------------------ 1 SORT AGGREGATE (cr=65 r=64 w=0 time=256400 us) 31192 INDEX RANGE SCAN T_PK (cr=65 r=64 w=0 time=134613 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ------------------------------- Waited -------- --------- SQL*Net message to client 2 0.00 0.00 db file sequential read 64 0.00 0.00 SQL*Net message from client 2 0.05 0.05 # db file scattered read 대기 이벤트
drop table t;
create table t as select * from all_objects;
alter table t add constraint t_pk primary key(object_id);
alter session set events '10046 trace name context forever, level 8';
alter session set sql_trace=true;
select /*+ index(t) */ count(*) from t where object_id > 0;
alter session set sql_trace=false;
select r.value || '/' || lower(t.instance_name) || '_ora_' || ltrim(to_char(p.spid)) || '.trc' trace_file
from v$process p, v$session s, v$parameter r, v$instance t
where p.addr = s.paddr
and r.name='user_dump_dest'
and s.sid=(select sid from v$mystat where rownum=1);
tkprof orcl_ora_12736.trc report.prf sys=no
********************************************************************************
SQL ID: 0bv35x3fm7trw
Plan Hash: 4152626091
select /*+ index(t) */ count(*)
from
t where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 151 162 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 151 165 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=162 pr=151 pw=0 time=0 us)
77434 INDEX RANGE SCAN T_PK (cr=162 pr=151 pw=0 time=25981 us cost=176 size=1037218 card=79786)(object id 119272)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 20 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 11
round(151/11) = 14번 I/O call이 발생할거라 생각했는데, 20 번 발생함.
PARSING IN CURSOR #3 len=58 dep=0 uid=84 oct=3 lid=84
tim=1393826797728574 hv=3711166204 ad='11e510aa0' sqlid='0bv35x3fm7trw'
select /*+ index(t) */ count(*) from t where object_id > 0
END OF STMT
PARSE #3:c=25996,e=41653,p=12,cr=144,cu=0,mis=1,r=0,dep=0,og=1,plh=4152626091,tim=1393826797728574
EXEC #3:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4152626091,tim=1393826797728639
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1393826797728703
WAIT #3: nam='db file scattered read' ela= 92 file#=4 block#=841401 blocks=7 obj#=119272 tim=1393826797729683
WAIT #3: nam='db file scattered read' ela= 98 file#=4 block#=841408 blocks=8 obj#=119272 tim=1393826797730285
WAIT #3: nam='db file scattered read' ela= 79 file#=4 block#=841417 blocks=7 obj#=119272 tim=1393826797730918
WAIT #3: nam='db file scattered read' ela= 88 file#=4 block#=841424 blocks=8 obj#=119272 tim=1393826797731507
WAIT #3: nam='db file scattered read' ela= 76 file#=4 block#=841433 blocks=7 obj#=119272 tim=1393826797732117
WAIT #3: nam='db file scattered read' ela= 90 file#=4 block#=841440 blocks=8 obj#=119272 tim=1393826797740584
WAIT #3: nam='db file scattered read' ela= 81 file#=4 block#=841449 blocks=7 obj#=119272 tim=1393826797741222
WAIT #3: nam='db file scattered read' ela= 86 file#=4 block#=841456 blocks=8 obj#=119272 tim=1393826797741808
WAIT #3: nam='db file scattered read' ela= 76 file#=4 block#=841465 blocks=7 obj#=119272 tim=1393826797742431
WAIT #3: nam='db file scattered read' ela= 87 file#=4 block#=842880 blocks=8 obj#=119272 tim=1393826797742988
WAIT #3: nam='db file scattered read' ela= 93 file#=4 block#=842889 blocks=7 obj#=119272 tim=1393826797743666
WAIT #3: nam='db file scattered read' ela= 86 file#=4 block#=842896 blocks=8 obj#=119272 tim=1393826797744225
WAIT #3: nam='db file scattered read' ela= 77 file#=4 block#=842905 blocks=7 obj#=119272 tim=1393826797744855
WAIT #3: nam='db file scattered read' ela= 86 file#=4 block#=842912 blocks=8 obj#=119272 tim=1393826797745914
WAIT #3: nam='db file scattered read' ela= 71 file#=4 block#=843010 blocks=6 obj#=119272 tim=1393826797746540
WAIT #3: nam='db file scattered read' ela= 85 file#=4 block#=843016 blocks=8 obj#=119272 tim=1393826797747035
WAIT #3: nam='db file scattered read' ela= 95 file#=4 block#=843024 blocks=8 obj#=119272 tim=1393826797747680
WAIT #3: nam='db file scattered read' ela= 94 file#=4 block#=843032 blocks=8 obj#=119272 tim=1393826797748309
WAIT #3: nam='db file scattered read' ela= 86 file#=4 block#=843040 blocks=8 obj#=119272 tim=1393826797748972
WAIT #3: nam='db file scattered read' ela= 101 file#=4 block#=843048 blocks=8 obj#=119272 tim=1393826797749633
FETCH #3:c=12998,e=21280,p=151,cr=162,cu=0,mis=0,r=1,dep=0,og=1,plh=4152626091,tim=1393826797750011
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=162 pr=151 pw=0 time=0 us)'
STAT #3 id=2 cnt=77434 pid=1 pos=1 obj=119272 op='INDEX RANGE SCAN
T_PK (cr=162 pr=151 pw=0 time=25981 us cost=176 size=1037218
card=79786)'
WAIT #3: nam='SQL*Net message from client' ela= 208 driver id=1650815232 #bytes=1 p3=0 obj#=119272 tim=1393826797750473
FETCH #3:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4152626091,tim=1393826797750513
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=119272 tim=1393826797750538
WAIT #3: nam='SQL*Net message from client' ela= 723 driver id=1650815232 #bytes=1 p3=0 obj#=119272 tim=1393826797751279
CLOSE #3:c=0,e=17,dep=0,type=0,tim=1393826797751317
select extent_id, block_id, bytes, blocks
from dba_extents
where owner=USER
and segment_name='T_PK'
and tablespace_name='USERS'
order by extent_id;
SQL> select extent_id, block_id, bytes, blocks
2 from dba_extents
3 where owner=USER
4 and segment_name='T_PK'
5 and tablespace_name='USERS'
6 order by extent_id;
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 841384 65536 8
1 841392 65536 8
2 841400 65536 8
3 841408 65536 8
4 841416 65536 8
5 841424 65536 8
6 841432 65536 8
7 841440 65536 8
8 841448 65536 8
9 841456 65536 8
10 841464 65536 8
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
11 842880 65536 8
12 842888 65536 8
13 842896 65536 8
14 842904 65536 8
15 842912 65536 8
16 843008 1048576 128
17 rows selected.
# 위 테스트 결과는 11g에서 수행한 것임. 10g 부터는 테이블 access 없이 index range scan할 경우에도 multiblock I/O 수행
Multiblock I/O 방식으로 읽더라도 익스텐트 범위를 넘지 못하기 때문에 db_file_multiblock_read_count이 11이라 하더라도,
db file scattered read 시 8개 이하로읽게 됨.
Single Block I/O시 읽은 블록들은 LRU 리스트 상 MRU쪽으로 연결되어 한번 적재되면 buffer cache에 비교적 오래 머문다.
Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU 쪽에 연결되므로, 적재되고 얼마 지나지 않아 버퍼 캐시에서 밀려난다.
대량의 데이터를 Full scan 했다고 해서 사용빈도 높은 블록들이 버퍼 캐시에서 모두 밀려날 것을 우려하지 않아도 됨.
Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU 쪽에 연결되므로, 적재되고 얼마 지나지 않아 버퍼 캐시에서 밀려난다.
대량의 데이터를 Full scan 했다고 해서 사용빈도 높은 블록들이 버퍼 캐시에서 모두 밀려날 것을 우려하지 않아도 됨.
대량의 데이터를 Full scan 했다고 해서 사용빈도 높은 블록들이 버퍼 캐시에서 모두 밀려날 것을 우려하지 않아도 됨.
대량의 데이터를 Full scan 했다고 해서 사용빈도 높은 블록들이 버퍼 캐시에서 모두 밀려날 것을 우려하지 않아도 됨.
|
2014년 7월 10일 목요일
Database I/O efficiency
피드 구독하기:
댓글 (Atom)
댓글 없음:
댓글 쓰기