racadm config -g cfgLanNetworking -o cfgNicSelection 2

We’ve been having troubles for a while now with a cluster of MySQL nodes which consume a never-ending amount of memory over time until it eventually runs out, forcing us to restart MySQL if we catch it in time, or reboot the box if we don’t. After doing tons of searches (and looking through countless bug reports), it seemed like it could have been a few memory leaks, fixed in late 5.1 or early 5.5 land. As this cluster was slightly older and not updated regularly, we pushed things over to new nodes and up to a modern version of 5.5. However, this didn’t seem to solve the problem. Every week or so, we’d fail over to a secondary node and restart things. The biggest hint towards it being caused by all the creates is that the “leak” flowed through replication and the secondary nodes would run out within several hours of the primary.

As it turns out, the answer was quite simple. According to the Percona MySQL Performance Blog, the MySQL dictionary cache memory is allocated with each opened table, and never given back – without any limit. This isn’t so bad I suppose, except if you’re opening something like half a million tables per week and you just so happen to have a machine with a finite amount of memory. Then you might have a bit of trouble.

The fix is bad news if you’re a MySQL community-build user. There’s no way to limit it without switching to Percona Server (which I highly recommend over the stock MySQL builds, anyway). If you have installed Percona Server in favor of MySQL you can set innodb_dict_size_limit to limit the growth. This is ’0′ by default which mimics current stock MySQL. Note that this is a soft limit, so if you still have that many tables open you’ll just need moar memories.

I should also note that while this setting says it’s dynamic, it’s a danger zone. I set this to a not-terribly-aggressive-yet-still-lower-than-current-dictionary-size value on the non-active node and everything came to a screeching halt with this in the error log:

--Thread 1264216384 has waited at dict0dict.c line 744 for 688.00 seconds the semaphore:

ALTER TABLE table ROW_FORMAT=COMPRESSED;

However, for me this did nothing. The size of the table didn’t change at all. After a bit of digging, I figured out that you have to set the default file format for InnoDB to Barracuda which has compression enabled. This is disabled by default, according to the documentation:

Although Oracle recommends using the Barracuda format for new tables where practical, in MySQL 5.5 the default file format is still Antelope, for maximum compatibility with replication configurations containing different MySQL releases.

Pretty sneaky, but easy enough to fixup:

set global variable innodb_file_format = Barracuda;

add it to your my.cnf (/etc/my.cnf on most distros):

...
innodb_file_format = Barracuda
...

After that, the ALTER TABLE converted the table and cut the size on disk in half. Very nice.

Since this table is rarely read, and even more rarely written to, I didn’t end up doing much by way of performance testing or tuning. However, I should also note blog entry I found that seems to indicate that performance degrades as the size of the buffer pool increases when compression is enabled. The workaround is to use multiple buffer pools. Fortunately, we’re already did this as a workaround for issues with slow DROP TABLE performance on older versions of 5.5 when innodb_file_per_table is enabled.


the-cloud:dev wwalter$ vagrant up
[default] VM already created. Booting if it's not already running...
[default] Clearing any previously set forwarded ports...
[default] Forwarding ports...
[default] -- 22 => 2222 (adapter 1)
[default] Creating shared folders metadata...
[default] Clearing any previously set network interfaces...
[default] Booting VM...
[default] Waiting for VM to boot. This can take a few minutes.
The VM failed to remain in the "running" state while attempting to boot.
This is normally caused by a misconfiguration or host system incompatibilities.
Please open the VirtualBox GUI and attempt to boot the virtual machine
manually to get a more informative error message.


I popped open the VirtualBox GUI as the error message suggested, and ended up with a slightly less helpful version:


Failed to open a session for the virtual machine dev_1340310725.

Result Code: NS_ERROR_FAILURE (0x80004005)
Component: Console
Interface: IConsole {1968b7d3-e3bf-4ceb-99e0-cb7c913317bb}


