Using UDF in mysql to automatically synchronize memcached efficiency notes

Next article: mysql uses mysql-udf-http Efficiency Test notes. Instead of using the rest architecture, this time it uses libmemcached and memcached_functions_mysql. The test version is:

Libmemcached-0.34.tar.gzand memcached_functions_mysql-0.9.tar.gz have problems with matching of other versions. I have installed and tested the versions with problems:

Copy codeThe Code is as follows: memcached_functions_mysql-1.1 in:
Libmemcached-0.49 \ libmemcached-0.48 \ libmemcached-0.47 \ libmemcached-0.30 \ libmemcached-0.43 \ libmemcached-0.42 \
Installation Error
Memcached_functions_mysql-0.10 in:
An error occurred while installing libmemcached-0.42 \
Memcached_functions_mysql-0.8 in:
Libmemcached-0.49 \ libmemcached-0.48 \ libmemcached-0.47 \ libmemcached-0.44 \ libmemcached-0.43 \
\ Libmemcached-0.42 \ Installation Error

MySQL test version: 5.1.55, Centos5.4 64bit, 2 GB memory

Install libmemcached-0.34 and memcached_functions_mysql-0.9,Copy codeThe Code is as follows: [root @ sunss24 libmemcached-0.34] #./configure \
-- With-memcached =/home/memcache/bin/memcached
[Root @ sunss24 libmemcached-0. 34] # make
[Root @ sunss24 libmemcached-0. 34] # make install
Run memstat again.
[Root @ sunss24 ~] # Ln-s/usr/local/lib/libmemcached. so.3/usr/lib/
[Root @ sunss24 ~] # Cd memcached_functions_mysql-0.9
[Root @ sunss24 memcached_functions_mysql-0.9] #./configure \
-- With-mysql =/usr/local/mysql/bin/mysql_config \
-- Libdir =/usr/local/mysql/lib/
[Root @ sunss memcached_functions_mysql-0.9] # make & make install

After the installation is complete, load the UDFs into MySQL:Copy codeThe Code is as follows: mysql> show variables like "% plugin % ";
+ --------------- + ----------------------------------- +
| Variable_name | Value |
+ --------------- + ----------------------------------- +
| Plugin_dir |/usr/local/mysql/lib/mysql/plugin |
+ --------------- + ----------------------------------- +
1 row in set (0.00 sec)

[Root @ sunss ~] # Find/-name "libmemcached_functions_mysql.so"
/Usr/local/mysql/lib/libmemcached_functions_mysql.so
// Root/memcached_functions_mysql-0.9/src/. libs/libmemcached_functions_mysql.so
You have new mail in/var/spool/mail/root
[Root @ sunss ~] # Cp/usr/local/mysql/lib/libmemcached_functions_mysql.so/usr/local/mysql/lib/mysql/plugin/
[Root @ sunss ~] # Cd memcached_functions_mysql-0.9/
[Root @ sunss ~] # Cd SQL/
Mysql> source install_functions. SQL;

View various versions:Copy codeThe Code is as follows: mysql> select memc_udf_version ();
+ -------------------- +
| Memc_udf_version () |
+ -------------------- +
| 1, 0.9 |
+ -------------------- +
1 row in set (0.00 sec)

Mysql> select memc_libmemcached_version ();
+ ----------------------------- +
| Memc_libmemcached_version () |
+ ----------------------------- +
| 1, 0.34 |
+ ----------------------------- +
1 row in set (0.00 sec)

Mysql>

Problems:Copy codeThe Code is as follows: No package 'libmemcached' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
Installed software in a non-standard prefix.

Alternatively, you may set the environment variables DEPS_CFLAGS
And DEPS_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
Solution:
[Root @ sunss24 memcached_functions_mysql-0.9] # whereis pkgconfig
[Root @ sunss24 memcached_functions_mysql-0.9] # export \
PKG_CONFIG_PATH =/usr/local/lib/pkgconfig:/usr/lib/pkgconfig

Export all memcache content:
Use: memcached-hack.zip
Efficiency Test:
Insert:Copy codeThe Code is as follows: <? Php
Include_once ("gettime. php ");
$ Btime = getmicrotime ();
$ I = 0;
$ Mem = new Memcache ();
$ Mem-> addServer ('192. 168.0.10 ', 192 );
$ Local_db = mysql_connect ("192.168.0.208", "sunss", "123456 ");
If (! $ Local_db)
{
Die ('could not connect: '. mysql_error ());
}
$ Local_db_sel = mysql_select_db ("test", $ local_db );
Mysql_query ("set names utf8", $ local_db );
While ($ I <1000 ){
$ Re_ SQL = "insert into urls (id, url) values ($ I, 'www .gongchang.com ')";
$ Res = mysql_query ($ re_ SQL, $ local_db );
$ I ++;
}
Mysql_close ($ local_db );
$ Etime = getmicrotime ();
$ RunTime = round ($ etime-$ btime, 4 );
Echo "runTime:". $ runTime. "\ r \ n ";
?>

1000, insert time: runTime: 1.4072
Delete:Copy codeThe Code is as follows: <? Php
Include_once ("gettime. php ");
$ Btime = getmicrotime ();
$ I = 0;
$ Mem = new Memcache ();
$ Mem-> addServer ('192. 168.0.10 ', 192 );
$ Local_db = mysql_connect ("192.168.0.208", "sunss", "123456 ");
If (! $ Local_db)
{
Die ('could not connect: '. mysql_error ());
}
$ Local_db_sel = mysql_select_db ("test", $ local_db );
Mysql_query ("set names utf8", $ local_db );
While ($ I <1000 ){
// $ Re_ SQL = "insert into urls (id, url) values ($ I, 'www .gongchang.com ')";
$ Re_ SQL = "delete from urls where id =". $ I;
// Echo "re_ SQL _1:". $ re_ SQL. "\ n ";
$ Res = mysql_query ($ re_ SQL, $ local_db );
$ I ++;
}
Mysql_close ($ local_db );
$ Etime = getmicrotime ();
$ RunTime = round ($ etime-$ btime, 4 );
Echo "runTime:". $ runTime. "\ r \ n ";
?>

Delete 1000 entries. Run Time: runTime: 1.5534
Not updated
Conclusion: There are about 650 queries per second, which is much faster than the last mysql-udf-http query.