Introduction to the Memcached plug-in of InnoDB

In the past few years, HandlerSocket was born to make people shine. At that time, I wrote an article to introduce its usage synopsis. Today, HandlerSocket is not really popular due to various reasons, fortunately, I was inspired by MySQL and developed the InnoDB-based Memcached plug-in to continue the popularity of NoSQL in MySQL.

In the past few years, HandlerSocket was born to make people shine. At that time, I wrote an article to introduce its usage synopsis. Today, HandlerSocket is not really popular due to various reasons, fortunately, I was inspired by MySQL and developed the InnoDB-based Memcached plug-in to continue the popularity of NoSQL in MySQL.

In the past few years, HandlerSocket was born to make people shine. At that time, I wrote an article to introduce its usage synopsis. Today, HandlerSocket is not really popular due to various reasons, fortunately, I was inspired by MySQL and developed the InnoDB-based Memcached plug-in, which eventually continued the popularity of NoSQL in MySQL. Previously, setting up a separate Memcached server not only wastes memory, in addition, you must maintain data inconsistency by yourself. With the Memcached plug-in, these problems do not exist, and with the replication function of MySQL, we can say that Memcached replication is implemented in disguise, which is even more surprising.

Install

To make the article more complete, we choose to install MySQL from the source code. Note that memory leakage exists in earlier versions, so we recommend that you install the latest stable version. As of press time, the latest stable version is 5.6.13. Let's take this as an example to describe that the process is very simple, as long as WITH_INNODB_MEMCACHED is activated:

shell> groupadd mysqlshell> useradd -r -g mysql mysqlshell> tar zxvf mysql-5.6.13.tar.gzshell> cd mysql-5.6.13shell> cmake . -DWITH_INNODB_MEMCACHED=ONshell> makeshell> make installshell> cd /usr/local/mysqlshell> chown -R mysql .shell> chgrp -R mysql .shell> scripts/mysql_install_db --user=mysqlshell> chown -R root .shell> chown -R mysql datashell> bin/mysqld_safe --user=mysql &shell> cp support-files/mysql.server /etc/init.d/mysql.server

After MySQL is installed, You need to import the table structure required by the Memcached plug-in:

mysql> SOURCE /usr/local/mysql/share/innodb_memcached_config.sql

After everything is ready, you can activate the Memcached plug-in (of course, you can also disable it ):

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

Note: Once you modify the configuration information of the existing Memcached plug-in the future, you must use the preceding command to restart the Memcached plug-in (uninstall first, and then install) to make the modification take effect.

The configuration information of the Memcached plug-in is as follows. For details, refer to the official documentation:

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

Note: daemon_memcached_r_batch_size and daemon_memcached_w_batch_size have a great impact on the performance. To put it simply, they control the transaction commit frequency. The default value of MySQL is 1, that is, commit every time, this is mainly from security considerations. you can adjust it according to your own situation.

Now the Memcached port is ready. You can try it:

shell> echo "stats" | nc localhost 11211

In other words, MySQL is compatible with the Memcached protocol and can directly use the Memcached command.

Configuration

In the installation procedure, we imported a script named innodb_memcached_config. SQL, which creates three tables (cache_policies, config_options, containers) in the innodb_memcached_config. SQL Library ):

mysql> USE innodb_memcachemysql> SHOW TABLES;+---------------------------+| Tables_in_innodb_memcache |+---------------------------+| cache_policies            || config_options            || containers                |+---------------------------+

Cache_policies defines the cache policy, which includes the following options:

• Innodb_only: Only InnoDB is used for data storage.

• Cache-only: only the traditional Memcached engine is used as the backend storage.

• Caching: both are used. If you cannot find it in Memcached, query InnoDB.

Config_options defines the separator:

• Separator: Memcached only recognizes single values. This separator (|) is used to connect values of multiple fields.

• Table_map_delimiter: use this separator (.) to confirm the table and key, for example, @ table. key.

If you want to access a table through the Memcached protocol, you need to configure it in containers:

mysql> SELECT * FROM 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: c5unique_idx_name_on_key: PRIMARY

As shown above, the demo_test table of the test database is available and the c2 value is queried through c1. The table structure is as follows:

