MySQL and memcached

转载 2007年10月08日 15:06:00
原贴:http://jan.kneschke.de/projects/mysql/udf/

UDF

MySQL and memcached

Get

Compile it with:

$ gcc -DDBUG_OFF -shared -o udf_memcache.so udf_memcache.c /
   -g -Wall -O2 -lmemcache `mysql_config --cflags`

Copy udf_memcache.so into a directory which contains the your shared libs (e.g. /usr/local/lib).

Install the functions into MySQL by executing the create-function script:

$ mysql < create-function-memcache.sql

UDF_LUA

You are tired of writing your UDFs for MySQL in C or never wanted to write them in C at all ? How about writing them in lua ?

LUA is

and not to forget lua has extensions for everything:

  • LuaSocket - how about some HTTP requests ?
  • LuaSQL - connecting back to the MySQL Server ?
  • ...

UDF_Lua is registered in the MySQL Forge.

functions

As example we want to read the real startup time of the errorlog.

-- we are run with the permissions of the mysqld
--
-- let's try the read the "ready for connections" from the errorlog and look
-- for the last [Note]:
--
-- 061124 17:28:39 [Note] /usr/sbin/mysqld-max: ready for connections.

local f = assert(io.open(params[1], "r"))
local readysince = nil

while true do
        local line = f:read()

        if not line then break end

        local match = string.match(line, "^([0-9]+ [0-9:]+) %[Note%]")

        if match then
                readysince = match
        end
end
f:close()

return readysince

Let's execute it:

> select lua_file(".../ready-since.lua", "/var/lib/mysql/jan-dev.err") as ready_since;
+-----------------+
| ready_since     |
+-----------------+
| 061125 18:10:48 |
+-----------------+

Yeah, that matches the string from the errorlog.

Without a script-file

If shorter script you can also just pass the lua-code around. As usual a string is returned and the parameters are available via the params table.

]> select lua("return params[1]", 1) as echo;
+------+
| echo |
+------+
| 1    |
+------+
1 row in set (0.02 sec)

Aggregation

For Aggregation functions like SUM() you have to provide a function which is called for each row (mysql_udf_aggr) and one which returns the final result (mysql_udf_result).

_G["mysql_udf_aggr"] = function (params)
        if nil == rows then
                rows = 0
        end

        rows = rows + params[1]
end
_G["mysql_udf_result"] = function (params)
        return rows
end

Running it against the world-database:

root@localhost [world]> select lua_aggr_file("/home/jan/sum.lua", population) from City;
+------------------------------------------------+
| lua_aggr_file("/home/jan/sum.lua", population) |
+------------------------------------------------+
| 1429559884                                     |
+------------------------------------------------+
1 row in set (0.02 sec)

root@localhost [world]> select sum(population) from City;
+-----------------+
| sum(population) |
+-----------------+
|      1429559884 |
+-----------------+
1 row in set (0.01 sec)

Installation

Requirements:

  • lua 5.1

... and the usual UDF requirements

$ wget http://jan.kneschke.de/projects/mysql/udf/UDF-mysql_udf_lua-0.9.2.tar.gz
$ gzip -cd UDF-mysql_udf_lua-0.9.2.tar.gz | tar xf -
$ cd UDF-mysql_udf_lua-0.9.2
$ ./configure
$ make
## copy to a directory which is checked by dlopen()
# cp ./.libs/mysql_udf_lua.so /usr/lib/
$ mysql
mysql> create function lua returns string soname 'mysql_udf_lua.so';
mysql> create function lua_file returns string soname 'mysql_udf_lua.so';
mysql> create function lua_aggr_file returns string soname 'mysql_udf_lua.so';

Thanks to Hartmuts CodeGenMySQLUDF the UDF is packaged with automake/autoconf.

parameter handling

The parameters of the SELECT call are passed as a lua-table to the functions. The indexing for this table is starting a 1 as it is expected by lua-devs. The type between MySQL and LUA are mapped as expected

return value

Whatever you return is either nil or converted into a string. In case of a execution error NULL will be return and the error will be logged to the mysqld-error-log.

My MySQL Server sends Mail !

For the UDF talk in a few hours I wanted to have a nice and hopefully useless example. It should show how the internals work and what has to be setup even to those who never have used a C-Compiler.

I chose to implement a function to send mail directly from the database which I hope will never be used. Really. It is not a good idea to move application code into the database.

#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

my_bool mail_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
        initid->maybe_null = 0;

        return 0;
}

