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 …

You crash MySQL/Innodb and you trace the error to a function that says:

“This function checks the consistency of an index page when we do not
know the index. This is also resilient so that this should never crash
even if the page is total garbage. ”

Oops… I guess its not that resilient after all:)

Yves pointed this out to me:

http://www.internetnews.com/software/article.php/3825426/

“Moving beyond just providing Oracle compatibility, EnterpriseDB is adding new scalability features to their database. One of them is a feature called ‘”Infinite Cache,” which is based on the open source memcached application.

Mlodgenski commented that distributed memory caches are common, though management from a system management and a database perspective is often difficult.

“What we did is we took the distributed memory cache that was sitting in front of the database and put it behind the database,” Mlodgenski said. “This allows a simple SQL (define) interface for developers that allows access to the distributed memory cache and now the system management overhead is handed by the database itself.”

Mlodgenski added that Infinite Cache leads to a 10x to 20x performance gain for read application loads. …

So Ronald Bradford has been getting some EC2 AMI’s setup for Waffle the last couple of days. Really he is the first person to work with us on testing out the 0.5 cream release. It’s funny but when you are heads down in the code you tend to overlook some of the easy and obvious bugs. Just by having him walk through the install we have opened up several new bugs and come up with some new Idea’s on how to make things easier in the future. I hope by early next week we can have some of annoying bugs fixed and code pushed to launchpad. When that happens I would encourage others to give it a whirl and help us make Waffle Grid better performing, more user friendly, and more stable.

You can check out some of the bugs here.

Thanks to Ronald for his help on this!

Lets get down to how the latest version of Waffle Grid performs.

Starting off simple lets look at the difference between the wafflegrid modes. As mentioned before the LRU mode is the “classic” Waffle Grid setup. A page is put into memcached when the page is removed from the buffer pool via the LRU process. When a page is retrieved from memcached it is expired so its no longer valid. In the New “Non-LRU” mode when a page is read from disk, the page is placed in memcached. When a dirty page is flushed to disk, this page is overwritten in memcached. So how do the different modes perform?

I wanted to let everyone know that we are releasing Waffle Grid 0.5 code named Cream. This release fixes the nasty secondary index bug that plagued the butter release. I have been running tests on this code base for about a week straight with no errors. While I think this release is much more stable I would remind everyone this is still not a fully GA release. This release includes the ability to choose the mode of Waffle grid. By setting innodb_memcached_enable to 1, we will push pages to memcached when a disk read is done or when a page write is done, setting this to 2 will enable the classic LRU mode. If you decide to set this to 1 ( non-lru) I would recommend using the standard memcached, as with previous versions the LRU mode works better with our slightly altered memcached ( expire from memcached on get ). I will be posting benchmarks and more details within the next couple of days. Right now you can grab the patch on …

So I spent several hours over the last few days on the Secondary index bug. Out of frustration I decided to try and bypass the LRU concept all together and try going to a true secondary page cache. In standard Waffle a page is written to memcached only when it is expunged ( or LRU’d ) from the main buffer pool. This means anything in the BP should not be in memcached. Obviously with this approach we missed something, as Heikii pointed out in a comment to a previous post, it seems likely we are getting an old version of a page. Logically this could happen if we do not correctly expire a page on get or we bypass a push/lru leaving an old page in memcached to be retrieved later on.

So I was thinking why not bypass the LRU process? While I feel this is the most efficient way to do this, its not the only way. I modified innodb to use the default LRU code and then modified the page get to push to memcached on any disk read. Additionally I added …

So I spent the weekend looking at places where we may have missed something in the code for waffle. You can actually see some of the stuff I tried in the bug on launchpad about this, but the weird thing is the very last thing I tried. As I took a step back and looked at the problem ( secondary index corruption ) and our assumption that we “missed” something, I decided to find the place where pages are written to disk and to push to memcached from here as well as from the LRU. With the double write buffer enabled that place should be buf_flush_buffered_writes. By pushing to memcached here we should eliminate the page that falls through the cracks of the LRU. Basically this should help ensure memcached has an exact copy of the data that exists on disk. The result? It failed with the same secondary index failure. This means:

a.) maybe we have a problem in the …

If you read Yves blog post about waffle yesterday we are seeing some weird gremlins in the system and could use some scoobey doo detective work if you have some ideas. The strange thing is it only exhibits under high load. So it really seems like we may have missed some background cleanup process that accesses or removes pages from disk or the buffer pool without going through the functions we call waffle in (buf_LRU_search_and_free_block & buf_read_page_low ).

One of the idea’s I had was trying to narrow the scope of what’s being pushed and read form Memcached. Even though I am using file per table, system tablespace pages are still making it in and out of memcached. I thought if we missed something maybe it was here ( even though I could not find it in the code ). I mean cleaning up undo or internal data would seem like a logical place to miss something. So I hacked …

You probably haven’t noticed but I have not blogged since the UC. It is not because I am upset by the perspective of working for Oracle, I have simply been busy tracking down an issue we have with WaffleGrid. We discovered that under high load, with DBT2 on a tmpfs, we end up with error in a secondary index. In the error of MySQL, we have entries like this one:

InnoDB: error in sec index entry update in
InnoDB: index `myidx1` of table `dbt2`.`new_order`
InnoDB: tuple DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000005; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000004; asc     ;;

TRANSACTION 14469, ACTIVE 1 sec, process no 7982, OS thread id 2995481488 updating or deleting
mysql tables in use 1, locked 1
26 lock struct(s), heap size 2496, 65 row lock(s), undo log …