17.20.3 Setting Up the InnoDB memcached Plugin (original) (raw)

17.20.3 Setting Up the InnoDB memcached Plugin

This section describes how to set up thedaemon_memcached plugin on a MySQL server. Because the memcached daemon is tightly integrated with the MySQL server to avoid network traffic and minimize latency, you perform this process on each MySQL instance that uses this feature.

Prerequisites

Installing and Configuring the InnoDB memcached Plugin

  1. Configure the daemon_memcached plugin so it can interact with InnoDB tables by running the innodb_memcached_config.sql configuration script, which is located in_`MYSQLHOME`_/share. This script installs the innodb_memcache database with three required tables (cache_policies,config_options, andcontainers). It also installs thedemo_test sample table in thetest database.
mysql> source MYSQL_HOME/share/innodb_memcached_config.sql

Running the innodb_memcached_config.sql script is a one-time operation. The tables remain in place if you later uninstall and re-install thedaemon_memcached plugin. 

mysql> USE innodb_memcache;  
mysql> SHOW TABLES;  
+---------------------------+  
| Tables_in_innodb_memcache |  
+---------------------------+  
| cache_policies            |  
| config_options            |  
| containers                |  
+---------------------------+  
mysql> USE test;  
mysql> SHOW TABLES;  
+----------------+  
| Tables_in_test |  
+----------------+  
| demo_test      |  
+----------------+

Of these tables, theinnodb_memcache.containers table is the most important. Entries in the containers table provide a mapping to InnoDB table columns. Each InnoDB table used with thedaemon_memcached plugin requires an entry in the containers table.
The innodb_memcached_config.sql script inserts a single entry in the containers table that provides a mapping for thedemo_test table. It also inserts a single row of data into the demo_test table. This data allows you to immediately verify the installation after the setup is completed. 

mysql> SELECT * FROM innodb_memcache.containers\G  
*************************** 1. row ***************************  
                  name: aaa  
             db_schema: test  
              db_table: demo_test  
           key_columns: c1  
         value_columns: c2  
                 flags: c3  
            cas_column: c4  
    expire_time_column: c5  
unique_idx_name_on_key: PRIMARY  
mysql> SELECT * FROM test.demo_test;  
+----+------------------+------+------+------+  
| c1 | c2               | c3   | c4   | c5   |  
+----+------------------+------+------+------+  
| AA | HELLO, HELLO     |    8 |    0 |    0 |  
+----+------------------+------+------+------+

For more information aboutinnodb_memcache tables and thedemo_test sample table, seeSection 17.20.8, “InnoDB memcached Plugin Internals”. 2. Activate the daemon_memcached plugin by running the INSTALL PLUGIN statement: 

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

Once the plugin is installed, it is automatically activated each time the MySQL server is restarted.

Verifying the InnoDB and memcached Setup

To verify the daemon_memcached plugin setup, use a telnet session to issuememcached commands. By default, thememcached daemon listens on port 11211.

  1. Retrieve data from the test.demo_test table. The single row of data in thedemo_test table has a key value ofAA.
telnet localhost 11211  
Trying 127.0.0.1...  
Connected to localhost.  
Escape character is '^]'.  
get AA  
VALUE AA 8 12  
HELLO, HELLO  
END
  1. Insert data using a set command.
set BB 10 0 16  
GOODBYE, GOODBYE  
STORED

where:

  1. Verify that the data inserted is stored in MySQL by connecting to the MySQL server and querying thetest.demo_test table.
mysql> SELECT * FROM test.demo_test;  
+----+------------------+------+------+------+  
| c1 | c2               | c3   | c4   | c5   |  
+----+------------------+------+------+------+  
| AA | HELLO, HELLO     |    8 |    0 |    0 |  
| BB | GOODBYE, GOODBYE |   10 |    1 |    0 |  
+----+------------------+------+------+------+
  1. Return to the telnet session and retrieve the data that you inserted earlier using key BB.
get BB  
VALUE BB 10 16  
GOODBYE, GOODBYE  
END  
quit

If you shut down the MySQL server, which also shuts off the integrated memcached server, further attempts to access the memcached data fail with a connection error. Normally, the memcached data also disappears at this point, and you would require application logic to load the data back into memory whenmemcached is restarted. However, theInnoDB memcached plugin automates this process for you.

When you restart MySQL, get operations once again return the key-value pairs you stored in the earliermemcached session. When a key is requested and the associated value is not already in the memory cache, the value is automatically queried from the MySQLtest.demo_test table.

Creating a New Table and Column Mapping

This example shows how to setup your ownInnoDB table with thedaemon_memcached plugin.

  1. Create an InnoDB table. The table must have a key column with a unique index. The key column of the city table is city_id, which is defined as the primary key. The table must also include columns forflags, cas, andexpiry values. There may be one or more value columns. The city table has three value columns (name,state, country).
    Note
    There is no special requirement with respect to column names as along as a valid mapping is added to theinnodb_memcache.containers table.
