Wednesday, November 28, 2007

Lesser-Known MySQL Performance Tips

dolphins by talkrhubarb
"dolphins" by talkrhubarb

I thought I knew a fair bit about MySQL before I started working on Wesabe, but I was wrong. Once your tables start to get really massive, all sorts of fun issues come out of the woodwork. There are a thousand articles out there on the basics of database optimization (use indices, denormalize when necessary, etc.), so I'm not going to cover those again. Here are some tips--most specific to MySQL--that I've learned on the job:




  • MySQL only uses one index per table when doing a query, and the query optimizer doesn't always pick the best column, so indexing everything is not necessarily a great idea. If you're always selecting on a certain set of columns, create a single multi-column index


  • related to the above, don't put indices on columns with a low cardinality. You can check the cardinality of a column by doing SHOW INDEX FROM table_name


  • if any text or blob fields are part of your SELECT query, MySQL will write any temp tables to disk rather than memory. So where possible, use large VARCHAR fields (on 5.0.3 or higher) instead. See: http://railspikes.com/2007/9/14/one-reason-why-MySQL-sucks


  • if you need to do case-sensitive matching, declare your column as BINARY; don't use LIKE BINARY in your queries to cast a non-binary column. If you do, MySQL won't use any indexes on that column.


  • SELECT ... LIMIT offset, limit, where offset is a large number == bad, at least with InnoDB tables, as MySQL actually counts out every record until it gets to the offset. Ouch. Instead, assuming you have an auto-incremented id field, SELECT * FROM foo WHERE id >= offset AND id < offset+limit (props to Coda for that one).



Update:

  • If you have a query in which you're looking for the most recent n items of something—e.g. SELECT * FROM foo WHERE bar = ? ORDER BY created_at DESC LIMIT 10)—your query will be significantly faster if you create a multi-column index on whichever field(s) you're selecting on plus the timestamp field that you're ordering by (in this case, CREATE INDEX idx_foo_bar_created_at ON foo (bar,created_at)). This allows MySQL to look up the records directly from the index, instead of pulling all the records where bar = ?, sorting them (probably first writing them to a tempfile), and then taking the last 10. EXPLAIN is your friend. If you're seeing "Using temporary table, Using filesort" in the "extra" section of EXPLAIN, that's bad.


I'll add more as we come across them. If you have any other non-obvious tips, leave them in the comments or email me (brad@ this domain) and I'll add them too.

By the way, an invaluable book for learning how to squeeze the most out of MySQL is High Performance MySQL, by Jeremy Zawodny and Derek Balling.

Monday, November 5, 2007

Tumblr

I decided to give tumblogging a try. I'm hoping that ease of use and lowered expectations will get me to post more often, and I'm liking it so far. It covers the large middle ground between blogging and twittering. I can put up something interesting without feeling like I need to construct a coherent post around it, and I don't feel like I'm pestering people with twitters (not to mention that it allows for richer media types).

