Synchronize memcache with mysql Databases

1: mysqlmemcachedUDF uses libmemcached to use a series of memcache functions. Through these functions, you can perform get, set, cas, append, prepend, delete, increment, decrementobjects operations on memcache, if we use these functions through mysqltrigger, we can use mysql

1: mysql memcached UDF uses libmemcached to use a series of memcache functions. Through these functions, you can get, set, cas, append, prepend, delete, increment, decrement objects operation. If we use these functions through mysql trigger, we can use mysql

1: mysql memcached UDF uses libmemcached to use a series of memcache functions. Through these functions, you can get, set, cas, append, prepend, delete, increment, decrement objects operations. If we use these functions through mysql trigger, we can use mysql to better and automatically manage memcache!

2: Installation Method:

1) install memcache

2) install libmemcached

Shell> cd libmemcached-0.35
Shell>./configure -- with-memcached =/usr/local/bin/memcached
Shell> make & make install
Shell> echo "/usr/local/lib">/etc/ld. so. conf
Shell> ldconfig

3) install memcached_functions_mysql
Shell> tar zxvf memcached_functions_mysql-0.9.tar.gz
Shell> cd memcached_functions_mysql-0.9
Shell>./configure -- with-mysql =/usr/local/mysql51/bin/mysql_config -- with-libmemcached =/usr/local/
Shell> make & make install

4) copy the lib file to the mysql plugin.
Shell> cp-R/usr/local/lib/libmemcached_functions_mysql. */usr/local/mysql51/lib/mysql/plugin/

5) Add the memcache UDF Function

Run source install_functions. SQL in mysql

In this way, we can use the mysql memcached UDF. We can use the following statement to check whether it has been properly installed.

1) Check mysql. func. There are many functions

Mysql> select * from mysql. func;
+ ------------------------------ + ----- + --------------------------------- + ---------- +
| Name | ret | dl | type |
+ ------------------------------ + ----- + --------------------------------- + ---------- +
| Memc_add | 2 | libmemcached_functions_mysql.so | function |
| Memc_add_by_key | 2 | libmemcached_functions_mysql.so | function |
| Memc_servers_set | 2 | libmemcached_functions_mysql.so | function |

2) Add a trigger to check whether or not to insert or update memcache.

For specific statements, refer:

1) trigger_fun. SQL under the memcached_functions_mysql-0.9/SQL directory

2) Reference document: http://dev.mysql.com/doc/refman/5.1/en/ha-memcached-interfaces-mysqludf.html

Note the following points:

1) do not include the '-- with-mysqld-ldflags =-all-static' parameter during mysql compilation, because this restricts the dynamic installation function of mysql.

2) When using the service, observe the mysql. err log. If you do not know whether it is intentional or unintentional, the udf update memcache will be recorded in the err log. Be sure to clear the log; otherwise, it will be full.

3) the mysql official website has the following sentence:

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

Therefore, when we restart mysql, we must use the select memc_servers_set ('192. 168.0.1: 192 .0.2: 100') statement to re-register the memcache server!

2. Create mysql insert, update, delete trigger update memcache, refer to http://blog.csdn.net/jiedushi/article/details/6176940

The test environment is run in Linux. The version system is CentOS5.
The following are the related software, including its version and:

Download mysql-5.1.30
Download memcached-1.2.6
Download libevent-1.4.7-stable
Download memcached_functions_mysql-1.1
Download libmemcached-0.26

Compile and install MySQL. The installation depends on your individual requirements. Many compilation details and parameters irrelevant to the test are omitted.

[C]View plaincopyprint?

1. [Root @ localhost ~] # Tar xzf mysql-5.1.30.tar_001.gz
2. [Root @ localhost ~] # Cd mysql-5.1.30
3. [Root @ localhost ~] #./Configure -- prefix =/usr/local/mysql51
4. [Root @ localhost ~] # Make
5. [Root @ localhost ~] # Make install
6. [Root @ localhost ~] #./Scripts/mysql_install_db -- user = mysql -- skip-name-resolve
7. [Root @ localhost ~] #/Usr/local/mysql51/bin/mysqld_safe

The commands for installing memcached and libevent are omitted. Install libevent by default and install memcached in the/usr/local/memcached directory.
Start memcached.

[C]View plaincopyprint?

1. /Usr/local/memcached/bin/memcached-d-m 50-u root-p 11211

Compile and install libmemcache.

[C]View plaincopyprint?

1. [Root @ localhost ~] # Tar xzf libmemcached-0.26.tar.gz
2. [Root @ localhost ~] # Cd libmemcached-0.26
3. [Root @ localhost ~] #./Configure -- with-memcached =/usr/local/memcached/bin/memcached
4. [Root @ localhost ~] # Make & make install

Compile and install Memcache UDFs for MySQL.

[C]View plaincopyprint?