mysql> CREATE TABLE city (  
       city_id VARCHAR(32),  
       name VARCHAR(1024),  
       state VARCHAR(1024),  
       country VARCHAR(1024),  
       flags INT,  
       cas BIGINT UNSIGNED,  
       expiry INT,  
       primary key(city_id)  
       ) ENGINE=InnoDB;
  1. Add an entry to theinnodb_memcache.containers table so that the daemon_memcached plugin knows how to access the InnoDB table. The entry must satisfy the innodb_memcache.containers table definition. For a description of each field, seeSection 17.20.8, “InnoDB memcached Plugin Internals”.
mysql> DESCRIBE innodb_memcache.containers;  
+------------------------+--------------+------+-----+---------+-------+  
| Field                  | Type         | Null | Key | Default | Extra |  
+------------------------+--------------+------+-----+---------+-------+  
| name                   | varchar(50)  | NO   | PRI | NULL    |       |  
| db_schema              | varchar(250) | NO   |     | NULL    |       |  
| db_table               | varchar(250) | NO   |     | NULL    |       |  
| key_columns            | varchar(250) | NO   |     | NULL    |       |  
| value_columns          | varchar(250) | YES  |     | NULL    |       |  
| flags                  | varchar(250) | NO   |     | 0       |       |  
| cas_column             | varchar(250) | YES  |     | NULL    |       |  
| expire_time_column     | varchar(250) | YES  |     | NULL    |       |  
| unique_idx_name_on_key | varchar(250) | NO   |     | NULL    |       |  
+------------------------+--------------+------+-----+---------+-------+

The innodb_memcache.containers table entry for the city table is defined as: 

mysql> INSERT INTO `innodb_memcache`.`containers` (  
       `name`, `db_schema`, `db_table`, `key_columns`, `value_columns`,  
       `flags`, `cas_column`, `expire_time_column`, `unique_idx_name_on_key`)  
       VALUES ('default', 'test', 'city', 'city_id', 'name|state|country',  
       'flags','cas','expiry','PRIMARY');
  1. After updating theinnodb_memcache.containers table, restart the daemon_memcache plugin to apply the changes.
mysql> UNINSTALL PLUGIN daemon_memcached;  
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
  1. Using telnet, insert data into the city table using a memcached set command.
telnet localhost 11211  
Trying 127.0.0.1...  
Connected to localhost.  
Escape character is '^]'.  
set B 0 0 22  
BANGALORE|BANGALORE|IN  
STORED
  1. Using MySQL, query the test.city table to verify that the data you inserted was stored.
mysql> SELECT * FROM test.city;  
+---------+-----------+-----------+---------+-------+------+--------+  
| city_id | name      | state     | country | flags | cas  | expiry |  
+---------+-----------+-----------+---------+-------+------+--------+  
| B       | BANGALORE | BANGALORE | IN      |     0 |    3 |      0 |  
+---------+-----------+-----------+---------+-------+------+--------+
  1. Using MySQL, insert additional data into thetest.city table.
mysql> INSERT INTO city VALUES ('C','CHENNAI','TAMIL NADU','IN', 0, 0 ,0);  
mysql> INSERT INTO city VALUES ('D','DELHI','DELHI','IN', 0, 0, 0);  
mysql> INSERT INTO city VALUES ('H','HYDERABAD','TELANGANA','IN', 0, 0, 0);  
mysql> INSERT INTO city VALUES ('M','MUMBAI','MAHARASHTRA','IN', 0, 0, 0);

Note
It is recommended that you specify a value of0 for the flags,cas_column, andexpire_time_column fields if they are unused. 7. Using telnet, issue a memcached get command to retrieve data you inserted using MySQL. 

get H  
VALUE H 0 22  
HYDERABAD|TELANGANA|IN  
END

Configuring the InnoDB memcached Plugin

Traditional memcached configuration options may be specified in a MySQL configuration file or amysqld startup string, encoded in the argument of thedaemon_memcached_option configuration parameter. memcached configuration options take effect when the plugin is loaded, which occurs each time the MySQL server is started.

For example, to make memcached listen on port 11222 instead of the default port 11211, specify-p11222 as an argument of thedaemon_memcached_option configuration option:

mysqld .... --daemon_memcached_option="-p11222"

Other memcached options can be encoded in thedaemon_memcached_option string. For example, you can specify options to reduce the maximum number of simultaneous connections, change the maximum memory size for a key-value pair, or enable debugging messages for the error log, and so on.

There are also configuration options specific to thedaemon_memcached plugin. These include:

By default, you do not need to modifydaemon_memcached_engine_lib_name ordaemon_memcached_engine_lib_path. You might configure these options if, for example, you want to use a different storage engine for memcached (such as the NDB memcached engine).

daemon_memcached plugin configuration parameters may be specified in the MySQL configuration file or in a mysqld startup string. They take effect when you load the daemon_memcached plugin.

When making changes to daemon_memcached plugin configuration, reload the plugin to apply the changes. To do so, issue the following statements:

mysql> UNINSTALL PLUGIN daemon_memcached;

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

Configuration settings, required tables, and data are preserved when the plugin is restarted.

For additional information about enabling and disabling plugins, see Section 7.6.1, “Installing and Uninstalling Plugins”.