Mercury Distributed Cache for Oracle

Based on the same premise asWaffle Grid for MySQL andInfinite Cache for EnterpriseDB, Mercury gives database and system administrators the ability to improve Oracle performance by reducing the amount of disk-related I/O for reads using a unique, cluster-oriented distribution method.

What is Mercury?
Designed as a distributed exclusive main-memory cache management system, Mercury supplements the Oracle buffer cache with optimized network-based data block storage to reduce the amount of read-related disk I/O which must be performed when working with large-scale databases.

In short, if you’ve read a block from disk once, and it has already been flushed out of the Oracle buffer cache, it becomes managed by Mercury. If you request that block again at a later time, such as for an end-of-day batch processing job, Mercury will deliver Oracle the blocks it needs without ever having to go to disk.

Why would I want to use it?
If you have a large database, a memory-limited system, databases which can not easily be sharded, or you simply want to make use of memory in additional servers (such as a standby node), Mercury can improve database performance by reducing repetitive read-based disk I/O.

What versions of Mercury are there?
Mercury Express (the version released), Mercury Standard, and Mercury Enterprise. Both Mercury Standard and Enterprise require a relink of Oracle.

What Oracle Database platforms are supported?
Linux x86 and x86_64 are the only database platforms currently supported.

What memcached platforms are supported?
Any memcached system can be used as part of the cache cluster.  Similarly, this also includes third-party systems which support the memcached protocol such as Tokyo Tyrant and appliances from Schooner orGear6.

What versions of Oracle does Mercury work with?
I develop against Standard and Enterprise Editions of 10gR2 with basic testing being performed with 11g. Specific testing of Mercury Express Edition has been performed with Oracle XE for the purposes of this blog post.

What kind of performance improvement can I expect?
You will only see an improvement from Mercury when working with a database larger than memory (if O_DIRECT is disabled) or larger then the Oracle buffer cache (if O_DIRECT is enabled). For example, running a 6500 warehouse DBT-2 OLTP workload on a system with 64GB of RAM showed a 34% increase in performance after adding another 32GB of RAM on a remote server via Mercury.

DBT-3 improvements show anywhere between 5% and 43% depending on the configuration.

Does Mercury go to the network for every block request?
As Mercury has been designed to account for network overhead and minimize the number of network requests, it only goes to the network when necessary.

First, Mercury makes use of a local Bloom Filter to reduce unnecessary network requests on block read operations which have not yet been cached. Second, Mercury is able to utilize some of the latest memcached advancements, such as multi-get, to satisfy multi-block reads. Lastly, because Mercury tracks all resource usage and response time information, it can adapt to changing conditions by varying cache expiration and switching between local I/O or distributed cache depending on available system resources.

Initial testing has also begun for using InfiniBand RDMA for block transfer, which will also improve performance.

Does Mercury cache all Oracle blocks?
No. Currently, Mercury only tracks Oracle data files and caches specific block types.

Is Mercury like Exadata?

Is Mercury like RAC?
No.  Unlike RAC, which has the ability to allow multiple active nodes to share database blocks, Mercury is designed for a single database node to utilize multiple nodes for cache-only purposes.  But if it helps you to visualize block transfers, you can look at a RAC diagram for Cache Fusion and take out all of the transaction-specific before-image/after-image stuff (i.e., the majority of Cache Fusion); it’s kinda like that.

Can I track Mercury statistics?
Yes! Mercury makes use of a shared, IPC-based statistics and configuration memory context which you can access using the Mercury Control utility (mercuryctl -p). Very soon, you’ll also be able to access this data from within Oracle via a SQL query.

Data collected includes the number of memcached gets/sets/hits/misses as well as the number of bytes sent/received, the Oracle PIDs using Mercury, and a wait event history table. NOTE: the wait events table in Mercury Express is defined at compile-time to hold only 50 records in a circular buffer. Standard and Enterprise editions have the ability to persist the data as it is flushed out of the buffer.

What happens to Mercury when Oracle shuts down?
One of two things can happen depending on the configuration.

By default, nothing will happen to the shared memory block when Oracle is shut down. But, it will be invalidated at Oracle startup by checking the Kernel Cache Recovery File Header. The second option is to invalidate the shared memory context when the last process detaches from it.

Can I run this in production?
NO! The current release of Mercury is a proof-of-concept with bugs and potential data integrity issues. Feel free to play with it, but use it at your own risk.

Is this supported by Oracle?
NO! This is an alpha-release of a hobby project which is most certainly not supported by Oracle. Do not use this on a production Oracle environment!

What license is Mercury released under?
A proprietary EULA that gives you the ability to play with it.

What programming language is Mercury written in?
None other than C and assembly!

Do you have access to Oracle’s source code?
No, but I someday hope to work on it!

