Speeding Inserts and Updates in Oracle Database by Caching Reads and Writes to VMware Host
Beefing up Buffer Cache size in Oracle Database alone may not solve all your slow database issues. If your Oracle Database runs in a VMware VM and it has performance issues due to heavy insert and/or update load, the root cause of which is slow storage, then our host side caching software for VMware, called VirtuCache, will be able to fix this problem. This post explains how VirtuCache does this by explaining the read and write storage IO paths in Oracle Database that factor into transaction times, with and without VirtuCache.
WRITE IO PATH IN ORACLE DATABASE (WITHOUT VIRTUCACHE):
Step 1. When inserts and/or updates are made to the Database, writes are done to both the data files and the redo log files. These writes are cached to the in memory buffer cache.
Step 2. When Oracle Database commits the transaction, the redo log buffers in VM memory are flushed to persistent storage. The transaction is not committed until the redo log buffers are written to persistent storage. So, slow backend storage will result in long transaction times (high write latency). A highly transactional application with many small transactions makes this problem worse.
Step 3. Thereafter the data file buffers are also flushed to persistent storage. Though the flushing of data pages to persistent storage doesn’t factor into transaction commit times, it is only the flushing of transaction log entries to persistent storage that does.
N.B.You can assign large amounts of RAM for Oracle Database buffer cache, however VM RAM assigned to the buffer cache doesn’t improve write performance since writes are committed in Oracle Database only when the transaction redo log for those writes is flushed to persistent storage from RAM.
Write IO Path in Oracle Database running in a VMware VM (without VirtuCache)
WRITE IO PATH WHEN VIRTUCACHE IS INSTALLED:
Step 1 above remains the same.
In Step 2, when transaction redo log entries are being flushed from VM RAM to persistent storage, VirtuCache will intercept these and write them to cache media (SSD or RAM) in the local host and another copy will be written to cache media in another ESXi host in the same ESXi cluster. VirtuCache mirrors writes to a second host to protect against data loss if the host were to fail. This policy in VirtuCache is called ‘Write-Back’ Caching. Once the redo log file writes are cached to in-host RAM / SSD by VirtuCache, VirtuCache will send a write commit to VMware, VMware then conveys the commit to the VM, and SQL Server commits the transaction. Note that the redo log has still not been written to the backend array. The fact that VirtuCache is caching writes to VMware host SSD or RAM, is seamless to the application, Oracle Database, VM, and VMware, and they all think that the writes are committed to backend array, when in fact the writes have not yet been committed to backend storage. Now VirtuCache does have a background job that runs every few seconds that flushes the writes from VirtuCache cache media to backend array, but Oracle Database has committed the transaction much before VirtuCache flushes the writes to backend array. So, the performance of your storage appliance and network doesn’t factor into Oracle Database transaction completion times if VirtuCache is installed on the ESXi hosts.
Step 3: Separately VirtuCache also intercepts flushing of data file buffers from RAM to persistent storage, and caches these to cache media in the ESXi host in exactly the same way that VirtuCache does for transaction redo log records, thus speeding up the process of writing data files as well. However, transaction completion times in Oracle database are not affected by the write IO path of data files.
Write IO Path in Oracle Database with VirtuCache
READ IO PATH IN Oracle Database (WITHOUT VIRTUCACHE):
Reads in Oracle Database retrieve data from the in memory buffer cache of the database. If the data is not in buffer cache, Oracle Database first retrieves this data from persistent storage, so, if your backend storage is slow, then these reads will be slow.
Also, slow writes will reduce the performance of reads. Since reads and writes are interspersed on the same thread, if writes are slow then the reads behind these writes on the same thread will be slow as well, even though these reads will most likely be serviced from buffer cache (in RAM) when they get to the front of the queue.
Read IO Path in Oracle Database running in a VMware VM (without VirtuCache)
READ IO PATH WHEN VIRTUCACHE IS INSTALLED:
When VirtuCache is installed in the ESXi host, reads that were earlier retrieved from backend storage will now be retrieved from VirtuCache cache media. Also, since VirtuCache caches both reads and writes, the problem of reads being slowed down by writes in front of it on the same thread is also taken care of.
Read IO Path in Oracle Database with VirtuCache
