1 / 35

David Scott, CSG International GOusers, March 9, 2012

Scalability: Super-Heroes and Super-Villains. David Scott, CSG International GOusers, March 9, 2012. Speaker Qualifications. Principal Consultant, CSG International Currently works with high-volume, RAC projects Metrics and analytics Data Warehousing

channery
Download Presentation

David Scott, CSG International GOusers, March 9, 2012

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Scalability: Super-Heroes and Super-Villains David Scott, CSG International GOusers, March 9, 2012

  2. Speaker Qualifications • Principal Consultant, CSG International • Currently works with high-volume, RAC projects • Metrics and analytics • Data Warehousing • Past president of GOusers (Georgia Oracle Users Group) • Has presented at OOW, IOUG, GOuser, and other conferences • Oracle DBA/developer since 1992

  3. ABSTRACT • Oracle databases can scale to handle tremendous volumes of data, but some effort is involved to avoid the dastardly villains that would sabotage your success. This presentation will help DBAs avoid the villainous techniques and pitfalls in code and schema designs, as well as summon the super-heroes that can assist you in creating super-scaled data applications.

  4. Objectives • Identify the super-heroes of scalability - techniques and design choices that allow for system growth and performance improvement. • Identify the super-villains of scalability - the typical design and coding mistakes that inhibit efficiency and high-speed performance. • Identify opportunities within the system development lifecycle for improving scalability.

  5. Scalability • What is scalability? • Techniques and design choices that allow for system growth and performance improvement. • Why is scalability essential? • User expansion • The drive for lower TCO • 24*7*365 and other SLAs • SoA and proliferation of new uses

  6. Scalability Targets • Number of users • Number of records/rows • Number of queries/transactions per … • Process time windows • Define your targets carefully!

  7. Tuning and Scaling are Related • A well-tuned system is a candidate for scaling (and the converse is also true) • Low waits • Efficient use of db structures • Balance I/O for bandwidth and time • Tuning tools help identify scalability inhibitors • Wait events • Profilers • Monitors

  8. The Super-Heroes of Scalability

  9. The Flash • In general, the faster something is, the more scalable it is. • Multiple fast CPUs • High-speed I/O and networks • Tuning and proper configuration • Tight, optimized code • Profiled operations • RAMsan and other specialized hardware

  10. The Incredible Hulk • The more capable the system, the more scalable • AKA “The Hardware Option” • Exadata and other “Exa-stuff” • See http://www.oracle.com/us/products/engineered-systems/ • I/O capacity • Network bandwidth • GBs of RAM

  11. Carnac The Magnificent • If you can answer the question before it is asked, you can optimize your service levels. • Planning & Design • Materialized Views • Cubes and summary tables • Optimizer statistics

  12. The Invisible Man • Processes and operations that impact users are often identified as non-scalable • Offload processes to other systems • Leverage background processing • Optimize batch windows

  13. An Army of Ants • Small tasks are more scalable than large ones • How do you boil the ocean? • Partitioning • Parallel Query • ‘Divide and conquer’ designs • Exadata, Hadoop • Be careful of communications overhead

  14. Weapons in the War • DESIGN! • SAN bandwidth • Partitioning • Parallel Query • Bitmap Join Index • PDML • Materialized Views • Powerful hardware • ATEP • IOTs • NOLOGGING • Temporary Tables • Registration Tables • Direct Path • Compression • Statistics Find the ‘2nd right answer’

  15. More Firepower… • Grid Control, tracing, and tkprof: absolutely essential • Direct insert into table with different partitioning scheme • Derive summaries from staging table • Bind variables – if appropriate • “Choreography” for data and processes • DBMS_STATS.SET_TABLE_STATS • Configuration & memory management

  16. The Super-Villains of Scalability

  17. Mr. Magoo: Unreasonable Assumptions and Vision • Extrapolating results from smaller tests • Lack of experience with larger platform • Short testing windows • Inadequate staffing • The longevity of the temporary • Previous limitations still exist? • Previous methods still the best? • “Good enough for now…”

  18. Mr. Magoo, page 2 • “Just add ____; everything will be fine”. • Disk / RAM / CPU • Job threads / Parallel Query • Exadata / RAC / The latest Oracle version • Magic pixie dust • Inadequate/Over-adequate SW Architecture • I/O saturation masks other bottlenecks • Creating test data • Extrapolation rarely, if ever, matches reality

  19. The light at the end of the tunnel…is an oncoming train. • “Just run it in parallel…” • PDML, INSERTS, indexing, summaries • CPU usage • The “Sears Tower” syndrome • All features/processes must scale • You don’t know what you don’t know! • Unless you’ve “been there, done that”

  20. Various Coding Villains • Dr. Deadlock • Concurrency: blocking and locking • Lex Latcher • Large collisions on small objects • Sequences, headers • Writing to the same file from different processes • Captain Commit • Inside a loop, no less • And a host of others…

  21. Why the Code Doesn’t Scale • Inadvertent blocking and locking • Ignores new code & database features • Can you rewrite the SQL? • Trigger happy? • Inefficient SQL & PL/SQL • Reliance on hints, bad hints • Ill-conceived scalar sub-queries • Function calls in SQL • Query generators: Beware! • Misuse of BULK operations • ‘Hacking’ the dictionary?

  22. Why the DB Doesn’t Scale • Unnecessary I/O • Non-essential constraints • Data movement • Poor design, really awful design… • Incorrect indexing: Bitmaps & B-trees • Inadequate configuration • How robust is your tuning? Hints?

  23. Example: UPDATE Millions of Rows • Just say “NO” • Enough UNDO? ORA-1555? NOLOGGING? • If millions, updates are usually a status or other limited domain value • INSERT pk of original into status registration table • Partitioning, subpartitioning can help • Use GTT tables • Read asktom.oracle.com for LOTS more.

  24. Opportunities in the Software Life Cycle

  25. First Steps – Preparing to Scale • Re-read the original requirements • Identify current efficiencies & problems • Characterize your workload • Find the ‘low hanging fruit’ • Identify the main challenges • Start with a well-operating implementation • Establish milestones & interim targets • For processes and data, not just the project

  26. Basic Architecture • Hardware choices • Specialized vs. commodity • Drive / SAN choice • Model IOPS requirements • RAM • Software choices • Build vs Buy • Network choices • Model throughput

  27. Service Level Agreements • Data volume • Query response times • Batch performance • Backup and disaster recovery requirements • … and many others • These often define and interpret scalability within an organization

  28. Architectural Options for Database • Platform changes • Hadoop, NoSQL • “The Cloud” • Oracle and non-Oracle • RAC • Oracle “Exa-stuff” • Exadata, Exalytics • Leveraging your existing investment

  29. Development Opportunities • Can your coding team scale??? • Inventory your human resources • Think ahead • Code for scalability • Avoid maintenance and performance issues • Be consistent • Coding and naming standards • Multi-site issues • Sequences and site identifiers

  30. Testing for Scalability • Complexity at scale • Users • Data Volume • Is it possible to generate adequate test data? • Testing to a goal, testing to destruction

  31. Operations Opportunities • Process choreography • Do not start all batch processes at 0000 hours! • Think in terms of hourly tasks • Does ‘control and command’ have inherent conflict?

  32. Maintenance Opportunities • Re-factoring • What about bugs? • Mitigate these via Oracle Support and other resources • Architect to avoid the problems • Each system will have different challenges • Inevitable re-architecting of supporting systems

  33. Re-Architecture 101 • Investigate: Profile code & process architecture • Is the old way still the best way? • What is the anticipated workload? • Evaluate: Choose proper targets for rework • Find bottlenecks and blind spots, current and future • Create: Find the “second right answer” • Do your homework, and be creative! • “Experiment-ate”: Test to destruction • Or at least, to the allocated time window • Does it perform/scale?

  34. Questions?

  35. Thank You for participating! • Please complete your evaluation form… • David ScottScalability: Super-heroes and Super-villains • To Contact, please email: David.Scott@csgi.com

More Related