Technical Blog Post
Abstract
Improve Oracle Database performance with IBM Systems server-side caching
Body
Update : Oracle has confirmed server-side caching as a generic storage technology. Thus the use of server-side caching in the production environment does not have any certification requirements.
Within an application ecosystem there are always challenges and performance most of times tops the chart.
In spite of having a well-balanced storage system and fully tuned Oracle Database instance, with ever growing users there is always a need to get more out of system. So from the tuning point when the efforts start, the entire stack has to be looked. While application server, database host can be easily upgraded, and to some extent network too, but when it comes to storage things not always easy. Really? Well think again..
The solutions right is here. Starting with AIX Version 7.1 with Technology Level 4 Service Pack 5 (7100-04-05) and later, IBM Storage Systems support a new feature called server-side caching that uses SAN attached flash storage to improve read performance. IBM FlashSystems are best known for their microsecond latency. When it comes to performance, nothing can stand in the way.
The server-side caching is very well supported right from the OS level and it is also well integrated to make use of the power of IBM FlashSystems. By making the server-side caching available at operating system level is not only storage agnostic but also eliminates the need for data migration to newer storage system. Moreover, now that the FlashSystem is available as part of SAN infrastructure, it can be shared with multiple servers that require performance improvement from storage.
In the following sections, we take a deeper dive at this feature to understand the key components involved in building a solution targeted towards Oracle databases.
For more information about configuring various storage data caching modes, refer to the IBM Knowledge Center for AIX at: ibm.com/support/knowledgecenter/ssw_aix_71/com.ibm.aix.osdevice/caching_configuring.htm
For more information about dedicated cache configuration refer to IBM Knowledge Center for AIX at: ibm.com/support/knowledgecenter/ssw_aix_71/com.ibm.aix.osdevice/caching_dedicated_mode.htm
Benefits of server-side cache
During the lab testing, various cache sizes were configured on the database host. The workload was run with a constant number of virtual users to see the effect of different cache sizes. It was observed that the cache takes approximately 30 to 45 * minutes to warm up. During this warm-up period heavy write and read activity was seen since the data not found in cache must be written to cache (write operation to cache). At the same time the data found in cache is read (read operations to cache) and returned to the application.
Note: The warmup time may be different depending on the size of cache device.
Figure 1 shows the TPS data captured during a five hour run. The run was started without starting the server-side cache. We see that the application quickly attains a saturation level and the TPS at a baseline value. One hour into the run, the cache was started, and there is a gradual improvement seen in the TPS. After the configured cache is completely filled, there is no more room for new data to cache. At this point, the flash cache is serving the application read requests. Data not found in cache will be written to cache based on the caching algorithm. Blocks heavily accessed are seen as hot blocks. Blocks transition from cold to warm to hot state and then from hot to warm to cold state depending on their access pattern.
Figure 1: TPS improvement with server-side cache
For the run data showed in Figure 1, a cache size of 128 GB (that is, 10% of the database size) was configured with only 58 virtual users simulating a business application. Figure 2 shows the I/O activity recorded on the base storage system, and at 0:38:36 shows the increased read activity enabled by the sever-side cache system which leads to the TPS improvement.
Figure 2: Total storage I/O read rate improvement
In the tests documented above, a flash cache that is 10% of the database size was used. Table 1 shows relative TPS measurements made with larger cache partition sizes used.
Cache size in GB |
Cache size % relative to DB size |
Relative TPS improvement |
128 |
~ 10% |
2.4x |
256 |
~ 20% |
3.2x |
500 |
~ 40% |
4.6x |
700 |
~ 60% |
5.2x |
Table 1: TPS throughput improvement with different server-side cache sizes
Note: The TPS throughput listed in Table 3 is observed in the controlled lab environment. The actual values for TPS might change depending on the workload and overall network traffic.
Resources
Detailed technical white paper : https://www.ibm.com/common/ssi/cgi-bin/ssialias?htmlfid=TSW03489USEN&
IBM AIX Infocenter : ibm.com/support/knowledgecenter/ssw_aix_71/com.ibm.aix.osdevice/caching_configuring.htm
Oracle OpenWorld
Oracle OpenWorld
Visit IBM booth at Oracle OpenWorld and I'll be there in person if you would like to know more.
See you in San Francisco !!
UID
ibm16164871