Colin Charles Agenda

This is the Sharding for the masses: Introducing the SPIDER storage engine by Giuseppe Maxia, given at OpenSQLCamp, at FrOSCon, in August 2009. These are somewhat live notes, and the slides are available too.

Why sharding? Scaling, of course. The MySQL way to solve this, is replication (even Yahoo! and Google use this).

When the master doesn’t have enough resources to cope with what you do (i.e. large data sets), replication chokes.

You can use proxies for sharding. There exists MySQL Proxy (can be programmed using a scripting language – Lua), HSCALE (built on top of MySQL Proxy), SpockProxy (a fork of MySQL Proxy, without LUA scripting, specialised for sharding), in the market these days. This however, is the single point of failure – everything has to pass through one proxy.

Enter SPIDER – a MySQL storage engine, built on top of the partitions engine. It associates a partition with a remote server, and is transparent to the user. Its developed by Kentoku Shiba.

Installation: Get 5.1.37 sources, then get the source code for Spider 1.0, and then get the patch for condition pushdown.

Why the condition pushdown patch? Remote server works less, by receiving the condition. The SPIDER engine without the condition pushdown patch is still fast, but it can be more than 10x faster with condition pushdowns.

http://dev.mysql.com/doc/refman/5.1/en/condition-pushdown-optimization.html (works with NDBCLUSTER), http://dev.mysql.com/doc/refman/5.4/en/condition-pushdown-optimization.html (works with MyISAM). The patch by Kentoku, will add cond_push and cond_pop, to ha_partition – so now, every storage engine that uses table partitioning can get condition pushdown through ha_partition.

You need to setup the engine first: http://datacharmer.org/downloads/spider_setup.sql (the SQL is also available in the DOCS).

spider_remote_employees.sql – use this in conjunction with http://launchpad.net/test-db/ – a good example of how to use the SPIDER storage engine.

Who remembers snaps? This is the place to go, when you wanted nightly source code snapshots of stuff that comes out of MySQL AbSun Microsystems build systems, that is related to the MySQL product line. There you can get all the snapshots for GA releases, as well as archives; (albeit not very up-to-date).

Anyway, its good to know now there is a focus, just for server snapshots, available at MySQL Labs. These are testing builds, that come out directly from pushbuild (the build system). Its not for production use, but what’s really useful is the fact that there’s also a recommendation to use the MySQL Sandbox, written by Giuseppe Maxia. Today, I see builds for 5.1 and 5.1 with GIS extensions.

In due time, I expect 5.4 and 6.0 builds to make its way. This should help QA a lot more as well, as people start playing with daily builds, and finding bugs. In case you’re wondering why there have been no updates in a couple weeks, just hang in there. Its currently manually pushed, but will soon be done automatically via cron(8).

RethinkDB is all the rage today, as its a Y Combinator funded startup, which also launched a developer pre-alpha today. So what is RethinkDB you ask? Yet-another-MySQL-storage-engine, that’s what. But this time, its tuned for solid-state drives (SSDs), which also happen to be all the rage these days.

Anyway, check them out more, and the materials currently tell me that they’re using append-only algorithms, which allow for live schema changes and hot backups, with instantaneous recovery from power failure. Those are just some of the exciting bits.

What didn’t excite me so much was the fact that you were only getting 32-bit or 64-bit Linux binaries, built against MySQL 5.1.31 and you’ll just install it via the INSTALL PLUGIN option. But they are trying to get some semblance of a community growing, with their getting involved page, filled with some papers, as well as a support mailing list (I see Mark Callaghan is already busy asking them questions). And of course you can follow them on their blog, or on Twitter. All this without source ;-)

One of the developers also confirmed that they’re adding “features required by WordPress so we could eat our own dogfood”. They haven’t started profiling (much yet?), and they’ve probably got ways to go on performance. Seems like “getting it working for WordPress”, is slowly becoming a good testing ground – Jeff Waugh did so for WordPress and Drizzle, too.

Anyway, it seems like its time to get some SSDs, as we start seeing things like this pop up. RethinkDB will also face another problem for mass adoption – how many hosting providers are using SSDs? Probably not many (if at all).

Have you tried RethinkDB? Your thoughts?

An unknown error connecting to MySQL on 10.0.6.28? Oh dear me… It came back up within 2 minutes though from the time I got the screenshot.

We had 12 projects, and by the time we’ve hit mid-terms, we’ve decided to cull 2 project so far, leaving us with 10 projects.

This year, the MySQL project can really divide itself into three groups – those hacking on MySQL, Drizzle, or phpMyAdmin. Next year, will we see others? I certainly hope so…

Drizzle – Padraig O’Sullivan is doing an excellent job at working on a new implementation of the INFORMATION_SCHEMA. Nathan Williams is doing great work at code cleanup for Drizzle, and making it conform to C++ standards. Jiangfeng Peng is hacking on batch nested loop join’s in Drizzle.

phpMyAdmin – Derek Schaefer is adding import improvements to phpMyAdmin, while Tomas Srnka is working on adding MySQL Replication support for phpMyAdmin (and impressing his mentor!). Zahra Naeem is working on change tracking of data/structures, and you’d expect some more work after the mid-term, once some problems are worked through.

MySQL – Joseph Lukas is working on new commands to allow the changing of session variables temporarily as needed in a query. Haihao Tang is working on WL#4034, which is focused around the I_S storage engine. Tulay Meuzzinoglu is working on an SQL optimiser for mod_ndb, and there’s a lot of good stuff already going into the codebase, as is.

