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側で確認してみる。
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