Joshua Zhu’s Blog

Memcached becomes more and more popular nowadays. It is widely used by many heavy loaded sites. Why does it succeed?

Well, of course the first and the most important reason is that it meets the need for speed of the web 2.0 sites, by caching data and objects in memory. However, from the point of view of a server developer, what I want to emphasize is that it is the simplicity of memcached’s protocol design makes it more successful. Take a look at memcached’s protocol:

With the textual protocol as shown above, memcache can be easily supported and implemented in various programming languages. No wonder dozens of different memcache clients appear. And then it consequently boosts memcached’s use. Simple thing usually will withstand the test of time. The old simple textual protocols, e.g., HTTP, FTP, SMTP and POP3 are still in use on the modern Internet. Not only because textual protocols can be easily parsed and extended, but also they are convenient for human being to read and debug. This is where the UNIX philosophy shines.

In conclusion, always prefer textual protocol when designing your own application. It would turn out to be really a wise decision.

For a long time, I was not satisfied with vmstat, because it does not generate timestamps. Then I came across a cool program named Dstat yesterday, which can be an excellent replacement for vmstat.

A screenshot of Dstat

By using Dstat, now not only all my needs can be met but also it is pretty easy to do resource usage analysis and graphing.

As the author described, “Dstat is a versatile replacement for vmstat, iostat, netstat, nfsstat and ifstat. Dstat overcomes some of their limitations and adds some extra features, more counters and flexibility. Dstat is handy for monitoring systems during performance tuning tests, benchmarks or troubleshooting.” So why use three or more tools when one tool can give you everything you need?

Here is an example showing how to use it.

First, capture the resource usage information (CPU and memory) to a file, e.g. stat.dat:

$ dstat -tcmn > stat.dat

Then use the scripts below to create CPU and memory usage graphs:

#!/usr/bin/gnuplot
 
set terminal png
set output "cpu.png"
set title "CPU usage"
set xlabel "time"
set ylabel "percent"
set xdata time
set timefmt "%d-%m %H:%M:%S"
set format x "%H:%M"
plot "stat.dat" using 1:4 title "system" with lines, 
"stat.dat" using 1:3 title "user" with lines, 
"stat.dat" using 1:5 title "idle" with lines

#!/usr/bin/gnuplot
 
set terminal png
set output "memory.png"
set title "memory usage"
set xlabel "time"
set ylabel "size(M Bytes)"
set xdata time
set timefmt "%d-%m %H:%M:%S"
set format x "%H:%M"
plot "stat.dat" using 1:9 title "used" with lines, 
"stat.dat" using 1:10 title "buff" with lines, 
"stat.dat" using 1:11 title "cach" with lines, 
"stat.dat" using 1:12 title "free" with lines

Resource usage graph examples:

Download the scripts.
http://www.zhuzhaoyuan.com/download/dstat/cpu.sh
http://www.zhuzhaoyuan.com/download/dstat/memory.sh

I write this tutorial, simply because it is undocumented.

MySQL Proxy has only long options. Therefore, it is a little bit inconvenient to type the long command line. For example, suppose we are going to setup a MySQL Proxy server forwarding connections to two MySQL backends. We also want to 1) use a Lua script, 2) start the proxy in daemon mode, 3) log all messages of level debug and higher to a specific file, and 4) write the proxy’s pid to its pid file. What would the command line look like?

$ mysql-proxy --proxy-address=192.168.0.189:3307 \
--proxy-backend-addresses=192.168.0.189:3306 \
--proxy-backend-addresses=192.168.0.192:3306 \
--proxy-lua-script=/home/josh/mysql-proxy/dispatch-by-client-address.lua \
--daemon \
--log-file=/home/josh/mysql-proxy/mysql-proxy.log --log-level=debug \
--pid-file=/home/josh/mysql-proxy/mysql-proxy.pid

As you can see, it’s quite long. Not to mention, MySQL Proxy can be plugged into many plugins, which could import more options.

Is there any way to make it less repetitive? Yes. By taking advantage of MySQL Proxy’s configuration file, we no longer need to type such long command line.

MySQL Proxy uses GLib’s key file (GKeyFile) as its configuration file format. A key file is very similar to a .ini file. It consists of groups of key value pairs which can be strings, booleans, integers and lists of these. Each key value pair must be contained in a group, where the name appears between enclosed square brackets.

Here goes the proxy’s configuration file of the example above:

# MySQL Proxy's configuration file (mysql-proxy.cnf)
 
[mysql-proxy]
daemon = true
pid-file = /home/josh/mysql-proxy/mysql-proxy.pid
log-file = /home/josh/mysql-proxy/mysql-proxy.log
log-level = debug
proxy-address = 192.168.0.189:3307
proxy-backend-addresses = 192.168.0.192:3306,192.168.0.189:3306
proxy-lua-script = /home/josh/mysql-proxy/dispatch-by-client-address.lua

And the command line:

$ mysql-proxy --defaults-file=mysql-proxy.cnf

It is apparently much shorter. We now just need to specify the name of the configuration file!

