MySQL5.6から利用出来る、memcached pluginの動作確認になります。
Webサイトではユーザーセッション管理などでmemacheとrepcachedを利用して、
高速データ処理をしてました。MySQLでもPluginとしてmemcachedが使えるようになり、
NOSQL処理も出来るようになりました。mysqlのmemcachedはACID対応可能で、
innodbと連携出来るので、整合性を保ったNOSQLとして利用出来る事が特徴になります。
また、SETコマンドなどで処理したデータはテーブルに保存されるので、
再起動しても残す事が出来ます。

Pluginのインストール

root@localhost [(none)]>system cat /usr/local/mysql/share/innodb_memcached_config.sql
create database innodb_memcache;

use innodb_memcache;


-- ------------------------------------------------------------------------
-- Following are set of "configuration tables" that used to configure
-- the InnoDB Memcached.
-- ------------------------------------------------------------------------

-- ------------------------------------------------------------------------
-- Table `cache_policies`
--
-- Each record in this table represents a named caching policy, specifying:
--  * How the memcache GET command is executed, including whether to get
--    records from local cache only, from InnoDB only, from local cache if
--    present (treating InnoDB as a backing store), or not at all.
--  * Similarly, how memcache SET commands are executed.
--  * How memcache DELETE commands are executed.
--  * Whether flushing the cache should cause a mass delete from NDB.
--
-- ------------------------------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cache_policies` (
        `policy_name` VARCHAR(40) PRIMARY KEY,
        `get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
         NOT NULL ,
        `set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
         NOT NULL ,
        `delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
         NOT NULL,
        `flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
         NOT NULL
) ENGINE = innodb;


-- ------------------------------------------------------------------------
-- Table `containers`
--
-- A container record describes an InnoDB table used for data storage by
-- InnoDB Memcache.
-- There must be a unique index on the `key column`, and unique index name
-- is specified in the `unique_idx_name_on_key` column of the table
-- `value_columns` are comma-separated lists of the columns that make up
-- the memcache key and value. Each column width is defined such that they
-- are in consistent with NDB memcached.
-- ------------------------------------------------------------------------

CREATE  TABLE IF NOT EXISTS `containers` (
        `name` varchar(50) not null primary key,
        `db_schema` VARCHAR(250) NOT NULL,
        `db_table` VARCHAR(250) NOT NULL,
        `key_columns` VARCHAR(250) NOT NULL,
        `value_columns` VARCHAR(250),
        `flags` VARCHAR(250) NOT NULL DEFAULT "0",
        `cas_column` VARCHAR(250),
        `expire_time_column` VARCHAR(250),
        `unique_idx_name_on_key` VARCHAR(250) NOT NULL
) ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `config_options` (
        `name` varchar(50) not null primary key,
        `value` varchar(50)) ENGINE = InnoDB;

-- ------------------------------------------------------------------------
-- This is an example
-- We create a InnoDB table `demo_test` is the `test` database
-- and insert an entry into contrainers' table to tell InnoDB Memcache
-- that we has such InnoDB table as back store:
-- c1 -> key
-- c2 -> value
-- c3 -> flags
-- c4 -> cas
-- c5 -> exp time
-- PRIMARY -> use primary key to search
-- ------------------------------------------------------------------------

INSERT INTO containers VALUES ("aaa", "test", "demo_test",
                               "c1", "c2",  "c3", "c4", "c5", "PRIMARY");

INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
                                  "innodb_only", "innodb_only", "innodb_only");

INSERT INTO config_options VALUES("separator", "|");
INSERT INTO config_options VALUES("table_map_delimiter", ".");

USE test

-- ------------------------------------------------------------------------
-- Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
-- Bytes
-- Value (c2) must be VARCHAR or CHAR type
-- Flag (c3) is a 32 bits integer
-- CAS (c4) is a 64 bits integer, per memcached define
-- Exp (c5) is again a 32 bits integer
-- ------------------------------------------------------------------------
CREATE TABLE demo_test (c1 VARCHAR(32),
                        c2 VARCHAR(1024),
                        c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
ENGINE = INNODB;

INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);
root@localhost [(none)]>


root@localhost [(none)]>source /usr/local/mysql/share/innodb_memcached_config.sql
Query OK, 1 row affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

root@localhost [test]>


root@localhost [(none)]>show variables like 'plugin%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]>install plugin daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.16 sec)

root@localhost [(none)]>


root@localhost [information_schema]>select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,LOAD_OPTION from PLUGINS
    -> where PLUGIN_NAME like '%memcached';
+------------------+----------------+---------------+-------------+
| PLUGIN_NAME      | PLUGIN_VERSION | PLUGIN_STATUS | LOAD_OPTION |
+------------------+----------------+---------------+-------------+
| daemon_memcached | 1.0            | ACTIVE        | ON          |
+------------------+----------------+---------------+-------------+
1 row in set (0.00 sec)

root@localhost [information_schema]>



root@localhost [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb_memcache    |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)


root@localhost [innodb_memcache]>use innodb_memcache
Database changed
root@localhost [innodb_memcache]>show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+
3 rows in set (0.00 sec)

root@localhost [innodb_memcache]>

root@localhost [innodb_memcache]>desc cache_policies;
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| Field         | Type                                                  | Null | Key | Default | Extra |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| policy_name   | varchar(40)                                           | NO   | PRI | NULL    |       |
| get_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| set_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| delete_policy | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| flush_policy  | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

root@localhost [innodb_memcache]>desc config_options;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   | PRI | NULL    |       |
| value | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [innodb_memcache]>desc 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    |       |
+------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

root@localhost [innodb_memcache]>

root@localhost [innodb_memcache]>select * from cache_policies;
+--------------+-------------+-------------+---------------+--------------+
| policy_name  | get_policy  | set_policy  | delete_policy | flush_policy |
+--------------+-------------+-------------+---------------+--------------+
| cache_policy | innodb_only | innodb_only | innodb_only   | innodb_only  |
+--------------+-------------+-------------+---------------+--------------+
1 row in set (0.00 sec)

root@localhost [innodb_memcache]>select * from config_options;
+---------------------+-------+
| name                | value |
+---------------------+-------+
| separator           | |     |
| table_map_delimiter | .     |
+---------------------+-------+
2 rows in set (0.00 sec)

root@localhost [innodb_memcache]>select * from containers;
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table  | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| aaa  | test      | demo_test | c1          | c2            | c3    | c4         | c5                 | PRIMARY                |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)

root@localhost [innodb_memcache]>select * from test.demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

root@localhost [innodb_memcache]>

TELENETでmemcachedのDefault Portに接続してデータ操作してみる
set, replace, append, prepend
<コマンド>

get, gets, delete
<コマンド> <キー>

[root@misc admin]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END
quit
Connection closed by foreign host.
[root@misc admin]#

[root@misc admin]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
set a11 10 0 9
123456789
STORED
get a11
VALUE a11 10 9
123456789
[root@misc admin]#


root@localhost [innodb_memcache]>select * from test.demo_test;
+-----+--------------+------+------+------+
| c1  | c2           | c3   | c4   | c5   |
+-----+--------------+------+------+------+
| a11 | 123456789    |   10 |    3 |    0 |
| AA  | HELLO, HELLO |    8 |    0 |    0 |
+-----+--------------+------+------+------+
2 rows in set (0.00 sec)

root@localhost [innodb_memcache]>

root@localhost [test]>delete from test.demo_test;
Query OK, 2 rows affected (0.07 sec)

root@localhost [test]>select * from test.demo_test;
Empty set (0.00 sec)

[root@misc admin]# telnet localhost 11211 
Trying ::1...
Connected to localhost.
Escape character is '^]'.
set a11 10 0 9
123456789
STORED
set AA 0 0 12
HELLO, HELLO
STORED
get all
END
get AA
VALUE AA 0 12
HELLO, HELLO
END
set BB 0 0 12
HELLO, MEMCA
STORED
get BB
VALUE BB 0 12
HELLO, MEMCA
END
quit
Connection closed by foreign host.
[root@misc admin]# 

root@localhost [test]>select * from test.demo_test;
+-----+--------------+------+------+------+
| c1  | c2           | c3   | c4   | c5   |
+-----+--------------+------+------+------+
| a11 | 123456789    |   10 |    1 |    0 |
| AA  | HELLO, HELLO |    0 |    2 |    0 |
| BB  | HELLO, MEMCA |    0 |    3 |    0 |
+-----+--------------+------+------+------+
3 rows in set (0.00 sec)

root@localhost [test]>

DB側の設定とmemcachedのステータス

root@localhost [test]>show variables like '%memcached%';
+----------------------------------+------------------+
| Variable_name                    | Value            |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog   | OFF              |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path |                  |
| daemon_memcached_option          |                  |
| daemon_memcached_r_batch_size    | 1                |
| daemon_memcached_w_batch_size    | 1                |
+----------------------------------+------------------+
6 rows in set (0.00 sec)

root@localhost [test]>

[root@misc admin]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
stats
STAT pid 3646
STAT uptime 913
STAT time 1420435227
STAT version 5.6.22
STAT libevent 5.6.22
STAT pointer_size 64
STAT rusage_user 0.467361
STAT rusage_system 0.858934
STAT daemon_connections 2
STAT curr_connections 3
STAT total_connections 4
STAT connection_structures 3
STAT cmd_get 0
STAT cmd_set 0
STAT cmd_flush 0
STAT auth_cmds 0
STAT auth_errors 0
STAT get_hits 0
STAT get_misses 0
STAT delete_misses 0
STAT delete_hits 0
STAT incr_misses 0
STAT incr_hits 0
STAT decr_misses 0
STAT decr_hits 0
STAT cas_misses 0
STAT cas_hits 0
STAT cas_badval 0
STAT bytes_read 24
STAT bytes_written 7
STAT limit_maxbytes 67108864
STAT accepting_conns 1
STAT listen_disabled_num 0
STAT rejected_conns 0
STAT threads 4
STAT conn_yields 0
STAT evictions 0
STAT curr_items 0
STAT total_items 0
STAT bytes 0
STAT reclaimed 0
STAT engine_maxbytes 67108864
END

MySQL側でデータを追加してMemcached側で確認してみる。
set-memcached

14.18.3.2 Installing and Configuring the InnoDB memcached Plugin
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-installing.html
14.18.7 Internals of the InnoDB memcached Plugin
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html
14.18.2 Architecture of InnoDB and memcached Integration
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-intro.html

Comments are closed.

Post Navigation