Presented by Dan Foreman Dan Foreman Progress User since 1984 Author of several Progress related Publications Progress Performance Tuning Guide Progress Database Administration Guide Progress VST amp System Tables ID: 535794
Download Presentation The PPT/PDF document "Top Performance Enhancers & Destroye..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Top Performance Enhancers & Destroyers
Presented by: Dan ForemanSlide2
Dan ForemanProgress User since 1984Author of several Progress related PublicationsProgress Performance Tuning Guide
Progress Database Administration GuideProgress VST & System TablesBasketball & Bicycle Fanatic…which sometimes leads to “unscheduled outages” and trips to the Emergency Room: WARNING POTENTIALLY DISTURBING CONTENTSlide3Slide4
Just got back from JapanSlide5
Audience Survey - DemographicsHow many have used Progress (not Mfg/Pro) for
less than one year?How many are in a company that has used Progress for less than one year?Slide6
Audience Survey - TechnicalLargest Single Progress DBHighest Progress VersionLowest Progress Version32-Bit?
Are you using AuditingMulti-TenancyOE ReplicationTDETable PartitioningSlide7
IntroductionThe slide titles are numbered, but that is not necessarily a rank or priority orderingSlide8
Enhancers #1 - Basic DB Tuning – Crucial-spin (-spin)
Generally no higher than 50000Ignore any formula that includes CPUsDB Buffer Cache (-B)As large as possible WITHOUT exhausting system memoryBefore Image Cluster Size (proutil –bi)Faster Server/Storage = Larger Cluster Size32-64mbBeware of Cluster Formatting issuesAPWs (proapw)
BIW (
probiw
)
AIW (
proaiw
)Slide9
Enhancers #2 – DB Configuration Database Block Size 8k or 4kCan be controversialMy preference is 8k because it tends to reduce record
fragmentation; also more index keys per blockThe default empty DB Block Size has been 4k since V10Records Per BlockNot 256Not 1 for Index AreasLesser Importance (but critical for future proofing)Type 2 Storage AreasLeastFixed ExtentsControversialSlide10
Enhancers #3 - IndexingThe cost of adding a new index is relatively small compared to the benefits….assuming that the index is properly utilizedIs the Index used at all?Bracketing on all components of the key?
Proper hierarchy in the components?Not duplicated?A + B + CA + B + C + DSlide11
Enhancers #4 – Remote Client CommunicationMessage Size (-Mm) parameter for Remote ClientsDoes NOT apply to SQL/ODBC Clients
Best results seen with NO-LOCK readsMust be specified on the Database and the Client (fixed in V11.6!)Default: 1024Sweet spot: 4096-8192 (YUOMMV)TCP Jumbo PacketsSlide12
Enhancers #5 – Dump & LoadMetrics to useScatter Factor (mainly Type 1 Areas)How many have heard that Type 2 Areas don’t require a D&L?
Fragmentation – a percent is easier to understand than FactorTrack the time it takes to run proutil dbanalysUse –scan in early V10 to prevent locking conflictsTrack the elapsed time required to run a set of Killer ReportsSlide13
Enhancers #6 – Reporting DatabaseOLTP and Reporting exercise the DB Engine in somewhat contrary waysSome ad-hoc queries may not be able to take advantage of existing indexes
Ad Hoc & Period Ending reports run against a Production DB with some ODBC based BI or other Reporting ToolCopy of Production DBRefreshed periodically (backups or AI logs) or real time (OE Replication)Should reside on a different serverLook @ BravePoint’s Pro2* Product SuitePro2SQLPro2OraclePro2ProSlide14
Enhancers #7 – SQLRegularly run UPDATE STATISTICSUse Moderation!
Separate Login Broker for SQL Clients -m3-ServerType-minport / -maxportBroker Temp Files on a Fast Device (-T on preserve)Slide15
Enhancers #8 – StorageSSD StorageFlash StorageThey are not the sameSlide16
Destroyers #1 – Poor Coding TechniquesCode that reads the same record (or set of records) repeatedlyProduces Hit Ratios that exceed 10000:1Compare Table I/O (_tablestat
) with dbanalys record counts“Compensate” with –lruskips ParameterSlide17
Destroyers #2 – Poor Coding TechniquesCode that repetitively exits to the operating systemContext switchCreate sub-process
Terminate sub-processRepeatSlide18
Destroyers #3 – IndexingDevelopers who don’t understand (or ignore) the Progress Indexing RulesThe Rules are reasonably well documented
Progress DocumentationDan Foreman’s Progress Performance Tuning GuideSlide19
Destroyers #4 – AppServers & WebSpeed Tuning
Poorly Tuned AppServers & WebSpeed AgentsDepending on the State, one AS/WS might run code for thousands of usersImportant Parameters:Exec Buffer (-mmax #)Sort File Block Size (-TB 31)Sort Memory Buffers (-TM 32)R-Code Quick Request (-q)Temp-Table Buffers (-Bt #)Temp File Location (-T)Temp File Visibility (-t)
Message Compression (-mc) WAN
Connections onlySlide20
Destroyers #5 - JavaJava (or processes written in Java)WebSpeed Broker
AppServer BrokerAdmin ServerName ServerTomcatMonitor excessive memory use and/or high CPU utilizationSlide21
Destroyers #6 - CheckpointsThere are several major performance issues that can occur @ Checkpoint timeBuffers FlushedBI Cluster Formattings
ync call (old versions of Progress)fdatasync call (new versions of Progress)Other stuffCheckPoints will be covered in detail in another sessionSlide22
Destroyers #7 – Logical Scatter FactorWhen the physical contiguosity of the data doesn’t match the order used by the indexApplies to both Type 1 and Type 2 Areas!Slide23
Destroyers #8 – Remote Client AccessVersus Self-Service Client DB connectionsAbnormal death of a SELF Client can potentially bring the DB
DOWN!Slide24
Destroyers #9 – Accessing _Lock VSTPotentially very slowHigh CPU activityHigh Latch activityFixed in Progress V11.4Slide25
Destroyers #10 – StorageRAID 5RAID 6RAID DP – equals RAID 6Lot’s of Cache only delays the inevitable
NetAppSlide26
Destroyers #11 - NUMANon-Uniform Memory ArchitectureIf the number of CPU Cores exceeds 64 (approximately), Progress does not scale always wellLatch activity needs to be propagated to the cache of each CPUSlide27
Destroyers #12 – DB ConfigurationTables or Indexes stored in the Schema Area – frequently seen in custom or side DBsThe Schema Area is a Type 1 AreaAI Extents not separated from DB/BI Extents (mainly for integrity, performance is secondary)Slide28
Destroyers #13 – Miscellaneous32-Bit Database LicensePoorly configured or inadequately resourced Virtual MachinesSlide29
Thank You!Questions?
dforeman@bravepoint.comMobile: +1 541 908 3437
Request: Please thank
Conference
organizers for their hard work in putting together an excellent conference