Case Study DB2 Tuning at ACSM-LOS

สรุปสิ่งที่ได้ทำไประหว่างไปที่ Malaysia

1. ปรับ DBM และ DB Configuration
2. Monitor ดู SQL ที่มีการใช้ CPU สูง
3. สร้าง TQM มาแทน View
4. สร้าง Function Base Index
5. Full Text Search

การใช้ DB2 Command

โดยปกติจะชอบใช้ Command Line ของ DB2 เป็นหลัก โดยหลังจากที่ Add Instance และ Database ใน Control Center แล้วเมื่อต้องการติดต่อไปที่ DB2 จะใช้ 2 command นี้ โดยเปิดจาก DB2 Command Windows และ

db2 attach to (Instance) user (User) using (Password)
เพื่อ Connect ในระดับ Instance

db2 connect to (Database) user (User) using (Password)
เพื่อสร้าง Connection ของ DATABASE

ปรับ DBM และ DB Configuration
การปรับ Parameter มีแบ่งได้เป็นสองส่วนคือ

1. ระดับ Instance หรือ Database Manager

คำสั่งที่ใช้คือ db2 update dbm cfg using (Parameter) (Value)

Parameter ที่ปรับไว้คือ

INSTANCE_MEMORY 5859375 หมายถึง Memory ที่ให้ DB2 Instance ใช้ได้มากสุด หน่วยเป็น 4K
INTRA_PARALLEL YES ให้บาง Operation ทำงานแบบ Parallel จะเป็นการ Improve Performance
MAX_QUERYDEGREE 16 ให้มีการทำงาน Parallel กันได้สูงสุด

2. ระดับ Database
คำสั่งที่ใช้ db2 update db cfg for using (Parameter) (Value)

Parameter ที่ปรับไว้คือ

DATABASE_MEMORY 5855023 หมายถึง Memory ที่ให้ DB2 Database ตัวนั้นๆ ใช้ได้มากสุด หน่วยเป็น 4K
STMTHEAP 30000 เป็น Member ในการใช้ Statement
LOCKLIST 200000 AUTOMATIC
SORTHEAP 200000 AUTOMATIC
SHEAPTHRES_SHR 1000000 AUTOMATIC
MAXLOCKS 98 AUTOMATIC
PCKCACHESZ 200000 AUTOMATIC

Monitor ดู SQL ที่มีการใช้ CPU สูง

ใช้ Activity Monitor ทำการเก็บข้อมูลการใช้งาน Database ซึ่งจะต้อง Config ให้เปิดใช้งาน โดยจะทำให้ Performance ลดลงประมาณ 1% โดยใช้คำสั่งตามนี้

update dbm cfg using DFT_MON_BUFPOOL ON
update dbm cfg using DFT_MON_LOCK ON
update dbm cfg using DFT_MON_SORT ON
update dbm cfg using DFT_MON_STMT ON
update dbm cfg using DFT_MON_TABLE ON
update dbm cfg using DFT_MON_UOW ON
update dbm cfg using DFT_MON_TIMESTAMP ON

และปล่อยให้เก็บข้อมูลไว้สักวันหนึ่ง แล้วเอาผลมาดู โดยดูที่ Dynamic SQL Top CPU เป็นหลัก แล้วจะเห็นว่า SQL ตัวไหนใช้ CPU สูงๆ เพื่อจะได้นำไป Tuning SQL ต่อไป

สร้าง MQT มาแทน View

db2 มี feature ตัวหนึ่งคือ Materialized Query Tables (MQT) เป็น Object คล้ายๆ view แต่จะเป็นการสร้าง Table มาเก็บ Data จริงๆ จึงทำให้ค้นหาได้รวดเร็วกว่า แต่ต้องมีการสั่ง Refresh เพื่อให้ Data มีการ Update เหมาะกับ Data ที่ไม่มีการเปลี่ยนแบบ Real Time

ในส่วนของ ACSM-LOS ได้มีการสร้าง MQT มาช่วย และเพิ่ม Index ใน Field ที่ใช้ในการค้นหา ทำให้ Performance โดยรวมของระบบดีขึ้นมาก โดยเปลี่ยนจาก view V_FCP013_GETEPSTATUS และ V_FCP013_GETCCSTATUS แล้วทำการสร้าง Index ที่ Field AccountNo

สร้าง Function Base Index
เนื่องจากการค้นหา AppNo หรือ DocNo มีการค้นหาโดยผ่านทาง Function Right('0'||AppNo,11) เพราะว่าข้อมูล Column อาจจะเป็น 10 หรือ 11 ก็ได้ แต่ Criteria ที่ส่งมาจะเป็น 11 เสมอ ทำให้การค้นหาไม่ได้มีการใช้ Index อย่างเต็มที่ และทำให้ใช้ CPU ไปอย่างมากในการค้นหา เมื่อรวมกันเยอะๆทำให้ Performance โดยรวมช้าลง

ใน DB2 ไม่มี Function Base Index ตรงๆแต่สามารถสร้างได้โดยสร้าง Generate by Column ขึ้นมา และสร้าง Index บน Column นั้น ใช้คำสั่งตามนี้

SET INTEGRITY FOR COMMON.TB_CIF2ACC OFF;

ALTER TABLE COMMON.TB_CIF2ACC ADD CFCA_APPNO11 GENERATED ALWAYS AS( SUBSTR(RIGHT('0' || RTRIM(CFCA_APPNO),11),1,11));

SET INTEGRITY FOR COMMON.TB_CIF2ACC IMMEDIATE CHECKED FORCE GENERATED;

ในตอนแรก RIGHT('0' || RTRIM(CFCA_APPNO),11) แล้วไม่สามารถสร้าง Index ได้ มีการฟ้อง Error ประมาณว่า Column มีขนาดใหญ่ไป จึงลอง SubStr ครอบอีกชั้นก็สามารถสร้าง Index ได้

ในบางครั้งเมื่อสร้าง Index แล้ว ต้องทำการ runstats ด้วย เพื่อให้ DB2 มีการใช้ Index ใน Query
คำสั่งคือ DB2 RUNSTATS ON TABLE  (Table) on all columns AND INDEXES ALL

Full Text Search

เป็นการสร้าง Index สำหรับการ Search ด้วยบาง Word ที่มีอยู่ใน Column นั้นๆ ซึ่งโดยปกติจะใช้ Like แต่ถ้าทำงานกับ Feature นี้ จะใช้ Function Contains แทน

โดย Full Text Search จะอยู่ในแผ่น Extension ที่ชื่อว่า BM DB2 Net Search Extender เมื่อลงแล้วจะเห็น Service อีกตัวหนึ่ง และใช้คำสั่งในการสั่งงานต่างๆผ่าน db2text สามารถดูการใช้งานได้จาก link

http://www.ibm.com/developerworks/data/tutorials/dm-0810shettar/dm-0810shettar-pdf.pdf