New to Postgres Plus Advanced Server 8.3 is the integration and distribution of memcached. This newly bundled functionality gives database administrators and developers the ability to utilize high-performance memory caching from within the database itself; providing additional options for meeting the diverse performance requirements of Web 2.0 applications.
2.3.3.1 What is memcached?
In short, memcached is a high-performance, distributed memory object caching system which is intended primarily for reducing database load in order to speedup dynamic web applications.
Initially, memcached was developed to enhance the speed of LiveJournal.com, a site which was already doing 20 million+ dynamic page views per day for 1 million users with a bunch of web servers and a bunch of database servers. After deployment, memcached dropped the database load to almost nothing, yielding faster page load times for users, better resource utilization, and faster access to the databases on a memcache miss.
2.3.3.2 How Does memcached Work?
Being a distributed memory cache, memcached works by utilizing two hashes; behaving simply as a giant hash table for looking up key/value pairs. When finding an object in memcached, the client first hashes the key against the entire array of servers. Once a server has been chosen, the client then issues its request to that server, and the server performs an internal hash key lookup for the actual item data. If the data is found, it is returned to the client.
2.3.3.3 How Does Postgres Plus Advanced Server and memcached Integrate With Each Other?
Because memcached is a generic and modular caching system, a project called pgmemcache was created to allow users to call memcached functions from within Postgres. EnterpriseDB has bundled pgmemcache in its distribution of Postgres Plus Advanced Server.
2.3.3.4 Limitations of memcached
Generally, the basic limitations you may encounter when using memcached are the key and item size limits. Currently, keys are restricted to 250 characters and stored data cannot exceed 1 megabyte in size.
2.3.3.5 Limitations of Postgres Plus Advanced Server and memcached
By default, there is no integration between Postgres Plus Advanced Server and memcached. It is up to the developer or database administrator to decide what objects to cache, and how to best integrate those objects into memcached.
2.3.3.6 Recommendations for Best Performance
When using memcached with Postgres Plus Advanced Server, the following tips generally yield the best performance:
● Use 64-bit systems with a significant amount of RAM
● If using 32-bit systems, only use < 4GB of RAM
If you’re using Linux, make sure to use /dev/epoll.
2.3.3.7 Postgres Plus and memcached Concepts
The following are the steps generally followed when using Postgres Plus Advanced Server with memcached:
1. Install memcached
2. Configure and start memcached
3. Install pgmemcache into a Postgres Plus Advanced Server database
4. Add objects to memcached using the pgmemcache API
5. Retrieve objects from memcached using the pgmemcache API
2.3.3.8 Installing memcached
By default, memcached is installed with Postgres Plus Advanced Server in /opt/PostgresPlus/8.3AS/memcached. You’ll just need to configure/start it and make sure pgmemcache is installed properly.
2.3.3.9 Configuring and Starting memcached
Unlike other systems, memcached does not require a configuration file. Instead, its configuration parameters are all passed-in on the command line.
enterprisedb@edb-jharris /opt/PostgresPlus/8.3AS/memcached $ ./memcached -help memcached 1.2.2 -p <num> TCP port number to listen on (default: 11211) -U <num> UDP port number to listen on (default: 0, off) -s <file> unix socket path to listen on (disables network support) -l <ip_addr> interface to listen on, default is INDRR_ANY -d run as a daemon -r maximize core file limit -u <username> assume identity of <username> (only when run as root) -m <num> max memory to use for items in megabytes, default is 64 MB -M return error on memory exhausted (rather than removing items) -c <num> max simultaneous connections, default is 1024 -k lock down all paged memory -v verbose (print errors/warnings while in event loop) -vv very verbose (also print client commands/reponses) -h print this help and exit -i print memcached and libevent license -b run a managed instanced (mnemonic: buckets) -P <file> save PID in <file>, only used with -d option -f <factor> chunk size growth factor, default 1.25 -n <bytes> minimum space allocated for key+value+flags, default 48
So, to start memcached with a 128M cache, you would do the following:
$ export EDB_HOME=/opt/PostgresPlus/8.3AS
$ cd $EDB_HOME/memcached
$ ./memcached –d –m 128
2.3.3.10 Installing pgmemcache into Postgres Plus Advanced Server
In order to use memcached from within Postgres Plus Advanced Server, you must first install pgmemcache. While it is installed into the edb database by default, if you create a new database, or drop the edb database, you’ll need to install the pgmemcache functions using the example below. Note, the EDB_HOME environment variable should be set to the path you installed Postgres Plus Advanced Server into.
Installing pgmemcache with PSQL:
$ export EDB_HOME=/opt/PostgresPlus/8.3AS
$ edb-psql <connection info> -d dbname -f $EDB_HOME/dbserver/share/contrib/pgmemcache.sql
2.3.3.11 Exported memcached Functions Used in Postgres Plus Advanced Server
The following functions have been exported from the libmemcache library for use in Postgres Plus Advanced Server.
Table 2-1 memcached Functions
Function |
Description |
memcache_server_add |
Adds a server to the list of available servers. |
memcache_server_list |
Returns a row describing each element of the server list. |
memcache_server_remove |
Disconnects from the specified server. |
memcache_server_find |
Returns a tuple containing the hostname and port of the server assigned to the specified hash value. |
memcache_add |
Adds a key to the cache cluster, if the key does not already exist. |
memcache_decr |
If key exists and is an integer, atomically decrements by the value specified (default decrement is one). |
memcache_delete |
Deletes a given key. |
memcache_flush_all |
Flushes all data on all servers in the memcache cluster. |
memcache_flush |
Flushes all keys from the backend that the given key maps to. |
memcache_get |
Fetches a key out of the cache. |
memcache_hash |
Returns the hash value for a given key. |
memcache_incr |
If key exists and is an integer, atomically increment by the value specified (the default increment is one). |
memcache_replace |
Replaces an existing key's value if the key already exists. |
memcache_set |
Regardless of whether the specified key already exists, set its current value to “value”, replacing the previous value if any. |
memcache_stats |
Returns a TEXT string with all of the stats from all servers in the server list. |
2.3.3.12 Add a Server to the List of Available Servers (memcache_server_add)
This function adds a server to the list of available servers. If the port is not specified, the libmemcache default port (11211) is used. This function should only be done in one central place in the code (normally wrapped in an IF statement).
API Definition
memcache_server_add(/* hostname */ TEXT, /* port */ TEXT) memcache_server_add(/* hostname */ TEXT)
Example
CREATE OR REPLACE FUNCTION mc_init() RETURNS VOID AS $$ BEGIN PERFORM memcache_server_add('mc1.example.com', '11211'); PERFORM memcache_server_add('mc2.example.com', '11211'); RETURN; END; $$ LANGUAGE 'plpgsql';
2.3.3.13 Return Data Describing Each Element of the Server List (memcache_server_list)
This function returns a row describing each element of the server list such as the hostname, port, and current status.
API Definition
memcache_server_list() => SETOF (text, int4, text)
2.3.3.14 Disconnects from a Specific Server (memcache_server_remove)
This function disconnects from the server specified.
API Definition
memcache_server_remove(/* hostname */ TEXT, /* port */ TEXT) memcache_server_remove(/* hostname */ TEXT)
2.3.3.15 Return a Tuple Containing the Hostname and Port of the Server Assigned to the Specified Hash Value (memcache_server_find)
The following function returns a tuple containing the hostname and port of the server assigned to the specified hash value.
API Definition
memcache_server_find(/* hash */ INT4) => (TEXT, INT4) memcache_server_find(/* key */ TEXT) => (TEXT, INT4)
2.3.3.16 Add a Key to the Cache Cluster (memcache_add)
The following function adds a key to the cache.
API Definition
memcache_add(/* key */ TEXT, /* value */ TEXT, /* expire */ TIMESTAMPTZ) memcache_add(/* key */ TEXT, /* value */ TEXT, /* expire */ INTERVAL) memcache_add(/* key */ TEXT, /* value */ TEXT)
2.3.3.17 Atomically Decrement an Integer-Based Key Value (memcache_decr)
If the given key exists and is an integer, this function atomically decrements by the value specified (default decrement is one), and returns the integer value after decrement.
API Definition
newval = memcache_decr(/* key */ TEXT, /* decrement */ INT4) newval = memcache_decr(/* key */ TEXT)
2.3.3.18 Delete a Given Key/Value Pair (memcache_delete)
This function deletes a given key. If a hold timer is specified, a key with the same name cannot be added until the hold timer expires.
API Definition
memcache_delete(/* key */ TEXT, /* hold timer */ INTERVAL) memcache_delete(/* key */ TEXT)
Example
-- The above is not transaction safe, however. A better approach is -- to have pgmemcache invalidate the cached data, but not replace it. CREATE OR REPLACE FUNCTION auth_passwd_trg_upd() RETURNS TRIGGER AS $ BEGIN IF OLD.passwd != NEW.passwd THEN PERFORM mc_init(); PERFORM memcache_delete('user_id_' || NEW.user_id || '_password'); END IF; RETURN NEW; END;$ LANGUAGE 'plpgsql'; -- Here's an example delete trigger CREATE OR REPLACE FUNCTION auth_passwd_trg_del() RETURNS TRIGGER AS $ BEGIN PERFORM mc_init(); PERFORM memcache_delete('user_id_' || NEW.user_id || '_password'); RETURN OLD; END;$ LANGUAGE 'plpgsql';
2.3.3.19 Flush All Data on All Servers in the memcache Cluster (memcache_flush_all)
The following function flushes all data on every server in a memcached cluster.
API Definition
memcache_flush_all()
2.3.3.20 Flushes All Keys from the Backend that a Given Key Maps to (memcache_flush)
The following function flushes a particular key from a memcached cluster.
API Definition
memcache_flush(/* key */ TEXT)
2.3.3.21 Fetches a Key from Cache (memcache_get)
The following function fetches a key out of the cache. Returns null if the key does not exist; otherwise, it returns the value of the key as TEXT. Zero-length values are allowed.
API Definition
value = memcache_get(/* key */ TEXT)
2.3.3.22 Return the Hash Value for a Given Key (memcache_hash)
The following function returns the hash value for a given key.
API Definition
hash = memcache_hash(/* key */ TEXT)
2.3.3.23 Atomically Increment an Integer-Based Key Value (memcache_incr)
If the given key exists and is an integer, this function atomically increments by the value specified (default increment is one), and returns the integer value after decrement.
API Definition
newval = memcache_incr(/* key */ TEXT, /* increment */ INT4) newval = memcache_incr(/* key */ TEXT)
2.3.3.24 Replaces an Existing Key’s Value (memcache_replace)
The following function replaces an existing key’s value if the key already exists.
API Definition
memcache_replace(/* key */ TEXT, /* value */ TEXT, /* expire */ TIMESTAMPTZ) memcache_replace(/* key */ TEXT, /* value */ TEXT, /* expire */ INTERVAL) memcache_replace(/* key */ TEXT, /* value */ TEXT)
2.3.3.25 Set a Key’s Current Value (memcache_set)
The following function sets the current value of a key to “value”, replacing the previous value if any, regardless of whether the specified key already exists.
API Definition
memcache_set(/* key */ TEXT, /* value */ TEXT, /* expire */ TIMESTAMPTZ) memcache_set(/* key */ TEXT, /* value */ TEXT, /* expire */ INTERVAL) memcache_set(/* key */ TEXT, /* value */ TEXT)
2.3.3.26 Returns a TEXT String with All of the Stats from All Servers in the Server List (memcache_stats)
The following function returns a TEXT string with all of the stats from all servers in the server list. Statistics are derived from a summation of the statistics of all the servers in the server list.
API Definition
stats = memcache_stats() stat = memcache_stats(/* statistic key */ TEXT)
2.3.3.27 Full Example
The following is a full example. Components of this have been used in previous examples.
-- Altered to put data into memcached CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); -- Add cache entry PERFORM memcache_set('emp_ename_' || p_empno, v_ename); PERFORM memcache_set('emp_disp_date_' || p_empno, v_disp_date); PERFORM memcache_set('emp_sal_' || p_empno, v_sal); PERFORM memcache_set('emp_comm_' || p_empno, v_comm); PERFORM memcache_set('emp_dname_' || p_empno, v_dname); -- Perform output DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; / -- Retrieve data from memcached (if exists), otherwise revert to select_emp CREATE OR REPLACE PROCEDURE select_emp_cached ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT memcache_get('emp_ename_' || p_empno), memcache_get('emp_disp_date_' || p_empno), memcache_get('emp_sal_' || p_empno), memcache_get('emp_comm_' || p_empno), memcache_get('emp_dname_' || p_empno) INTO v_ename, v_disp_date, v_sal, v_comm, v_dname FROM dual; IF v_ename IS NULL THEN DBMS_OUTPUT.PUT_LINE('From select_emp!'); select_emp(p_empno); ELSE -- Perform output DBMS_OUTPUT.PUT_LINE('From select_emp_cached!'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; / -- Populate memcached (generally use a trigger) SELECT memcache_server_add('localhost'); -- Demo using standard query (populating cache) exec select_emp (7654); -- Demo using cached data query exec select_emp_cached (7654); -- Demo populating cache from select_emp_cached exec select_emp_cached (7900); exec select_emp_cached (7900);
2.3.3.28 How Else Can I Use Postgres Plus Advanced Server and memcached?
In addition to the examples already given, it should be mentioned that because memcached is a separate server, many other clients can populate and change the data it stores. As such, you could have external clients populate user information, or other objects which are easily returned from within the database.
