IBM Software Group / DB2 Information Management Advanced

IBM Software Group / DB2 Information Management Advanced

IBM Software Group / DB2 Information Management Advanced Performance Tuning and Monitoring of the IBM Informix Dynamic Server Jerry Keesee, Director of the Informix lab Dan Wood, IDS Architect 2004 IBM Corporation IBM Software Group / DB2 Information Management Advanced Performance Tuning and Monitoring of the IBM Informix Dynamic Server 2 2004 IBM Corporation IBM Software Group / DB2 Information Management Main Topics Establishing a Base Line Deciding What to Capture Analysis of Performance Data Resolutions Derived from Data 3 2004 IBM Corporation IBM Software Group / DB2 Information Management Know Thyself! 4 -Oracle at Delphi 873 B.C.

2004 IBM Corporation IBM Software Group / DB2 Information Management Create a Performance Database Periodically capture key information Include information from engine onstat p, onstat g iof, etc. Sysmaster database Include information from the OS sar, iostat, vmstat, mpstat, etc Be sure to run onstat z after capturing database information 5 2004 IBM Corporation IBM Software Group / DB2 Information Management Creating a Base Line Run periodically onstat Capture information now. sysmaster Try to have severalCapture weeks of The capture script data before you need it so that could a simple sar be trends and cycles can be understood. dbaccess script, the

iostat of a stored execution Time IO/Sec User CPU% ReadCache 9/1/2002 8:00 41 0.23 99.6 procedure, or awk/perl 9/1/2002 9:00 63.3 0.41 98.2 9/1/2002 9/1/2002 9/1/2002 9/1/2002 9/1/2002 10:00 11:00 12:00 13:00 14:00 92.34 102.2 50.32 100.23 104.2 0.61 0.63 0.55 0.71 0.72 98.3

98.1 97.3 98.1 98.5 Analysis 6 2004 IBM Corporation IBM Software Group / DB2 Information Management OS capture At a minimum you need disk IO/sec and processor utilization sar u vmstat iostat Consider capture of paging and memory usage 7 2004 IBM Corporation IBM Software Group / DB2 Information Management What do I capture from the engine? 8 2004 IBM Corporation IBM Software Group / DB2 Information Management onstat -p dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached Number

of times we had to wait 14583 59454 93.51 Number of times lock times a request on request a Number buffer inof the buffer pool. was If ratio between seqscans issued table/page/row. isamtot open on start a locked read write rewrite delete commit made for arollbk buffer noneshared Percentage of reads from This can indicate that aand single 139364

21933 between 22499 36438 lokwaits/lockreqs 16624 1747 1445 is 1160 0 andRatio isamtot greater than If ratio was available. Indicates either between buffer reads and to disk reads. pagememory is beingrelative altered too much. 1%, then we might is too Above high, then too small ofsystem awant buffer

pool or applications learn what ismay normal for your buffer writes. Normally for OLTP gp_read gp_write gp_rewrt all gp_del gp_alloc gp_free gp_curs For OLTP should be Also this can occur if the page Number of table/page/row usage. between rollback and commits.

41be single-threading. 15 system 147 0before 4to check 0 Ratio 4 index too many dirty pages in the attempting to make any systems this is about 10:1, that is above 98 %. Indicator of too is being flushed to disk too locks that haveIfbeen too high

(> 1%) then application buffer pool changes. 10 reads for every write. This few buffers in system. often (LRU_MIN set to 0) ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes raised. is probably not designed correctly. an 0 0 0 24.07 2.87 gives 7 17 indication as to whether Need to should examine so many emphasis

bewhy placed on chunk bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans rollbacks and take corrective action. IO or on keeping the buffers fairly 0 1 54737 0 0 4 1623 690 clean. ixda-RA idx-RA da-RA RA-pgsused lchwaits But as in all cases, the ratio between 0 0 0 0 349 buffer reads and buffer writes will vary based on your normal system usage. 2004 IBM Corporation Know Thyself!!! 1083 9

1653 193884 99.44 3860 IBM Software Group / DB2 Information Management onstat -D Dbspaces address number flags fchunk nchunks flags ad067d8 1 0x20001 1 1 b4ad870 2 0x28001 2 1 Examine chunks with high owner name splitting N activity. informixConsider rootdbs N S data informix

sbsp1 chunks. on those b4d9720 3 0x28001 3 1 NS informix sbindex b4ec880 4 0x28001 4 1 NS informix sbsp2 b4ed9e0 5 0x28001 5 1 NS If the total number of IO for a informix sbspace

