Transcript Optimize Oracle on VMware

Optimize Oracle on VMware
Tips for Optimal Virtualized Oracle Databases
PHLOUG
Philadelphia, PA
June 16th
© 2008 Quest Software, Inc. ALL RIGHTS RESERVED.
Bert Scalzo …
Database Expert & Product Architect for Quest Software
Oracle Background:
•
Worked with Oracle databases for over two decades (starting with version 4)
•
Work history includes time at both “Oracle Education” and “Oracle Consulting”
Academic Background:
•
Several Oracle Masters certifications
•
BS, MS and PhD in Computer Science
•
MBA (general business)
•
Several insurance industry designations
Key Interests:
•
Data Modeling
•
Database Benchmarking
•
Database Tuning & Optimization
•
"Star Schema" Data Warehouses
•
Oracle on Linux – and specifically: RAC on Linux
Articles for:
•
Oracle’s Technology Network (OTN)
•
Oracle Magazine,
•
Oracle Informant
•
PC Week (eWeek)
Articles for:
•
Dell Power Solutions
Magazine
•
The Linux Journal
•
www.linux.com
•
www.orafaq.com
2
Books by Bert …
Coming in 2009 …
3
Couple of Questions…
• How many people are doing Oracle on
– VMware Server (freeware)
– VMware Workstation 6.x
– VMware ESXi (freeware)
– VMware Infrastructure 3
– Other Virtual Platforms
– Virtual Box
– Hyper Visor
– Zen Server
– Oracle VM Server
– Sun xVM
Agenda
• Discuss the Virtualization Trend
• Challenge: Shared Resources
• Used Quest Tools to Simplify & Maximize Results
– Foglight for VMware
– Foglight Database Performance Analysis
– Benchmark Factory for Databases
• 440% Database Improvement on Same Hardware!
• How is that possible – What Black Magic was used?
• Result = Easy Best Practices for Oracle on VMware
The Trend is Clear …
• Virtualization is proliferating for many good reasons
• Hardware new purchase costs continue to decrease
• Hardware overall performance continues to increase
• Database Administrators have been hesitant to date
• But minimal virtualization overhead costs are quickly
becoming much less of an issue, even for databases
• In the near future, databases too will be the norm …
Fundamental Challenge
Understanding the impact of resource sharing
CPU
Network
Multi-Core Processors
Shared Connectivity
Load Balancing
Redundancy
Memory
Sharing
Over-commitment
Core 4
Resources
Disk
Shared storage
Fiber, iSCSI, NAS
Nothing New Here …
• DBA’s have always cared deeply about database
server CPU, memory, disk and network utilization
• But often in the past databases were on dedicated
hardware – so databases were more “solo islands”
• Now we must account for shared everything – and
not just disk space as with SAN/NAS/iSCSi storage
servers
• We might even have different database platforms
sharing our resources (e.g. Microsoft SQL Server)
Some Tools I used …
•
Not pitching or selling anything here – just disclosure
•
There are lot’s of good tools out there - pick whatever
•
In brief – here’s what I used and why (its purpose):
–
–
–
Load Generator = Benchmark Factory for Databases
Virtualization Monitor = Foglight for VMware
Database Performance = Foglight Database
Performance Analysis
–
Database Ad-Hoc Diagnostics = Spotlight for Oracle
Foglight for VMware
Monitor
Shared
Resources
Find & Fix
Genuine VM
Performance
Issues
Help to
Squeeze
Maximum
“Overall”
Throughput
Foglight for Database Analysis
Monitor
Database
Resources
Find & Fix
Genuine DB
Performance
Issues
Help to
Squeeze
Maximum
“Database”
Throughput
Benchmark Factory for Databases
Generate
Database
Workload
Stress the
Database
Introduce
Performance
Issues
Help to
Expose
Potential
Candidates
Each step above is an individual test for my
proposed best practices – and their results
440% Improvement for Proper Setup
1080 ms
Average
Response
Time
200 ms
Average
Response
Time
The Top Ten Tricks Applied …
1. Obtain a baseline test for relative comparisons
2. On the VM host, exclude VM clients from active, online virus scans
3. Remove Windows Indexing Service (because really don’t need fast
file system searches for an Oracle database)
4. Remove other extraneous Windows services (there are lots)
5. Change the VM host registry settings to improve file system IO for
databases
6. Optimize the VM host configuration and options
7. Optimize the VM client OS configuration and options for Oracle
database
8. Remove other extraneous VM client OS services and daemons
9. Change VM client file system settings to improve IO performance
for databases
10. Adjust VM client file system block size to more closely match
Oracle block size
Optimize All File Systems for Database …
From ten overall steps – focus on these two:
• #5 = Change the VM host registry settings to improve
file system IO for databases
• #9 = Change VM client file system settings to improve
IO performance for databases
For Windows, simply adjust the following registry entry:
• HKEY_LOCAL_MACHINE\System\CurrentControlSet\
Control\FileSystem\NtfsDisableLastAccessUpdate = 1
Since we know that Oracle “touches” data files every three
seconds or more – why track the “date/timestamp” of
when each file was lasted touched – we don’t need it
Can do this from some PC tools …
Let’s not Forget My Favorite - Linux …
We can set the arrtibute for the individual Oracle files:
chattr +A file_name
Or we can do it for an entire directory:
chattr –R +A directory_name
However the best method (because it automatically
handles any file additions) is to edit the /etc/fstab and
add the NOATIME attribute:
/dev/sda6
/dev/sda1
/dev/cdrom
/dev/sda5
/
/boot
/mnt/cdrom
swap
ext3
ext2
iso9660
swap
defaults,noatime
defaults,noatime
noauto,owner,ro
defaults
1
1
0
0
1
2
0
0
Match File System & Database Block Size …
From ten overall steps – focus on this one:
 #10 = Adjust VM client file system block size to more
closely match Oracle block size
Assume we have a Linux host running a Linux based Oracle database client.
Let’s examine Scenario #2 from the chart next page.
Start by assuming that the host file system using the default block size: 2K.
Further assume that the client file system also using the default block size: 2K.
But that the Oracle DBA sets the DB block size to 4K – so we have a mismatch.
Thus each Oracle physical IO requests asks the client OS for two IO’s,
and each client IO asks the host for one IO. That’s a total of four logical
IO requests (although only two physical IO’s in reality – but note that
there’s overhead for each logical IO request, so larger numbers are worse)
Services, Services, Services …
From ten overall steps – focus on this one:
 #4 = Remove other extraneous Windows services (there
are lots)
 #8 = Remove other extraneous VM client OS services and
daemons



This step is very, very subjective – do so with great care
Your situation & needs will dictate what can be turned off
No two scenarios will be 100% alike – so mileage will vary

Therefore take my next two slides with a “grain of salt” ….
Lessons Learned (i.e. Best Practices)
• Tons of “low hanging fruit” (i.e. easy no-brainer stuff)
• Optimize each of the four major VM platform stacks
– Optimize the Host Machine (BIOS too)
– Optimize the Host VMware Setup
– Optimize the Guest VM Configuration
– Optimize the Guest Operating System
• Remember: 440% improvement for practically free 
Questions and Answers …
Thank You
Presenter:
Bert Scalzo: [email protected]
Note: these slides should be available on OOUG web site, but
we’ll also make sure to post them on our company’s web site:
www.toadworld.com/Experts/BertScalzosToadFanaticism/tabid/318/Default.aspx