1. [Root @ localhost ~] # Tar xzf memcached_functions_mysql-1.1.tar.gz
2. [Root @ localhost ~] # Cd memcached_functions_mysql-1.1
3. [Root @ localhost ~] #./Configure -- with-mysql-config =/usr/local/mysql51/bin/mysql_config
4. [Root @ localhost ~] # Make & make install

After compilation, copy the compiled library file to the mysql plug-in directory to facilitate loading and usage.

[C]View plaincopyprint?

1. Cp/usr/local/lib/libmemcached_functions_mysql */usr/local/mysql51/lib/mysql/plugin/

Go to the source code directory of memcached_functions_mysql. There is an SQL file for UDF initialization.

Run SQL/install_functions. SQL IN THE memcached_functions_mysql source code directory in the mysql shell.
Or run the perl script utils/install. pl under the memcached_functions_mysql source code directory and add memcache function as a UDF to mysql.

[Root @ localhost ~] # Mysql

Check whether the installation is successful

Mysql> select name, dl from mysql. func;

Ii. Test Case Design:

(1) create two tables: urls and results to update the content in the urls table and let the system automatically update the memcached content. Results is used to record the memcached failure update records.
The SQL statement is as follows:
Use tests;
Drop table if exists urls;
Create table 'urls '(
'Id' int (10) not null,
'Url' varchar (255) not null default '',
Primary key ('id ')
);

Drop table if exists results;
Create table 'results '(
'Id' int (10) not null,
'Result' varchar (255) not null default 'error ',
'Time' timestamp null default CURRENT_TIMESTAMP,
Primary key ('id ')
);

(2). Create Three triggers.

When inserting data into the urls table, perform the set operation on memcached. The trigger is as follows:
DELIMITER //
Drop trigger if exists url_mem_insert;
Create trigger url_mem_insert
Before insert on urls
FOR EACH ROW BEGIN
Set @ mm = memc_set (NEW. id, NEW. url );
If @ mm <> 0 then
Insert into results (id) values (NEW. id );
End if;

END //
DELIMITER;

When updating the data in the urls table, perform the replace operation on memcached. The trigger is as follows:

DELIMITER //
Drop trigger if exists url_mem_update;
Create trigger url_mem_update
Before update on urls
FOR EACH ROW BEGIN
Set @ mm = memc_replace (OLD. id, NEW. url );
If @ mm <> 0 then
Insert into results (id) values (OLD. id );
End if;

END //
DELIMITER;

When you delete the data in the urls table, perform the delete operation on memcached. The trigger is as follows:

DELIMITER //
Drop trigger if exists url_mem_delete;
Create trigger url_mem_delete
Before delete on urls
FOR EACH ROW BEGIN
Set @ mm = memc_delete (OLD. ID );
If @ mm <> 0 then
Insert into results (id) values (OLD. id );
End if;

END //
DELIMITER;

(3). Set memcached Parameters

Set the IP address and port of the memcaced machine simultaneously operated by the UDF:

Mysql> SELECT memc_servers_set ('192. 168.3.184: 100 ');
+ --------------------------------------- +
| Memc_servers_set ('192. 168.3.184: 100') |
+ --------------------------------------- +
| 0 |
+ --------------------------------------- +
1 row in set (0.00 sec)

Mysql> select memc_server_count ();
+ --------------------- +
| Memc_server_count () |
+ --------------------- +
| 1 |
+ --------------------- +
1 row in set (0.00 sec)

List the behaviors that can modify the memcached parameter on the mysql command line:
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

1 row in set (0.00 sec)

Set MEMCACHED_BEHAVIOR_NO_BLOCK to open, so that when memcached encounters a problem (when the connection fails)
When the data is inserted into mysql, an error is returned. If this value is not set, mysql needs to wait until timeout if memcached fails.
Can be inserted into the table.
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)

Iii. Simple Function Testing:

1. Insert data into the table urls and check whether memcached is also set into the data:

Mysql> insert into urls (id, url) values (1, 'HTTP: // www.sina.com.cn ');
Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql> select memc_get ('1 ');
+ ------------------------ +
| Memc_get ('1') |
+ ------------------------ +
| Http://www.sina.com.cn |
+ ------------------------ +
1 row in set (0.00 sec)

1> telnet 192.168.3.184 11900
Trying 192.168.3.184...
Connected to 192.168.3.184 (192.168.3.184 ).
Escape character is '^]'.
Get 1
VALUE 1 0 22
Http://www.sina.com.cn
END

2. Update the data in the table urls and query whether memcached is updated as well:
Mysql> update test. urls set url = 'HTTP: // blog.sina.com.cn 'where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Mysql> select memc_replace ('1', 'HTTP: // blog.sina.com.cn ');
+ --------------------------------------------- +
| Memc_replace ('1', 'HTTP: // blog.sina.com.cn ') |
+ --------------------------------------------- +
| 0 |
+ --------------------------------------------- +
1 row in set (0.00 sec)

Mysql> select memc_get ('1 ');
+ ------------------------- +
| Memc_get ('1') |
+ ------------------------- +
| Http://blog.sina.com.cn |
+ ------------------------- +
1 row in set (0.00 sec)