single device exceeds 8/sec, consider moving some data off. 5 active, 2047 maximum Chunks address chunk/dbs offset page Rd page Wr pathname ad06928 1 1 0 1621 b4ad9c0 2 2 0 b4d9870 3 3 0 b4ec9d0 4

4 0 b4edb30 5 5 188000 243 15163 254 73 32 352 233 /work/mpruet/db/Chunk1 Avoid having two chunks on the /work/mpruet/db/Chunk3 same device 342 /work/mpruet/db/Chunk4 18 /work/mpruet/db/Chunk2 /work/mpruet/db/Chunk1 5 active, 2047 maximum Expanded chunk capacity mode: disabled 10

2004 IBM Corporation IBM Software Group / DB2 Information Management onstat g iof AIO global files: gfd pathname totalops dskread dskwrite io/s 3 RootChunk 11771 8723 3049 3.27 4 Chunk1 25341 25341 4611 8.32 5 Chunk2 12420 5436 6984 3.45 1260 1120

140 0.35 108 107 1 0.03 6 SBChunk 7 Chunk3 Need to consider moving data from this chunk. It is starting to get too much activity. If it is only one table, then consider fragmentation of that table. Generic File Descriptor an internal number that is used to identify the chunk across all virtual processors. 11 2004 IBM Corporation IBM Software Group / DB2 Information Management onstat -F Fg Writes 0 LRU Writes 24 2829 address flusher state add461c 0

Chunk Writes I 0 data Writes done as part of a checkpoint = 0X0 Writes done states: Exit Idle Chunk Lru between checkpoints. Foreground Writes page flush requested by user thread - If this is ever not zero then buffer pool is too dirty. LRU_MIN/LRU_MAX, BUFFERS and/or CKPTINTVL need adjusting. 12 2004 IBM Corporation IBM Software Group / DB2 Information Management onstat g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd 0x100123 8698 0 0

0x100124 4660 322 31 0x100125 1366 0 0 0 652 210 148 0x100126 1015 0 0 0 65 306 0x100127 771 0

0 0 251 0x100128 506 0 0 0 72 0x100129 534 0 0 0 940 0 53 3163 243 242 62

6553 3797 148 964 bfwrt 12209 1219 seqsc rhitratio 3 41278 11832 100 0 100 62 3850 1138 1 0 111 5330 1410 1 110 0

0 1390 380 0 100 357 0 0 1207 778 0 100 0 0 1750 12 359 100 100 67

N.B. Available from sysmaster (sysptprof) In general numbers here indicate that the Buffer Read Hit Ratio Inwork. general numbers here indicate application may need some Lock Times Requests thread Number was of put times on wait that a for lock a lock timed out Number ofbe deadlocks

Number encountered of sequential scans Partition number of Can table/fragment used tomight determine May require setting Ratio of buffer that we want to reads checkthat to see Check for hot while rows,waiting going against the on a issued lock. against the partition which tables/fragments TBLSPACE_STATS 1 if adding an index would be in order

did not require a physical same data in opposite directions, etc. tend to have in onconfig file. the diskmost readactivity. 13 2004 IBM Corporation IBM Software Group / DB2 Information Management Sysmaster syssqexplain table Contains information about statements currently open and prepared statements Can be used to find queries that are requiring sorts, temporary tables, sequential scans, etc Includes actual query statement Can aid in finding problem queries 14 2004 IBM Corporation IBM Software Group / DB2 Information Management Select * from syssqexplain; 15 sqx_sessionid 23

sqx_sdbno 0 sqx_iscurrent Y The first part of the output is actual costs that the statement has already encountered. Number of times the statement has been executed sqx_executions 4 sqx_cumtime 0.21 sqx_bufreads 21 sqx_pagereads 4 sqx_bufwrites 0 sqx_pagewrites 0

sqx_totsorts 4 sqx_dsksorts 0 sqx_sortspmax 0 sqx_conbno 0 sqx_ismain Y Total time execution of statement has encountered Statistics about statement. Max disk space needed for sort 2004 IBM Corporation IBM Software Group / DB2 Information Management second part contains PayThe close attention to queries that information query plan

require an auto about index,the temp files, and sequential scans. Select * from syssqexplain; sqx_selflag SQ_SELECT sqx_estcost 654 sqx_estrows 200 sqx_seqscan sqx_seqscan 2 sqx_srtscan 0 sqx_autoindex sqx_autoindex Optimizer Cost 0 sqx_index 0

