Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55

    Unanswered: Troubleshooting high disk i/o

    Hi DB2 friends. As a sophomore DB2 DBA, I could use a little assistance understanding a problem and hopefully some direction towards a solution.

    I have an OLTP production system running DB2 LUW ESE 9.7 fix pack 4 on an AIX server running 7.1. There is only 1 database and 1 instance running on this server. I am using a SAN and an XIV storage subsytem.

    I'm concerned because I'm seeing the disk containing tablespace data get very busy at times (very high 90's) Here is a sample snapshot:

    I have my diag level set to 3 and it's reporting no errors or issues of concern at all.
    My bufferpools are decent (see attached).
    User's are NOT complaining.
    My system monitoring doesn't alert of any abnormalities (See the attached snapshot from Foglight)

    When I query SYSIBMADM.LONG_RUNNING_SQL, I find a couple of agents that have been
    elapsed times of 200+ minutes. The agents are attempting to execute the exact same statement. See below.

    For the last several weeks I've been seeing the disk i/o spike high at different times during the week and then come back down. When I look at what's running
    via the SYSIBMADM.LONG_RUNNING_SQL, I see different queries with the highest elapsed times...not just 1 poorly query showing up when disk i/o jumps up.

    I'm struggling to find what is causing the high disk i/o. If you were in my situation, what would you look at? Could I have a memory issue that is causing
    the disk i/o? I'm sorry to sound so novice, but I am new to troubleshooting and performance tuning. I've attached a memory snapshot.

    Any help anyone can provide would be awesome!


    Today's statement that appears to be the cause of the high disk i/o:

    SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID, T1.ROW_ID, T1.ATTRIB_27, T1.ATTRIB_41, T1.INSCLM_ID, T1.CON_ID, T1.ATTRIB_47, T1.X_PR_PER_ADDR_ID, T1.ATTRIB_46, T1.X_FAX_PH_NUM, T1.X_FST_NAME, T1.X_SEX_MF, T1.X_HOME_PH_NUM, T1.X_OCCUPATION, T1.X_LAST_NAME, T1.X_PER_TITLE, T1.X_ALLEGED_INJURIES, T3.X_NAME, T1.X_ATTORNEY_ID, T1.X_BUSINESS_NAME, T1.X_BUSINESS_TYPE, T1.X_FINAL_ORM_DT, T1.X_CMS_INCIDENT_DT, T1.X_INIT_ORM_SENT_DT, T1.X_LAST_TPOC_DT, T1.X_ORM_END_DT, T1.X_CMS_PIP_EX_DT, T1.X_CMS_PIP_LIMIT, T1.X_REPORT_ORM, T1.X_REPORT_TPOC, T1.X_CMS_VENUE_STATE, T8.INSCLAIM_NUM, T1.X_COMP_RATE, T1.X_CONTACT_PER_ID, T7.X_NAME, T1.ATTRIB_42, T1.X_ALIAS_NAME, T1.X_CMS_DEATH_DT, T1.X_DAYS_WEEK, T1.ATTRIB_43, T1.X_DRVRS_LCNSE_NUM, T1.X_DRVRS_LCNSE_ST, T1.X_EMPLOYED, T1.X_EMPLOYER, T1.X_EMPLMNT_STAT_CD, T4.X_NAME, T1.X_GUARDIAN_ID, T1.X_ADMITED, T1.X_HOSPITAL_ID, T1.X_HOSPITAL_NIGHTS, T1.X_HOURLY_RATE, T1.X_HOURS_DAY, T1.X_ICD9_CME_CD, T1.X_IRS_PAYMENT_TYPE, T1.ATTRIB_01, T1.X_JOB_TITLE, T1.X_LAST_WORK_DT, T5.X_NAME, T1.X_LAW_FIRM_ID, T1.X_LEGAL_REP_ADDR, T1.X_LEGAL_REP_PHONE, T1.X_LIGHT_DUTY, T1.ATTRIB_02, T1.X_MEDICARE_ELGB, T1.X_MEDICARE_NO, T1.X_PAGER_NUM, T1.X_PAID_FULL, T1.X_CONTACT_TYPE, T1.X_PASS_RESTRAINT, T1.X_PAY_SCHEDULE, T1.X_PHYS_ID, T1.X_POSITION_IN_VEH, T6.X_NAME, T1.X_POWER_ATT_ID, T1.X_NICKNAME, T1.X_PR_ACC_ROLE_ID, T1.X_PR_ROLE_ID, T1.X_PR_PARTY_ID, T1.X_PROGNOSIS, T2.PRPTY_DESC, T2.VIN, T1.X_REFERENCE_NUM, T1.X_RETURN_WORK_DT, T1.X_SAFETY_PROVIDED, T1.X_SAFETY_USED, T1.X_SALARY_FLG, T1.X_2ND_EMPLOYER, T1.X_SOURCE, T1.X_HIRE_STATE, T1.X_TAX_OR_SSN, T1.X_TIME_PREFERENCE, T1.X_TIME_DAY_BEGAN, T1.X_TIME_DAY_ENDED, T1.X_TMZONE_CD, T1.X_TOTAL_PAID, T1.X_TRANS_METHOD, T1.X_TRANSPORTED, T1.X_TREATED, T1.X_URL, T1.X_WAGE_LOSS, T1.X_WITHOLD_AMT, T1.X_IRS_TAX_WITHHOLD, T1.X_MARITAL_STAT_CD, T1.X_MID_NAME, T1.X_NAME, T1.X_SOC_SECURITY_NUM, T1.X_PER_TITLE_SUFFIX, T1.X_WORK_PH_NUM, T9.X_ATTRIB_35, T9.X_ATTRIB_03, T9.X_ATTRIB_05, T9.X_ATTRIB_04, T9.X_ATTRIB_47, T9.X_ATTRIB_01, T9.X_ATTRIB_34, T9.ROW_ID
    FROM siebel.S_INSCLM_CON T1
    LEFT OUTER JOIN siebel.S_INSCLM_PRPTY T2 ON T1.X_WHICH_VEHICLE = T2.ROW_ID
    LEFT OUTER JOIN siebel.S_INSCLM_CON T3 ON T1.X_ATTORNEY_ID = T3.ROW_ID
    LEFT OUTER JOIN siebel.S_INSCLM_CON T4 ON T1.X_GUARDIAN_ID = T4.ROW_ID
    LEFT OUTER JOIN siebel.S_INSCLM_CON T5 ON T1.X_LAW_FIRM_ID = T5.ROW_ID
    LEFT OUTER JOIN siebel.S_INSCLM_CON T6 ON T1.X_POWER_ATT_ID = T6.ROW_ID
    LEFT OUTER JOIN siebel.S_INSCLM_CON T7 ON T1.X_CONTACT_PER_ID = T7.ROW_ID
    LEFT OUTER JOIN siebel.S_INS_CLAIM T8 ON T1.INSCLM_ID = T8.ROW_ID
    LEFT OUTER JOIN siebel.CX_INSCLMCON_XM T9 ON T1.X_PR_PER_ADDR_ID = T9.ROW_ID
    WHERE
    (T1.X_CONTACT_TYPE = 'Person')
    AND
    (T1.ATTRIB_46 LIKE ?)
    FOR FETCH ONLY OPTIMIZE FOR 1 ROW
    Attached Thumbnails Attached Thumbnails Foglight snapshot.JPG   Bufferpool snapshot.JPG   Memory snapshot.JPG   iostat nmon - disk, cpu.JPG  

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Questions:

    1) is anything else using the disks in question?
    2) Have you generated access plans for the SQL you are having issues with?


    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Tracygirl View Post
    For the last several weeks I've been seeing the disk i/o spike high at different times during the week and then come back down. When I look at what's running
    via the SYSIBMADM.LONG_RUNNING_SQL, I see different queries with the highest elapsed times...
    I'm not sure if elapsed time necessarily correlates with high I/O. By the way, what's on hdisk7 and hdisk8?

    May be it's better to start with looking at the MONREPORT.DBSUMMARY() output and MON_CURRENT_SQL.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    If I were in your situation,I will do the following things:
    1、 using iostat -D 2 to get which disk is busy.
    2、 using db2pd -tablespaces to get which tablespace container locates in the busy disk.
    3、 using db2 get snapshot for tables to see which tables are hot in these containers
    4、 using db2 get snapshot for all applications to see which sqls running currently reference these tables.
    5、using db2exfmt to check the access plan for these sqls.

    and you also need to check database level snapshot ( db2 get snapshot for db )
    and database configuration ( db2 get db cfg ) to see whether your database is well configed .

  5. #5
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Thanks for the replies!

    Nothing else is using the disk that I'm seeing high i/o, it's just data tablespace containers and index tablespace containers

    I did generated the attached access plan for the suspicious sql. Like my other troubleshooting skills, I'm weak and still learning about query optimization. Any hints or suggestions would be a great learning opportunity for me. Also, I see the i/o spike at times when this statement doesn't appear to be executing.

    hdisk7 is index tablespaces
    hdisk8 is table tablespaces

    Oh! SYSIBMADM.MON_CURRENT_SQL is awesome! Thanks for that hint. Looks likes there's valuable info there.

    When I query SYSIBMADM.MON_DB_SUMMARY, it returns 0's and NULL's. It sounds like from reading IBM DB2 9.7 Information Center for Linux, UNIX, and Windows, I need to run the db2updv97 command. The database was originally created < v8.1 and was upgraded to V9.7. Any thoughts or experience for this newbie on running that command. Is it a relatively safe or should I anticipate any potential side effects?

    Thanks again for all of your assistance.

    fengsun2, I will check out your suggestions. Regarding the 'well configured database' comment. I wish I was experienced enough to know my database is well configured. I'm learning every day, but I feel like I have so much more to learn.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Hey experts, could I have a memory value set too low? I hate sounding like a complete idiot, but any idea how I would know if something is spilling to disk because of a memory issues?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Tracygirl View Post

    I did generated the attached access plan for the suspicious sql.
    Could you use db2exfmt for the explain plan? It is easier to read and has more information.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    Please check Is there any requirement of index reorganization for the indexes which belongs to that specific high disk utilization space ?
    you can run command reorgchk and look into Index statistics part.
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •