|
DB2 Performance – Just the Q&As
|
|
|
?????. DB2 ? XpertMon ??? ???! ? ????????? DB??????.
?? ?? XpertMon ????? ?? ??? ???? ???? ? ? ???? ????? ?? ?? ??? ?? ???, XpertMon? ??? ????? ?? ???? History? ????(DPF, OLTP, Batch …) ? ?? ??? KPI? ???? ?? ?? ???? ? ??? ??? ????. ?? ???? ???? ???? ????? ??? ???? ?? ???? ?????.
?? ??? “DB2 Performance ?? Q&A” ? ?? ?? ????? ?????.
???? ?? ????? ???, ?? ?????? ?? ???? ( www.iteg.co.kr) ???? ????? ?? ? ????. ???? ?? ?? ???? ??? ??? ??? ??????. ???? ????? ?????.
1. Do I still need to use raw containers for best performance?
l Filesystem??? ??? DB?? Filesystem Caching? ???? overhead? ??? ???????.
l NO FILE SYSTEM CACHING ??? ?? Filesystem Caching Overhead? ????, ?? ?? Performance? ???????. (?? option? DB2V9.5?? default?.)
Ø ??, V9.5 ?? ???? Tablespace? ??? ? ? Engine Migration? ??? ??? Tablespace? Default option ? ???? ????, “Alter tablespace” ? ???? ???..
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0408lee/
2. Can I keep things simple and use DMS for everything, including Tempspaces?
l ??? ???? Temp Tablespace ?? ? DMS ??? SMS ???? ???? ????.
Ø ?? ??? ?? Size? Filesystem? ????? ???? ???? ?? ? ???? ??? overhead? ???? ???. ??? ?? ???? ????? Temporary Tables? Activity? ??? ? ????.
Ø Tip: db2pd –tcbstats or db2 snapshot for tables
3. How much boost will I get from a multi-core processor?
l CPU Core ?? ??? ??? CPU ??? ??? ?? ???? ???? CPU? Clock Speed? ???? ??? ??? ?? ??? ? ? ????.
l ?? CPU ? ?? ? ?? ???, ???? ???? ?? capacity ??? ???? ???.
4. Can I put the transaction logs on a storage server with the containers?
l Containers? Logs ?? Disk Access ??? ??? ??? ????.
l Log ??? ?? ?????? ??? ? ? ????.
l ??? ???? ??? ?? ?? ???? ????.
5. What’s the best setting for number of prefetchers & pagecleaners?
l DB2 V9?? AUTOMATIC ?? ?? ??(??).
l # cleaners = # CPUs / # logical DPF nodes
Ø # prefetchers = a function of ( tablespace parallelism & DB2_PARALLEL_IO)
Ø ???? ? ?? prefetchers? ?? ?? ???? ???????.
l NUM_IOSERVERS ? ??? ?? ?? ??? ?? ??? ?? ? ??? ??? ?? AUTOMATIC ???? ???? ????.
6. Why isn’t load always faster than import?
l Load? Parallel, Scalable, Extremely Fast? data? ??? ? ????.
l Import? SQL INSERT Job?? ?? overhead? ???? ???. ???, ? 1000 row ?? ???? ?? Load ??? Import ? ?????.
7. What is the impact of moving from a 32bit environment to a 64bit one?
l ??? DB System? 64bit? ????? ?? Performance ??? ???? ?? ????. ?, 32bit?? ? registers, pointer, instruction? ??? ?? ???, ?? 32bit? ???? ??? ???? ??? ????? ?????.
l 64bit ? ?? ??? ??? ???? ?? STMM(Self Tuning Memory Manager) ? ???? ?? ?????.
8. Should I use Alternate Page Cleaning?
l Bufferpool Page Cleaning ????? DB2 V8.1.4 ?? ???????.
l ?? page cleaning
Ø Dirty Page Size? LSN Gap? ?? ?????.
Ø CHNGPGS_THRESH ?? ?? Bufferpool Page Cleaning? ?????.
l Alternate page cleaning
Ø SOFTMAX ???? ??
Ø DB2_USE_ALTERNATE_PAGE_CLEANING=ON ?? ??
9. What should I collect for basic performance monitoring?
l ??? ??? ?? ???, ??? ?, ????? ???? ??????? ??? ?? ? ?? ??? ???? ???, ?? snapshot table function? ?? ???? ????.
l monitoring?? ???? ?? ??? ?? ?? SQL
|
General level of activity in the system
|
COMMIT_SQL_STMTS, SELECT_SQL_STMTS,
UID_SQL_STMTS
|
|
Bufferpool hit ratio
( index & data)
|
(POOL_DATA_L_READS – POOL_DATA_P_READS)/
POOL_DATA_L_READS
|
|
Rows read
divided by rows selected
|
ROWS_READ / ROWS_SELECTED
|
|
Sort time
(per transaction)
|
TOTAL_SORT_TIME / COMMIT_SQL_STMTS
|
|
Lock wait time
(per 1,000 transactions)
|
1000 *
LOCK_WAIT_TIME / COMMIT_SQL_STMTS
|
|
Deadlocks
(per 1,000 transactions)
|
1000 *
DEADLOCKS / COMMIT_SQL_STMTS
|
|
Dirty steals
(per 10,000 transactions)
|
10000 *
POOL_DRTY_PG_STEAL_CLNS / COMMIT_SQL_STMTS
|
|
Package cache inserts
per 1000 transactions)
|
1000 * PKG_CACHE_INSERTS / COMMIT_SQL_STMTS
|
10. Should I use literals or parameter markers in dynamic SQL?
|
Why use literals
|
Why use parameter markers
|
|
l ??? Query ? ?? ????
|
l ?? ???? ??? ??? ? SQL ??? ? CPU ???? ??.
l SQL reuse? ?? resource ???.
|
11. What is the overhead of using Unicode?
l ??? ??? ??
: ????? 2? ??? ??? ????, ? ??? ??? ?? ????.
l CPU ??? ??
: ???? ? ?? ????, Collection? ?? ?? ??? ????? ???? ?? ?? String ?? ? Sorting?? Overhead? ?? ?????.
12. How much will data compression affect performance?
l ??? ??? ?? ???? ??? ???? ?????..
l ??? ??? ?? ?? ??? ??? ????.
Ø ??? I/O? ?? (??? ? page? ???)
Ø CPU ??? ?? (??/?? ??)
l db2inspect ??? ?? ???? ??? ? ????.
13. What are the current recommended AIX kernel settings for DB2?
l AIX? ?? ?? Kernel ?? ?? ????? ?????.
l ??? ?? ?? ?? ???? ??? ?? ? ???, ? ?, ??? ??? ?????.
l I/O-bound? ?? ?? Performance ??? Compress? ???? ??? ????, CPU-bound? ?? ??? Performance ? ?????.
14. How do I get the best bulk insert performance I can?
l ?? ???? ??? ???? insert ? ? literal ??? parameter maker? ???? ?? ??? ??? ???? ? ????.
l Multi-row SQL? ?? ??? insert ? ????? ??? ????.
Ø ? ?? ??? ? ?? ??? ??? ???? ?? reject? ????.
Ø ????? ?? ?? ???? insert ?? ?? ???? ??? ??
l DPF ???? Buffered Insert ??? ??? ?????.
l Embedded SQL, CLI & SQLJ? ????, JDBC? Buffered Insert? ???? ????.
|