sqx_remsql 0 sqx_mrgjoin 0 sqx_dynhashjoin 0 sqx_keyonly 0 sqx_tempfile sqx_tempfile 1 sqx_tempview 0 sqx_softheads 0 sqx_sqlstatement select * from account, transactions Number Sequential Scans Number Sort Scans Number of Auto Indexes Number of Remote Queries Required Number of Temp Files

Number of Temp Views Actual Query where account.acct_id = transactions.account_id order by tran_amount 16 2004 IBM Corporation IBM Software Group / DB2 Information Management General Guidelines Run update statistics Do not skimp on memory Try to avoid running client and server on the same machine Allocate fewer larger memory segments rather than many smaller Try to avoid having too much disk capacity on a limited number of controllers Learn the characteristics of your system 17 2004 IBM Corporation IBM Software Group / DB2 Information Management Avoid Long Rows 18 2004 IBM Corporation IBM Software Group / DB2 Information Management

Ways to Avoid Long Rows Avoid having rows larger than the page size Consider separating columns that you filter on from columns that you dont and then join Consider using CLOBS for variable length columns Avoid updating varchar columns 19 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters NUMCPUVPS The endless debate. Given enough physical processors performance will peak out between 16 to 32 CPU VPs but you need to not use all the physical processors for CPU VPs. On a small box; <= 4 CPUs, you might use all 4 CPUs for CPU VPs. On a larger box; > 4 CPUs, you might use 5 CPU VPs per 6 CPUs. However, there are many factors which can affect this. Are there many client processes running on the same box? Are you using NET VPs for the poll threads handling many connections? Have you disabled KAIO and configured many AIO VPs. The only real rule is to add CPU VPs until it no longer provides any improvement. 20 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters

RESIDENT Set to 1. Or 2 if you have enough memory. This makes a big performance difference on big Sun boxes. NOAGE Set to 1 Processor Affinity (AFF_SPROC/AFF_NPROCS) Turn on if engine is only thing running on the machine 21 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters CLEANERS < 20 disks set to 1 per disk 20 - 100 disks set to 1 for every other disk > 100 disks - set to 1 for every four disks Striping and RAID-5 Cut number of cleaners down by 1/3 22 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters LRU Set to max of 128. 512 on 64-bit platforms LRU_MIN/LRU_MAX Avoid setting LRU_MIN to 0 as it can cause additional buffer waits 1 and 2 in most large memory OLTP systems higher values for DSS systems. In 9.4 you can set these to float

values like .2. This is useful when 1% of a huge buffer pool is large causing slow checkpoints. 23 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters Read Ahead RA_PAGES Number of pages to read ahead when scanning 32-64 Too large a value can cause additional buff waits and/or block the IO channel. RA_THRESHOLD Point where next big buffer read is triggered 24 2004 IBM Corporation IBM Software Group / DB2 Information Management Onconfig Parameters PHYSDBS and DBSPACE for logs Place both of these on separate dedicated fast disks DBSPACETEMP Locations for temporary tables and sort space Place on separate devices 25 2004 IBM Corporation IBM Software Group / DB2 Information Management

Onconfig Parameters OPTCOMPIND 0 give preference to nested loop join (OLTP) DIRECTIVES 1 turn on optimizer directives 26 2004 IBM Corporation IBM Software Group / DB2 Information Management Environment Variables PSORT_NPROCS Number of threads that the server can use to perform a parallel sort Should be no greater than number of CPUVPS 27 2004 IBM Corporation IBM Software Group / DB2 Information Management Environment Variables IFX_NETBUF_SIZE Can be used to increase the network buffer size from the default 4K Useful if doing large loads or blob work 28 2004 IBM Corporation IBM Software Group / DB2 Information Management PDQ/Fragmentation

Consider fragmenting any large table in a dbspace that is getting a lot of IO activity Consider fragmenting any large table if scans must be done against the table Do not put multiple fragments of the same table on the same physical device Avoid using round robin fragmentation for indexes. Do not over-fragment. The cost of managing fragmentation can outweigh the benefits when there are excessive fragments. 29 2004 IBM Corporation IBM Software Group / DB2 Information Management Typical Query Sort Send to client Join Scan 30 2004 IBM Corporation IBM Software Group / DB2 Information Management Typical Query with PDQ Sort Sort

Send to client Join Scan Scan 31 Scan 2004 IBM Corporation IBM Software Group / DB2 Information Management PDQ Configuration MAX_PDQPRIORITY Set highest percentage of PDQ resources that a single client can use DS_MAX_QUERIES Max number of DSS queries that can be run together DS_TOTAL_MEMORY Total memory reserved for PDQ DS_MAX_SCANS Max number of parallel scans allowed. Leave at default (1048567) 32 2004 IBM Corporation IBM Software Group / DB2 Information Management PDQ Configuration If the site is primary a DSS system, then it is recommended that most of the allocated memory be in the virtual buffers and that