Common problems? Timezones and language barriers. How do other open source projects deal with this?

Much thanks to all the mentors who are doing a great job! If you want to keep track, either watch the individual Launchpad accounts, or check out the summer of code list for weekly progress reports

We’ve had MySQL meetup’s organised thanks to mysql.meetup.com for years now, and its sad to see the relationship end in about a fortnight. Never fret, because it seems that Facebook can do all that, and more.

Migration

If you’re the meetup organiser, and you checked the Members list, or even attempted to download it, you’ll be disappointed. You get a CSV file, with the name of the person, some other metadata, if they’re on the mailing list (chances is yes to all), and the URL of the member profile. What’s missing? An email address.

So while its nice to have a CSV copy of the member list, it will not assist you in any migration, whatsoever. Totally data lock in :-(

Mailing list/message board

Keep in mind that you’ve got mailing lists and message boards on meetup.com. You’ll want to make use of this, to tell people to join your new Facebook group. Mailing lists are of the form: mysql-GROUPNUM@meetup.com, and you can find out more from mysql.meetup.com/GROUPNUM/messages/archive.

In case you’ve had people unsubscribe from the mailing list, better make things redundant, to announce the move to Facebook, and also post to the message board.

As the owner of the meetup group, you can also edit the description of your meetup group – do so, pointing to the new Facebook address of the group.

Facebook Groups

Look at the bottom of your screen, and you’ll notice a little toolbar, and its not too hard to spot groups. Once you see the groups page, you will be able to “Create a New Group”. The exact link to creating a new group will work, as long as you’re logged into Facebook. Once you’re done, don’t hesitate to enter some relevant group information:

It’s probably worth noting that naming nomenclatures might be useful. Like I liberally used: The Malaysian MySQL User Group (Kuala Lumpur), but if you’re in a country like the United States, and you’re doing this in San Francisco, a name such as: The San Francisco MySQL User Group would make sense. Used similarly, like “The New York City User Group”, or if you’re in Australia, something like “The Melbourne MySQL User Group” will make sense. In that sense, I should really be calling the Malaysian one that I organise, “The Kuala Lumpur MySQL User Group”, and I have a feeling that once there are more meetups in the states around here, I will do just that.

Next up, you’re asked to upload a picture. Go ahead, make one up, or leave it blank. Something with your city in it, might be a good one. If the meetup group has a website, then enter its URL. The default settings on “Step 2: Customize” are all accurate, so just leave it be.

Once that is done, and you click “Save”, you’re asked to publish it on your wall. This is what I call publicity to those closest in your social network — you should go for it! You want all the help you can get, to spread the word, to make your meetup even more successful!

Now comes the fun part — you can invite your friends, or invite people via email — the choice is yours.

And that’s it, now you have a Facebook group, with an appropriate URL to point people to. In my case, the Malaysian MySQL User Group is at: http://www.facebook.com/group.php?gid=82091206683. Yes, the URL’s are not friendly, so maybe you want to make use of a URL shortener (Something like bit.ly, powered by MySQL).

What to do in the Facebook Group

The Facebook group you just created is useful. It displays the members, it has a discussion board facility, it has a wall (which may not be so useful, when it comes to actual discussion). It allows for members to post photos, links, videos, and more. In short, as long as all attendees are members of Facebook and the group, you’ve just created your own little virtual community!

Now, one thing that shone with Meetup.com was the ability to have a meetup once every month, and details go out to all members about topics, et al. Facebook allows this too. Select “Create Related Event”. In fact if you scroll down a little more, below Group Type, and Admins, you can also Create Events, as there is a nice Events tab, right before the Related Groups one.

Once you’ve selected Create an Event, you’re told to enter some event information, as follows:

That’s just basically filling up relevant information, the time, the location, and some contact details of the meetup organiser. Click Create Event, and magically, you now have a new event! Again, the defaults under customisation are perfectly safe, except, for the bottom, where you should scroll down, and under the “Publicize:” option, you definitely want to tick it, and say “Show this event in search results“. The more publicity, the better.

Click Save, and again, you’re asked to publish this to your wall, like above. If I were you, I’d go for it, so that even your friends who aren’t in the meetup group, might want to come depending on the topic being addressed in that particular month.

Like earlier, you can now invite guests (even via email) to the event, and once you’re done, you should have a similar page (looks so familiar to your group page, huh?) for the event in particular. Go forth and publish that URL on Twitter and other social networks, so more people show up :-)

Remember, that if anything changes, go back to the same event page, and select: “Message Guests”. This will allow them to all receive a Facebook message with the relevant information, changes, and so forth.

What else?

That’s basically it.

I can think of one more thing that Facebook has, that you don’t have on Meetup.com – that’s chat within the browser. You can chat with your friends, and meetup attendees potentially might want to confirm some last minute details, and the chat is a great feature.

One thing I find lacking is that you don’t have mailing lists. I’m an email type of guy, and if you want mailing lists, don’t hesitate to ask for one, at lists.mysql.com. We’ll be happy to provide yet another avenue to keep your meetups going strong.

Another strong point? You can be a member of multiple meetup groups, without having to leave your own. As someone who travels somewhat frequently, I love attending other user groups just to meet the community and see what they’re doing with MySQL. I can now, as events are open ;-)

Have questions? Leave a comment and I’ll get back to you. Otherwise, happy meeting users in your own areas and areas you’re travelling to!