How to manage… 10,000 Progress DB In a Cloud Computing environment How to manage… 10,000 Progress DB 1 Proginov 2 Meters and Records 3 Real-time analysis 4 Code optimization 5 Conclusion How to manage… 10,000 Progress DB 1 Few words about the speakers Julien Dournon • Progress® developper since 2005 • At Proginov since 2005 • SaaS Team since 2006 • jdournon@proginov.com Patrice Perrot • Progress® developper since 2001 • At Proginov since 2008 • Performances optim. since 2005 •SaaS Team since 2010 • pperrot@proginov.com 3 How to manage… 10,000 Progress DB 1 Proginov Staff 163 employees Average age: 36 years old Staff turnover < 1% Turnover 2012 €22.6M in 2012 (around $31M) Forecasts : €24M for 2013 (around $33M) Doubled in 5 years 3rd French Business ISV (Top TRUFFLE 2010) 3rd Price for highest growth AFDEL 2007 11% growth in 2010, 12,5% growth in 2011, 14% growth in 2012 4 How to manage… 10,000 Progress DB 1 Proginov activities 30 years of experience as a publisher 30 developers dedicated to the Proginov standard solution (R&D) Proginov ERP Integrator 25 project managers 23 developers dedicated to the customers 20 engineers in charge of deployments and R&D 16 Hotliners Hosting provider since 2001 5 How to manage… 10,000 Progress DB 1 Proginov Business Services Bundle 6 How to manage… 10,000 Progress DB 1 Proginov Business Services Bundle Business solutions • Trade • Repair shops and workshops • Industry • Packaging • Distribution • Wood industry • Retail • Industrial joinery • Mail order sales • Quarries and sandpits • Agro-food industry • Rent • Clothing and textile industries • Ecommerce • Building and civil Engineering 7 How to manage… 10,000 Progress DB 1 Cloud : applications and hosting A hosting architecture at your service 24/7 Since 2001, Proginov offers to its customers an easier way to manage their IT system: the SaaS mode 8 How to manage… 10,000 Progress DB 1 Cloud : applications and hosting Cloud Key facts • • • • • • • 331 hosted customers 580 remote sites 6,500 on-premise and mobile users 191,000 connections/month 2 Datacenters + 450 servers Revenue: €13.8M ($19M) representing 61% of global turnover Quality of service as the heart of the infrastructure • • • • • Packeteer Progibox All the infrastructure is duplicated Private multi-operator links between customer sites and Proginov 24/7 monitoring 9 How to manage… 10,000 Progress DB 1 Cloud : applications and hosting PROGICLOUD • • • • • « Limited » and finite resources What is critical ? Availability ? 24/7 ! Quite heavy-loaded servers (pooling) A patchwork of configurations for our DB-Servers 10 Meters and Records How to manage… 10,000 Progress DB 2 Meters and Records Pooling system drawbacks • Machine limitation: principle of communicating vessels • CPU and IO Activity: Edge effect • With great DB numbers comes great … inertia 12 How to manage… 10,000 Progress DB 2 Meters and Records DAN FORMAN Performance Rule n°2 • Best performances are usually met with substantial increase in : – Financial cost (Best servers/Network/Storage system) – Spending more time in system administration – Developing more complex applications 13 How to manage… 10,000 Progress DB 2 Meters and Records How to lessen/soften the previous rule • • • • Access control: not just who but how many Taking action: where is the priority Regular and trustworthy checks (-n, log, …) … 14 How to manage… 10,000 Progress DB 2 Meters and Records From where ? Simple, « just » with: • DBAnalys • VST : – _ActSummary – _table Stat – _IndexStat – _Connect • System information • Logs (Session, db) 15 How to manage… 10,000 Progress DB 2 Meters and Records Researching optimization everywhere • Trying to decrease DB access: programs optimization • Trying to decrease Access Time: DB Managment , with the best possible schema and organization • Evaluate the best startup parameters considering every DB: DB Administration, looking at each db activity 16 How to manage… 10,000 Progress DB 2 Meters and Records Our « Home-Made » DB Management Tool • Entry point for the optimization work 17 How to manage… 10,000 Progress DB 2 Meters and Records ACCESS Recording • Daily task, from 08:00 AM to 06:00 PM • Based on an average working day • From ? – DB : _actsummary, … – Tables : crud (_tablestat) 18 How to manage… 10,000 Progress DB 2 Meters and Records Using the « DBANALYS » • Severals DBANALYS each night • At least, one analys a week for each database 19 How to manage… 10,000 Progress DB 2 Meters and Records 20 Using the « DBANALYS » - What are we looking at mainly: - Scatter Factor - Fragmentation - %util (index) ⇒ - Two ways of weighting results: - By records number - By access number « Disorganization value » for the whole DB, with an A/B/C evaluation from 0 (OK) to 9 (KO): ⇒ A : « Scatter Factor » ⇒ B : « Split Rec. » ⇒ C : « %Util » How to manage… 10,000 Progress DB 2 Meters and Records User counting • Every 30 minutes • Progress Task (Batch) 21 How to manage… 10,000 Progress DB 2 Meters and Records Analysis and Help for decision making We have now a lot of numbers and meters, but what could we do with it ? • Choosing which DB to D/L • Choosing which startup parameters to modify • Focusing on unusual DB access increase 22 How to manage… 10,000 Progress DB 2 Meters and Records 10,000 DB, I NEED HELP ! • Doing a « one shot task » for all DB is just impossible. • Increasing all startup parameters for 10,000 DB would be great… but we don’t have magic server, with infinite resources! 23 How to manage… 10,000 Progress DB 2 Meters and Records Decision Help Process 24 How to manage… 10,000 Progress DB 2 Meters and Records 25 « -B » Startup Parameter Finite resources : if we increase one, which one will we decrease ? How to manage… 10,000 Progress DB 2 Meters and Records About « -M » Family 26 How to manage… 10,000 Progress DB 2 Meters and Records Unusual Read Increase 27 How to manage… 10,000 Progress DB 2 Meters and Records Dumping & Loading : who’s next ? 28 How to manage… 10,000 Progress DB 2 Meters and Records 29 Dump & LOAD preparatory work • Record Mean Size • Table Access • Table Size -> Area -> Nb record blocks -> Nb extend How to manage… 10,000 Progress DB 2 Meters and Records Target DB Generation 30 How to manage… 10,000 Progress DB 2 Meters and Records Past is good, present is better… To know what is causing trouble at T-Time on a db-server, we need to have a global point of view over our system, with instant and precise information on each 31 Real-time analysis How to manage… 10,000 Progress DB 3 Real-time analysis Server perf analysis • Global view of a server activity 33 How to manage… 10,000 Progress DB 3 Real-time analysis Tables information Available information: • DB logical name • Table name • CRUDs (_tablestat) • index (_indexstat) activity 34 How to manage… 10,000 Progress DB 3 Real-time analysis Tables information VST _indexstat : Top index VST _UserTableStat : Top users 35 How to manage… 10,000 Progress DB 3 Real-time analysis Users information Performance meters: • _UserIO-DbAcces • _UserIO-Dbread • _LockReq-RecLock • _LockReq-TrnLock 36 How to manage… 10,000 Progress DB 3 Real-time analysis Users information _UsertableStat : Top Tables Connect Client Database Request Statement Caching information. 37 How to manage… 10,000 Progress DB 3 Real-time analysis Be careful… Some warning if you have to deal with this kind of configuration: • Gathering _usertablestats increases DB access • Activating the CDR Statement Caching could cause great IOs 38 Code optimization How to manage… 10,000 Progress DB 4 Code optimization We know where is the problem, but not why Two main kind of performance problem: • Too many access on a table • Slowdown effect in a function Tools at disposal: Request Analyzer « Compile Xref » On the Fly DB-Consolidated Profiler 40 How to manage… 10,000 Progress DB 4 Code optimization Request Analyzer • hQuery:INDEX-INFORMATION, • VST : _index, _index-field, _file, _field 41 How to manage… 10,000 Progress DB 4 Code optimization Source-Program Analyzer Spot the difference… 42 How to manage… 10,000 Progress DB 4 Code optimization XREF Database • Performance issues • Internal development rules nonconformity • CRUDs localization • Program stacking 43 How to manage… 10,000 Progress DB 4 Code optimization Access localization 44 How to manage… 10,000 Progress DB 4 Code optimization Where are the CRUDs ? 45 How to manage… 10,000 Progress DB 4 Code optimization Dev rules nonconformity 46 How to manage… 10,000 Progress DB 4 Code optimization Program stack 47 How to manage… 10,000 Progress DB 4 Code optimization PROFILER • Used to know where the time is spend • Easily activated and deactivated ASSIGN PROFILER:PROFILING = NO PROFILER:ENABLED = NO PROFILER:LISTINGS = YES PROFILER:COVERAGE = YES PROFILER:TRACE-FILTER = "" PROFILER:DIRECTORY = SESSION:TEMP-DIR PROFILER:FILE-NAME = SESSION:TEMP-DIR + "\profile.out" PROFILER:DESCRIPTION = "". ASSIGN PROFILER:ENABLED = yes /*No to disable*/ PROFILER:PROFILING = YES /*No to disable*/. 48 How to manage… 10,000 Progress DB 4 Code optimization Profiler: Time by procedure 49 How to manage… 10,000 Progress DB 4 Code optimization Profiler: Time spend by line of code • If no line details are available: « compile [PGM] Debug-List » 50 How to manage… 10,000 Progress DB 4 Code optimization Profiler: Some useful tips • What we have added – Request control – Program calling stack – Copy-Paste – Time measure comparison – No-lock option (former Ttable) – New index added on Profiler DB • Watch out for the first memory loading (Disk Read) 51 How to manage… 10,000 Progress DB 4 Code optimization « Sniffer »: Another way to use statement caching 52 How to manage… 10,000 Progress DB 4 Code optimization Other « small » stuff • « -n » parameter – The smallest value as possible but… – … evolution possible • Automatic extend addition – « Enable Large File » forbidden in our cloud! 53 How to manage… 10,000 Progress DB 4 Code optimization Other « small » stuff • DB logs file: « .lg » – Automatic « prolog » – Error recording (Dedicated Progress DB) 54 Conclusion How to manage… 10,000 Progress DB 5 Conclusion PROGRESS doesn’t provide any tools nor advices in order to manage such a big amount of DB in the same environment. That’s why we had to imagine, design and develop these management and optimization programs. 56 How to manage… 10,000 Progress DB Conclusion 5 57 Some figures Cloud Environment • • • • More than 12 000 DB More than 300 hosted customers 20 DB-Server (IBM AIX) About 150 000 source files – Standard versioning – Customed source code for specific needs DB tasks • Perf issues • Optimization courses • DB Param tuning • D&L • DB Tools development • version upgrades Proginov • • • • • Employees: 163 Developers: 65 Hosting Team: 13 DB Managment: 4 Performances: 1 (Patrice!) How to manage… 10,000 Progress DB 5 Conclusion 58 To sum things up Looking backward… • • • • • Access Disk reads DBAnalys Nb User XREF … to build the future • Better performances for our customers • Greater reactivity and faster DB Management • And the most important: limiting any risk -> mistakes could be irrecoverable in a shared environment