mysql> DESC test.demo_test;+-------+---------------------+------+-----+---------+-------+| Field | Type                | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| c1    | varchar(32)         | NO   | PRI |         |       || c2    | varchar(1024)       | YES  |     | NULL    |       || c3    | int(11)             | YES  |     | NULL    |       || c4    | bigint(20) unsigned | YES  |     | NULL    |       || c5    | int(11)             | YES  |     | NULL    |       |+-------+---------------------+------+-----+---------+-------+

By default, there is a row of data:

mysql> SELECT * FROM test.demo_test;+----+--------------+------+------+------+| c1 | c2           | c3   | c4   | c5   |+----+--------------+------+------+------+| AA | HELLO, HELLO |    8 |    0 |    0 |+----+--------------+------+------+------+

Let's use the Memcached protocol for access:

shell> echo "get @@aaa.AA" | nc localhost 11211VALUE @@aaa.AA 8 12HELLO, HELLOEND

We can also set the default table to be accessed, and then we can write only the key name for subsequent queries:

shell> (echo "get @@aaa"; echo "get AA") | nc localhost 11211VALUE @@aaa 0 14test/demo_testENDVALUE AA 8 12HELLO, HELLOEND

Although my examples are all executed through command lines, it is easy for everyone to change the methods such as PHP.

Restrictions

The Memcached plug-in is very simple to use, but not everything is perfect. For example, when we configure a table, fields in the containers table, except key_columns and value_columns, include: flags, cas_column, and expire_time_column must also be set. However, in many cases, we cannot find the appropriate fields in the original table. At this time, we can only create three new fields, which is disgusting.

In addition, the containers table has the following restrictions:

• The key_columns field must be of the CHAR or VARCHAR type and have a maximum length of 250 characters.

• The value_columns field must be of the CHAR, VARCHAR, or BLOB type and has no length restrictions.

• The cas_column field must be of the BIGINT type.

• The expiration_time_column field must be of the INT type.

• The flags field must be of the INT type.

Note: As the MySQL version is updated, these restrictions may change. Please refer to the actual situation.

Practice

Let's take a user login example to test the learning results:

First, create a user table in the test database:

USE `test`CREATE TABLE `users` (    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,    `username` VARCHAR(15) NOT NULL,    `password` VARCHAR(32) NOT NULL,    `email` text NOT NULL,    `flags` INT(10) UNSIGNED DEFAULT '0',    `cas_column` BIGINT(20) UNSIGNED DEFAULT '0',    `expire_time_column` INT(10) UNSIGNED DEFAULT '0',    PRIMARY KEY (`id`),    UNIQUE KEY `username` (`username`)) ENGINE=InnoDB;

Then add several lines of test data:

INSERT INTO `users` (`username`, `password`, `email`)VALUES('foo', 'ffffffffffffffffffffffffffffffff', 'foo@domain.com'),('bar', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', 'bar@domain.com');

Configure the table in containers:

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', 'users', 'username', 'password|email',    'flags', 'cas_column', 'expire_time_column', 'username');

Here, we define multiple fields (password and email) as value_columns and use vertical bars as separators. In fact, separators such as spaces and commas can also be used, the following definition of delimiters can be found in the source code of the innodb_config.c file, which is not covered in this document:

static const char* sep = " ;,|\n";

Finally, use the Memcached protocol for access. Here we change the pattern to execute an MGET operation:

shell> echo "get foo bar" | nc localhost 11211VALUE foo 0 47ffffffffffffffffffffffffffffffff|foo@domain.comVALUE bar 0 47bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb|bar@domain.comEND

Since multiple fields are set when value_columns is defined, data of multiple fields is naturally returned when data is returned, and they are separated by the separator field in the innodb_memcache.config_options table, by default, it is a vertical line. If the content of your field contains a vertical line, it will conflict with the default value. In this case, you can update the definition of separator, such as changing it to three vertical lines, please note that do not forget to restart the Memcached plug-in after modification.

Note: Because we name the table as default during configuration, we do not need to pass the table name during the request.

...

When using the Memcached plug-in this article, all examples Use read operations. In fact, write operations are also supported, but in actual use, I prefer to execute all write operations through SQL, while the Memcached plug-in only processes read operations in the KV format. The actual stress test results show that Memcached plug-in is better than SQL, performance has improved by about 100%, although it is still inferior to the independent Memcached, but considering other advantages, this result is very gratifying. Next stop, the Memcached plug-in of InnoDB! Make preparations.

Original article address: Let's take a look at InnoDB's Memcached plug-in. Thank you for sharing it with me.