memcached与mysql以及MYSQL CLUSTER的NDB_engine(一)

2012 年 12 月 19 日2,6882

本站主要内容均为原创,转帖需注明出处www.alexclouds.net

        Memcached是用来加速的,在其开发过程中,也同时支持MYSQL指令。我们很清楚,从MySQL本身出发,单台MySQL的数据库每天上亿次的操作,平均到每秒大概上千次本身是没有问题的。按理来说,前段WEB+后端MYSQL应该单机能够支持千万级的PV。但是通常情况下那些论坛的WEB服务器大都是前面SQUID缓存,为什么会这样,难道这是缓存还是王道??

这也许和WEB架构和数据库优化本身有很大的关系,WEB的PV在百万级的时候,很有可能MYSQL已经不堪重负了。MySQL官方当然是建议这种情况下使用memcache或者使用cluster。利用Memcache是可以达到减轻MySQL服务器负载的目的的,这点已经有无数实践了,且通常简单的是最有效的。

要说mysql CLUSTER的NDB就还是需要先介绍 memcached for mysql

因为MYSQL CLUSTER实现有MEMCACHE有两种方式,一种是MEMCACHED直接调用MYSQL,还有一种是使用使用NDB_ENGINE。

Memcache_ndb

 

篇幅原因,我们只能在这里先叙述MEMACHED和MYSQL共用的方式, 以及调试方法。从这里应该可以了解到MEMCACHE和MYSQL的数据同步方式喽。。。。
NDB的事情下面几篇里再说起。
memcached MySQL User-Defined Functions (UDFs)可以让你使用SQL的界面去取值和插值,当然在5.0以后的版本中。

好处如下:

  • You can update and retrieve cached items directly from within SQL scripts, stored procedures, and triggers. For example, you might already have triggers in place to increment counters and set status flags based on general database activity.

  • You can pass information back and forth with other applications that use memcached, without adding database connection and query code to them.

  • You can avoid installing and configuring a memcached client on every combination of language and server that you use for your MySQL applications. The applications can relay memcached requests through the database server they connect to.

  • You can access memcached servers from languages that do not have a memcached client.

安装 MySQL memcached UDFs, download the UDF package from https://launchpad.net/memcached-udfs. Unpack the package and run configure to configure the build process. When running configure, use the --with-mysql option and specify the location of the mysql_config command.

 

MySQL UDFs (user defined functions)的工作方式就是使用 libmemcached和  memcacheD一起工作。

This is a set of MySQL UDFs (user defined functions) 包含下面一些功能:

memc_servers_set()
memc_set(), memc_set_by_key()
memc_cas(), memc_cas_by_key(),
memc_get(), memc_get_by_key()
memc_get_cas(), memc_get_cas_by_key(),
memc_delete(), memc_delete_by_key()
memc_append(), memc_append_by_key()
memc_prepend(), memc_prepend_by_key()
memc_increment(),
memc_decrement()
memc_replace(), memc_replace_by_key(),
memc_behavior_set(), memc_behavior_get(),
memc_behaviors_list(), memc_server_count(),
memc_udf_version(), memc_version()

 首先安装libmemcached,下载源码安装也可以,否则就用yum install libmem*,安装libmem和DEV包。

[root@lvs1 memcached_functions_mysql-1.1]# yum install libmem*
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirror.bit.edu.cn
* extras: mirror.bit.edu.cn
* updates: mirror.bit.edu.cn
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package libmemcached.x86_64 0:0.31-1.1.el6 will be installed
---> Package libmemcached-devel.x86_64 0:0.31-1.1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
Package                                Arch                       Version                           Repository                Size
====================================================================================================================================
Installing:
libmemcached                           x86_64                     0.31-1.1.el6                      base                      80 k
libmemcached-devel                     x86_64                     0.31-1.1.el6                      base                     116 k

Transaction Summary
====================================================================================================================================
Install       2 Package(s)

。。。。。。。。。。。。。。。。。。。。。。。。。。。。

Installed:
  libmemcached.x86_64 0:0.31-1.1.el6                            libmemcached-devel.x86_64 0:0.31-1.1.el6                

 

接着安装UDFS memcached_functions_mysql-1.1.tar.gz

shell> tar zxf memcached_functions_mysql-1.1.tar.gz
shell> cd memcached_functions_mysql-1.1
shell> # If memcached library is not found, set LDFLAGS=-Llibrary_directory before next command.
shell> ./configure --with-mysql=/usr/local/mysql/bin/mysql_config

Now build and install the functions:

shell> make
shell> make install

Copy the MySQL memcached UDFs into your MySQL plugins directory:

shell> cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql/lib/mysql/plugins/
 MySQL plugins directory是在一个plugin_dir system variable变量里,在MYSQL里面查询变量是可以看到的。

安装好后,需要初始化MYSQL UDF,需要制定返回值和库文件,

Once installed, you must initialize the function within MySQL using CREATE and specifying the return value and library. For example, to add the memc_get() function:

mysql> CREATE FUNCTION memc_get RETURNS STRING SONAME "libmemcached_functions_mysql.so";
下面是需要安装的所有标准功能。。。好多吧?

MySQL memcached UDF Function
Equivalent Standard Function

memc_get()
Generic get().

memc_get_by_key(master_key, key, value)
Like the generic get(), but uses the supplied master key to select the server to use.

memc_set()
Generic set().

memc_set_by_key(master_key, key, value)
Like the generic set(), but uses the supplied master key to select the server to use.

memc_add()
Generic add().

