我试用了MySQL InnoDB memcached插件

2 年 ago
科, 颖
7 minutes

首先

MySQL 5.6版本推出了新的InnoDB memcached插件。该插件利用memcached协议和客户端库允许访问MySQL的InnoDB表。

通过进行简单的操作验证，本文将调查该插件的概述。

前提 – tí

    • Dockerのmysql:5.7 を利用して検証する

インストールされるバージョンは 5.7.20

引入方式

按照原版手册的指引进行导入方法，非常简单。

    1. 执行以下SQL语句 source /usr/share/mysql/innodb_memcached_config.sql

执行 INSTALL PLUGIN daemon_memcached soname “libmemcached.so”;

一旦安装了插件，每次重新启动MySQL服务器时，插件将自动启用。

mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

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

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.01 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)

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.01 sec)

導入結束了，但我們仍然想知道innodb_memcached_config.sql執行了什麼。下面我們來看看這個SQL文件。

innodb_memcached_config.sql 是做什么的？

innodb_memcached_config.sql 的完整内容如下。此SQL文件执行以下操作。

    1. 创建innodb_memcache数据库

 

    1. 创建cache_policies表

 

    1. 创建containers表

 

    1. 创建config_options表

 

    1. 将test.demo_test表配置为InnoDB Memcache的后端

 

    1. 创建test数据库

 

    1. 创建demo_test表

 

    注册测试记录
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", ".");

CREATE DATABASE IF NOT EXISTS test;
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);

InnoDB Memcache数据库是什么？

请您查阅手册以获取详细信息。在本节中，将简要介绍各个表格的用途，这些信息都来自于手册。

mysql> use innodb_memcache;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+
3 rows in set (0.00 sec)

缓存策略表

可以设置每个操作的get、set、delete和flush时的策略。换句话说，这可以被视为在InnoDB表中保存还是作为常规的memcached进行设置。

mysql> 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)

配置选项表

这是一个存储memcached相关配置值的表。我认为当前的配置值没有问题。

mysql> select * from config_options;
+---------------------+-------+
| name                | value |
+---------------------+-------+
| separator           | |     |
| table_map_delimiter | .     |
+---------------------+-------+
2 rows in set (0.00 sec)

容器表格

这张桌子最重要。通过这张桌子，可以设置哪个InnoDB表与memcached进行映射。

mysql> 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                |
+------+-----------+------------+-------------+---------------+-------+------------+--------------------+------------------------+

用 Telnet 工具连接 Memcached 进行操作和测试

数据收集

在 demo_test 表中，已经注册了 1 条用于 innodb_memcached_config.sql 执行时的测试记录。

mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

我使用telnet连接到本地主机的memcached，并通过GET命令指定了AA键，成功地获取到了”HELLO, HELLO”的值。

root@7dbca47d81fc:/# telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END

数据的存储。

使用SET命令可以存储数据。确实可以通过使用memcached协议访问MySQL的InnoDB表。

set HOGE 0 0 4
FUGA
STORED

mysql> select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | HELLO, HELLO |    8 |    0 |    0 |
| HOGE | FUGA         |    0 |    1 |    0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)

请问？

通過简单的操作验证，我理解了安装方法和使用方式，但是还有一些疑问涌现出来。

不需要安装memcached吗？

由于MySQL中包含了一个memcached守护进程插件，libmemcached.so，所以不需要单独安装memcached。

root@7dbca47d81fc:/# ls -la /usr/lib/mysql/plugin/ | grep libmemcached
-rw-r--r-- 1 root root  185128 Sep 14 02:10 libmemcached.so

在启用InnoDB memcached的情况下，启动memcached守护进程会发生什么？

如果尝试在相同的端口号上启动memcached守护程序，答案是无法启动。在/var/log/memcached.log中输出了以下内容。

failed to listen on TCP port 11211: Address already in use

所以，只要将memcached守护进程的端口号更改为11211以外的值，就能够启动它。

是否可以使用多个InnoDB表进行切换？

如何在与最初创建的`demo_test`相同的表设计的基础上创建`demo_test2`表，并通过memcached获取该表的信息？

mysql> select * from test.demo_test2;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| BB | WORLD, WORLD |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.01 sec)

首先，将 demo_test2 的 InnoDB memcached 使用设置注册到 containers 表中。

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

以下是这样的。

mysql> 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                |
| bbb  | test      | demo_test2 | c1          | c2            | c3    | c4         | c5                 | PRIMARY                |
+------+-----------+------------+-------------+---------------+-------+------------+--------------------+------------------------+
2 rows in set (0.00 sec)

在这种情况下，如何实现demo_test和demo_test2的数据获取？

mysql> select * from test.demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | HELLO, HELLO |    8 |    0 |    0 |
| HOGE | FUGA         |    0 |    1 |    0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from test.demo_test2;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| BB | WORLD, WORLD |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

通过使用@@name，可以访问到答案。这也在手册上有写明。

get @@bbb.BB
VALUE @@bbb.BB 8 12
WORLD, WORLD
END

由于添加了demo_test2的设置，以前可以通过get AA获取demo_test侧的信息，现在无法获取。由于以前只设置了demo_test，因此被默认处理。

get AA
END

当然可以通过 @@aaa.AA 来获取 AA 的信息。

get @@aaa.AA
VALUE @@aaa.AA 8 12
HELLO, HELLO
END

如果使用InnoDB memcached插件来操作多个表的话，可能需要小心处理。

示例代码

我在这里提供了我创建的示例代码。

总而言之

根据标题，我们对InnoDB memcached插件进行了测试和验证。通过启用此插件，我们可以启动一个memcached守护进程，用于访问InnoDB表，并且可以将MySQL用作键值存储。优点是，切换到InnoDB memcached插件时，无需更改应用程序代码即可使用。然而，由于后端仍然是MySQL，并且仍然会写入磁盘，所以无法确定是否完全获得与memcached相同的性能。接下来，我们将调查针对InnoDB memcached插件的设置选项和性能。

请参考

    • MySQL :: MySQL 5.7 Reference Manual :: 14.20 InnoDB memcached Plugin

 

    • MySQL :: MySQL 5.7 Reference Manual :: 14.20.3 Setting Up the InnoDB memcached Plugin

 

    MySQL :: MySQL 5.7 Reference Manual :: 14.20.7 InnoDB memcached Plugin Internals
✖
广告
将在 10 秒后关闭
bannerAds