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: 262897
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 TablesAuthor of several useful Progress DBA ToolsProMonitor & ProCheck & LockMonPro Dump&LoadBalanced BenchmarkBasketball & Bicycle Fanatic…which sometimes leads to unexpected trips to the Emergency Room: WARNING POTENTIALLY DISTURBING CONTENTSlide3Slide4
April - June FlightsSlide5
I Won a Progress Earth Day Photo ContestSlide6
Audience Survey - DemographicsHow many have used Progress for less than one year?How many are in a company that has used Progress for less than one year?Slide7
Audience Survey - TechnicalLargest Single Progress DBHighest Progress VersionLowest Progress VersionAre you using Auditing
Multi-TenancyOE ReplicationTDETable PartitioningSlide8
IntroductionThe slide titles are numbered, but that is not necessarily a rank or priority orderingSlide9
Enhancers #1 - Basic DB Tuning – Crucial-spin (-spin)
DB Buffer Cache (-B)Before Image Cluster Size (proutil –bi)APWs (proapw)BIW (probiw)AIW (proaiw)Slide10
Enhancers #2 – DB Configuration Database Block Size 8k or 4kCan be controversialMy preference is 8k because it tends to reduce record fragmentation
The default empty DB Block Size has been 4k since V10LesserType 2 Storage AreasLeastFixed ExtentsSlide11
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?Slide12
Enhancers #4 – Remote Client CommunicationMessage Size (-Mm) parameter for Remote ClientsDoes NOT apply to SQL/ODBC ClientsBest results seen with NO-LOCK reads
Must be specified on the Database and the Client (fixed in V11.6!)Default: 1024Sweet spot: 4096-8192 (YUOMMV)Slide13
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 FactorTime it takes to run proutil dbanalysUse –scan in early V10 to prevent locking conflictsElapsed time to run a set of Killer ReportsSlide14
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 SuitePro2SQLPro2OraclePro2ProSlide15
Enhancers #7 – SQLRegularly run UPDATE STATISTICSUse Moderation!
Separate Login Broker for SQL Clients (-m3 & -ServerType)Slide16
Enhancers #8 – StorageSSD StorageFlash StorageSlide17
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 countsSlide18
Destroyers #2 – Poor Coding TechniquesCode that repetitively exits to the operating systemContext switchCreate sub-process
Terminate sub-processRepeatSlide19
Destroyers #3 – IndexingDevelopers who don’t understand (or ignore) the Progress Indexing RulesThe Rules are reasonably well documented
Progress DocumentationProgress Performance Tuning GuideSlide20
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 onlySlide21
Destroyers #5 - JavaJava (or processes written in Java)WebSpeed Broker
AppServer BrokerAdmin ServerName ServerTomcatMonitor excessive memory use and/or high CPU utilizationSlide22
Destroyers #6 - CheckpointsThere are several major performance issues that can occur @ Checkpoint timeBuffers Flushed
BI Cluster Formattingsync call (old versions of Progress)fdatasync call (new versions of Progress)Other stuffCheckPoints will be covered in detail in another sessionSlide23
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!Slide24
Destroyers #8 – Remote Client AccessVersus Self-Service Client DB connectionsAbnormal death of a SELF Client can bring the DB downSlide25
Destroyers #9 – Accessing _Lock VSTPotentially very slowHigh CPU activityHigh Latch activitySlide26
Destroyers #10 – StorageRAID 5RAID 6RAID DP – equals RAID 6
Lot’s of Cache only delays the inevitableNetAppSlide27
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 CPUSlide28
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)Slide29
Destroyers #13 – Miscellaneous32-Bit Database LicensePoorly configured or inadequately resourced Virtual MachinesSlide30
Thank You!Questions?
dforeman@bravepoint.comMobile: +1 541 908 3437
Request: Please thank PCA organizers for their hard work in putting together an excellent conference