memc_add_by_key(master_key, key, value)
Like the generic add(), but uses the supplied master key to select the server to use.

memc_replace()
Generic replace().

memc_replace_by_key(master_key, key, value)
Like the generic replace(), but uses the supplied master key to select the server to use.

memc_prepend(key, value)
Prepend the specified value to the current value of the specified key.

memc_prepend_by_key(master_key, key, value)
Prepend the specified value to the current value of the specified key, but uses the supplied master key to select the server to use.

memc_append(key, value)
Append the specified value to the current value of the specified key.

memc_append_by_key(master_key, key, value)
Append the specified value to the current value of the specified key, but uses the supplied master key to select the server to use.

memc_delete()
Generic delete().

memc_delete_by_key(master_key, key, value)
Like the generic delete(), but uses the supplied master key to select the server to use.

memc_increment()
Generic incr().

memc_decrement()
Generic decr().

除了标准功能之外还有附加功能

The memcached UDFs include some additional functions:

  • memc_server_count()

    Returns a count of the number of servers in the list of registered servers.

  • memc_servers_set_behavior(behavior_type, value), memc_set_behavior(behavior_type, value)

    Sets behaviors for the list of servers. These behaviors are identical to those provided by the libmemcachedlibrary. For more information on libmemcached behaviors, see Section 15.6.3.3, “Using libmemcached with C and C++”.

    You can use the behavior name as the behavior_type:

    mysql> SELECT memc_servers_behavior_set("MEMCACHED_BEHAVIOR_KETAMA",1);
  • memc_servers_behavior_get(behavior_type), memc_get_behavior(behavior_type, value)

    Returns the value for a given behavior.

  • memc_list_behaviors()

    Returns a list of the known behaviors.

  • memc_list_hash_types()

    Returns a list of the supported key-hashing algorithms.

  • memc_list_distribution_types()

    Returns a list of the supported distribution types to be used when selecting a server to use when storing a particular key.

  • memc_libmemcached_version()

    Returns the version of the libmemcached library.

  • memc_stats()

    Returns the general statistics information from the server.

The list of servers used by the memcached UDFs is not persistent over restarts of the MySQL server. If the MySQL server fails, then you must re-set the list of memcached servers.

 

除非你愿意一个功能一个功能的加,否则还有更加简便的办法,使用如下指令

To simplify the process, use the SQL script provided in the memcached UDFs package:

shell> mysql <sql/install_functions.sql

Alternatively, if you have Perl installed, then you can use the supplied Perl script, which checks for the existence of each function and creates the function/library association if it is not already defined:

shell> utils/install.pl --silent

The --silent option installs everything automatically. Without this option, the script asks whether to install each of the available functions.

The interface remains consistent with the other APIs and interfaces. To set up a list of servers, use thememc_servers_set() function, which accepts a single string containing and comma-separated list of servers:

 

  安装成功后SHOW一下: 

mysql> select memc_list_behaviors()\G;                                    
*************************** 1. row ***************************
memc_list_behaviors():
MEMCACHED SERVER BEHAVIORS
MEMCACHED_BEHAVIOR_SUPPORT_CAS
MEMCACHED_BEHAVIOR_NO_BLOCK
MEMCACHED_BEHAVIOR_TCP_NODELAY
MEMCACHED_BEHAVIOR_HASH
MEMCACHED_BEHAVIOR_CACHE_LOOKUPS
MEMCACHED_BEHAVIOR_SOCKET_SEND_SIZE
MEMCACHED_BEHAVIOR_SOCKET_RECV_SIZE
MEMCACHED_BEHAVIOR_BUFFER_REQUESTS
MEMCACHED_BEHAVIOR_KETAMA
MEMCACHED_BEHAVIOR_POLL_TIMEOUT
MEMCACHED_BEHAVIOR_RETRY_TIMEOUT
MEMCACHED_BEHAVIOR_DISTRIBUTION
MEMCACHED_BEHAVIOR_BUFFER_REQUESTS
MEMCACHED_BEHAVIOR_USER_DATA
MEMCACHED_BEHAVIOR_SORT_HOSTS
MEMCACHED_BEHAVIOR_VERIFY_KEY
MEMCACHED_BEHAVIOR_CONNECT_TIMEOUT
MEMCACHED_BEHAVIOR_KETAMA_WEIGHTED
MEMCACHED_BEHAVIOR_KETAMA_HASH
MEMCACHED_BEHAVIOR_BINARY_PROTOCOL
MEMCACHED_BEHAVIOR_SND_TIMEOUT
MEMCACHED_BEHAVIOR_RCV_TIMEOUT
MEMCACHED_BEHAVIOR_SERVER_FAILURE_LIMIT
MEMCACHED_BEHAVIOR_IO_MSG_WATERMARK
MEMCACHED_BEHAVIOR_IO_BYTES_WATERMARK
MEMCACHED_HASH_DEFAULT
MEMCACHED_HASH_MD5
MEMCA
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

 

Memcache-mysql

 

接着需要设定跑MEMCACHE的SERVER值和端口号,有多台就设置多台。

mysql> SELECT memc_servers_set('192.168.10.208:11211,192.168.10.209:11211');

The respective *_by_key() functions are useful to store a specific value into a specific memcached server, possibly based on a differently calculated or constructed key.

需要设定NO_BLOCK和TCP_NODELAY值

mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
+--------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select memc_server_count();
+---------------------+
| memc_server_count() |
+---------------------+
|                  2|
+---------------------+
1 row in set (0.00 sec)

mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
+--------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');
+-----------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

0 0