Filtering by table is now possible with WaffleGrid

Since I have been a home recently, I put some time correcting bugs in WaffleGrid and adding new features. Thanks to gdb, I have been able to understand a silly bug that was affecting WaffleGrid with sysbench but, weird enough, not with dbt2. Everything is in the way connections are established. I will blog more about that soon.

Regarding the new features, it is now possible to choose which tables you want to push to memcached. For that purpose, two new parameters have been introduce:

innodb_memcached_table_filter_enable = 0 | 1   (default to 0)

to enable the filtering and

innodb_memcached_table_list = db1/table1,db2/table2   

to list the tables. This feature is filtering based on the space id so, innodb_file_per_table has to be set. Right now, the association table <-> space_id is done only at startup so, the table has to exist. Also, since an alter table change the space_id… you need to restart MySQL to restore the filtering after an alter table. Eventually, it will be more dynamic. Here are some tests done with innodb_memcached_enable = 1, caching around the disk IO, sbtest is in the filter list while sbtest_non_waffle is not. I really like mode 1, it works with the normal memcached and set to memcached are handled by the IO write threads of InnoDB, so they happened in the background. Both tables have exactly the same structure and content (100k rows, sysbench oltp test).

mysql> select avg(length(pad)) from sbtest;select avg(length(pad)) from sbtest_non_waffle;
+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.31 sec)

+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.98 sec)

Not bad, one third of the time and no disk IO, the data was already in memcached. For the test, the buffer pool is at 8M (I tweaked 5.4 to lower the minimum). Data length and Index length for both table is approximately 24 MB. Then, just to prove my point about altering tables:

mysql> alter table sbtest engine=innodb;
Query OK, 100000 rows affected (8.50 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> select avg(length(pad)) from sbtest;select avg(length(pad)) from sbtest_non_waffle;
+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.92 sec)

+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.96 sec)

As you can see, the timings are now similar. If you want to play with WaffleGrid, grab the current code from launchpad at lp:~y-trudeau/wafflegrid/cream-5.4-tree. It is a full tree so… be patient. Use the build scripts showing memc in their name. For more information, you can also go to www.wafflegrid.com.

Remember, the project is still fairly young, don’t put any valuable data on WaffleGrid yet. Coming next, apart from making the table filtering more dynamic, I would like to add an exclusion list instead of just the current inclusion list. Of course, we also need to test WaffleGrid much more and for that we need help. If you test and find bugs, please use Launchpad at https://code.launchpad.net/wafflegrid to report the bugs you found.

About Yves Trudeau

I work as a senior consultant in the MySQL professional services team at Sun. My main areas of expertise are DRBD/Heartbeat and NDB Cluster. I am also involved in the WaffleGrid project.
This entry was posted in innodb internals, mysql, Waffle Grid, yves. Bookmark the permalink.

Comments are closed.