Some notes:
1) GLib uses the ‘;’ character as the default list separator, while MySQL Proxy uses ‘,’.
2) Ungrouped keys are not allowed in key files.
3) The –version and –defaults-file options can not appear in MySQL Proxy’s configuration file.
4) A handy feature: the values in MySQL Proxy’s configuration file can be overridden by the command line options.

To learn more about GKeyFile, please visit:
http://www.gtkbook.com/tutorial.php?page=keyfile
http://library.gnome.org/devel/glib/2.18/glib-Key-value-file-parser.html
http://standards.freedesktop.org/desktop-entry-spec/desktop-entry-spec-1.0.html

Below is a full step by step guide to compiling and installing MySQL Proxy from Bazaar on a CentOS 5.2 box. It should also work for CentOS 5.

First, if you don’t have the EPEL (Extra Packages for Enterprise Linux) repository enabled, you should enable it:

# rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-2.noarch.rpm

Make sure you have GNU Autotools, flex, pkg-config, and bazaar, as well as MySQL client libraries installed. If not, please follow this command:

# yum install autoconf automake libtool flex pkgconfig bzr mysql-devel

Since the version of libevent that CentOS ships is too old, you need to build a newer one (>= 1.4.0, for better threading support):

$ wget http://monkey.org/~provos/libevent-1.4.9-stable.tar.gz
$ tar zvfx libevent-1.4.9-stable.tar.gz
$ cd libevent-1.4.9-stable
$ ./configure
$ make
# make install

Again, CentOS 5.2 ships with an old version of GLib, a newer one is required (>= 2.16.0, for the GLib testing framework):

$ wget http://ftp.gnome.org/pub/gnome/sources/glib/2.18/glib-2.18.4.tar.gz
$ tar zvfx glib-2.18.4.tar.gz
$ cd glib-2.18.4
$ ./configure
$ make
# make install

And Lua 5.1 should be installed:

$ wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
$ tar zvfx lua-5.1.4.tar.gz
$ cd lua-5.1.4
$ make linux
# make install
# cp etc/lua.pc /usr/local/lib/pkgconfig/

Important: you should make pkg-config know where the libraries are!

$ export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig

Now, check out the latest source code of MySQL Proxy and build:

$ bzr branch lp:mysql-proxy
$ cd mysql-proxy
$ ./autogen.sh
$ ./configure
$ make
# make install

Run it and see if everything is okay:

$ mysql-proxy -V

Done!

Pierre asked if clients could be redirected to different servers by client address in MySQL Proxy. The answer is clearly yes, and that’s one of the reasons why MySQL Proxy is designed for. Moreover, you can set the connection’s backend (proxy.connection.backend_ndx) to what you want in the connect_server() stage, according to your own rules.

I wrote a simple Lua script to do this job. Here is the scirpt:

--
-- author: Joshua Zhu (http://www.zhuzhaoyuan.com)
-- 
 
local string = require("string")
 
local is_debug = true
 
local cli_svr_map
 
function get_server(client)
    -- initialize the map table
    if not cli_svr_map then
        -- note: you should modify the client IPs and the backend addresses below!!!
        cli_svr_map = {
            ["192.168.0.189"] = {
                address = "192.168.0.192:3306",
                backend_ndx = -1 },
            ["192.168.0.192"] = {
                address = "192.168.0.189:3306",
                backend_ndx = -1 },
         }
 
        for _, v in pairs(cli_svr_map) do
            for i = 1, #proxy.global.backends do
                local backend = proxy.global.backends[i]
                if v.address == backend.address then
                    v.backend_ndx = i
                    break
                end
            end
        end
 
        if is_debug then
            print("map table: ")
            for k, v in pairs(cli_svr_map) do
                print("   [" .. k .. "] = {" .. v.address .. ", " .. v.backend_ndx .. "}")
            end
        end
    end
 
    local host = string.match(client, "([^:]+):")
    if cli_svr_map[host] then
        return cli_svr_map[host].backend_ndx
    end
 
    return -1
end
 
function connect_server()
    if is_debug then
        print("[connect_server] " .. proxy.connection.client.address)
        print("we have " .. #proxy.global.backends .. " backends:")
 
        for i = 1, #proxy.global.backends do
            local backend = proxy.global.backends[i]
            print("   [" .. i .. "].connected_clients = " .. backend.connected_clients)
            print("   [" .. i .. "].address = " .. backend.address)
            print("   [" .. i .. "].state = " .. backend.state)
            print("   [" .. i .. "].type = " .. backend.type)
            print("   [" .. i .. "].uuid = " .. (backend.uuid or "(nil)"))
        end
    end
 
    local index = get_server(proxy.connection.client.address)
    if index > 0 and index <= #proxy.global.backends then
        if proxy.global.backends[index].state ~= proxy.BACKEND_STATE_DOWN then
            if is_debug then
                print("redirect the client to backends[" .. proxy.global.backends[index].address .. "]")
            end
 
            proxy.connection.backend_ndx = index
        end
    end
end

Download the script.
If you find bugs in this script, please correct me