Practical Performance Tuning For Your Progress Open Edge
- Slides: 48
Practical Performance Tuning For Your Progress Open. Edge Database
A Few Words About The Speaker • Tom Bascom, Roaming DBA & Progress User since 1987 • President, DBAppraise, LLC – Remote Database Management Service. – Simplifying the job of Managing and Monitoring the world’s best business applications. – tom@dbappraise. com • VP, White Star Software, LLC – Expert Consulting Services related to all aspects of Progress and Open. Edge. – tom@wss. com
Tom’s Top Twenty Tuning Tips In No Particular Order.
{inc/disclaimer. i} • Your kilometerage will vary. • These tips are in no special order. But sometimes order does matter. • In an ideal world you will change one thing at a time, test and remove changes that do not have the desired effect. • The real world isn’t very ideal.
#20 Dump & Load • • • You shouldn’t need to routinely dump & load. If you’re on OE 10, Using type 2 areas, That have been well defined, And you have a well-behaved application. • The rest of us benefit from an occasional D&L.
#20 Dump & Load DB-20 Highly Parallel Dump & Load http: //www. psdn. com/ak_download/media/exch_audio/2007/DB/DB-20_Bascom. pdf
#19 Stay Current • Up to date releases of Progress, your OS and your application are essential components of a well tuned system. • You cannot take advantage of the best techniques, improved algorithms or new hardware without staying up to date. • Failure to stay up to date may mean poor performance, increased costs and uncompetitiveness.
#19 Stay Current • TRX throughput in v 8 -> v 9 (CCLP) • Major 4 GL execution speed improvements in 9. 1 E. • 64 bit platform support and large memory. • 10. 1 A Automatic Defragmentation. • 10. 1 B Workgroup/Multicore bug addressed. • Significant improvements in DB read performance in 10. 1 C (-spin enhancements).
#19 The Impact of an Upgrade co nv 89
#18 Parallelize • Step outside of the box and consider what portions of your system could benefit from being parallelized: • MRP Runs • Nightly Processing • Reports • Data Extracts • Data Imports
#18 Parallelize $ mbpro dbname –p exp. p –param “ 01|0, 3000” $ mbpro dbname –p exp. p –param “ 02|3000, 6000” $ mbpro dbname –p exp. p –param “ 03|6000, 9999” /* exp. p */ define variable start. Cust as integer no-undo. define variable end. Cust as integer no-undo. start. Cust = integer( entry( 1, entry( 2, session: parameter, “|” ))). end. Cust = integer( entry( 2, session: parameter, “|” ))). output to value( “export. ” + entry( 1, session: parameter, “|” ). for each customer no-lock where cust. Num >= start. Cust and cust. Num < end. Cust: export customer. end. output close. quit.
#17 Update Statistics • SQL-92 uses a cost based optimizer… • But it cannot optimize without knowledge of the cost! (data distribution). • Weekly or monthly “update statistics” is appropriate for most people. • Or when 20% of your data has changed. • This is a data intense process: – Run it during off hours if you can. – You might want to only do a few tables/indexes at a time.
#17 Update Statistics $ cat customer. sql UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB. Customer; For More Information: Open. Edge Data Management: SQL Development Chapter 10, Optimizing Query Performance COMMIT WORK; $DLC/bin/sqlexp -db dbname -S portnum -infile customer. sql -outfile customer. out -username -password passwd >> customer. err 2>&1
#16 Progress App. Server • Used to reduce network traffic and latency. • When properly implemented it will minimize the path length between business logic and data persistence layers. • IOW, for best performance, the App. Server should live on the same server as the database and use a self-service connection. • Exception: An App. Server which is dedicated to making Windows API calls.
#16 Progress App. Server run asproc on … procedure asproc … for each … end.
#15 fork() & exec() • Very expensive system calls. • Multiple context switches, process creation & tear-down, IO to load executables etc. • Complex shell scripts (the old UNIX “lp” subsystem). • Long PATH variables. • 4 GL usage of UNIX, DOS, OS-COMMAND and INPUT THROUGH.
#15 fork() & exec() define variable i as integer no-undo. define variable f. Size as integer no-undo. etime( yes ). do i = 1 to 1000: input through value( "ls -ld. . " ). import ^ ^ f. Size. input close. end. display etime f. Size. etime( yes ). do i = 1 to 1000: file-info: file-name = ". f. Size = file-info: file-size. end. display etime f. Size. 3140 ms, at least 1000 calls to each of open(), close(), fork(), exec(), read() complete with multiple context switches per invocation. 16 ms, 1, 000 stat() calls.
#14 -spin • Almost all new machines, even desktops & laptops are now multi-core. • Do NOT use the old X * # of CPUs rule to set –spin. It is bogus. • Bigger is not always better with –spin! • Modest values (5, 000 to 10, 000) generally provide the best and most consistent results for the vast majority of people. • Use readprobe. p to explore. • Check out Rich Banville’s Superb Exchange 2008 Presentation!
#14 -spin OPS-28 A New Spin on Some Old Latches http: //www. psdn. com/ak_download/media/exch_audio/2008/OPS-28_Banville. ppt
#13 bi cluster size • The idea is to reduce the number and frequency of checkpoints giving APWs plenty of time to work. • Larger bi clusters permit spikes in the workload to take place without ambushing the APWs. • Easy to overlook when building new db via prostrct create… • 512 is the default OE 10 bi cluster size. • 8192 is good for small systems. • 16384 is “a good start” for larger systems. • Longer REDO phase on startup so don’t get crazy. • NOT a good idea for “Workgroup” database licenses. For WG small values (512 or 1024) are better.
#13 bi cluster size $ grep ‘(4250)’ dbname. lg (4250)Before-Image Cluster Size: 524288. $ proutil dbname -C truncate bi -bi 16384 … (1620) Before-image cluster size set to 16384 kb. (1621)Before-Image Cluster Size: 16777216. $ proutil dbname -C -bigrow 8
#12 APW, AIW, BIW & WDOG • • Always start a BIW Always start an AIW Start WDOG Two APWs are usually enough: – Too many is just a (small) waste of CPU cycles. – If you are consistently flushing buffers at checkpoints increase bi cluster size and add an APW (one at a time until buffer flushes stop).
#12 APW, AIW, BIW & WDOG A PW D a t a BIW & AIW Da ta
#11 Larger db Blocks • Larger blocks result in much more efficient IO. • Fewer IO ops mean less contention for disk. • Moving from 1 k to 4 k is huge. 4 k to 8 k is relatively less huge but still very valuable. • 8 k works best in most cases. Especially readheavy workloads. • Better disk space efficiency (tighter packing, less overhead). • Don’t forget to adjust –B and Rows Per Block!
#11 Larger db Blocks • Large Blocks reduce IO, fewer operations are needed to move the same amount of data. • More data can be packed into the same space because there is proportionally less overhead. • Because a large block can contain more data it has improved odds of being a cache “hit”. • Large blocks enable HW features to be leveraged. Especially SAN HW.
#11 Larger db Blocks 8 k 8 k N Blo ck s Blo erv S w e ck s er
#10 Type 2 Storage Areas • Data Clusters – contiguous blocks of data that are homogenous (just one table). • 64 bit ROWID. • Variable (by area) rows per block. • All data should be in type 2 areas – until you prove otherwise. • Storage Optimization Strategies!
#10 Type 2 Storage Areas St or ag ea re as
#9 Transactions • Distinguish between a “business transaction” and a “database transaction”. • Do not try to abuse a database transaction to enforce a business rule: – You may need to create “reversing (business) transactions”. – Or restartable transactions. • For large database operations “chunk” your transactions.
#9 “Chunking” Transactions define variable i as integer no-undo. outer: do for customer transaction while true: inner: do while true: i = i + 1. find next customer exclusive-lock no-error. if not available customer then leave outer. discount = 0. if i modulo 100 = 0 then next outer. end.
#8 Minimize Network Traffic • Use FIELD-LIST in queries. • Use –cache and –pls. • NO-LOCK queries pack multiple records into a request and eliminate lock downgrade requests. • Watch out for client-side sorting and selection on queries. • Remember that CAN-DO is evaluated on the CLIENT (yet another reason not to use it). • Use -noautoresultlist/FORWARD-ONLY.
#8 Minimize Network Traffic • Use a secondary broker to isolate high activity clients (such as reports). • Consider setting –Mm to 8192 or larger. • Use –Mn to keep the number of clients per server low (3 or less). • Use –Mi 1 to spread connections across servers.
#7 Runaways, Orphans, Traps & Kills Consume entire cores doing nothing useful. These are sometimes caused by bugs. But that is rare. More likely is a poorly conceived policy of restricting user logins. • The UNIX “trap” command is often at the bottom of these problems. • •
#7 Runaways, Orphans, Traps & Kills http: //dbappraise. com/traps. html
#6 The Buffer Cache • The cure for disk IO is RAM. • Use RAM to buffer and cache IO ops. • Efficiency of –B: – Is loosely measured by hit ratio. – Changes follow an inverse square law. – So to make a noticeable change in hit ratio you must make a large change to –B. • 100, 000 is “a good start” (800 MB @ 8 k blocks).
#6 The Buffer Cache In Big B You Should Trust! Layer Time Progress to –B # of Recs # of Ops Cost per Op Relative 0. 96 100, 000 203, 473 0. 000005 1 -B to FS Cache 10. 24 100, 000 26, 711 0. 000383 75 FS Cache to SAN 5. 93 100, 000 26, 711 0. 000222 45 -B to SAN Cache* 11. 17 100, 000 26, 711 0. 000605 120 SAN Cache to Disk 200. 35 100, 000 26, 711 0. 007500 1500 -B to Disk 211. 52 100, 000 26, 711 0. 007919 1585 * Used concurrent IO to eliminate FS cache
#5 Rapid Readers • Similar to a runaway – consumes a whole CPU • But is actually doing db IO • Usually caused by: – Table scans – Poor index selection. – Unmonitored batch processes and app-servers. – Really bad algorithm choices.
#5 Rapid Readers R h g i H Ra d a e te Sus pic iou Co su s er IO de be ing ru n !
#4 Balance & Isolate IO • Use more than one disk: – A fast disk can do 150 or so random IO Ops/sec. – Kbytes/sec is a measure of sequential IO. – OLTP is mostly random. • • Don’t waste time trying to “manually stripe”. Instead, use “hardware” striping and mirroring. Isolate AI extents for safety, not performance. Isolate temp-file, application, OS and “other” IO.
#4 Balance & Isolate IO fill. Time = cache. Size / (request. Rate – service. Rate) Typical Production DB Example (4 k db blocks): 4 GB / ( 200 io/sec – 800 io/sec ) = cache doesn’t fill! Heavy Update Production DB Example: 4 GB / ( 1200 io/sec – 800 io/sec ) = 2621 seconds (RAID 10) 4 GB / ( 1200 io/sec – 200 io/sec ) = 1049 seconds (RAID 5) Maintenance Example: 4 GB / ( 5000 io/sec – 3200 io/sec ) = 583 seconds (RAID 10) 4 GB / ( 5000 io/sec – 200 io/sec ) = 218 seconds (RAID 5)
#3 Manage Temp File IO Temp-file IO can exceed db IO. Sometimes by 2: 1, 3: 1 or more! -T isolates temp file IO. -t helps you to crudely diagnose the source of IO. -y provides some detail regarding r-code swapping. -mmax buffers r-code, 4096 is a good start for Ch. UI, 16384 for GUI. • Memory mapped procedure libraries cache r-code. • Use –Bt & -tmpbsize to tune 4 GL temp-tables. • • •
#3 Manage Temp File IO -rw-r--r--rw-r--r--rw-r--r--rw-r--r--rw-r--r-- 1 1 1 1 1 VEILLELA wrightb STEELEJL THERRIKS root wrightb BECKERLM CALUBACJ users users users CLIENT. MON (-y) Program access statistics: Reads from temp file: Writes to temp file: Loads of. r programs: Saves of compilation. r's: Compilations of. p's: Checks of files with stat: 579312 35697664 36772864 0 17649 34704 811008 8192 Times 0 0 14 0 0 165 Oct Oct Oct 19 19 19 15: 16 07: 12 15: 16 11: 06 09: 16 Bytes 0 0 524594 0 0 0 srtr. Ayh. Eb srt. H 6 miqb srtz 37 kyb srt--Elab lbi. V 6 Qp 7 a lbi-Tym. Ma DBIHDmiqc DBI--Abac DBI--Abyc
#2 Index Compact • Compacts Indexes. • Removes deleted record placeholders. • Improves “utilization” = fewer levels & blocks and more index entries per read. • Runs online or offline. • Available since version 9.
#2 Index Compact proutil dbname –C idxcompact table. index target% • Do NOT set target % for 100! • Consider compacting when utilization < 70% • … and blocks > 1, 000. INDEX BLOCK SUMMARY FOR AREA "APP_FLAGS_Idx" : 96 ---------------------------Table Index Fields Levels Blocks Size %Util PUB. APP_FLAGS App. No 183 1 3 4764 37. 1 M 89. 9 Fax. Date. Time 184 2 2 45 259. 8 K 72. 4 Fax. User. Notified 185 2 2 86 450. 1 K 65. 6 Factor 1. 2 1. 6 1. 7
#1 Stupid 4 GL Tricks • Bad code will defeat any amount of heroic tuning and excellent hardware. • Luckily bad code is often advertised by the perpetrator as having been developed to “improve performance”. • Just because a feature of the 4 GL can do something doesn’t mean that it should be used to do it.
#1 Stupid 4 GL Tricks /* SR#1234 – enhanced lookup to improve performance! */ update c. Name. find first customer where c. Name matches customer. name use-index cust. Num no-error. -Or – find first customer where can-do( c. Name, name ) use-index cust. Num no-error.
Questions?
Thank You!
- Physical progress and financial progress
- Open innovation open science open to the world
- Rising edge and falling edge
- Thomson tuning
- Sql 2005 performance tuning
- How to reduce mips in mainframe
- Database tuning tutorial
- Glusterfs slow write performance
- Apache performance tuning
- Abap performance tuning
- Moodle
- Toad performance tuning
- Ssas performance tuning
- Sshfs performance
- Ibm maximo architecture diagram
- Apache web server performance tuning
- Mysql huge pages
- Database performance tuning and query optimization
- Ms access performance analyzer
- Harrison performance and tuning
- Navision sql performance tuning
- Data warehouse performance tuning
- Terminal server performance tuning
- Walker performance tuning
- Informix performance tuning
- Performance based payment
- Progress and performance measurement and evaluation
- Progress and performance measurement and evaluation
- Performance based practical design
- Unit 2 practical sports performance
- Eyes of slat
- Open edge computing
- Whats abl
- Check your progress 1
- Give us your hungry your tired your poor
- Don't put your cup on the edge of the table
- Kontinuitetshantering i praktiken
- Novell typiska drag
- Nationell inriktning för artificiell intelligens
- Ekologiskt fotavtryck
- Varför kallas perioden 1918-1939 för mellankrigstiden?
- En lathund för arbete med kontinuitetshantering
- Underlag för särskild löneskatt på pensionskostnader
- Tidbok för yrkesförare
- A gastrica
- Vad är densitet
- Datorkunskap för nybörjare
- Tack för att ni lyssnade bild
- Att skriva en debattartikel