As it turns out, /Applications on my MacBook had somehow been set to be world-writable.. I’m not sure what application I would have installed that caused this, but it’s definitely not cool. Anyway, easy fix is to run Disk Utility and Repair Permissions.


Permissions differ on “Applications”; should be drwxrwxr-x ; they are drwxrwxrwx .
Repaired “Applications”


First, a little backstory. When we started out creating the Flickr integration for ShutterGuides, we used what seemed to be the most mature and stable Python Flickr API binding out there, flickrapi. The only drawback with this library is that it doesn’t support oauth. It seemed to be the most solid and well-documented that we found, so we went ahead anyway. In the meantime, Flickr announced that they were end-of-life-ing their old auth mechanism in favor of oauth at the end of July this year. Great. (I found a blog post at one point promising oauth, but can’t seem to find it now.) Anyway, we decided that instead of adding oauth support to the library, to just migrate to a new library, we chose this one.

All right, with that out of the way, let’s get to the point.

One of the swanky things about this new library is that it transparently retrieves information about Photo objects that aren’t necessarily returned by Flickr by default. One of the bad things about this new library is that it transparently retrieves information about Photo objects that aren’t necessarily returned by Flickr by default.

When I first developed the integration, I noticed it was a bit slower than the old one. I thought maybe I could chalk this up to extra latency from my home network to Flickr vs. our cloud provider to Flickr. However, once we got things into production, and got a real user to use it with a good amount of photos, the thing slowed to a crawl. Like, 20+s for a web request. Doh. As it turns out our call to flickr.Photosets.getPhotos (retrieve photos in a Flickr album) was not returning some of the data that we expected to be in the Photo object, and the library was graciously making an API call per photo to Flickr during our web request! (sorry about that, Flickr) The fix was trivial, we just needed to add the fields we use to the “extras” option during the original call, I just needed to notice that it was happening.

Like I said, put this one under “what a n00b!”. I knew better and yet it happened anyway. Let this be a reminder to everyone to check out that new code you just integrated into your app. I will leave you with a warning that you should take just as seriously.

The code was pretty typical and worked great in development, triggering after a save and throwing the job onto the queue. The workers consumed the jobs and pulled in photos exactly as expected.

However, I noticed that when using MySQL, this task seemed to not do the right thing as it found nothing to import. As it turns out, this was because the post_save signal in Django is fired before the MySQL transaction has been applied. So, if a worker picks it up off the queue immediately, it may not have access to the object in the database yet; or in the case of updates, will not have access to the newest version of the object.

The solution in the StackOverflow question referenced above seems to indicate something like Celery would solve this, which of course isn’t true since the job can fire before the transaction finishes as well. My solution was fairly simple, though perhaps a bit of a workaround:


- ImportPhotosTask.delay(instance)
+ ImportPhotosTask.apply_async([instance], countdown=2)


There’s also a small patch on Github to add a post_commit signal to Django, but I try to stay away from anything that calls itself a “monkey-patch” right in the description. Might be good code, got stopped at that statement..

Nothing earth-shattering so far, but some takeaways from sessions I hit up today:

Building a Multi-Master, Mult-Region Database Infrastructure in Amazon EC2

When I first realized this was going to be just a demo of some commercial software (Tungsten Enterprise), not a “lessons-learned” type of session I was pretty disappointed (I didn’t read the program carefully enough!). As the speaker got deeper into the talk, though, I became more and more impressed with the technology. Continuent has built some interesting technology, letting you not only failover (and failback!) individual nodes easily, but also entire clusters. They also have some pretty hot backup/restore/replication functionality as well.

One to Many: The Story of Sharding at Box

Definitely the most engaging session of the day for me, personally. The session was a pretty open story about Box’s migration from a single database architecture into a sharded architecture (which seems pretty new for them). There were quite a few gotcha’s along the way, especially on the application side. One of the more relevant pieces for me was in their clever use of the Tungsten Replicator to move databases between clusters in a fairly ad-hoc fashion.

The 5 Minute DBA: MySQL DBA 101 for Non-DBA’s

