Mysql+memcached Key-Value database

Key value Database

Memory Centric Design

Memory centric design is a very popular architecture, which is used to solve the performance stress caused by the massive reading and writing of data in the web2.0 era.

Implementation ideas:

Increase buffer layer, take advantage of high I/O performance of memory

Processing large amounts of real-time data in memory in the form of Key_value

Reduce IO pressure on back-end databases with techniques such as read-ahead and merge writes

Key-value Database Introduction

Memcached and Redis

What is memcached?

Memcached is a high-performance distributed memory object caching system for dynamic Web applications to mitigate database load. He improves the speed of dynamic, database-driven Web sites by caching data and objects in memory to reduce the number of times a database is read.

Memcached is based on a hashmap that stores key/value pairs.

Web

|

Memcached

|

Mysql

memcached syntax

Memcached's server client Communicator uses a simple text-line-based protocol instead of using a format such as complex XML. Therefore, you can also use Telnet to save data on memcached to obtain data.

Memcached-d-C 10240-m 1024-u Root

247 Ps-ef|grep memcached

248 telnet 127.0.0.1 11211

249 Yum Install Telnet-y

11211 telnet 127.0.0.1

Mysql-memcached are usually not directly related to each other, and are determined by the application or middleware.

A new memcached plugin has been added to the mysql5.6, which makes it easy to implement a combination of MySQL and memcached. Allows us to operate the MSYQL database with memcached syntax, or we can use SQL syntax to manipulate memcached, which enables MySQL to support NoSQL

Memcached plugin:innodb--memcached, local cache

Implementation of memcached based on the official MySQL plugin

Installation:

If you use the official release RPM package installed by MySQL, then the memcached plugin has been shipped with the Mysql-server package

If you are installing MySQL in a source package, you will need to include the corresponding configuration item during the compilation process

In addition the system to install the Libevent package

Cmake-dwith_innodb_memcached=on

In terms of MySQL RPM packages or binary packages

1, first view the plug-in storage directory, and file

Mysql> SELECT @ @plugin_dir;

+------------------------------+

| @ @plugin_dir |

+------------------------------+

| /usr/local/mysql/lib/plugin/|

+------------------------------+

1 row in Set (0.00 sec)

Mysql> Ctrl-c--exit!

Aborted

[Email protected] ~]# ls/usr/local/mysql/lib/plugin/| grep memcached

Libmemcached.so

2, Import table structure

Next, import the table structure required by the memcached plug-in

[Email protected] ~]# Find/-name Innodb_memcached_config.sql

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

Mysql-h 127.0.0.1-p </usr/local/mysql/share/innodb_memcached_config.sql

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Innodb_memcache | ********

| MySQL |

| Performance_schema |

| T9 |

| Test |

| viewdb |

+--------------------+

Mysql> Use Innodb_memcache

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> Show tables;

+---------------------------+

| Tables_in_innodb_memcache |

+---------------------------+

| cache_policies |

| config_options |

| Containers |

+---------------------------+

3. Loading and downloading plugin commands

Then load the plugin in MySQL, use the install command to load or use uninstall to uninstall

If you need to restart the plugin, you need to uninstall and then install the

mysql> Install plugin daemon_memcached soname ' libmemcached.so ';

Query OK, 0 rows affected (0.25 sec)

Mysql>uninsall plugin daemon_memcached;

4. View plug-in configuration information

Mysql> Show variables like '%memcache% ';

+----------------------------------+------------------+

| variable_name | Value |

+----------------------------------+------------------+

| Daemon_memcached_enable_binlog |  OFF | Whether to record the data of the memcached operation in Binlog, the default is to close

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

5. Check the memcached status

[Email protected] ~]# PS Axu | grep memcached

Root 10783 0.0 0.0 331864 136? SSL Jan20 0:01 memcached-d-c 10240-m 1024-u Root

Root 12570 0.0 0.1 103244 836 pts/2 s+ 01:39 0:00 grep memcached

[Email protected] ~]# NETSTAT-TNLP | grep 11211

TCP 0 0 0.0.0.0:11211 0.0.0.0:* LISTEN 10783/memcached

TCP 0 0::: 11211:::* LISTEN 10783/memcached

6,innodb_memcache Library

After importing the table structure required by the plug-in, a library named Innodb_memcache is built in the database.

Cache_policies Defining buffering Policies |

| Config_options Defining Separators |

| Containers container

Cache_policies:

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)

Cache_police table

Cache_police defines the cache policy, which includes the following choices:

Innodb_only: Just use InnoDB as the data store.

Cache_only: Use only the traditional memcached engine as back-end storage,

Caching: Both are used, if found in the memcached, if not found, on the query InnoDB

mysql> Update cache_policies set get_policy= ' caching '; Check the cache first, if you're not checking InnoDB, and then cache

Query OK, 1 row affected (0.04 sec)

Rows matched:1 changed:1 warnings:0

Mysql> select * from Cache_policies;

+--------------+------------+-------------+---------------+--------------+

| Policy_name | Get_policy | Set_policy | Delete_policy | Flush_policy |

+--------------+------------+-------------+---------------+--------------+

| Cache_policy | caching | innodb_only | innodb_only | innodb_only |

+--------------+------------+-------------+---------------+--------------+

1 row in Set (0.00 sec)

Config_options:

Config_options defines the delimiter:

Separate:memcached only recognizes single values and uses this delimiter (|) to connect the values of fields

Table_map_delimiter: Use this delimiter (.) To confirm tables and keys, such as: @ @table. Key

Mysql> select * from Config_options;

+---------------------+-------+

| name | Value |

+---------------------+-------+

|     Separator | | | Field split symbol

|     Table_map_delimiter |.  | Table

+---------------------+-------+

Containers table

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 |

+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------- -----------------+

1 row in Set (0.00 sec)

memcached Syntax Introduction-parameters

Key to find cached values

Flags can include integer parameters for key-value pairs, which the client uses to store additional information about key-value pairs,

Expiration the length of time a key-value pair is saved in the cache

Bytes the number of bytes stored in the cache

Value stored by

Grammar:

Set is used to add a new key-value pair to the cache. If it already exists, replace the previous new.

Set UID 0 0 5

Add the Add command adds a key-value pair to the buffer only if the cache does not exist, and if the key already exists in the cache, the previous value remains the same and the activity not_stored

Get query key value pairs

Replace specifically updates existing key-value pairs

Delete for memcached lunch any existing value, if present in the cache, removes the value and returns Not_found if it does not exist

Advanced Syntax:

Gets returns more information,

CAS (check & set) is a very handy memcached command, with a set name,

Flush--all is used to clean up all names, value pairs in the cache (use caution, and delete the data in the database, just like delete)

Status state

Stats commands for tuning the use of the cache

Mysql>update cache_policies set flush_policy= ' cache_only '; When doing flush_all operations in memcached later, only the cache is manipulated, not the table data of the database

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1613253

Mysql+memcached Key-Value database