Using the MariaDB memcached User-Defined Functions

The memcached MariaDB User-Defined Functions (UDFs) enable you to set and retrieve objects through a SQL interface, in MariaDB 5.0 or greater. This technique has the following benefits:

• You can update and retrieve cached items directly from within SQL scripts, stored procedures, and triggers. For example, you might already have triggers in place to increment counters and set status flags based on general database activity.
• You can pass information back and forth with other applications that use memcached, without adding database connection and query code to them.
• You can avoid installing and configuring a memcached client on every combination of language and server that you use for your MariaDB applications. The applications can relay memcached requests through the database server they connect to.
• You can access memcached servers from languages that do not have a memcached client.

To install the MariaDB memcached UDFs, download the UDF package from https://launchpad.net/memcached-udfs. Unpack the package and run configure to configure the build process. When running configure, use the --with-mysql option and specify the location of the mysql_config command.

shell> tar zxf memcached_functions_mysql-1.1.tar.gz
shell> cd memcached_functions_mysql-1.1
shell> # If memcached library is not found, set LDFLAGS=-Llibrary_directory before next command.
shell> ./configure --with-mysql=/usr/local/mysql/bin/mysql_config

Now build and install the functions:

shell> make
shell> make install

Copy the MariaDB memcached UDFs into your MariaDB plugins directory:

shell> cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql/lib/mysql/plugins/

The plugin directory is given by the value of the plugin-dir system variable. For more information, see , "Compiling and Installing User-Defined Functions".

Once installed, you must initialize the function within MariaDB using CREATE and specifying the return value and library. For example, to add the memc_get() function:

mysql> CREATE FUNCTION memc_get RETURNS STRING SONAME 'libmemcached_functions_mysql.so';

Repeat this process for each function to provide access to within MySQL. Once you have created the association, the information is retained, even over restarts of the MariaDB server. To simplify the process, use the SQL script provided in the memcached UDFs package:

shell> mysql <sql/install_functions.sql

Alternatively, if you have Perl installed, then you can use the supplied Perl script, which checks for the existence of each function and creates the function/library association if it is not already defined:

shell> utils/install.pl --silent

The --silent option installs everything automatically. Without this option, the script asks whether to install each of the available functions.

The interface remains consistent with the other APIs and interfaces. To set up a list of servers, use the memc_servers_set() function, which accepts a single string containing and comma-separated list of servers:

mysql> SELECT memc_servers_set('192.168.0.1:11211,192.168.0.2:11211');

Note

The list of servers used by the memcached UDFs is not persistent over restarts of the MariaDB server. If the MariaDB server fails, then you must re-set the list of memcached servers.

To set a value, use memc_set:

mysql> SELECT memc_set('myid', 'myvalue');

To retrieve a stored value:

mysql> SELECT memc_get('myid');

The list of functions supported by the UDFs, in relation to the standard protocol functions, is shown in the following table:

MySQL memcached UDF Function	Equivalent to
memc_get()	Generic get().
memc_get_by_key(master_key, key, value)	Like the generic get(), but uses the supplied master key to select the server to use.
memc_set()	Generic set().
memc_set_by_key(master_key, key, value)	Like the generic set(), but uses the supplied master key to select the server to use.
memc_add()	Generic add().
memc_add_by_key(master_key, key, value)	Like the generic add(), but uses the supplied master key to select the server to use.
memc_replace()	Generic replace().
memc_replace_by_key(master_key, key, value)	Like the generic replace(), but uses the supplied master key to select the server to use.
memc_prepend(key, value)	Prepend the specified value to the current value of the specified key.
memc_prepend_by_key(master_key, key, value)	Prepend the specified value to the current value of the specified key, but uses the supplied master key to select the server to use.
memc_append(key, value)	Append the specified value to the current value of the specified key.
memc_append_by_key(master_key, key, value)	Append the specified value to the current value of the specified key, but uses the supplied master key to select the server to use.
memc_delete()	Generic delete().
memc_delete_by_key(master_key, key, value)	Like the generic delete(), but uses the supplied master key to select the server to use.
memc_increment()	Generic incr().
memc_decrement()	Generic decr().

The respective *_by_key() functions are useful to store a specific value into a specific memcached server, possibly based on a differently calculated or constructed key.

The memcached UDFs include some additional functions:

• memc_server_count()
  

  Returns a count of the number of servers in the list of registered servers.

• memc_servers_set_behavior(behavior_type, value), memc_set_behavior(behavior_type, value)
  

  Sets behaviors for the list of servers. These behaviors are identical to those provided by the libmemcached library. For more information on libmemcached behaviors, see , "Using libmemcached with C and C++".

  You can use the behavior name as the behavior_type:

  mysql> SELECT memc_servers_behavior_set('MEMCACHED_BEHAVIOR_KETAMA',1);
  

• memc_servers_behavior_get(behavior_type), memc_get_behavior(behavior_type, value)
  

  Returns the value for a given behavior.

• memc_list_behaviors()
  

  Returns a list of the known behaviors.

• memc_list_hash_types()
  

  Returns a list of the supported key-hashing algorithms.

• memc_list_distribution_types()
  

  Returns a list of the supported distribution types to be used when selecting a server to use when storing a particular key.

• memc_libmemcached_version()
  

  Returns the version of the libmemcached library.

• memc_stats()
  

  Returns the general statistics information from the server.

Retornar