VMWare performance for Oracle at Oracle User Group Scotland Conference 2013
Main highlight Oracle User Group Scotland Conference 2013 was a presentation by Dean Richards of Confio Software on VMWare performance for Oracle.
Confio’s servers were at 1-5% CPU, so underutilised, but databases use memory rather than CPU.
They virtualised 50 physical servers > 1 esx host – was at 40-50% CPU.
Later moved to two esx hosts to reduce CPU load.
Databases are just another application, but are the most resource demanding = planning required.
For licensing, often set up a dedicated cluster farm for oracle, save on per-core license fees. Same with Microsoft SQL Server.
vMotion automatically moves around physical boxes. Happens live, no loss of service, but takes around 30 seconds which appears to the user as a pause, although their SQL queries keep going as if nothing happened once has come up fully at the other side. To avoid that, often pin oracle servers so they won’t flip over.
Purchase option High Availability, automated restart VMs after physical esx host reboots.
Purchase option Fault Tolerance, mirrored copy VM takes over with no downtime.
vSphere windows GUI download to PC lets you see metrics. Just need a read only account on VM to use this, don’t need admin powers.
To use, go to http://VMmachineName and click on the ‘download’ link there.
VM Memory
Configured – max allowed allocation of RAM (starts of at zero unless ‘reservation’ is set, in which case starts off at ‘reservation’)
Reservation – guaranteed amount of RAM (default 0)
Limit – never used in practice – maximum RAM (default unlimited), overrides ‘Configured’.
Shares – priority given to this VM of getting RAM, higher the value, higher your priority.
Ballooning – BAD – takes back RAM to give to another VM
Swapping – VERY VERY BAD – crisis ballooning, not-nice grab of memory and swap to disk = bad performance, system hang, RAC node eviction.
Shared Memory – GOOD – VMs share identical memory. E.g. 20 VMs all with Oracle 11.2.0.7 databases binaries and RHEL 5.8 OS will share the binaries and OS in RAM.
Compression – VERY BAD – compresses memory, better than swapping but not as good as ballooning for performance.
Memory Tips:
1) VMWare tools control ballooning and so must be at most up to date version to avoid swapping.
2) Set memory reservation >= database SGA + leave room for OS, PGA, etc.
3) Be careful about overcommitting in production. Don’t give out more memory than is physically there.
4) Large pages are supported ok on VMWare.
Primary Metrics for VM memory:
Swapping – never want to see this.
Ballooning – little bit is ok, otherwise bad.
Memory utilisation – not as relevant as swapping and ballooning, but possible warning state for future growth capacity.
Fix with more physical RAM or reduce ‘reservation’s.
VM CPU
Similar to memory, has:
Configured
Reservation
Limit
Shares
Databases are not CPU bound, so start with 1 or 2 CPUs and increase later.
Metric for VM CPU
VM Ready Time
If > 10-20% then BAD
Fix is to either buy more CPUs or allocate more CPU to a particular VM, depending on whether host CPU is high or not.
VM Storage
Thin Provision Data Store = NOT for databases, because that allocates storage as required = slow.
Other presentations I attended were:
2) Julian Dyke on Sorts
Get trace file name and OS pid:
select value from v$diag_info where name = ‘Default Trace File’ ;
10046:
exec dbms_monitor.session_trace_enable/disable
SQL ID and previous SQL ID:
select sql_id, prev_sql_id from v$session where sid = SID ;
Linux trace process
strace -p PID 2> strace.txt
3) Heli Helskyaho on Data Modeller
Oracle SQL Developer Data Modeller free download included in Oracle SQL Developer or download stand alone
Is the new Designer – allows reverse engineer
4) Jonathan Lewis on Compression
allthingsoracle.com = has Jonathan Lewis articles
Basic Table compression = for read only tables/partitions
Table compression for OLTP = extra cost option, useless – not worth paying for or using.
Index compression = good, for OLTP as well as read only.
SQL> compress for all operations ; — 11gR1
create index t1_i1 on t1 (
owner –col1
, object_type –col2
, object_name
, subobject_name
) compress 2 ; — because first two columns are repetitive, contain duplicates
rebuild index t1_i1 ; — needed to compare like for like new indexes
validate index t1_i1 ; — careful, locks table while it is running
select opt_cmpr_cont, opt_cmpr_pct_save from index_stats ; — shows best column number for compress and gives amount to be saved.
Index compression very good even for OLTP = compress indexes now.
Basic table compression = good for read only tables/partitions, never for OLTP.
OLTP table compression = poor and expensive, so never use.
5) Tony Hasler on the SQL Model Clause
Allows SQL to perform excel-like transformations
Excel = Model Nomenclature
Worksheets = Partitions
Rows+Columns = Dimensions
Formulas = Rules
Values = Measures
Partitions – cannot reference each other
Dimensions – excel has two (row-column), model has 1 or more.
Measure – Model can have one or more measures referenced by a dimension (equivalent to value pairs or triplets in a single cell)
Leave a Reply