DS_TOTAL_MEMORY be very large PDQ can be used in smaller memory environments by setting PDQ_PRIORITY to 1 so that parallel scans can be done. 33 2004 IBM Corporation IBM Software Group / DB2 Information Management PDQ Configuration onmode can be used to dynamically change PDQ parameters onmode M (DS_TOTAL_MEMORY) onmode Q (DS_MAX_QUERIES) onmode D (MAX_PDQPRIORITY) onmode S (DS_MAX_SCANS) It is not recommended to try to do DSS queries at the same time that the system is doing OLTP 34 2004 IBM Corporation IBM Software Group / DB2 Information Management Next Chat with the Lab Informix on Linux Wednesday, December 1, 2004 11:00 a.m. Eastern, 10:00 a.m. Central, 9:00 a.m. Mountain, 8:00 a.m. Pacific What a story we have to tell! Informix was there at the start, IBM loves Linux and Microsoft looks nervous! We speak Linux and were packing a lot into this session. Join us to hear about where IBM is headed with Linux, Informix products on Linux, maximizing performance and scalability, open source databases, 64-bit Linux and tips and tricks You may RSVP to this event at: https://ww4.premconf.com/webrsvp/register?conf_id=875614

Chat replays at: http://www-1.ibm.com/partnerworld/pwhome.nsf/weblook/eac_index_biz25aug.ht ml 35 2004 IBM Corporation IBM Software Group / DB2 Information Management http://www.ibm.com/software/data/informix 36 2004 IBM Corporation IBM Software Group / DB2 Information Management http://www.ibm.com/software/data/informix 37 2004 IBM Corporation

Recently Viewed Presentations

  • Is there a Minimal Standard of Care ... - Washington State CASA

    Is there a Minimal Standard of Care ... - Washington State CASA

    Washington State CASA Conference. Spokane, Washington. ... Any acceptable application of a minimal standard of care in dependency cases must connect such a standard to the requirement that "children are returned home when they are considered to be safe for...
  • A, b, c, ch, d, e, f, g, h, i, j, k, l, ll, m, n, Z, o, p, q ...

    A, b, c, ch, d, e, f, g, h, i, j, k, l, ll, m, n, Z, o, p, q ...

    Title: A, b, c, ch, d, e, f, g, h, i, j, k, l, ll, m, n, Z, o, p, q, r, rr, s, t, u, v, w, x, y, z. Author: pyoung Last modified by: Wayne Young Created Date
  • Elements of Rhetoric - David-Glen Smith

    Elements of Rhetoric - David-Glen Smith

    The introduction takes the form of a letter, written by Thomas More to Peter Giles. In this fashion, More prepares the reader for a direct approach on the topic. In a tongue-in-cheek approach, he apologizes for the flaws in the...
  • Ancient Olmec - Weebly

    Ancient Olmec - Weebly

    This is a story about the Ancient Olmec. First ,the Olmec's civilization started in 200B.C. and it id believed that they are the an sisters of the Maya and Aztecs . They were a trading society .Framers created crops and...
  • Wheel Alignment Service - Cengage

    Wheel Alignment Service - Cengage

    Wheel Alignment Service Chapter 68 Objectives Perform a prealignment inspection of the steering and suspension Describe how to adjust caster, camber, and toe Understand the different ways of adjusting wheel alignment angles Introduction Steering and suspension Inspect before aligning wheels...
  • INTA Proper Use Presentation

    INTA Proper Use Presentation

    For example, use of a singularized or pluralized trademark may not be taken to be use of the same trademark in all countries. A trademark may be considered abandoned if use of the actual mark has not occurred, and thereby...
  • Soil Degradation &amp; Conservation

    Soil Degradation & Conservation

    Prevents water from making gullies Slows the flow of rain water to increase soil infiltration. Terracing - turning a hillside into a series of ascending terraces or steps Saves soil from being eroded and conserves water. Contour Plowing and Terracing...
  • Virginia Transportation Construction Alliance (VTCA) Joint ...

    Virginia Transportation Construction Alliance (VTCA) Joint ...

    Focus is on auditing and reporting on indirect costs and resultant overhead rates. "Contract pre-awards are performed to evaluate the reasonableness and accuracy of a cost proposal for a specific contract."