1> telnet 192.168.3.184 11900
Trying 192.168.3.184...
Connected to 192.168.3.184 (192.168.3.184 ).
Escape character is '^]'.
Get 1
VALUE 1 0 23
Http://blog.sina.com.cn
END

3. Delete the data in the table urls and check whether memcached is also deleted:
Mysql> delete from test. urls where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Mysql> select memc_get ('1 ');
+ --------------- +
| Memc_get ('1') |
+ --------------- +
| NULL |
+ --------------- +
1 row in set (0.00 sec)

1> telnet 192.168.3.184 11900
Trying 192.168.3.184...
Connected to 192.168.3.184 (192.168.3.184 ).
Escape character is '^]'.
Get 1
END

4. Use the php script insert, update, and delete data in the urls table for testing.

The average length of each record is 17 K.

It takes 75 seconds to insert 0.1 million records into the table urls separately.
It takes 70 seconds to update 0.1 million records in the table urls separately.
It takes 0.1 million seconds to delete 105 records in a table's urls.

Insert, update, and delete operations of 0.3 million data at the same time take 241 seconds

No memcached failure occurs in the above operations:
Mysql> select * from results;
Empty set (0.00 sec)

The test script is as follows:

Insert script:
0> more a. php

$ Conn = mysql_connect ("192.168.1.61", "test", "test") or die (mysql_error ());

Mysql_select_db ("test", $ conn) or die (mysql_error ());

// $ SQL = "show tables ";

Echo date ("Y-m-d H: I: s ");
// Mysql_query ($ SQL) or die (mysql_error ());

For ($ I = 1; $ I <= 100000; $ I ++ ){
$ SQL = "insert into urls (id, url) values ($ I, 'HTTP: // $ I $ I $ I .com '); ";
Mysql_query ($ SQL) or die (mysql_error ());
}

Echo "/n ";
Echo date ("Y-m-d H: I: s ");

?>

Update script:
0> more B. php

$ Conn = mysql_connect ("192.168.1.61", "test", "test") or die (mysql_error ());

Mysql_select_db ("test", $ conn) or die (mysql_error ());

// $ SQL = "show tables ";

Echo date ("Y-m-d H: I: s ");
// Mysql_query ($ SQL) or die (mysql_error ());

For ($ I = 1; $ I <= 100000; $ I ++ ){
$ SQL = "update test. urls set url = 'HTTP: // xxxx. $ I $ I $ I .com 'where id = $ I; ";
Mysql_query ($ SQL) or die (mysql_error ());
}

Echo "/n ";
Echo date ("Y-m-d H: I: s ");

?>

Delete script:
0> more c. php

$ Conn = mysql_connect ("192.168.1.61", "test", "test") or die (mysql_error ());

Mysql_select_db ("test", $ conn) or die (mysql_error ());

// $ SQL = "show tables ";

Echo date ("Y-m-d H: I: s ");
// Mysql_query ($ SQL) or die (mysql_error ());

For ($ I = 1; $ I <= 100000; $ I ++ ){
$ SQL = "delete from test. urls where id = $ I ;";
Mysql_query ($ SQL) or die (mysql_error ());
}

Echo "/n ";
Echo date ("Y-m-d H: I: s ");

?>

V. Conclusion:
The test depends on a large number of environments, and the data may be inaccurate. Overall, the speed is good.
Suitable for scenarios with relatively small project applications.

Advantages:
Using UDFs in triggers to directly update Memcached content reduces the complexity of Application Design and writing.

Disadvantages:
1. If the mysql service restarts, You need to reset the memcached connection relationship (SELECT memc_servers_set ('192. 168.3.184: 100 '))
2. A bug may occur, resulting in mysql crash (not encountered during testing :)).

The real online environment is much more complicated than this one. I think of the following issues to consider:
1. Network factors: whether mysql and memcached are deployed in the same IDC, and whether the network performance between them is good. The better the network performance, the faster the speed. If memcached of the local machine is used, the network overhead can be appropriately reduced.
2. The inserted data volume, the size of each record inserted into mysql, and the size of the Data updated to memcached. The larger the data size of mysql and memcached, the slower the update speed.
Therefore, the two columns (key-value) in memcached are the key to the preliminary plan.
3. latency issues need to be considered. If the resources on the machine where mysql is located are used, it will lead to slow update of memcached (similar to the latency problem of m/s ).
4. consider the Disaster Tolerance problem. If there is a down problem between the two, you need to consider how to recover. The current test is like this: Create an error table. If there is a problem when updating mc, automatically insert update error records
In a table, you can check the table to know when the data is updated or incorrect. If it is applied to the production environment, you need to consider monitoring and recovery in case of problems (write a script to complete this job ).
5. mysql factors, such as the efficiency of mysql statements executed and the connection overhead of the client program connecting to mysql (php.

Reference

Http://blog.sina.com.cn/s/blog_499740cb0100g45p.html

Http://www.libing.name/2009/02/06/mysql-map-data-to-memcached.html