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°¡ Áö¿øµÇÁö ¾Ê½À´Ï´Ù.
|