How did you add this to Oracle without their source code?
Because I do not have access to the Oracle Database source code, almost everything Mercury does has to be performed at a level below the Oracle Kernel using function interposing.

However, because the version of Mercury for Oracle Standard/Enterprise is more closely coupled to the Oracle Database, I have replaced several Oracle kernel functions with my own variants which retain the same functionality (by calling Oracle’s own functions), but allow me to access the information needed. This is why a relink of Oracle is required for the Standard and Enterprise versions.

How did you know how to do this?
In addition to the fairly sizable amount of Oracle Database internals knowledge required to do this in a fairly safe manner, I had already written something similar to this for PostgreSQL as well as a couplecopy-on-write systems based on function interposing.

Why did you do this?
I was bored.

Can I get the source code?

How do I install it?
Download Oracle Database 10g Express Edition (XE) and install is as follows (on RHEL):

[root@rhel53vm array0]# rpm -ivh oracle-xe-univ-
Preparing...                ########################################### [100%]
   1:oracle-xe-univ         ########################################### [100%]
Executing Post-install steps...
groupadd: group dba exists
You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

[root@rhel53vm array0]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
This will configure on-boot properties of Oracle Database 10g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:
Starting Oracle Net Listener...Done
Configuring Database...Done

Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.

To access the Database Home Page go to ""

[root@rhel53vm array0]# ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server && export ORACLE_HOME && $ORACLE_HOME/bin/sqlplus system@XE

SQL*Plus: Release - Production on Sun Mar 29 09:32:40 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release - Production


PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 10g Express Edition Release - Production

Install Mercury Express:

[root@rhel53vm array0]# tar jxf mercury-0.1.2.tar.bz2
[root@rhel53vm array0]# patch -p0 < /etc/init.d/oracle-xe mercury-0.1.2/etc/oracle-xe.patch
[root@rhel53vm array0]# cp mercury-0.1.2/dbs/mercuryXE.ora $ORACLE_HOME/dbs
[root@rhel53vm array0]# cp mercury-0.1.2/lib/* $ORACLE_HOME/lib
[root@rhel53vm array0]# cp mercury-0.1.2/bin/* $ORACLE_HOME/bin

Restart Oracle:

[root@rhel53vm array0]# /etc/init.d/oracle-xe restart

Check the Mercury stats:

[root@rhel53vm array0]# $ORACLE_HOME/bin/mercuryctl -p

You should see something similar to the following:

[root@rhel53vm array0]# $ORACLE_HOME/bin/mercuryctl -p

Mercury Express: Release 0.1.2 - Alpha

Copyright (c) 2008,2009 Jonah H. Harris.  All rights reserved.

key = 0x037a839f

cfgVersionNumber = 0

mcSetCount  = 49
mcGetCount  = 0
mcHitCount  = 0
mcMissCount = 0

bytesSent     = 0
bytesReceived = 0

attachedPidCount = 22
attached PID List:


PID   WAIT EVENT                       P1         P2         P3 WAITED (us)
----- ------------------------ ---------- ---------- ---------- -----------
5549  memcached: set                    0          0          0           0
5549  memcached: connect                0          0          0           0
4322  disk: data block read           508  234356736       8192           0
4322  disk: data block read           508  218931200       8192           0
5527  memcached: set                    0          0          0           0
5527  memcached: connect                0          0          0           0
5505  memcached: set                    0          0          0           0
5505  memcached: connect                0          0          0           0
4710  disk: data block read           612    7028736       8192           0
4710  disk: data block read           612    7020544       8192           0
4710  disk: data block read           612    7036928       8192           0
4710  disk: data block read           612    8617984       8192           0
4710  disk: data block read           612    8552448       8192           0
4710  disk: data block read           612    8470528       8192           0
4710  disk: data block read           612    8462336       8192           0
4710  disk: data block read           612    8478720       8192           0
4710  disk: data block read           611   56532992       8192           0
4710  disk: data block read           611  160071680       8192           0
4710  disk: data block read           611   56541184       8192           0
4710  disk: data block read           612   36405248       8192           0
4710  disk: data block read           612    7897088       8192           0
4710  disk: data block read           612    7110656       8192           0
4710  disk: data block read           612    7634944       8192           0
4710  disk: data block read           612    7241728       8192           0
4710  disk: data block read           612    6979584       8192           0
4710  disk: data file open            613          0          0           0
4710  disk: data block read           612    6504448       8192           0
4710  disk: data block read           612    6496256       8192           0
4710  disk: data block read           612    6512640       8192           0
4710  disk: data block read           612    6414336       8192           0
4710  disk: data block read           612    6455296       8192           0
4710  disk: data block read           611   15867904       8192           0


Click here to download Mercury 0.1.2 – Alpha for Linux x86.