People always have conversations at the UC that spark ideas for new projects or features, this is my random collection of features or items I need to look into.  Most people can ignore it… but if your interested in what goes on in my head here ya go:

General Innodb:

• I started looking into this before, but I was reminded of it again today…  it would be useful to setup separate innodb buffer pools like the key caches in MyISAM.
• maybe an easy hack to put in a tiered LRU based on file per table.  What I mean by this is internally the internal BP in inno actually uses the space id ( file per table will have a new ID per table )…  this should make it feasible to ( maybe easy ) to move certain spaceid’s up or down the internal LRU list  This maybe an interesting feature, not exactly like pinning a table into the BP but close.    Basically, if a table is accessed and its …

One last reminder, we ( Yves and I ) will be appearing all over the place at the UC. Here is a quick run down of our schedule:

• Tuesday 10:50 am - 11:35 pm (Free MySQL Camp) I will be presenting “Learning from others’ MySQL Performance Mistakes” … a journey into the sometimes silly things we all end up doing.

• Tuesday 2-3pm Yves and I are co-presenting “Distributed Innodb Caching with memcached” … this will be an intro into the Waffle Grid project, what it’s about and what it means to you.

• Tuesday 4:25 pm - 5:10 pm (Free MySQL Camp) Yves will be presenting “NBD (MySQL Cluster) performance tuning and pitfalls”

• Wednesday 2-3pm I will be presenting: “SAN Performance on a Internal Disk Budget: The Coming Solid State Disk Revolution” … the title sounded better when I submitted it, because the fact is SSD’s are here! But the big question is what does it …

Yes we are still plugging away working on Waffle Grid, in fact I am testing the heck out of the plugin release this week. Some good news, great news, and bad news to report.

The good news on the testing is using the new Waffle Grid release, I am able to consistently get up close to 15K TPM, that’s up from 3K TPM without WaffleGrid ( 5x increase woohoo! ). This performance boost holds true through several tests, and based on this testing I am working on a set of recommended parameters for getting the most performance out of Waffle. I should have my recommendations for our User Conference presentation.

The great news is I have been able to get close to 20K TPM by disabling the read-ahead! This represents a huge boost in performance, over both the read-ahead enabled and the non-waffle tests. The bad news is in extended tests with the read-ahead disabled the plugin version takes a crap on me and consistently dies about 22-24 minutes …

Quick note I got memcached 1.3.2 working with waffle. I added a lot more code then I needed, and I think I may be able to get this down to a 1 or 2 line patch… In case your interested in a slightly bloated waffle version of 1.3.2 its here: https://code.launchpad.net/~yonkovim/wafflegrid/memcached-1.3.2

I will clean this up this week and push a better more streamlined version then.

So I got our Waffle specific code over into Memcached 1.3.2 last night…  not 100% sure why at this point but I am seeing a huge change in the the hit rate between 1.2.5 and 1.3.2.  Take a look:

Old 1.2.5:

10290.89 new-order transactions per minute (NOTPM)

Server: 192.168.2.105 (11211)
pid: 16522
uptime: 2190
time: 1233504281
version: 1.2.5
pointer_size: 32
rusage_user: 23.753484
rusage_system: 127.611975
curr_items: 46800
total_items: 3487050
bytes: 769596712
curr_connections: 20
total_connections: 23
connection_structures: 21
cmd_get: 1752218
cmd_set: 1801081
get_hits: 1685969
get_misses: 66249
evictions: 1680669
bytes_read: 769596712
bytes_written: 769596712
limit_maxbytes: 805306368
threads: 1

New (1.3.2):
6695.67 new-order transactions per minute (NOTPM)

Server: localhost (11211)
         pid: 9778
         uptime: 2087
         time: 1238184346
         version: 1.3.2
         pointer_size: …

The MySQL UC 2009 is coming and it is time for my own little marketing. As Matt already annonced it a few months ago we (Matt and I) are doing a WaffleGrid presentation, Distributed InnoDB caching with Memcached, Tuesday at 2PM. I am also presenting at the MySQL Camp or unconference, NBD (MySQL Cluster) performance tuning and pitfalls, also Tuesday at 4:25PM.

For the interested, I just ported Wafflegrid to the newest version of the InnoDB pluging, v. 1.0.3. Meanwhile, I also corrected a small bug with the CRC_32 code that wrongly cause MySQL to report corrupted blocks after being retrieved from memcached. To get the new code, simply do:

bzr branch lp:~y-trudeau/wafflegrid/waffleGrid-Innodb-plugin

and replace the directory storage/innobase by what you will retrieve from launchpad. If you are new to WaffleGrid, you will also need a custom version of memcached and a modified build script for MySQL. All these can be obtained at:

http://bazaar.launchpad.net/~yonkovim/wafflegrid/Mattscode/files/8

You will also need to change the file memcached_constants.h in libmemcached with the following value:

#define MEMCACHED_MAX_BUFFER 16500

This gives a 20x perf gain… Yes… WaffleGrid is still experimental.

Last night I was on a plane to a client site and I was reviewing the waffle grid code for inclusion of multi-gets when calling read-ahead functions (potentially in other area’s as well)…  their I noticed something that may slow down our performance in waffle grid.  You see we read from memcached in the buf_read_page_low function.  This function is responsible for checking wether a page exists in the buffer and if not making an IO request for it via the function fil_io.  What I saw in buf_read_page_low was:  we check the buffer, then check memcached, then go get the page off disk… you follow?  Alright The get page from disk part is challenge.  Let me do a quick deep dive on the internal function calls.

You see buf_read_page_low is called by a few different functions in a few different ways, the ones I am concerned with are the functions that are pasing a sync of false ( you do not want synchronous IO, …

Alright my stats patch appears to be working ( its a first step anyways ), eventually this is going to make it into waffle grid… but I thought it would be useful as a standalone patch.  Basically what this patch does is turn off the dynamic collection of stats by disabling the calls to dict_update_statistics_low & btr_estimate_number_of_different_key_vals for everything but the analyze statement. At this point its not really refined, but it does appear to work.  i.e. It will need more refining in the future and may not 100% work in every little case.  The short of it, this means that once you set your stats you should not have to worry about the cardinality of your tables changing dramatically.

Other databases ( even other storage engines ) allow you to control when and how your stats are gathered.  Lots of shops analyze their tables once a week using a large sample set with the idea that their execution plans will …

Recently, I have been traveling a lot to the west coast (I live in Quebec) so I had plenty of time to update WaffleGrid while flying. Here are the new features/ports:

So now, if you update the innodb_memcached_servers with a statement like “set global innodb_memcached_servers = ’serverA,serverB:11212′;” then MySQL will disconnect from the current servers and reconnect to the new ones. The key prefix is incremented to prevent cache coherency problems.

You might have noticed that Matt added many WaffleGrid statistic counters like shown here. Those counters have been ported to the InnoDB plugin version.

I also added a CRC32 checksum for each block sent to memcached, the …