Top 10 Performance Hints Adam Backman White Star

Top 10 Performance Hints Adam Backman White Star Software adam@wss. com

What is performance tuning? Performance tuning is a process where application response times are recorded and modifications to the environment are made is a systematic manner in an effort to reduce the amount of time it takes to complete system tasks.

What is performance tuning? A process of moving the bottleneck to the fastest resource.

Hardware Resources (Slowest to Fastest) ä Network ä Disk ä Memory ä CPU

When to Tune Performance? ä Installation or upgrade of hardware ä Installation or upgrade of software ä Change in workload ä Number of users ä Volume of data

How Fast is Fast? Remember: Performance is relative so it is important to have a baseline.

Application Performance The single largest component of performance tuning is application performance. A well written application will run well given the proper resources while a poorly written application will rarely run well despite resource allocation.

A few application hints ä Use indexes - not USE-INDEX ä Use NO-UNDO ä Use TEMP-TABLES not WORKFILES ä Use NO-LOCK ä Use FIELD-LIST ä Go n-tier - Progress application server

But enough about applications let’s talk about database and system tuning

Network contention The network is the slowest resource for client/server applications so you want to eliminate contention for this resource before moving on to the other resources

Networking tips ä Keep things local ä No temp files on network drives ä Move the application “close” to the user ä Use -cache to speed initial connection ä Use -pls if you are using program libraries over the network ä Application issues are magnified over a network (field-lists, no-lock, indexes, …)

Odd Network Issues ä Bandwidth ä Speed (10/100/Auto) ä Duplex (Always full) ä Sharing TCP/IP and Apple. Talk ä Latency (WAN Issue) ä Mixing hardware (Cisco and Fore)

Disk contention In most environments disks are the largest area for improvement. All of the data flows from the disks to the other resources so this effects both local and networked users.

Disk tips ä No RAID-5 ä Use storage areas ä Use 8 k block size ä Use the correct BI cluster size ä Use page writers ä Use private buffers (-Bp) ä Use -T to eliminate variance

Disk Tips (continued) ä Buy many small disks Two heads are better than one ä Buy fast disks Buy at least 10, 000 RMP ä Buy fast controllers Fibre channel is better than SCSI

No RAID 5 ä Poor man’s mirroring - This is the kiss of death for OLTP performance ä User information is striped ä Parity information is striped WITH user information ä OK for 100% read only applications ä Poor performance for writes

If no RAID 5 what do I do? ä RAID 0+1 for the database ä RAID 1 for the AI and BI logs ä Hardware RAID is almost always better than software RAID

Storage Areas • • Progress-specific way to distribute I/O Only way to eliminate I/O indirection in a Progress environment Only way to pre-allocate database blocks Every database has at least 1 storage area in Progress version 9

Storage Areas ä Pros: ä Reduce utility time ä Increase sequential access performance ä Cons: ä Adds complexity ä More potential room for failure

8 k block size ä Most systems will benefit from using 8 k block size (NT should use 4 k) ä You will retrieve more information per physical I/O especially on index reads ä I/O is done how the operating likes it to be done. ä Remember to modify records per block if necessary

BI Cluster size ä Somewhere between 1 MB and 4 MB works for most people ä If you are checkpointing every 2 minutes or more often during peak periods increase the cluster size ä If you a “workgroup” version of Progress leave your cluster size alone (512 kb)

Progress page writers ä Every database that does updates should have a before image writer (BIW) ä Every database that does updates should have at least 1 asynchronous page writer (APW) ä Every database that is using after imaging should have a after image writer (AIW)

Tuning APWs ä Start with 1 APW ä Monitor buffers flushed at checkpoint on the activity screen (option 5) in promon ä If buffers flushed increases during the “important” hours of the day add 1 APW

Private Buffers (-Bp) ä Not really private ä Use different LRU chain ä Good for read only clients (reports) ä _My. Connection. _My. Conn- Num. Seq. Buffers to allocate or deallocate from the application level

Use -T to level disk I/O Local (host based) users and batch jobs should use the -T parameter to place their temporary file (. srt, . pge, . lbi, …) I/O on a drive that is not working as hard as the other drives on the system Note: -T should never point to a network drive

Memory contention Memory should be used to reduce disk I/O. Broker (server) side parameters should be tuned first and then user parameters can be modified. In a memory lean situation, memory should be taken away from individual users before reducing broker parameters.

Memory hints ä Swapping and/or excessive paging is bad, buy more memory or reduce parameters to avoid it ä Increase -B in 10% increments until the point of diminishing returns or swapping, whichever comes first ä Use V 9 private buffers (-Bp) for reporting ä Do not use private buffers (-I) prior to V 9

Memory hints (continued) ä ä ä Use memory for the users closest to the customer first (developers increase last) Use -Bt for large temp tables Set -bibufs between 50 and 120. Look at the activity screen in promon (BI buffer waits) to see if additional tuning is necessary. Use –pinshm to keep broker memory from being paged out. Use -directio to bypass the OS buffer pool

CPU contention High CPU activity is not bad in and of itself but high system CPU activity is bad and should be corrected.

Components of CPU activity ä USER - This is what you paid for ä SYSTEM - This is overhead ä WAIT - This is waste ä IDLE - This is nothing ; -)

CPU activity goals The goal is to have as much USER time as possible with as little SYSTEM and WAIT. A practical split is USER: SYSTEM: WAIT: IDLE: 70% 20% 0% 10%

Eliminating high SYSTEM CPU activity ä Always use -spin ä Use a setting of 1 for single CPU systems ä Use a higher setting for multiple CPU systems ä Testing has shown that the optimal setting for -spin is somewhere between 2000 and 10000. First try 2000 ä -napmax should default to 5000 but in some late 7 and early 8 versions of Progress it is set to 100 which is way too low

Eliminating high WAIT CPU activity ä WAIT = Waiting on I/O ä If you still have IDLE time it generally is not a big problem ä Look at paging/swapping first ä Next look at your disk I/O

Points to remember ä Document where you are now before changing anything ä Change 1 thing at a time so you know what made a difference ä Move from the slowest to the fastest resource ä Buying faster disks generally does more good than buying faster CPUs

Questions