1 / 83

Performance Tuning Tips

Performance Tuning Tips. Mike Lowe Certified Principal Trainer IBM. Performance Tuning Tips. What is our primary goal as a DBA?. Keep the users off our phone!. Performance Tuning Tips. Primary factors affecting performance Memory CPU Disk Network Application. Performance Tuning Tips.

minowa
Download Presentation

Performance Tuning Tips

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PerformanceTuningTips Mike Lowe Certified Principal Trainer IBM

  2. Performance Tuning Tips • What is our primary goal as a DBA? Keep the users off our phone!

  3. Performance Tuning Tips • Primary factors affecting performance • Memory • CPU • Disk • Network • Application

  4. Performance Tuning Tips MEMORY

  5. Performance Tuning TipsMemory • IBM IDS Memory Structure RESIDENT VIRTUAL MESSAGE

  6. IBM IDS Memory Structure – Resident Performance Tuning TipsMemory BUFFERS LRU Queues OTHER STUFF LOG. LOG STRUCTURE LOCKS PLOG BUFFERS LLOG BUFFERS

  7. Performance Tuning TipsMemory IBM IDS Memory Structure – Virtual Session Info Memory Pools Thread Info Stored ProcedureCache Data DictionaryCache Even MoreStuff User Stuff Sort Pools

  8. COMMUNICATIONS BUFFERS Performance Tuning TipsMemory IBM IDS Memory Structure – Message

  9. Performance Tuning TipsMemory • Application Environments • OLTP • Many users • Single-record processing • Short response times • DSS • Few users • Large amounts of data processed • Complex queries

  10. Performance Tuning TipsMemory • Application Environments – OLTP • Tuning Parameters • BUFFERS • RESIDENT portion approx 75% of real memory • BUFFERS = data cache • Increase BUFFERS to increase read cache rate • SHMVIRTSIZE • One virtual segment • Performance Goals • 95% read cache • 85% write cache • 0-2 second checkpoints

  11. Informix Dynamic Server 2000 Version 9.21.UC1 -- On-Line -- Up 00:39:28 -- 25600 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1155 1173 2705156 99.96 221283 223216 2678873 91.74 isamtot open start read write rewrite delete commit rollbk 4022775 3086 4080 5922 2002875 186 1 62 0 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 27256 1420.66 178.97 8 44 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 470 0 2013748 0 0 140 317 54 ixda-RA idx-RA da-RA RA-pgsused lchwaits 28 0 0 28 905 Performance Tuning TipsMemory onstat -p output

  12. Segment Summary: id key addr size ovhd class blkused blkfree 501 1381451777 a000000 2097152 215344 R 275 237 2502 1381451778 a200000 4194304 728 V 1017 7 3 1381451779 a600000 8388608 856 V 1370 678 4 1381451780 ae00000 1048576 632 M 164 92 Total: - - 15728640 - - 2826 1014 (* segment locked in memory) Performance Tuning TipsMemory onstat -g seg output New segment size: (1017 + 7 + 1370 + 200) * 4 KB = 10376 KB

  13. Performance Tuning TipsMemory • Application Environments – DSS • Tuning Parameters • BUFFERS • RESIDENT portion approx 25% of real memory • Small BUFFERS helps enable light scans • SHMVIRTSIZE • approx 75% of real memory • Allows more work/sorting in memory

  14. Performance Tuning TipsMemory • Total memory no more than physical memory • One VIRTUAL segment • No swapping • Monitor UNIX with vmstat -S, sar -w

  15. procs memory page r b w swap free si so pi po fr de sr 0 0 29 693336 1848 0 0 0 1736 2688 0 6540 0 0 29 693316 2432 0 0 0 8324 9649 0 4742 0 0 29 693288 3956 0 0 0 8514 10348 0 2398 0 1 29 693256 2944 0 0 33 7317 9248 0 3209 0 7 29 693304 2108 0 0 112 7834 8546 0 4271 0 22 29 693444 2000 0 0 94 7124 7716 0 6092 0 27 65 692992 3036 0 2 32 7096 8811 336 4593 0 32 72 692292 31720 1 0 241 5922 5966 2464 1812 0 48 58 692152 60684 0 0 1338 0 0 1800 0 0 25 58 692148 56396 1 0 1241 0 0 1388 0 0 2 55 651156 53408 0 0 720 0 0 1016 0 0 3 54 651144 50288 1 0 604 0 0 848 0 0 3 52 651128 48300 0 0 582 0 0 604 0 0 1 52 651128 46300 0 0 593 0 0 364 0 0 2 52 651128 44276 0 0 605 0 0 148 0 Performance Tuning TipsMemory vmstat -S output

  16. Performance Tuning Tips • Possible solutions • Reduce segment allocations • Move applications/processes to another machine • Add physical memory

  17. Performance Tuning Tips CPU

  18. Performance Tuning TipsCPU • IBM IDS Processes (oninit) • Virtual Processors • Each assigned to a specific class

  19. Performance Tuning TipsCPU • VP Classes • CPU – runs user sessions, main processing • ADM – manages instance internal clock • MSC – does miscellaneous tasks • AIO – reads/writes to disk, primarily to ‘cooked’ files • LIO – writes logical log buffers to logical logs • PIO – writes physical log buffers to physical log • ADT (optional) – runs secure auditing process • OPT (optional) – runs optical subsystem • NET (optional) – runs communications poll threads • SHM, TLI, SOC, STR

  20. Single-Threaded Processes P3 CPU P2 CPU P1 CPU IBM IDS Multi-Threaded Processes CPU T1 T2 T3 T4 T5 Performance Tuning TipsCPU Context switch oninit (CPU VP)

  21. Performance Tuning TipsCPU • Configuration • 1 or 2 CPUs • 1 CPU VP • Set SINGLE_CPU_VP to 1 • More than 2 CPUs • Max CPU VPs = Number of Physical CPUs

  22. Performance Tuning TipsCPU How Many CPU VPs???

  23. Performance Tuning TipsCPU Do we have enough CPU VPs??? • Monitor IBM IDS with onstat -g rea Informix Dynamic Server 2000 Version 9.21.UC1 -- On-Line -- Up 00:00:59 -- 339968 Kbytes Ready threads: tid tcb rstcb prty status vp-class name 87 1deb5a10 1d9206a8 2 ready 4cpu sqlexec 90 1e299788 1d921878 2 ready 1cpu sqlexec 91 1e265890 1d921e68 2 ready 3cpu sqlexec 92 1df214f0 1d922458 2 ready 5cpu sqlexec 93 1df2c418 1d922a48 2 ready 5cpu sqlexec 94 1df2ccf8 1d923038 2 ready 5cpu sqlexec 97 1e366c38 1d924208 2 ready 1cpu sqlexec 98 1e2c5608 1d9247f8 2 ready 5cpu sqlexec 100 1e407ae0 1d9253d8 2 ready 3cpu sqlexec 101 1e419ae0 1d9259c8 2 ready 1cpu sqlexec 103 1e407dd0 1d9265a8 2 ready 4cpu sqlexec 104 1de805b0 1d926b98 2 ready 1cpu sqlexec 105 1e4c6ae8 1d927188 2 ready 1cpu sqlexec

  24. Performance Tuning TipsCPU What can we do? • Add CPU VPs • onmode –p +1 cpu • Monitor again • onstat –g rea

  25. Performance Tuning TipsCPU • Monitor UNIX with vmstat, sar –u • Look for high level of UNIX Process queues • r – in run queue, waiting for CPU • b – ready, but blocked, waiting for resources • w – runnable, but swapped

  26. procs memory page disk faults cpu r b w swap free re mf s0 s6 s3 s3 in sy cs us sy id 0 0 0 7530544 3885768 0 4 0 0 1 0 611 192 151 0 0 100 7452 3 0 3999272 2705840 0 1978 112 0 0 0 1890 2113 2007 1 99 0 7268 3 0 4143088 2739880 15 1814 125 0 0 0 2042 2165 2050 2 98 0 7084 3 0 4287720 2771296 3 1918 131 0 0 0 2141 2276 2222 1 99 0 6873 4 0 4426368 2798792 20 1971 129 0 0 0 2294 2575 2321 1 99 0 6665 4 0 4559320 2825344 2 1492 130 0 0 0 2184 2122 1970 1 99 0 6433 4 0 4688232 2851568 0 1479 127 0 0 0 2210 2067 1978 1 99 0 5851 4 0 4910648 2899376 1 4713 122 0 0 0 2380 4897 2253 2 98 0 1695 4 0 6248768 3201208 14 10829 166 0 0 0 2868 8881 3170 3 97 0 Performance Tuning TipsCPU vmstat output

  27. Performance Tuning Tips DISK

  28. Performance Tuning TipsDisk • Goal is load balancing • Across devices • Across controllers • Check both IBM IDS AND hardware • Monitor IBM IDS with onstat –D, onstat –g iof • Monitor UNIX with iostat, sar -d

  29. Informix Dynamic Server 2000 Version 9.21.UC1 -- On-Line -- Up 00:39:28 -- 25600 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1155 1173 2705156 99.96 221283 223216 2678873 91.74 isamtot open start read write rewrite delete commit rollbk 4022775 3086 4080 5922 2002875 186 1 62 0 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 27256 1420.66 178.97 8 44 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 470 0 2013748 0 0 140 317 54 ixda-RA idx-RA da-RA RA-pgsused lchwaits 28 0 0 28 905 Performance Tuning TipsDisk onstat -p output

  30. Dbspaces address number flags fchunk nchunks flags owner name aa0b7d0 1 0x1 1 1 N informix rootdbs aa0beb8 2 0x1 2 1 N informix logspace b19d9f0 3 0x1 3 1 N informix dbs01 b19db38 4 0x1 4 1 N informix dbs02 b19dc80 5 0x1 5 1 N informix dbs03 b19ddc8 6 0x1 6 1 N informix dbs04 b19e018 7 0x1 7 1 N informix dbs05 b19e160 8 0x1 8 1 N informix dbs06 b19e2a8 9 0x1 9 1 N informix dbs07 b19e3f0 10 0x1 10 1 N informix dbs08 b19e538 11 0x1 11 1 N informix dbs09 b19e680 12 0x1 12 1 N informix dbs10 b19e7c8 13 0x1 13 1 N informix dbs11 b19e910 14 0x1 14 1 N informix dbs12 b19ea58 15 0x1 15 1 N informix dbs13 b19eba0 16 0x1 16 1 N informix dbs14 b19ece8 17 0x1 17 1 N informix dbs15 b19ee30 18 0x1 18 1 N informix dbs16 b19f018 19 0x1 19 1 N informix dbs17 b19f160 20 0x1 20 1 N informix dbs18 b19f2a8 21 0x1 21 1 N informix dbs19 b19f3f0 22 0x1 22 1 N informix dbs20 24 active, 2047 maximum Performance Tuning TipsDisk onstat -D output (part 1 – dbspaces)

  31. address chk/dbs offset page Rd page Wr pathname aa0b918 1 1 0 178 482 /dev/rdsk/rootdbs aa4a410 2 2 0 19 8 /dev/rdsk/logspace aa4a578 3 3 0 21 11120 /dev/rdsk/Chunk01 aa4a6e0 4 4 0 21 11142 /dev/rdsk/Chunk02 aa4a848 5 5 0 21 11141 /dev/rdsk/Chunk03 aa4a9b0 6 6 0 21 11141 /dev/rdsk/Chunk04 aa4ab18 7 7 0 21 11142 /dev/rdsk/Chunk05 aa4ac80 8 8 0 21 11139 /dev/rdsk/Chunk06 aa4ade8 9 9 0 21 11141 /dev/rdsk/Chunk07 aa0ba80 10 10 0 21 11137 /dev/rdsk/Chunk08 aa0bbe8 11 11 0 21 11141 /dev/rdsk/Chunk09 aa0bd50 12 12 0 21 11117 /dev/rdsk/Chunk10 aa3b830 13 13 0 21 11139 /dev/rdsk/Chunk11 aa3b998 14 14 0 21 11045 /dev/rdsk/Chunk12 aa3bb00 15 15 0 21 11025 /dev/rdsk/Chunk13 aa3bc68 16 16 0 21 11016 /dev/rdsk/Chunk14 aa3bdd0 17 17 0 21 11005 /dev/rdsk/Chunk15 b19d018 18 18 0 21 10865 /dev/rdsk/Chunk16 b19d180 19 19 0 21 10919 /dev/rdsk/Chunk17 b19d2e8 20 20 0 21 10837 /dev/rdsk/Chunk18 b19d450 21 21 0 21 10738 /dev/rdsk/Chunk19 b19d5b8 22 22 0 21 10704 /dev/rdsk/Chunk20 Performance Tuning TipsDisk onstat -D output (part 2 – chunks)

  32. tty sd0 sd1 sd3 sd4 cpu tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id 0 2 6 1 21 1 0 324 0 0 19 0 0 6 1 0 0 99 4 0 0 0 0 152 52 382 0 0 0 0 0 0 40 4 57 0 0 0 0 0 0 135 48 413 0 0 0 0 0 0 40 3 57 0 0 0 0 0 0 154 50 399 0 0 0 0 0 0 47 3 50 0 0 0 0 0 0 145 48 414 0 0 0 0 0 0 34 3 63 0 0 0 0 0 0 131 49 407 0 0 0 0 0 0 39 3 58 0 0 0 33 6 7 137 53 376 0 0 0 0 0 0 44 5 51 0 0 0 0 0 0 132 48 419 0 0 0 0 0 0 31 2 67 0 0 0 0 0 0 144 52 383 0 0 0 0 0 0 41 3 55 0 0 0 0 0 0 136 49 407 0 0 0 0 0 0 40 4 56 0 Performance Tuning TipsDisk iostat output

  33. Performance Tuning TipsDisk What happened??? /dev/rdsk>ls -l Chunk* lrwxrwxrwx informix informix Chunk01 -> /dev/rdsk/dev101a lrwxrwxrwx informix informix Chunk02 -> /dev/rdsk/dev102a lrwxrwxrwx informix informix Chunk03 -> /dev/rdsk/dev103a lrwxrwxrwx informix informix Chunk04 -> /dev/rdsk/dev104a lrwxrwxrwx informix informix Chunk05 -> /dev/rdsk/dev105a lrwxrwxrwx informix informix Chunk06 -> /dev/rdsk/dev106a lrwxrwxrwx informix informix Chunk07 -> /dev/rdsk/dev107a lrwxrwxrwx informix informix Chunk08 -> /dev/rdsk/dev108a lrwxrwxrwx informix informix Chunk09 -> /dev/rdsk/dev109a lrwxrwxrwx informix informix Chunk10 -> /dev/rdsk/dev110a lrwxrwxrwx informix informix Chunk11 -> /dev/rdsk/dev111a lrwxrwxrwx informix informix Chunk12 -> /dev/rdsk/dev112a lrwxrwxrwx informix informix Chunk13 -> /dev/rdsk/dev113a lrwxrwxrwx informix informix Chunk14 -> /dev/rdsk/dev114a lrwxrwxrwx informix informix Chunk15 -> /dev/rdsk/dev201a lrwxrwxrwx informix informix Chunk16 -> /dev/rdsk/dev202a lrwxrwxrwx informix informix Chunk17 -> /dev/rdsk/dev203a lrwxrwxrwx informix informix Chunk18 -> /dev/rdsk/dev204a lrwxrwxrwx informix informix Chunk19 -> /dev/rdsk/dev205a lrwxrwxrwx informix informix Chunk20 -> /dev/rdsk/dev206a

  34. dev101a dev101b dev101c dev102a dev102b dev102c dev103a dev103b dev103c dev104a dev104b dev104c Performance Tuning TipsDisk Chunk01 Chunk02 Chunk03 Chunk04

  35. Performance Tuning TipsDisk • What can we do? • Use symbolic links for device paths • ln –s /dev/rdsk/dev102a Chunk02 • Move chunks to different devices • Use the UNIX ‘dd’ command to copy the device • dd if=/dev/rdsk/Chunk02 of=/somewhere/else bs=2048 • Change the link • rm Chunk02 • ln –s /dev/rdsk/dev101b Chunk02 • Copy the chunk back • dd if=/somewhere/else of=/ dev/rdsk/Chunk02 bs=2048

  36. Performance Tuning TipsDisk What does it look like now? /dev/rdsk>ls -l Chunk* lrwxrwxrwx informix informix Chunk01 -> /dev/rdsk/dev101a lrwxrwxrwx informix informix Chunk02 -> /dev/rdsk/dev101b lrwxrwxrwx informix informix Chunk03 -> /dev/rdsk/dev101c lrwxrwxrwx informix informix Chunk04 -> /dev/rdsk/dev101d lrwxrwxrwx informix informix Chunk05 -> /dev/rdsk/dev102a lrwxrwxrwx informix informix Chunk06 -> /dev/rdsk/dev102b lrwxrwxrwx informix informix Chunk07 -> /dev/rdsk/dev102c lrwxrwxrwx informix informix Chunk08 -> /dev/rdsk/dev102d lrwxrwxrwx informix informix Chunk09 -> /dev/rdsk/dev103a lrwxrwxrwx informix informix Chunk10 -> /dev/rdsk/dev103b lrwxrwxrwx informix informix Chunk11 -> /dev/rdsk/dev103c lrwxrwxrwx informix informix Chunk12 -> /dev/rdsk/dev103d lrwxrwxrwx informix informix Chunk13 -> /dev/rdsk/dev104a lrwxrwxrwx informix informix Chunk14 -> /dev/rdsk/dev104b lrwxrwxrwx informix informix Chunk15 -> /dev/rdsk/dev104c lrwxrwxrwx informix informix Chunk16 -> /dev/rdsk/dev104d lrwxrwxrwx informix informix Chunk17 -> /dev/rdsk/dev105a lrwxrwxrwx informix informix Chunk18 -> /dev/rdsk/dev105b lrwxrwxrwx informix informix Chunk19 -> /dev/rdsk/dev105c lrwxrwxrwx informix informix Chunk20 -> /dev/rdsk/dev105d

  37. dev101a dev101b dev101c dev102a dev102b dev102c dev103a dev103b dev103c dev104a dev104b dev104c Performance Tuning TipsDisk Chunk01 Chunk02 Chunk03

  38. tty sd0 sd1 sd3 sd4 cpu tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id 0 8 6 0 21 9 2 464 3 1 181 3 1 29 4 1 1 94 0 0 0 0 0 73 36 466 80 40 273 72 36 39 91 8 0 2 0 33 0 0 0 80 39 269 81 39 200 67 33 27 91 8 0 2 0 0 0 0 0 64 31 272 69 34 249 75 37 27 91 6 0 3 0 33 0 0 0 80 39 250 82 40 394 79 39 29 89 9 0 3 0 33 0 0 0 165 29 282 126 27 307 139 27 103 40 5 0 56 0 33 0 0 0 80 36 232 79 37 240 83 41 33 90 8 0 1 3 0 0 0 0 80 39 340 79 38 240 81 40 32 90 7 0 3 2 33 13 3 3 69 34 349 77 38 393 93 45 37 85 9 0 6 0 33 0 0 0 85 41 434 81 39 393 89 44 31 90 8 0 2 0 0 1 0 1 89 44 329 86 42 330 76 37 27 89 8 0 3 0 33 0 0 0 77 38 378 76 38 299 73 36 24 90 7 0 2 0 0 0 0 0 92 44 373 89 42 261 73 35 22 92 8 0 1 0 33 1 0 2 81 39 348 74 37 257 75 37 37 90 7 0 2 Performance Tuning TipsDisk iostat output

  39. Performance Tuning Tips CheckpointsandLRUs

  40. Performance Tuning TipsCheckpoints/LRUs • Performance goals • Minimize Checkpoint duration • Maximize write cache %

  41. Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 1 6 4 7 2 5

  42. Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 A 1 6 4 7 2 5 A

  43. A Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 A 1 6 7 4 2 5

  44. A Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 A 1 6 7 4 2 5

  45. A Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 B 1 6 7 4 2 5

  46. A Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 B 1 6 7 4 2 5 Checkpoint

  47. Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 B 1 6 7 4 2 5 Checkpoint B

  48. Performance Tuning TipsCheckpoints/LRUs LRUS 0 f 1 m 2 f 3 m 4 f 5 m 6 f 7 m BUFFERS 3 8 B 1 6 7 4 2 5 Checkpoint Complete! B

  49. FUZZY CHECKPOINT Does not write pages from fuzzy operations Faster than full checkpoint Performance Tuning TipsCheckpoints/LRUs • FULL CHECKPOINT • Writes all modified pages to disk • Blocks user processing

  50. Performance Tuning TipsCheckpoints/LRUs • FUZZY OPERATIONS • Inserts, updates, deletes for built-in data types • NON-FUZZY OPERATIONS • Inserts, updates, deletes for user-defined data types, BLOBS • Table alters and loads • Create or alter indexes

More Related