YOUR machine and MY database - a performing relationship!?

50 %
50 %
Information about YOUR machine and MY database - a performing relationship!?

Published on January 29, 2016

Author: MartinKlier

Source: slideshare.net

1. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Martin Klier Senior / Lead DBA Klug GmbH integrierte Systeme Las Vegas, April 10th, 2014 YOUR machine and MY database - a performing relationship!? (#141)

2. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? •Introduction •NUMA + Huge Pages •Disk IO •Concurrency •Engineers to work together Agenda

3. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? •Martin Klier (twitter: @MartinKlierDBA) •Lead DBA for Oracle at Klug-IS •Focus on Performance, Tuning and High Availability •Linux since 1997, Oracle since 2003 •Email: martin.klier@klug-is.de •Weblog: http://www.usn-it.de Speaker

4. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? •Klug GmbH integrierte Systeme (http://www.klug-is.de) 92552 Teunz, GERMANY •Specialist leading in complex intralogistical solutions •Planning and design of automated intralogistics systems, focus on software and system control / PLC •>300 successful major projects in Europe, America, Asia

5. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? iWACS®

6. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? •DOAG - Deutsche Oracle Anwendergruppe (German Oracle User's Group) •Biggest Oracle Technology Conference in Europe (2,000+ attendees) •Save the date: Nuremberg, November 18th - 21st 2014

7. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Server / CPU

8. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA Core(s) +SharedCache, LLC Core(s) +SharedCache, LLC QPI-C Core(s) +SharedCache, LLC Core(s) +SharedCache, LLC PCIePCIe IMC IMCQPI-C IMCQPI-CQPI-CIMC Non Unified Memory Access

9. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA _enable_NUMA_support = TRUE MOS Doc ID 864633.1 •Multiple Buffer Caches •Striped pools => cross context :(( => pool access :(

10. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA 26 GB

11. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA 13GB+13GB=26 GB One buffer cache for each node

12. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA •Partitioned access •Can be up to 40% faster •But....

13. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA Non-NUMA with NUMA With my workload and only one listener: Saved <1 page alloc miss per second ?

14. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA 26 GB Fits into RAM of one node. OS NUMA optimization at work. So WHY?

15. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA System Admin DBA Developer Relevance-and-care chart

16. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? NUMA Suggestions NUMA •Useful in big environments only (think: DB consolidation) •Make friends with the system admin, have a joint opinion •Test thoroughly and quantify use vs. effort (think: bugs)

17. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Server / RAM

18. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? RAM Shared Memory Segment Small OS Pages PMON etc. Server OS Kernel managed Access (permission check) New Process ➔ Grant permission ➔ Integrate in serialization structures Per page

19. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? RAM Problems •Memory Fragmentation •Wasting CPU with page alloc •OS_THREAD_STARTUP waits

20. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Shared Memory Segment OS Page New Process Large / Huge OS Pages FAST startup PMON Server FAST access Huge Pages

21. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Alert Log (17408-3164)*2048kB=28GB Huge Pages

22. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? System Admin DBA Developer Relevance-and-care chart Huge Pages

23. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Suggestions Large/Huge Pages •Useful with SGA >=16GB •Use largest available & sane page size •Talk your sysadmin into DOing IT •Combine with PRE_PAGE_SGA=TRUE Huge PagesHuge Pages

24. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Storage / SSD

25. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD Words Bits “Cell” Block

26. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD

27. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD 16kB – 512kB pro Block 1. erase

28. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD 16kB – 512kB pro Block 2. write

29. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD Types and Figures from 2009 - But the terrors are still intact. :) 120:60 = *2 85:60 = *1.4

30. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? SSD 8171 IOPS like 60 HDDs 8k/16k blocks 80% write 20% read Samsung SSD 840 PRO 8k/16k blocks8k/16k blocks

31. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? System Admin DBA Developer Relevance-and-care chart SSD

32. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Suggestions •Know your IO load profile (AWR, nmon) •Use enterprise-level devices w/ Single Level Cell (SLC) •SSDs require different lifecycle handling in doubt, consider an array of HDDs of same IO power SSD

33. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Concurrency means collisions and serialization

34. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Occurrence Concurrency •Data Access (Row Lock, Block Header) •Shared memory organization (Buffer / Library Cache etc.) •CPU queueing •Disk / Network IO ..........

35. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Concurrency 5 5 6 ? 7 ? X State B Protected or limited resource Delayed State A

36. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Row Lock

37. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Block/Buffer Header Row Space Free Space =============== =============== =============== =============== =============== =============== =============== =============== ITL entry Row

38. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Row Lock =============== =============== =============== =============== =============== =============== =============== =============== Session 1 Session 2 Incompatible Lock Attempt Spin ITL entry Row Lock and Access enq: TX - row lock contention

39. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Spinning means Concurrency •Active checking of a value in memory •“Wasting” CPU for non-productive work •Oracle Spin Count limits and Wait Events are a generosity to limit, see and measure the impact

40. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? ITL Stress =============== =============== =============== =============== =============== =============== =============== =============== Resizing ● Limited Space ● Concurrent Buffer modif. spin! one does it other one(s) buffer busy wait enq: TX - allocate ITL entry

41. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Mutex Library Cache Object Session 1 Session 2 Sleep = Wait S2 spinning on M. M S1 holding Mutex Same for Latches, but a bit uglier. Mutex Contention cursor: mutex S cursor: mutex X ...

42. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? CBC CBC 4F BH 1 BH 77 CBC 51 BH 99 BH 32 Chains Buffer Headers (references in Shared Pool) Latches Cache Buffer Chains: Is this block in the BC?

43. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? CBC CBC 4F BH 1 BH 77 Session 1 Session 2 Same or diff. Buffer (Chain), same latch :( Spin Locks the chain and looks for a buffer CBC 51 BH 99 BH 32 latch: cache buffer chain

44. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? System Admin DBA Developer Relevance-and-care chart Conc. Wait

45. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Suggestions •Check workload (think: SQL efficiency) => Reduce logical reads/writes •Be ready for decent diagnosis (think in Wait Events) Concurrency

46. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Collaborate It's all about humans working together

47. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Layers Application Network Database Server Operating System SAN Storage

48. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Engineers to work together People

49. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? System Admin DBA Developer Relevance-and-care chart All is slow

50. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? System Admin DBA Developer Relevance-and-care chart It works

51. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? TEAM Make sure you have enough parallel beer!

52. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Thank you very much for your attention! Martin Klier Senior / Lead DBA Klug GmbH integrierte Systeme Las Vegas, April 26th, 2012

53. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? More resources on this topic • Kevin Closson, on NUMA and Huge Pages https://kevinclosson.wordpress.com/2010/03/18/you-buy-a-numa-system-oracle-says-disable-numa-what-gives-part-i/ http://kevinclosson.wordpress.com/2010/09/28/configuring-linux-hugepages-for-oracle-database-is-just-too-difficult-part-i/ • Craig Shallahamer, on Cache Buffer Chain visualization http://shallahamer-orapub.blogspot.de/2010/09/buffer-cache-visualization-and-tool.html • Arup Nanda, on ITL / Locks http://arup.blogspot.de/2011/01/more-on-interested-transaction-lists.html • Andrey Nikolaev on Mutexes “Exploring mutexes, the Oracle RDBMS retrial spinlocks” • Ronan Bourlier & Loïc Fura, IBM “Oracle DB and AIX Best Practices for Performance & Tuning” • My Oracle Support Doc ID 864633.1 “Enable Oracle NUMA support with Oracle Server Version 11gR2” Doc ID 1392497.1 “USE_LARGE_PAGES To Enable HugePages” Doc ID 361468.1 “HugePages on Oracle Linux 64-bit” Read on...

54. @MartinKlierDBA – YOUR machine and MY database - a performing relationship? Thank you Many people have helped with suggestions, critics or taking daily work off me during preparation and travel phase. Guys, you are top! Special thanks to: My boss and company, for endorsement My team, for digging out the interesting stuff

Add a comment

Related pages

YOUR machine and MY database - a performing relationship ...

@MartinKlierDBA – YOUR machine and MY database - a performing relationship? •Introduction •NUMA + Huge Pages •Disk IO •Concurrency •Engineers ...
Read more

YOUR MACHINE AND MY DATABASE A RELATIONSHIP!? I

Database – “Oracle Internals & Performance” YOUR MACHINE AND MY DATABASE - A PERFORMING RELATIONSHIP!? Martin Klier, Senior / Lead DBA at Klug GmbH ...
Read more

YOUR machine and MY database - - performing databases ...

YOUR machine and MY database - a performing relationship!? Martin Klier Performing Databases GmbH Mitterteich
Read more

Learn the structure of an Access database - Access

Learn the structure of an Access database. ... If your database isn't ... The Relationships object tab appears and shows you the relationships between all ...
Read more

Data: The Information Behind Your Application

... and you can define relationships between data ... you can connect to an existing data source such as a SQL Server database, or you ... Performing Data ...
Read more

Database Management - Oracle Help Center

Database management involves ... Data masking determines the database foreign key relationships and adds foreign ... if you performing cloning outside ...
Read more

sentinelmachinesafety.com | Verification and Risk Analysis ...

Performing a formal risk assessment and ... Machine Safety Risk Assessment for Your ... How does Sentinel help my organization achieve Due ...
Read more

SQL for Beginners: Part 3 - Database Relationships ...

There are several types of database relationships. ... to many" relationship, you would need an ... Relationships. My current favorite ...
Read more

Microsoft Access Performance Increase the Speed of Your ...

Microsoft Access Performance Tips to Speed up ... you can create a relationship between ... After all, your database does not need to be in a ...
Read more

Virtual Machine Manager FAQ

This document provides answers to frequently asked questions about System Center Virtual Machine ... my virtual machines ... database objects that you ...
Read more