Download
1 / 49
500 likes | 763 Vues
Database Administration Worst Practices. A Catalog of Wince Inducing Practices To Be Avoided For Progress OpenEdge , Or Indeed Any, Database. A Few Words About The Speaker. Tom Bascom , Roaming DBA & Progress User since 1987 President, DBAppraise , LLC Remote Database Management Service.
E N D
Database AdministrationWorst Practices A Catalog of Wince Inducing Practices To Be Avoided For Progress OpenEdge, Or Indeed Any, 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 OpenEdge. • tom@wss.com
We Won’t Need That!“Oops…” • Do not EVER destroy your production database. • Even if it is Damaged. • Never Trust a Backup. • Or a Dump & Load. • Or any other destructiveactivity.
We Won’t Need That!“Belt and braces…” • NEVER restore on top of your production database. • Always restore to a fresh location. • Always build a new database. • Always defer production db retirement until its replacement has been verified and put into use. It may be your last resort.
Mirror, Mirror“What Could Go Wrong?” • The firmware might fail. • Or the SAN could just fall through the floor… • rm –rf • And the ever popular:FOR EACH customer: DELETE customer. END. • Mirrors faithfully produce 2 copies of everything. • Even when you don’t want them too.
Mirror, Mirror“Think outside the box!” • All databases that have business value must have after-imaging enabled. • After-image logs must be continuously archived to a safe location. “Safe” usually means a different time-zone. • Ideally, after-image logs should be continuously rolled forward against a backup database to prove that both the backup and the ai mechanism are working.
Faith-Based Backups“Our backups are good, why worry?” • Backups aren’t just for last night. • Tapes degrade over time. • Backup technologies go obsolete quickly. • Old backups can be just as dangerous as no backups (think lawsuits and “e-discovery”, or tapes falling off trucks). • It used to fit on the tape… • The only known-good backup is onewhich has been restored and verified. • Or destroyed.
Faith-Based Backups“Trust. But Verify.” • Test for success, failure and lack of success. • Log everything. • Have a backup to your backup: • Backup to Disk with probkup. • Backup to Tape with OS tools. • Continuously backup and verify after-image logs. • Verify backups: • Restore your backup. • Roll forward after-image logs. • Test Restore & Recovery Procedures (at least) Annually. • Certify the destruction of old backups.
Great Expectations“Go ahead, it will work.” • It might also take 3 days to complete. • Or lock a billion or so records. • Or consume all of the machine’s resources. • Or break code. • Or break 3rd party systems… • Silver bullets are usually made of tin-foil.
Great Expectations“Curb your enthusiasm.” • Old DBAs are paranoid DBAs. • New DBAs tend to be fearless -- learning from someone else's experience can help instill some much needed paranoia. • Create a “sandbox” test environment that closely mimics the production system. • Require written plans, with a backout plan, and tested, repeatable scripts for everything. • Log everything. • Practice, practice, practice.
The Cart Before the Horse“It’s slow? Let’s go buy something!” • We’ve purchased some new hardware! • How should we configure Progress to run on it? • We have licenses for X. What do we do with them?
The Cart Before the Horse“Not so fast…” • Determine the best way to invest your money. • Consult with experts before you make expensive decisions… • Plan first, then spend your money.
RAID 5“It won’t be a problem…” • Great performance when there is no load. • And when there are no disk failures. • And if your database is roughly the same size as the SAN cache. • All of the RAM that you can use where it will do you the least amount of good. • All of the performance of a single disk with none of the cost savings.
How to Saturate a RAM Cache“Just Say No!” • fillTime = cacheSize / (requestRate – serviceRate) • Typical Production DB Example (4k db blocks): • 4GB / ( 200 io/sec – 800 io/sec ) = cache doesn’t fill! • Heavy Update Production DB Example: • 4GB / ( 1200 io/sec – 800 io/sec ) = 2621 seconds (RAID10) • 4GB / ( 1200 io/sec – 200 io/sec ) = 1049 seconds (RAID5) • Maintenance Example (online backup): • 4GB / ( 5000 io/sec – 3200 io/sec ) = 583 seconds (RAID10) • 4GB / ( 5000 io/sec – 200 io/sec ) = 218 seconds (RAID5)
Laissez-Fire DBA“The users will let us know when there is a problem”
Laissez-Fire DBA“Be prepared.” • Familiarize yourself with baseline performance so that you will recognize exceptions when they occur. • Collect historical statistics to facilitate both forward planning (trending) and forensic performance analysis. • Implement availability and performance monitoring systems so that issues are identified and resolved before they cause outages.
Kim’s Game“We’ll remember” • Many DBA activities are only rarely executed. • Under extreme pressure. • While working with hostile and uncooperative 3rd parties. And users. • At awkward times of the night. • By the backup DBA.
Kim’s Game“Put it in writing!” • Maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail. • Use collaboration tools (a Wiki) so that these documents are easily discovered, readily searchable in an emergency and living. • Enforce the discipline of documentation and check it periodically: • When was this object created, by whom, and with what script? • What tasks were performed on a particular day? • What tasks need to be performed on some schedule?
The Blame Game“It’s the developer’s fault that query is in production!”
The Blame Game“A DBA is part of a team!” • Cultivate a team attitude by structuring continuous DBA involvement in every project rather than just at project milestones. • Make developer and customer support a clear part of the job description linked to performance evaluations. • Make sure that developers and testers have access to a full size copy of the db. • Avoid post-release software issues by proactively working with developers and testers to ensure that all production software is stable and high-performance.
Techno-Bust“Upgrade? We don’t need no steenkin upgrade!” • Version 9 is: • Ancient. It was released in 1999. • Obsolete. Lacking Type 2 Areas, DateTime, Multi-Core support, Security, SAX, OO, .NET, Eclipse, Pro Datasets, LOBs, 64 bit goodness, Diagnostics and a few hundred other major enhancements. • Unsupported. 9.1E04 is the very last release of v9 ever. It is 5 years old. There will never be another update, bug fix or enhancement to v9.
Techno-Bust II“Upgrade? We don’t need no steenkin upgrade!” • Increased Risk: • Exposure to unfixable bugs. • Exposure to security breaches. • Decreased Productivity: • Foregone performance enhancements. • Unrealized improvements in functionality. • Unresponsive development. • Increased Cost: • Using labor to work around all of the above. • Eventual re-work when you finally do upgrade. • Major license costs if maintenance has lapsed.
Techno-Bust“An ounce of prevention is worth a pound of cure.” • Apply patches routinely. • Keep your licenses and maintenance up to date. • Escalate Vendor FUD. • Take advantage of new features when it is appropriate. • Don't be afraid to acquire the right technology.
Techno-Lust“There’s an App for that!” • Things would be so much betterif only we had the latest gizmo! • New isn’t always better. • Or cost-effective. • Specious complexity multipliestrouble in every direction. • Not all that long ago enormous enterprises were run on servers with the capacity of your BlackBerry.
Techno-Lust“Look before you leap…” • Don’t blindly upgrade your hardware infrastructure without first considering tuning opportunities. • Understand the ongoing maintenance commitment and costs of new systems and features before you put them into production.
Eye Candy“Hello Sailor!” • Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks: • Inhibits learning. • Hides risk. • Difficult to automate. • Enables irreversible damage by point-and-click. • False sense of security.
Eye Candy“It takes more than a pretty face.” • Good DBA tools help you to: • Alert & Inform • Log & Analyze • Automate • Manage • Graphics should support and reinforce data. • DBA Tools should be meaningful and insightful without being noisy – not just a metrics browser.
The Lone Ranger“I know what I’m doing and I don’t need any help!” • Database Administration is complex. Everyone needs a sanity check. • Even the most senior DBAs can'tpossibly know every last detail. • No single person can match theexpertise and experience ofeven a relatively small group. • And then there is that “bus”thing!
The Lone Ranger“Even the Lone Ranger had Tonto…” • Foster a culture where it is acceptable for DBAs to admit they don't know the answer and to ask for help. • Provide a safety net of tech resources such as outside experts and consultants on call. • Participate in PUGs, PSDN, PEG and ProgressTalk.
Hero Worship“Gus said it, it must be true!” • Gus, Dan, Adam et al are great but they are mortal • Context is everything. • Your hero probably isn’t workingon (or writing about) your system. • The situation may have changedconsiderably over time.
Hero Worship“Nobody is perfect.” • It is more important to understand the reasoning than it is to blindly implement a rule of thumb. • Take everything with a grain of salt – no matter the source. • Test, test, test...
