Using UDF in mysql to automatically synchronize memcached efficiency notes memcache
BitsCN.com 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:
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 the libmemcached-0.42/below
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 under error
MySQL test version: 5.1.55, Centos5.4 64bit, 2 GB memory
Install libmemcached-0.34 and memcached_functions_mysql-0.9,
[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:
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:
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:
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:
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:
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: The number of queries per second is about 650, which is faster than the previous mysql-udf-http bitsCN.com.