Binlog Storage Engine - MySQL Proxy Edition

May 04, 2009

A binlog storage engine was talked about many times already. Being able to JOIN against the binlogs, the just SELECT it, search for entries … is not possible with current SQL commands in the MySQL Server.

As you have read the previous articles here, I wrote a binlog encoder/decoder for MySQL Proxy and wrapped with with a Lua interface. Some years ago I also wrote a scriptable storage engine which can call lua scripts to return data for SELECTs. Putting the two together gives us: a Binlog Storage Engine.

In the datadir we just create

$ ls -la test/binlog_*.*
-rw-r--r--  1 jan  staff  1342  4 Mai 12:30 test/binlog_000016.lua

mysql> select * from test.binlog_000016;
Query OK, 0 rows affected (0.00 sec)

+----------------------------------+------------+----------------+
| type                             | event_size | next_event_pos |
+----------------------------------+------------+----------------+
| FORMAT_DESCRIPTION_EVENT         |        102 |            106 |
| QUERY_EVENT                      |         71 |            177 |
| QUERY_EVENT                      |         71 |            248 |

That binlog_000016 table is a .lua script that the scriptable storage engine can load:

local binlog = assert(require("mysql.binlog"))

local binlog_table = { }

-- Lua does OOP and and inheritance ...
function binlog_table:new(o)
    o = o or {}
    setmetatable(o, self)
    self.__index = self
    return o
end

As we don’t want to mess with CREATE TABLE statements, we just create the .frm files on the fly by providing a discover() function.

-- return a the table structure
function binlog_table:discover()
    return {
            { name = "type", type = 254, length = 32 },
            { name = "event_size", type = 3, length = 32 },
            { name = "next_event_pos", type = 3, length = 32 }
    }
end

After SELECTing from the table the first time the mysql-server will create the .frm files for us automaticly:

$ ls -la test/binlog_*.*
-rw-rw----  1 jan  staff  8652  4 Mai 12:29 test/binlog_000016.frm
-rw-r--r--  1 jan  staff  1342  4 Mai 12:30 test/binlog_000016.lua

The most basic command is get row for a table-scan. Our :next() method sets up the iterator and returns the iterator-function that is called until it returns nil.

-- returns a iterator
function binlog_table:next()
    -- set the iterator
    self.f:seek(4)
    self.f_iter = self.f:next()

    if not self.f_iter then return nil end
    -- return the iterator function
    return function ()
            self.event = self.f_iter()

            if not self.event then return nil end

            return { self.event.type, self.event.event_size, self.event.log_pos }
    end
end

To create our binlog table we just inherit from that binlog_table class and return it:

-- this table is a binlog-table
local tbl = binlog_table:new({ })

function tbl:open()
    self.f = binlog.open("/Users/jan/sandboxes/msb_5_1_35/data/msandbox.000016")

    return (self.f ~= nil)
end

return tbl

The last piece is the storage engine plugin:

$ mysql <<EOF
FLUSH TABLES;
UNINSTALL PLUGIN lua;
EOF
$ make install
$ mysql << EOF
INSTALL PLUGIN lua SONAME 'libwormhole_engine.so';
EOF

If everything works out nicely you should see:

$ mysql 
mysql> show plugins;
+------------+--------+----------------+-----------------------+---------+
| Name       | Status | Type           | Library               | License |
+------------+--------+----------------+-----------------------+---------+
...
| LUA        | ACTIVE | STORAGE ENGINE | libwormhole_engine.so | GPL     |
...

Update

Properly normalized you can now decode the different events by just JOINing the ..._query table based on the event_pos

> select * from test.test_query AS q join test.test AS e using (event_pos) where e.type = "QUERY_EVENT" and event_pos = 106;
+-----------+-------------+-----------+--------------+-----------+-------------+------------+-----------------+------------+
| event_pos | type        | thread_id | query        | server_id | type        | event_size | next_event_posH | timestamp  |
+-----------+-------------+-----------+--------------+-----------+-------------+------------+-----------------+------------+
|       106 | QUERY_EVENT |         2 | FLUSH TABLES |         1 | QUERY_EVENT |         71 |             177 | 1241432054 | 
+-----------+-------------+-----------+--------------+-----------+-------------+------------+-----------------+------------+

And that is supposed to be fast as event_pos is our PRIMARY KEY:

> explain select * from test.test_query AS q join test.test AS e using (event_pos) where e.type = "QUERY_EVENT" and event_pos = 106;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | q     | ref  | PRIMARY       | PRIMARY | 5       | const |   10 | Using where |
|  1 | SIMPLE      | e     | ref  | PRIMARY       | PRIMARY | 5       | const |   10 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

On the implementation side the ..._query table just inherits from the base-table and overwrites the discover (for the new table-structure) and the get_row (to return the corresponding information) methods. Everything else stays the same.

local binlog_table = assert(require("wormhole.binlog_table"))

-- this table is a binlog-table
local tbl = binlog_table:new()

function tbl:discover()
    return {
            { name = "event_pos", type = 3, is_nullable = false, is_primary = true },
            { name = "type", type = 254, length = 64 * 3, is_nullable = false },
            { name = "thread_id", type = 3, is_nullable = false },
            { name = "query", type = 254, length = 256, is_nullable = false },
    }
end

function tbl:get_row()
    local e = self.event
    if e.type == "QUERY_EVENT" then
            return { e.log_pos - e.event_size,
                    e.type,
                    e.query.thread_id,
                    e.query.query,
            }
    else
            return { e.log_pos - e.event_size,
                    e.type,
                    nil,
                    nil
            }
    end
end

function tbl:open()
    self.f = binlog.open("/Users/jan/sandboxes/msb_5_1_35/data/msandbox.000016")

    return (self.f ~= nil)
end

return tbl

mysql-proxy

---

Comments