In this blog (the third in a series on virtualizing Oracle), I will describe the best practices that EMC IT developed as we virtualized our most mission critical and highly transactional databases. You can find the earlier blogs here: [Running Oracle on Virtual Infrastructure Really Pays Off, Best Practices for Virtualizing Your Oracle Database]
There are two trains of thought when you talk to people about virtualization. From the infrastructure point of view, it is all about getting more efficiency out of the physical infrastructure layer. On one hand you can try to go extreme with this approach, but it will come at the expense of incurring higher administrative costs required to constantly troubleshoot performance and functionality issues. The other point of view is mainly about reserving all of the resources of the underlying servers, just in case the application needs it. Fortunately, with VMware vSphere you can have both, by using a more balanced approach.
I promised, in my earlier posts, that I would publish the secret sauce to achieving both great performance and high efficiency when virtualizing Oracle databases – so here it is. I have broken it up into four categories: memory, networking, CPU and storage (vSphere datastores). I will actually save the datastore best practices for the next and last post in this series, due to their complexity.
I have attempted to explain the settings and the reasons why they work as non-technically as possible but they are, by nature, quite technical. The most important point of this blog is to give you the recommendations; the explanations are primarily for the curious. So if the reasons don’t make sense or don’t matter to you, no need to worry. Treat adhering to these specifications like putting oil in your car— just put the recommended amount in and don’t be concerned about what would happen if you put too much or too little in.
Memory—NUMA and the Oracle Database
Oracle databases, up to and including 11g, have tried to make use of local access to memory on systems that support Non-Uniform Memory Access, or NUMA. NUMA is the technology of managing memory, equally divided, locally by each socket. The implementations, however, were not efficient and almost always resulted in performance degradation and not improvement. In Linux, the default implementation does not properly link into the NUMA shared library, so the NUMA “optimizations” are not actually working. In testing in the lab it was confirmed that enabling NUMA caused performance to either get worse or at best stay the same. To be sure that Oracle NUMA is not used, it should be explicitly disabled with
_enable_NUMA_optimization=FALSE and _db_block_numa=1.
- Disable NUMA in DB–Poor Implementation
- Set vSockets=1– Try to keep Database VMs on the fewest sockets–Improve NUMA.
- Use preferHT–VM prefers local hyper threads on same core when vCPUs exceed the number of cores in the socket.
Virtual memory, within vSphere, is only given to the VM when it is actually used. This is a very good feature as it allows the unused memory to be allocated to other VMs that actually need it.
VMware vSphere 5.x supports large pages, which is very good for performance since larger pages give you larger blocks of memory to manage rather than smaller blocks. However, vSphere creates hashed addresses in those large pages for small pages. This is done to prepare for utilizing Transparent Page Sharing, in the event that the cluster comes under memory pressure. For most applications this is fine and causes no performance penalty, but for Oracle, as well as most other databases and JVMs, it adds additional overhead to the memory access. This is due to the way these applications access memory.
Instead of mapping where a particular segment of memory exists, they do a linear search when looking up something in their cache. This causes the search of memory to walk the 4k metadata chunks, instead of the 2M chunk. With memory reservations set, on the VM, the metadata about what is in that memory is maintained at a large page size (2 MB). The overhead, for non-reserved memory, consistently measures around 15% additional latency, for applications that do linear memory searches, like databases and JVMs.
- Utilize Memory Reservation–Size of the SGA + 2 times Aggregate PGA Target + 500MB for the OS (assuming some flavor of Linux.
- These should be for Production Clusters only, as Development and Test databases do not usually require peak performance.
Network – Dynamic Coalescing (RAC)
Dynamic coalescing is a feature where vSphere will wait a very short time before forwarding a network packet to its destination in an attempt to improve network utilization. Normally this is a good feature as this reduces network traffic, which can reduce latencies. With Oracle RAC, this is not a good thing. RAC databases are especially sensitive to interconnect latencies and the network is generally segregated and does not benefit from the coalescing. In this case, for the interconnect network card, this feature should be disabled.
- Set EthernetX.coalesceScheme = ‘disabled’ in the .vmx file.
Network – Latency
To reduce the network latency to physical server levels, increase the Interrupt rate on Network Adapter within the VM network adapter settings. This will significantly increase the number of times the network card is polled to see if there is work to do. There is a slight increase in CPU consumption, but the performance improvement is worth the overhead.
- Set the Interrupt rate on Network Adapter to 30K
The Oracle database is a multi-threaded application. Each process or query that runs in the database runs on its own process space, or thread. Many people will look at the amount of CPU that is being consumed by the database and use that measurement to determine the number of vCPUs to set for the database VM. This does not take into account that the database threads require very low latency, since most database queries return very quickly and there are actually many more actual statements that are fired against the database. It is more important to ensure that the OS has a CPU that is ready to perform the work, so there is no delay in finding a CPU to execute on.
- Set the number of vCPUs to the number of active foreground sessions in the database.
Stay tuned for my next blog detailing Best practices for virtualizing your Oracle database – Datastores.