DMS ?? ????? ??? DB ????
?????. DB2 ? XpertMon ??? ???! ? ????????? DB??????.
?? ?? XpertMon ????? ?? ??? ???? ???? ? ? ???? ????? ?? ?? ??? ?? ???, XpertMon? ??? ????? ?? ???? History? ????(DPF, OLTP, Batch …) ? ?? ??? KPI? ???? ?? ?? ???? ? ??? ??? ????. ?? ???? ???? ???? ????? ??? ???? ?? ???? ?????.
?? ?? “DMS ?? ????? ??? DB ????” ? ?? ????? ?????.
???? ?? ????? ???, ?? ?????? ?? ???? ( www.iteg.co.kr) ???? ????? ?? ? ????. ???? ?? ?? ???? ??? ??? ??? ??????. ???? ????? ?????.
AGENDA
1. ??
A. I/O ??
2. ?? ???? ??? SMS ?? DMS ??? ????? I/O ??
A. Buffer? ??? ?? ??? (File system buffered I/O)
B. Memory Map? ??? ?? ??? (Memory mapped I/O)
C. Direct I/O
D. Concurrent I/O
3. DB2 UDB ???? DIO ? CIO ????
A. ??? ???? ??? DIO/CIO ??
B. ?? ??? ???? ??
4. DB2 UDB?? DIO/CIO? ???? ?? AIX ML & Fixes
5. ??
1. ??
??????? ?? ???? ?? ??? ?? ?? ?? I/O? ?? ?????. ?? I/O ??? ???? ?? ???? ?? ? ???? ??? ?? ??? ????? ?????.
A. I/O ??
DB2 UDB V8.2 ? ??? ??? ??? ?? ??? ??? I/O ??? ?????.
l Buffer? ??? ?? ???
l Memory Map? ??? ?? ??? (only AIX)
l Raw I/O
? ??? ? ? ???? ?? ??? ?? ??? ?? ??? Raw I/O? ??? ?????. ?? ?? ???? ??? ?? ???? caching ? ?? lock? ???, ?? Disk??? ?? Data? ?? ?? ?????.
???, ?? ? ? ?? ?? ??? Vendor? ?? DIO (Direct I/O) ?? ??? I/O ????? ???? ??? Raw I/O? ?? ???? ???????. ?? ???? ??? ??? ???? ??? ??? Raw I/O? ??? ??? ?? ??? ??? ???? caching ? ?? lock? ??? ? ? ?? ? ? ???. ??? AIX 5.2.10 ??? ??? ??? ??? CIO(Concurrent I/O)? JFS2 ?????? ?? Raw I/O? ???? ??? ??? ?????.
2. ?? ???? ??? SMS ?? DMS ??? ????? I/O ??
A. Buffer? ??? ?? ??? (File system buffered I/O)
?? ???? ?? ??? ?? ???? Process? File?? Data? ?? ??? ? ?, Disk?? ?? ?? ?? ?? ??? File System Buffer Cache ? ??? Main Memory? Data? Loading ? ?? ?? ?? ?????.
File? ?? ??? ??(read) ??? ??? ??, ?????? ????? ?? ??? Buffer cache? ??? ??? ???? ???. ?? Buffer cache? Data? ?? ?? ?? ??, Disk??? Data? ?? Buffer cache? ?? ? ? ??? ?? ???.
?? ??? ?? ??? ??? ?? ??(Read) ??? ?? ?? ????.
File cache? ??? ?? ??(write)? Disk? ?? Write ????? ?? ? ??? ???. ? ?? ??? ??? ??, ?? ?? ??? ?????? ???? ??? ?? ?? Data? Disk? Write ?? ?? ??? ? ?? ????(Wait)? ?? ? ?? ???.
?? ??? File System Buffer Cache? ?? ??(Write) ??? ??? ?? ?? ????.
File System Buffer Cache? I/O ??? ?? ??? ???? ???? ??? ??? ???? ?? ??? ????.
?? ???? ?? AIX? ?? JFS(JFS2)? ?? ??? ???? ?? ?? ??? File System Buffer Cache ?? ??? ? ????.
(maxclient% Parameter??: Real Memory?? ??? ? ?? ?? File System Buffer Cache Ratio (%))
B. Memory Map? ??? ?? ??? (Memory mapped I/O)
Memory Map I/O(MMAP I/O) ??? ??? ?? Data? ????(DB2 agent ?)? Address Space? Mapping? ?? ???? ?????.
Memory Map? ??? ??, File? ??? ??? ??(read)? ??(write)? ??? ??? ? ??? ?? ????.. JFS2? ??? MMAP I/O? DIO? ?? ??? ? ??? ?? ????.
MMAP I/O ??? ?? ?? ??? ????? ??? ??, ?? ??? ?? ?? ??? ???(mount) ? cio ? dio ? “???? ??” ?? ??? ??? ??. DB2 UDB??? ????? ??? DB2_MMAP_READ ? DB2_MMAP_WRITE? ??? MMAP I/O ??? ??? ? ?????. (V9.5 ????? DB2 Process? Thread ??? ??????, ?? DB2_MMAP_READ, DB2_MMAP_WRITE Registry ??? Setting ????, ??? ??? ????.)
C. Direct I/O
?? ????? Data? ?? ?? ?? Application? ???? Buffer? ??? File System? ??, File Buffer Cache??? ???? Data Ratio? ?? ?? ?? Buffer??? ??? ??? ???. ??? DB2 UDB? ?? “Bufferpool” ??? Data Cache? ?? ??? ?? ??? File System Buffer Cache? ????? ?? ?????? Data? File Buffer Cache? ??? ? ??? Application Buffer? Data? ??? ?? Double-Copying ? ???? ???. ? ? File System Buffer Caching? ???? ??, ?? Application Buffer? Disk? Data? ?? ?? ?? Direct I/O ?????. (AIX? ??, JFS ? JFS2 ??? DIO ????)
Direct I/O? ??? ?? ??? ????.
D. Concurrent I/O
Buffered I/O? Direct I/O? ??? ??? File? ?? inode-lock? write-lock? ????? ????. ?? ???? ??? ??? ???? ?? ??? ??? ???? ?? ????, ?? ?????? ?????? ?? ? ?? ??? ???? ???.
Inode-Lock ?? ??? ?? ???, ?? ? ?? ?? ??? ?? ??? ??? inode? ?? ??? ??? ?? inode ?? ???? ?? ?? ??????? ? ??? inode? ??? ? ??? inode ?? ???? ?? lock? ????. ? ?? inode lock? ?? ? ??? ??? ?? ?? ?? ??? ?? ? ?? ????.
JFS2 ?? ??? ??? ? ?? Thread? ??? ??? ??? ?? ??? ? ??, read? ???? ?? ?? ?? ?? ??? ? ? ???, Thread2? ?? Write ?? ? Thread1? ?? ?? ??? ???? ???.
AIX 5.2 ML 01 ??? Concurrent I/O (CIO) ??? ?? ?????. CIO? ???? ?? inode lock ???? ?? ??? overhead? ??? ?? ??? ?? ? ??? ???? inode? shared lock ??? ??????, ??? ?? ?? ??(extend), ??(truncate), ?? ?? ?? ??? ?? lock ??? write-exclusive lock ?? ????? ????.
CIO ???? Inode lock ??? ??? ??, ??? ?? Read-Write? ??? ???? ?? ? ? ???, ??? ??? ????(extend, truncate) ?? ??? inode lock? shared lock?? write exclusive? ?? ?? ???. ? ???? ??? ?? ?? ??? ????? ??, ??? ?? ??? ?? ?? ??? ??? ??? ??????. ?? ?? CIO? ??? ? Raw I/O? ???? ??? ??? ? ?? ? ???.
3. DB2 UDB ???? DIO ? CIO ????
CIO? ???? ???? ????? ???? MMAP I/O ??? ?? ???? ???? ???? ?? ???? ??? ???? ?? ????. ? ??? ???? DB2 UDB V 8.2? ????? DB2_MMAP_READ? DB2_MMAP_WRITE ????? ??? ????? CIO ??? ??? ? ??? ?? ?? ??? ?? ??? ?? ????.
A. ??? ???? ??? DIO/CIO ??
CREATE/ALTER TABLESPACE ?? ??? ??? NO FILE SYSTEM CASHING ? FILE SYSTEM CASHING ??? ?? DIO ? CIO? ?? ?? ??? ? ? ??? ?????.
? 1. CREATE TABLESPACE <tablespace name> …
? ?? ????? FILE SYSTEM CASHSING ??? ??? ?? ??? ??? I/O ??? ???? ???.
? 2. CREATE TABLESPACE <tablespace name> … NO FILE SYSTEM CASHING
?? ??? ????? ??? ?? caching ??? OFF ??? ? ? ????.
? 3. ALTER TABLESPACE <tablespace name> … NO FILE SYSTEM CASHING
???? ??? ???? ??? I/O ??? ???? ??? ? ?, ???? ???????? ?? ????? ??? ??? ?????.
?, ? ??? ???? ???? ??????? ??? ??????? ???? ??? ???.
??? ??? NO FILE SYSTEM CASHING ??? ???? ????.
l SMS large file (LF)
l SMS large object files (LOB)
l SMS/DMS temporary tablespaces
B. ?? ??? ???? ??
JFS ? JFS2 ?? ???? ?? ???(mount) ? ??? ?? DIO/CIO ??? ??? ? ????.
File System Types
|
Mount commands
|
Direct I/O (JFS)
|
mount –o dio <file system name>
|
Concurrent I/O (JFS2)
|
mount –o cio <file system name>
|
?? ???? ?? -o dio/cio ??? ???? ?? ?? ??? ?? ??? ?? ??? ??? DIO/CIO? ????? ?????.
4. DB2 UDB?? DIO/CIO? ???? ?? AIX ML & Fixes
Platforms
|
File System Types
|
Recommended Fixes
|
I/O mechanism
|
AIX 4.3.3
|
JFS
|
None
|
Direct I/O
|
AIX 5.1
|
JFS, JFS2
|
None
|
Direct I/O
|
AIX 5.2
|
JFS
|
None
|
Direct I/O
|
AIX 5.2
|
JFS2
|
Maintenance Level 3 and
Additional AIX APARS
|
Concurrent I/O
|
5. ??
??????? ????? ??? ??? ?? CIO/DIO? ???? ?? ?? ?? ?? ??? ??? ?? ?? CPU ? ?? ???? ???? ?? ? ???, Raw ????? ??? ??? ??? ???? ???.
?? ???? DB2 V9.5??? NO FILE SYSTEM CASHING ??? ???? ?????.
l ?? ??
Improve database performance on file system containers in IBM DB2 UDB V8.2 using Concurrent I/O on AIX
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-04081ee
|