I realize I could just do the same thing on this blog if I wanted to, but there's something about the minimalist formatting and ease-of-use that just seems to work. So I'll continue to post longer pieces here (I've got a number that I need to just sit down and write), and I'll keep posting inane comments to Twitter, but I imagine that the majority of interesting things I find will end up on my tumblelog.

Saturday, September 22, 2007

Social Graphs Must Die

Chris Wanstrath made a perfectly reasonable request earlier today, so I came up with the following Greasemonkey script:

// ==UserScript==
// @name Social Graphs Must Die
// @namespace Footle
// @description Redact all mentions of "social graphs"
// ==/UserScript==
document.body.innerHTML = document.body.innerHTML.replace(/(social\s+graph(s?))/ig,"<span style='background:#000;color:#000'>$1</span>");


Actually closing a tab (that wasn't opened by Javascript) in Firefox 2 isn't possible AFAICT, but this is more fun anyway.

Tuesday, August 14, 2007

I Get No Spam

After hearing a couple complaints from friends about the amount of spam they're getting, I decided to take a quick look to see where I stood. At the risk of setting myself up, I should say that I get no spam (channeling John C. Dvorak). Or at least very little--maybe one every other day gets through to my inbox.

I think the trick is just to have multiple levels of filtering. I use a Gmail address for most online transactions (for online transactions that I really don't care about--if I'm doing a one-time registration just to get a trial key, for example--I use my Yahoo address, which is nothing but a spam bucket). My Gmail address gets forwarded to my personal email address (@footle.org), which has SpamAssassin running on the server. If it makes it past SA to Mail.app, it gets inspected by SpamSieve, an awesome Bayesian spam filter for OS X mail clients.

Anyway, my stats for the last 24 hours:

Server-level filters (spam caught):
  SpamAssassin: 281
  Gmail: 32

Client-level filter (spam caught):
  SpamSieve: 17

Spam getting to my inbox: 0
Non-spam messages: 52
False positives: 1 (just Flavorpill, so not a big deal)

(Wow...330 pieces of spam in a single day. That seems way worse than when I last checked.)

Thursday, August 9, 2007

Idea-That-I-Don't-Have-Time-To-Work-On of the Week

Bonjour-enabled Git. (Although Coda tells me that Dave F. at Powerset already approached him with this idea. Neither of us have time to work on it.)

I started using Git recently and although it's a bit confounding at times, I love how cheap and easy branching is, and that you can commit incremental changes to your local repository, then later commit them in batch to subversion. Git allows for distributed development, but unless you're on the same server as other developers, or you go through the trouble of mounting their repository over the network, you can't check out their repository. Seems like it shouldn't be too difficult to give Git Bonjour powers.

Update: Chad Fowler has started such a project.

Tuesday, July 31, 2007

Binary multipart POSTs in Javascript

We recently released a very slick Firefox extension at Wesabe. It was written by my colleague Tim Mason, but I helped figure out one small piece of it—namely, how to do binary multipart POSTs in Javascript—and since it involved many hours of hair-pulling for both of us, I thought I'd share the knowledge. (Tim says I should note that "this probably only works in Firefox version 2.0 and greater _and_ that it uses Mozilla specific calls that are only allowed for privileged javascript—basically only for extensions.")

One of the cool features of the plugin is the ability to take a snapshot of a full browser page and either save the snapshot to disk or upload it to Wesabe (so you can, for example, save the receipt for a web purchase along with that transaction in your account). The snapshot is uploaded to Wesabe via a standard multipart POST, the same way that a file is uploaded via a web form.

Tim was having trouble getting the POST to work with binary data at first, and he had other things to finish, so he wanted to just base-64-encode it and be done with it. I was reluctant to do that, as the size of the upload would be significantly larger (about 137% of the original). Also, Rails didn't automatically decode base-64-encoded file attachments. But Tim had other bugs to fix, so I submitted a patch to Rails to do the base-64 decoding. I was pretty proud of this patch until it was pointed out to me that RFC 2616 specifically disallows the use of Content-Transfer-Encoding in HTTP. Doh. They also realized that it is a colossal waste of bandwidth.

Since Tim was cramming to meet a hard(-ish) deadline set for the release of the plugin, I offered to lend my eyeballs to the binary post problem. This could be a very long story, but I'll just get to the point: you can read binary data in to a Javascript string and dump it right out to a file just fine, but if you try to do any concatenation with that string, Javascript ends up munging it mercilessly. I'm not sure whether it is trying to interpret it as UTF8 or if it terminates it as soon as it hits a null byte (which is what seemed to be happening), but regardless, doing "some string" + binaryData + "another string", as is necessary when putting together a mutipart post, just does not work.

The answer required employing Rube Goldbergian system of input and output streams. The seed of the solution was found on this post, although that didn't explain how to mix in all of the strings needed for the post and MIME envelope. So here it is, in all it's goriness:


var dataURL = this.canvas.toDataURL(this.getImageType()); // grab the snapshot as base64
var imgData = atob(dataURL.substring(13 + this.getImageType().length)); // convert to binary

var filenameTimestamp = (new Date().getTime());
var separator = "----------12345-multipart-boundary-" + filenameTimestamp;

// Javascript munges binary data when it undergoes string operations (such as concatenation), so we need
// to jump through a bunch of hoops with streams to make sure that doesn't happen

// create a string input stream with the form preamble
var prefixStringInputStream = Components.classes["@mozilla.org/io/string-input-stream;1"].createInstance(Components.interfaces.nsIStringInputStream);
var formData =
"--" + separator + "\\r\\n" +
"Content-Disposition: form-data; name=\"data\"; filename=\"snapshot_" + filenameTimestamp +
(this.getImageType() === "image/jpeg" ? ".jpg" : ".png") + "\"\\r\\n" +
"Content-Type: " + this.getImageType() + "\\r\\n\\r\\n";
prefixStringInputStream.setData(formData, formData.length);

// write the image data via a binary output stream, to a storage stream
var binaryOutputStream = Components.classes["@mozilla.org/binaryoutputstream;1"].createInstance(Components.interfaces.nsIBinaryOutputStream);
var storageStream = Components.classes["@mozilla.org/storagestream;1"].createInstance(Components.interfaces.nsIStorageStream);
storageStream.init(4096, imgData.length, null);
binaryOutputStream.setOutputStream(storageStream.getOutputStream(0));
binaryOutputStream.writeBytes(imgData, imgData.length);
binaryOutputStream.close();

// write out the rest of the form to another string input stream
var suffixStringInputStream = Components.classes["@mozilla.org/io/string-input-stream;1"].createInstance(Components.interfaces.nsIStringInputStream);
formData =
"\\r\\n--" + separator + "\\r\\n" +
"Content-Disposition: form-data; name=\"description\"\\r\\n\\r\\n" + description + "\\r\\n" +
"--" + separator + "--\\r\\n";
suffixStringInputStream.setData(formData, formData.length);

// multiplex the streams together
var multiStream = Components.classes["@mozilla.org/io/multiplex-input-stream;1"].createInstance(Components.interfaces.nsIMultiplexInputStream);
multiStream.appendStream(prefixStringInputStream);
multiStream.appendStream(storageStream.newInputStream(0));
multiStream.appendStream(suffixStringInputStream);

// post it
req.open("POST", "http://yoursite.com/upload_endpoint", true);
req.setRequestHeader("Accept", "*/*, application/xml");
req.setRequestHeader("Content-type", "multipart/form-data; boundary=" + separator);
req.setRequestHeader("Content-length", multiStream.available());
req.setRequestHeader("Authorization", "Basic " + btoa(username + ":" + password));
req.setRequestHeader("User-Agent", "YourUserAgent/1.0.0");
req.send(multiStream);


Update: Spaces removed from multipart boundary per Gijsbert's suggestion in the comments (thanks!).

Sunday, May 13, 2007

Reloading Ruby Classes in Rails

I ran across a bug in Date::Format today, and after spending a few hours hacking away at a fix (the date/format.rb code is *uuuuugly* and *sloooow*...someone should really rewrite that. Better yet, rewrite it in C), I thought I'd submit a patch. So I grabbed the ruby_1_8 branch and lo and behold, my issue had already been fixed!

So the question now was how to monkeypatch the entire Date::Format module? Simply require-ing it as a plugin doesn't work, since Date::Format is already loaded at that point. The trick then is to use load instead of require.

First, I tried this:


load 'date/format.rb'


(note: load needs the actual filename; it doesn't have the magic that require does) but that gave me an error:


in `load': wrong number of arguments (1 for 0) (ArgumentError)


Turns out Rails::Plugin::Loader defines its own load, so:


Kernel::load 'date/format.rb'


et voila!