While I wouldn’t exactly call myself a “Non-DBA”.. I know enough to be break things worse than they already are, and I am certainly not a full-time DBA. This was a great session a great overview of the basics (needed occasionally), and a solid overview of some of the tools available in the Percona Toolkit that I know I should be using but currently am not. For shame.

Backing up Facebook

I have to be brutally honest and say attending this session was easily the biggest letdown of the day. I was hoping for more from Facebook on this one. We spent about 25 minutes listening to them talk about how they use mysqldump every day on every database server.. in a non-consistency-ensuring way. And when a pointed question was asked about what would happen when they would need to recover a host server to a point in time? The question was basically brushed off saying they’d use some other mechanism to restore (presumably Xtrabackup or something similar).. I thought that’s what we were here for? Whatever. I did have some interesting hallway conversation with a couple of fine folks about how perhaps there’s some interestingness happening on the database that they’re not wanting to talk about that solves this problem for them. That very well could be (and probably is) the right answer, but then why are they at a conference giving a session on doing backups? Ugh. Again.. whatever.

Scaling MySQL Databases for the Web

This was another title that decieved me (‘fool me twice’, or something like that..). It was less about scaling MySQL and more about a tool developed in-house at YouTube that currently front-ends all MySQL traffic there, doing some intelligent caching and optimization.

One more thing

I usually don’t enjoy or look forward to walking around in the exhibit halls at conferences like this, but I did talk with one very interesting vendor that I hadn’t heard of before. NuoDB seems to be working on a pretty interesting product that’s just about ready to release. I don’t really have the time to dive into details about how it supposedly works, but they seem to understand and handle the scaling-back part of elasticity better than most other database vendors I’ve talked with. They also have a replication strategy that’s similar to the way bittorrent works which is a pretty awesome idea. There seems to be nothing open (as in source) about the product, except they want others to write various language bindings for them, so we’ll see how well this thing takes off. Definitely an interesting product to watch, though.

All you have to do is first plug the hard drive directory into your Mac, partition as desired and format filesystems as an HFS (Mac OS Extended). Then, plugin the drive and enable disk sharing on the Airport. After that’s done, your Aiport should show up in Finder. Once you mount it, you can select it from the Time Machine settings (it will appear as a Time Capsule).

That’s it! Glad I found out and wasn’t deterred by what Apple says and is on their forums. The Airport has been amazing, video streaming is far smoother now unlike with the aging wireless router I was replacing. Definitely a recommended buy!

I don’t really have any particular loyalties or love of GNOME, I just used it because that was the default and tends to be the one that’s been polished the most by Ubuntu. While the new desktop looks great, it seems to have gone backwards quite a bit in usability. The integrations with Banshee/volume controls seem to be gone (or at least not working after my upgrade), menus are confusing and way too click-heavy, and there seems to be almost not configurability to the desktop. Even the new gnome-tweak-tool seems to have very little customizability. It seems over half of the customizations have to do with fonts and really small things that don’t matter to me at all. There are those who are passionate about fonts, but I am not one of those people.

Perhaps worse, there’s a new menu item under your username on the menu bar for “Online Accounts”. When I clicked on it, it brought me to a screen asking to give access to my Google account.. I thought maybe this could have some promise, so I granted it access to my Goolge account and it said that it had access to, among other things, docs (which I thought was going to be really awesome). However, nothing really indicated any next steps for accessing things. After poking around a bit and not finding anything, I did a quick search online to see if anyone could give any indication for what this thing was supposed to do. And, as it seems.. It does nothing. While I do enjoy a good technology preview.. I had to remind myself that this was an actual release. Ugh. I just granted access to my Google account for.. what?

One pleasant detail that I did thoroughly enjoy about the new GNOME is the OSX-style alt-tab/alt-~ app/window switching. Grouping applications and switching windows within the apps that way has been something that I’ve desired for a long time when switching back and forth. I know that most will probably hate it, but I’m a fan