long long mail(UDF_INIT *initid, UDF_ARGS *args,
               char *is_null, char *error) {
        char *prg;
        FILE *p;
        int i;

        if (args->arg_count != 2) {
                strcpy(error, "MAIL() needs receipient and body");
                return 1;
        }

        if (args->arg_type[0] != STRING_RESULT ||
            args->arg_type[1] != STRING_RESULT) {
                strcpy(error, "MAIL() needs receipient and body as string");
                return 1;
        }

        for (i = 0; i < args->lengths[0]; i++) {
                char c = args->args[0][i];

                if (!((c >= 'a' && c <= 'z') ||
                      (c >= 'A' && c <= 'Z') ||
                      (c == '@') ||
                      (c == '.') ||
                      (c >= '0' && c <= '9') ||
                      (c == '-' ) ||
                      (c == '_' ))) {
                        strcpy(error, "the receipient contains a illegal char");

                        return -1;
                }
        }

        prg = malloc(strlen("/usr/lib/sendmail ") + args->lengths[0] + 1);
        strcpy(prg, "/usr/lib/sendmail ");
        strcat(prg, args->args[0]);


        p = popen(prg, "w");

        free(prg);

        if (NULL == p) {
                strcpy(error, "opening pipe failed");
                return -1;
        }


        fwrite(args->args[1], args->lengths[1], 1, p);
        fclose(p);


        *is_null = 0;
        *error = 0;

        return 0;
}

That's the code which has to be compiled with:

$ gcc -I /usr/local/mysql/include/ -shared -o udf_mail.so mail.c -Wall
$ cp udf_mail.so /usr/lib/

at mysql side:

mysql> create function mail returns integer soname 'udf_mail.so';
mysql> select mail('jan@kneschke.de',
           'Subject: UDF Mail/r/n/r/nYou got mail');

 

【Memcached】数据缓存一致性问题详解

写在前面的    首先来纠正一下我的观点错误,在之前的Memcached文章中介绍了一些基本的概念,其中有一条写到了“如果可以增加(或者无限制的增加)很多个结点的话,我们的存储压力会大大减小,丢失率...
  • SugaryoTT
  • SugaryoTT
  • 2016年12月16日 21:32
  • 778

让memcached和mysql更好的工作

来源:http://chaoqun.17348.com/2008/08/memcached_work_with_mysql  这次是Fotolog的经验,传说中比Flickr更大的网站,Foto...
  • bluejoe2000
  • bluejoe2000
  • 2014年05月05日 14:21
  • 2523

memcached分布式部署

memcache和memcached两者使用起来几乎一模一样。 $mem = new Memcache; $mem->addServer($memcachehost, '11211'); $mem-...
  • moqiang02
  • moqiang02
  • 2014年09月15日 17:19
  • 1613

如何快速高效的更新memcached缓存数据

在高并发的分布式系统中,加入缓存机制可以很大的提高性能。最近做的XX省的电信BSS项目中就用到了Memcached,系统中将查询到的结果缓存到Memcached 中(下面称MC好了)。对于配置类的规格...
  • likunwen_001
  • likunwen_001
  • 2015年07月08日 18:09
  • 2995

mysql 与缓存服务器集成的介绍(memcache+redis)

Memcached和Redis作为两种Inmemory的key-value数据库,在设计和思想方面有着很多共通的地方,功能和应用方面在很多场合下(作为分布式缓存服务器使用等) 也很相似,在这里把两者放...
  • changyanmanman
  • changyanmanman
  • 2015年05月15日 15:27
  • 1870

wordpress通过配置memcached缓存降低mysql内存占用

最近管理学书籍网站经常内存吃紧,所以准备试用下传说中的memcached缓存,据说可以大大提升MYSQL性能,进而节省内存。 先在http://news.12reads.cn/ 上试验下,OK了再...
  • Solmyr_biti
  • Solmyr_biti
  • 2016年12月31日 19:58
  • 866

利用mysql函数更新memcached

使用Memcached Functions for MySQL同步更新MySQL和Memcached   使用MySQL + Memcached进行Web已经是居家旅行之必备良药了。常规使用方式是依...
  • u011676589
  • u011676589
  • 2014年12月16日 15:11
  • 966

浅谈php-Memcached常规应用与分布式部署方案-

俊哥的linux私房菜
  • u010391029
  • u010391029
  • 2015年05月21日 09:09
  • 1313

使用Memcache缓存mysql数据库操作的原理和缓存过程浅析

对于大型网站如facebook,ebay等网站,如果没有Memcache做为中间缓存层,数据访问不可能吃得消,对于一般网站,只要具备独立的服务器,完全可以通过配置Memcache提高网站访问速度和减少...
  • liqfyiyi
  • liqfyiyi
  • 2016年03月15日 09:40
  • 5095

大型网站架构提速关键技术(页面静态化、memcached、Mysql优化)

大型网站架构关键技术介绍 1. pv值(page views),访问量大 带来问题 a. 流量大 10000000*2m ->解决方案 买带宽 ,优化程序(处理图片) b. 并发量,同时访问网...
  • u011986449
  • u011986449
  • 2014年03月09日 10:56
  • 10150
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL and memcached
举报原因:
原因补充:

(最多只允许输入30个字)