Where the flamingcow roams

Fun Python question

2012-05-17T21:00:00.000-07:00

What does this do?

def func():
  print 'foo'
  return
  yield 'bar'

print func()

Take a guess, then give it a try.

Google MySQL team is hiring

2012-03-09T14:56:00.000-08:00

Do you know MySQL inside and out? Do you have experience running it production? Have you dared to look at the code? Maybe even patched it? Google's MySQL team would love to speak to you. We're hiring several software engineers in Mountain View, CA, USA to primarily work on MySQL server improvements, contributing to Google's open source MySQL branch. In addition to feature work, Google MySQL developers work closely with operations-focused engineers tracking down bugs and solving tricky performance problems, at truly enormous scale.

Sound interesting? Email flamingcow@google.com.

The dangers of max_connections

2011-12-09T11:48:00.001-08:00

We have a database that's drastically scaling up its query rate from many different clients. This typically means more client jobs and more parallelization across connections, so the first limit we hit is max_user_connections (in the mysql.user table), which we use to provide isolation between runaway users/jobs. We increase that, and we eventually start hitting server-level max_connection limits. We increased that in an emergency push last night, and several hours later performance fell off a cliff.

SHOW PROCESSLIST showed tons of processes in "Opening tables" and "closing tables" state. Our InnoDB IO stats had dropped dramatically; we clearly weren't making significant progress actually answering queries. A quick search turned up a Percona post about this, which pointed to table_cache. A quick look at SHOW VARIABLES LIKE 'table_cache' showed 91. Our config has this set to 2048; something had clearly gone wrong.

One hint was the error log message:

111209 11:08:21 [Warning] Changed limits: max_open_files: 8192 max_connections: 8000 table_cache: 91

It turns out that MySQL tries to be smart about table_cache, based on the open file rlimit. The formula is (max_open_files - max_connections - 10) / 2, which in this case is (8192 - 8000 - 10) = 182 / 2 = 91. 91 active tables is smaller than the hot set in this database, and when query rate crossed a line, the global open/close table lock and underlying syscalls couldn't keep up, and performance death-spiraled from there. We run-time set the limit higher with:

SET GLOBAL table_cache = 2048;

This overrides the automatic setting but opens the possibility of hitting the rlimit when opening tables. The real fix is to increase the rlimit to accommodate both pools of file descriptors.

Converting subselects to joins, part 2

2011-11-29T13:50:00.001-08:00

I previously discussed this in depth. However, today I saw a case that I didn't cover:

You have a table of Users and a table of Logins, with a row for each user login event. You're looking for users that have logged in within the last 31 days. The initial version of this I saw used a derived table:

SELECT
    UserId,
    LastLogin
  FROM Users
    JOIN (
      SELECT
          UserId,
          DATEDIFF(NOW(), MAX(TimeStamp)) AS LastLogin
        FROM Logins
        GROUP BY UserId
    ) AS Temp USING (UserId)
  WHERE LastLogin <= 31;

We can convert this to a simple JOIN with the magic of HAVING. HAVING is like WHERE, but applies after aggregation:

SELECT
    UserId,
    DATEDIFF(NOW(), MAX(TimeStamp)) AS LastLogin
  FROM Users
    JOIN Logins USING (UserId)
  GROUP BY UserId
  HAVING LastLogin <= 31;

Safe(r) data changes

2011-11-29T12:48:00.001-08:00

It's a great goal to avoid making manual changes to your database. It never works 100%, though; there are always software bugs, unexpected interactions and other events that muck up your data, and you have to do one-off corrections. These are inherently hazardous: hard to test for unexpected data corruption, hard to apply consistently and hard to model the application behavior that results from them. Here are some strategies for the first issue, avoiding unexpected data corruption.

Don't run one-off executables against your database. Instead, have the executable print out SQL that it would have run to update the database. If something goes wrong, you don't have to model the behavior of the program; you can just look at the SQL.
Check the SQL files into source control somewhere. Manual changes tend to breed more manual changes to fix the fixes, so you never know when you'll want a record of what you twiddled in the past.
Include all fields from the primary key in the WHERE clause. This ensures that each statement only modifies one row. Even if this results in a huge list of changes, at least you know exactly what changed.
Include as many additional gating clauses as possible, linked with AND. For example, if you have a table of products and you want to set the price to 0.99 for everything that is currently set to 1.00, do:
UPDATE Products SET Price=0.99 WHERE ProductId=2762 AND Price=1.00;
This ensures that if something else changes Price just before you run your change, you don't destroy that update.
Record the number of rows affected by each statement, in case something unexpected happens.
Use transactions sensibly. Overly huge grouping of statements can block replication, but consider whether your changes will be toxic if partially applied.
Stop running changes on errors or warnings and let a human examine the output. Warnings like string truncation can be a sign of a broken change.

#4 can be a challenge if your verification statements are complex. Consider, for example, if you want to update rows in table A for which there is exactly one row for a particular CustomerId. It's relatively easy to do a SELECT statement to verify this by hand:

SELECT
    CustomerId,
    COUNT(CustomerId)
  FROM A
  WHERE
    CustomerId IN (15, 16)
  GROUP BY CustomerId;

To verify this at UPDATE time, however, you either need a subselect or an intermediate table. We'll use the latter:

CREATE TABLE ScratchTable AS
  SELECT
      CustomerId,
      COUNT(CustomerId) AS Customers
    FROM A
    WHERE
      CustomerId IN (15, 16)
    GROUP BY CustomerId;

UPDATE A
  JOIN ScratchTable USING (CustomerId)
  SET Updated=1
  WHERE
    A.Id=3
    AND Customers=1;

The same trick works if your data change inserts new rows:

INSERT INTO A (CustomerId)
  SELECT CustomerId
    FROM ScratchTable
    WHERE
      CustomerId=15
      AND Customers=1;

InnoDB as the default table type

2011-08-09T14:03:00.001-07:00

We recently switched from MyISAM to InnoDB as the default table type. This affects CREATE TABLE without an explicit ENGINE=, as well as implicitly-created tables for sorting and such. Mark had previously discussed issues with this here, but we thought it was worth giving another try.

We've found a far more basic problem: KILL takes a long time. Consider the following:
CREATE TABLE scratch.MyShortLivedTable SELECT * FROM A JOIN B JOIN C....;
There's no ENGINE= there, so it uses the default table type. It seems to run at about the same speed as it did with MyISAM, but then you kill it. If it's MyISAM, the KILL takes effect immediately. If it's InnoDB, the CREATE is non-transactional and stays, but the implicit INSERTs have to be rolled back. If that query took 10 minutes, it may take 10 minutes to roll back, during which time the query is unkillable and MySQL won't shut down cleanly.

Our failovers rely on being able to kill all connections to a slave that they're promoting to master. Our backups rely on being able to shut down MySQL. We don't have a good answer for this, short of hardwiring ENGINE=MyISAM onto writes we know are going to be large.

Database best practices for future scalability

2011-08-08T13:33:00.000-07:00

There’s a perpetual debate about how much effort to put into scalability when first designing and building a modern web application. The opposing arguments are roughly: “Scalability doesn’t matter if your app isn’t successful; features should come first” vs. “When you know that you need to scale, you won’t have time to do it.” Below are some suggestions for scalable MySQL schema design that should get you on the right path, without being onerous enough to slow you down. Let’s start with things that are almost free:

Use InnoDB. You’re eventually likely to want transactions, foreign keys, row-level locking or relative crash safety. You can Google and find lots of InnoDB vs. MyISAM comparisons, but if you don’t want to dig too deeply, “just use InnoDB” is a safe place to start.

Don’t store data that you don’t intend to use relationally. Using MySQL as a key/value store or to store encoded data (XML, protocol buffers, etc.) in BLOB/TEXT may work, but dedicated key/value stores are likely to be more efficient.

Try to design your schema with as few hierarchies as possible. For example, take the following table layout, with arrows representing many-to-one relationships:Perhaps A=”Users”, B=”DVDs Owned”, C=”Logins”, D=”Times Watched”, while E=”Administrators” and F=”Changes”. These are two hierarchies, since A and E have no links to each other. Minimizing the number of hierarchies (keeping it to just one is awesome!) makes it easier to shard later. Schemas with cross-links (say F also links to A, or a table records transfers between two different users, linking to A twice) are very difficult to shard.

Use BIGINT UNSIGNED NOT NULL (64-bit required numeric) primary keys on every table. AUTO_INCREMENT is fine, at least to start. You can skip this for many-to-many link tables; just put both link columns in the primary key. Having single-column, numeric primary keys makes it easier to do things like drift checking and traversing between tables.

Use BIGINT instead of INT for all keys. The cost in space (4 vs. 8 bytes) and compute time is so small that you’ll never notice it, but the cost of a schema change later to increase the field size, or an accidental wraparound, can be enormous. As you expand shards, your key space becomes sparse and grows rapidly, so wide keys are critical.

Split off your data access code into an ORM layer, even if it’s very simple to begin with. This will be where your read/write split, shard lookups and change tracking will live later.

Don't use triggers or stored routines. Keep this logic in your ORM layer instead, to give yourself a single point of truth and versioning system.

Avoid subselects; use joins instead.

Don’t use views, unless you’re using a third-party ORM (rails, django) that mandates a schema structure that isn’t ideal.

Avoid network round-trips whenever possible. Use the multi-row insert syntax where possible. Enable the CLIENT_MULTI_STATEMENTS flag at connect time, then send groups of statements separated by ";".

Then, things that cost development time, in increasing order of complexity:

Use foreign keys. Don’t make references nullable; use a flag field to mark whole (sub-)hierarchies deleted instead. Combined with the hierarchy rule above, this means that you guarantee yourself that you’ll never end up with orphaned rows.

Write to masters; read from slaves. This can be quite complex, since you have to worry about replication delay. For example, you can’t have one web page hit cause a write, then the next hit render the results by reading from the database, because the result might not have replicated. However, this enables significant scaling, because hooking up more slaves is much easier than sharding.

Don’t store event-based data as one row per event. If you record page views or clicks in the database, aggregate that data into one row per hour, or per day. You can keep logs of events outside of the database in case you need to change aggregation and re-generate historical data, but don’t keep every event in a hot table.

Stop using AUTO_INCREMENT. Instead, keep a table IdSequences, and do something like:
BEGIN;
UPDATE IdSequences SET LastId=LAST_INSERT_ID(LastId+Increment)
WHERE TableName='A' AND ColumnName='b';
INSERT INTO A (b, c) VALUES (LAST_INSERT_ID(), ‘foo’);
COMMIT;
This lets you change IdSequences later to modify your sharding scheme.

Create an empty shard (new database, same schema, no data) and add test rows. Teach your application to choose which shard to talk to. This will require some method to look up a shard for the root of each hierarchy; keep all data linked to a particular root on the same shard, so you can JOIN it. At its simplest, the lookup can be (ID mod NumShards). If you have uneven shard growth, you may need an indirection table to map from virtual shard (determined by modular division with a large divisor) to physical database.

Converting subselects to joins

2011-07-12T14:03:00.000-07:00

mysql> SELECT Title
    ->   FROM Articles
    ->   WHERE ArticleId IN (
    ->     SELECT ArticleId
    ->     FROM Views
    ->   );
+-------------------------+
| Title                   |
+-------------------------+
| Interesting things      |
| More interesting things |
+-------------------------+
2 rows in set (0.00 sec)

What's wrong with this statement? It looks like it's trying to get a list of article names that have been viewed, and it seems to be doing its job. It's easy to read and to tell what's going on, even for someone with limited SQL experience. So what's there to fix?

Notice that there are two SELECT statements above. The latter is called a subselect or subquery. Just like parentheses in mathematical expressions ("5 * (2 + 8)"), you're walling off part of your statement and asking for it to be completely executed first. If that inner statement produces a huge data set (imagine this is views of, say, reddit articles), it's bad if you have to store that entire result before moving on to finding the associated articles. In reality, database engines can optimize this and be smarter than storing the whole result set, but there are no guarantees.

Fortunately, most subselects can be converted directly to joins. Let's look at a few simple examples. Given the tables:

CREATE TABLE Articles (
  ArticleId bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  Title varchar(255) NOT NULL,
  PRIMARY KEY (ArticleId)
) ENGINE=InnoDB;

CREATE TABLE Views (
  ArticleId bigint(20) unsigned NOT NULL,
  ViewedAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ArticleId (ArticleId),
  CONSTRAINT Views_ibfk_1 FOREIGN KEY (ArticleId) REFERENCES Articles (ArticleId)
) ENGINE=InnoDB;

We'll start with a simple query:

SELECT Title
  FROM Articles
  WHERE ArticleId IN (
    SELECT ArticleId
    FROM Views
  );

This is easy because it's a positive relationship: "IN" as opposed to "NOT IN". As a join, it looks like:

SELECT DISTINCT Title
  FROM Articles
    JOIN Views USING (ArticleId);

"DISTINCT" is required because Views -> Articles is many -> one, and we only want each article title once. We can use "USING" instead of "ON" because the column name is the same in both tables.

So, what if we have a negative query? Say we're looking for unviewed articles:

SELECT Title
  FROM Articles
  WHERE ArticleId NOT IN (
    SELECT ArticleId
    FROM Views
  );

We can turn this into a join by using something called an outer join. Outer joins give us back all the rows in one table, then matching rows from another, or NULLs if they don't exist. An outer join between these two tables would look like:

mysql> SELECT Title, ViewedAt
    ->   FROM Articles
    ->     LEFT JOIN Views USING (ArticleId);
+-------------------------+---------------------+
| Title                   | ViewedAt            |
+-------------------------+---------------------+
| Interesting things      | 2011-07-12 14:09:28 |
| More interesting things | 2011-07-12 14:09:29 |
| More interesting things | 2011-07-12 14:09:31 |
| Rather boring things    |                NULL |
+-------------------------+---------------------+
4 rows in set (0.00 sec)

We can then filter back down to just unread articles. We'll also avoid referencing any columns but the ones we're already joining on:

mysql> SELECT Title
    ->   FROM Articles
    ->     LEFT JOIN Views USING (ArticleId)
    ->   WHERE Views.ArticleId IS NULL;
+----------------------+
| Title                |
+----------------------+
| Rather boring things |
+----------------------+
1 row in set (0.00 sec)

Take an example query that is looking for all articles that have not been read since a certain timestamp:

mysql> SELECT Title
    ->   FROM Articles
    ->   WHERE ArticleId NOT IN (
    ->     SELECT ArticleId
    ->       FROM Views
    ->       WHERE ViewedAt > '2011-07-12 14:09:30'
    ->   );
+----------------------+
| Title                |
+----------------------+
| Interesting things   |
| Rather boring things |
+----------------------+
2 rows in set (0.00 sec)

This is slightly more complex to convert, because the naive conversion returns the wrong answer:

mysql> SELECT Title
    ->   FROM Articles
    ->     LEFT JOIN Views USING (ArticleId)
    ->   WHERE ViewedAt > '2011-07-12 14:09:30'
    ->     AND Views.ArticleId IS NULL;
Empty set (0.00 sec)

To solve this, we have to remember that we want the ViewedAt condition to be before the join, while the Views.ArticleId condition should be after. We can rewrite this to:

mysql> SELECT Title
    ->   FROM Articles
    ->     LEFT JOIN Views ON (Articles.ArticleId = Views.ArticleId
    ->                         AND ViewedAt > '2011-07-12 14:09:30')
    ->   WHERE Views.ArticleId IS NULL;
+----------------------+
| Title                |
+----------------------+
| Interesting things   |
| Rather boring things |
+----------------------+
2 rows in set (0.00 sec)

Paris

2011-05-16T12:14:00.000-07:00

I'm on a two-week trip to Dublin for work and decided to break it up with a trip to a city that I'd never been closer to than the airport: Paris. Luckily, I have a French-speaking friend who's great company, so off we went.

As the airfare to Dublin was covered by Google, I decided to splurge a bit on Paris. We ended up staying at the Hotel Raphael, which was excellent throughout. It's beautiful, if a bit overwhelming for modern tastes. Having a great concierge and a room with everything at hand, however, makes a world of difference. Also, a balcony with a view of the Eiffel Tower: awesome.

Morgan arrived later than I did on Friday, so I ended up venturing out on my own to a restaurant with concierge directions and the ability to speak about 3 words of French. I ended up at Jamin for my first taste of real French food. Lesson #1: no one orders wine by the glass. Lesson #2: holy shit, this is good! French has never been my favorite genre for restaurants in the US, but this was amazing.

Morgan arrived around midnight, and after figuring out the horribly baroque lighting system in the hotel room, we turned in and got up early. We went first to the Jardin des Plantes, which is beautiful if a bit ill-cared-for. The menagerie is actually better kept than the botanical gardens, probably because it attracts a bigger crowd. If you go, do visit the greenhouses though.

We ate lunch looking out at Notre Dame Cathedral, then walked around it. The line was too long and we didn't head in. Instead, we headed up to Sacré-Cœur, with its amazing panoramic view of Paris. We stepped inside, but weren't terribly enthused; I don't think either of us is really a church person, even architecturally.

Of course, you can't go to Paris for the first time and not see the Eiffel Tower. Again we didn't go in/up because of the throng, but it's still impressive to see.

Then we were back to the hotel to clean up and change, and off to dinner with a quick stop at the rooftop bar.

We had dinner reservations at Restaurant Victor, described by the concierge as a "bistro"; Morgan scoffed. We arrived, the food arrived, Morgan no longer scoffed. I had an appetizer of morel mushrooms in cream sauce that I will remember for quite some time.

The next morning, we checked out and headed off to a tiny, unassuming building that marks the entrance to the Catacombs of Paris. Down a long staircase, though long, small tunnels, and then past the bones of 6 million former Parisians artfully arranged. The experience is surreal; while there are plenty of pictures online, the sheer scale of the thing in person is stunning. It seems to go on and back forever. What's also stunning is something that would never happen in the US: the only things separating you from the walls of bones are signs are signs reminding you not to touch.

After emerging into the sunlight slightly stunned, we took a quick trip to see the Louvre from the outside, then the hotel to pick up bags and off in our respective directions. I think I'll be headed back to Paris for a longer stay in the future. I seem to enjoy it very much.

Avoid MySQL round trips

2011-04-22T15:30:00.001-07:00

100ms (e.g. cross-US) latency to your MySQL server? Running 100,000 statements? That's 3 hours of overhead before you even get to your statement runtime on the server. Quick hack:

mysql> delimiter |
mysql> select 1;
 -> select 2;
 -> |
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Video sharing sucks

2011-04-19T11:50:00.001-07:00

So, I took some friends to iFlySFBay the other evening. We had a great time and they gave us a DVD. It's certainly not the awesomest format to share with your friends, so I used HandBrake to rip it to h264. Next up: how do I easily share it?

I started with DropBox. Upload was nice and easy but there's no playback UI; you're stuck with your browser UI, which is pretty lousy in Chrome, but works: http://dl.dropbox.com/u/13482590/iFLY.m4v

Next up: Amazon Cloud Drive. Hold on, what, no sharing?

Then: PicasaWeb. 1 GB size limit?!?

OK, I guess I'll fall back to YouTube, since they removed the video length limit. "Failed (unable to convert video file)." Chrome can play it but YouTube can't decode it?

Facebook: 1 GiB/20 minute limit.

This really should not be a hard problem. It's a compressed version of a 30 minute movie. Why is this so difficult?

A new generation of Google MySQL tools

2011-04-01T15:41:00.001-07:00

When I started at Google, the MySQL team had a moderate open source presence. The largest part was the Google server patches for 4.0, 5.0 and some starter work on 5.1. There was also a small set of tools: mypgrep, mstat and some others. Chip Turner, who did the tools releases, had already moved to another team, and we haven't released new tools since.

I'm now happy to announce a new round of tool releases. We're just getting started, but here's what's available so far:

db.py: Easily execute queries in parallel on a sharded database
sql.py: Interactive shell to db.py
permissions.py: Manage MySQL permissions in a Python-based format
validate.py: Parse SQL using pyparsing and apply rules with live DB data

These are the actual tools being used to run MySQL at scale inside Google, not one-time sanitized copies. You can get them at http://code.google.com/p/google-mysql-tools/. The old tools and patches are still available in old/.

Thanks to the whole team for their work on these tools and especially to Razvan Musaloiu-E. for handling release code reviews. Watch/subscribe to this blog or subscribe to the google-mysql-tools mailing list for future updates. If you'll be at the 2011 MySQL conference, stop by my talk with Eric Rollins on automatic failover and Mikey Dickerson's on detecting data drift.

Net Neutrality?

2010-08-22T22:27:00.000-07:00

A few weeks ago, Google announced a starting point framework for discussion of net neutrality. It's been attacked from all sides for lots of reasons. I was having trouble forming an opinion in any direction (other than some knee-jerk Google defense), until I read this. It made me realize that the problem with the Google proposal isn't specific stances, but lack of technical detail; it makes it hard for technical people to have a rational discussion about this. Even the reddit post doesn't spell this out wonderfully.

Instead, how about some simple (technical) questions? My personal opinions are in []. Note "may" instead of "can"; all of these things are technically possible today (which is how we got here), so we're talking about industry self-regulation or government regulation.

May ISPs prioritize some protocols over others? [yes]
e.g. may an ISP give HTTP traffic priority over bittorrent?
1. Must users be able to change these settings, including disabling prioritization? [yes]
  This may be a tricky network architecture problem if the ISP has choke points that aren't the DSL or cable line. If the ISP oversubscribes their own upstream links, they have to propagate the user preference through the whole network.
2. May prioritization consider sub-protocols? [yes]
  e.g. may an ISP give text/html HTTP responses priority over video/webm responses?
May ISPs prioritize some destinations over others? [no]
e.g. may an ISP give google.com traffic priority over bing.com traffic?
User choice probably doesn't make sense here, as it's hard to argue that this type of prioritization benefits users.
1. May ISPs accept money from destination sites for prioritization? [no]
  e.g. may Verizon take $10 million/year from Google to prioritize traffic to google.com?
May ISPs prioritize some users over others? [yes]
e.g. may an ISP provide more expensive service options that prioritize traffic?
1. May ISPs prioritize users punitively? [no]
  e.g. may an ISP move a high-traffic user to a lower priority?
May ISPs block some protocols? [no]
e.g. may an ISP block bittorrent entirely?
This typically comes up as an anti-piracy solution. [Protocols themselves are never inherently anti-copyright (even if 95% of their use is for software/media piracy); the courts get this one wrong frighteningly often.]
May ISPs block some destinations? [no]
e.g. may an ISP block porn sites?
This typically comes up as a protect-the-children solution. [Filtering technology has far too many false positives, and the same technology available to ISPs is available to end users. Parents concerned about what their children are exposed to can install home solutions, which children will bypass just as easily as they bypass ISP solutions.]
May ISPs charge per-byte? [yes]
e.g. may ISPs offer cheaper plans that charge per-megabyte, possibly after the user passes a threshold?
1. Must ISPs provide per-byte users tools to see their usage? [yes]
2. Must ISPs only use the word "unlimited" for plans which are truly unlimited? [yes]
  e.g. are ISPs prohibited from calling a plan with a 250GB cap "unlimited" in advertising?
Must ISPs disclose their answers to all of these questions? [yes]
e.g. "We prioritize some destinations over others."
1. Must ISPs disclose details? [yes]
  e.g. "We prioritize google.com over bing.com."

Playing with filters

2010-06-17T16:55:00.001-07:00

I was up in Russian River a few weekends ago on a lazy afternoon, boyfriend snoozing on the couch. I had just bought a bunch of filters for the camera; they're cheap relative to other camera accessories, and I thought they might be fun to play with. Here's a shot of the view from the deck with no filter:

First, linear polarizer (the GF1 does contrast auto-focus, so you can use them, and this was focused to infinity anyway):

Rotating the linear polarizer seemed to have no effect, which isn't what should happen, AFAIK. Switch to the circular polarizer (which is really a linear polarizer with a scatter filter behind it). Rotating this does something; here are the two extremes:

Note the emphasis on the sky in the left image and on the trees in the right (light polarized differently from each?). The left is 1/2500s, right is 1/1600s (less light coming from the trees than the sky).

Next up: stacking a linear polarizer in front of the circular one (light -> linear -> circular -> lens). In theory, this creates a variable neutral-density filter controllable by the alignment of the two polarizers. However, the alignment of the front polarizer against the outside light matters too, so it's not perfectly "neutral" (I think that's responsible for the blue tint below). Extremes:

These are auto-shutter-speed, so the significance of the difference isn't apparent: left is 1/1600s, right is 1/500s (and still much darker).

Next up: colors. As you would imagine, these make the image...colored. If you then desaturate, though, you can focus on different things. mikeyd points out that you can get almost the same effect digitally, except I might be able to pick up a bit more detail by chopping out the light I don't want before it gets to the sensor.

Yellow (nice glow along the horizon):

Red (very flat; not much anywhere near red in this image):

Blue (totally blown out from the sky, but you can see shadow detail that you can't see in other images here):

Green (similar to yellow, with a bit more sky):

And then we fall off the visual spectrum and into IR, relying on the sensor to pick up non-visual light, even past its built-in hot mirror.

Near IR (starting to lose the sky):

Deep IR (sky gone, significant shadow highlights):

These take patience and a tripod, and you can't shoot action; the first is 1/6s and the second is 8s (yes, 8 seconds).

List of filters used here:

Home video re-think

2010-04-10T14:10:00.000-07:00

Choosing a platform

There's no shortage of alternatives to the traditional cable box + TV model, from cable provider DVRs to TiVo (yes, people actually still own those) to more obscure offerings like Myka, or MythTV running on your closest whitebox. However, if you want to combine easy content sourcing, central storage/management with streaming, a nice remote control interface and solid, attractive hardware, there's really only one option: for better or worse, Apple's iTunes/Front Row.

Electronics

I already had an iMac that had ended up at a common-area computer desk in my apartment. This seemed a reasonable choice for a media server, though I suppose I could've shot for something that had a concept of running headless (another Mac Mini).

Output streams to the TVs had two options: the Mac Mini, or an Apple TV. The Apple TV is smaller and cheaper, but you're locked out of all the real computing functionality (the LAN-party-on-TVs option disappears). It would be nice to be able to control these with just Apple's media remote, but it's just not practical; OS X is interrupty as hell, with updates and license agreements splattered all over. The diNovo Mini makes a decent keyboard/mouse combo that you can stuff in a drawer, but don't expect to game on it.

As actual displays, I went with LED-backlit Samsung LCDs, for the lower power usage and the light weight for wall hanging. Add in some cables and we're good to go...except that it's all sitting on the floor.

Wall mounting

Fortunately, my two TVs were wall mount efforts #6 and 7 for Kacirek, so this went really smoothly. I picked up wall mount kits from Monoprice. In short: stud finder, level, pilot holes, lag bolts, bolts to the TV, hang, done. There are even nice wall mount kits for Mac Minis, naturally at more than twice the price of the LCD mounts, since they count as "designer". The Mac Mini power adapter fits really nicely in a cable-management cutout at the back of the TV. Add in an extension cord and some cable management from Fry's, and voila:

Front Row love and hate

Front Row is, at times, awesome. It remembers pause position across different streaming clients. The interface is simple and useful. Over a fast network, seeking and fast-forward are lightning-quick. It doesn't let you set a default streaming source, but that only adds a couple of clicks.

Sometimes, it's horribly frustrating. It hangs indefinitely and uninterruptably trying to load remote library contents. It forgets pause position, even on the same machine. None of these are repeatable, so trying to solve them seems nigh-impossible.

Unofficial content

iTunes also doesn't want you using their fancy toys with torrented files; it won't let you add them to your library, and if you change the file type to work around that, it still won't stream them to remote clients. Fortunately, this is pretty simple to work around. You need Quicktime Pro, which comes with Final Cut Studio, is cheap to buy separately, or can be obtained by whatever means you like. It hides in Utilities once installed, and is easy to confuse with Apple's stripped-down but base-install Quicktime Player. Follow steps 1-4 here, and your torrented content is now draggable into iTunes and streamable to Front Row clients. It doesn't re-encode unless you do steps 5-8, so it's fast and you don't lose quality.

Automatic wake-up

I also wanted waking up the Mini clients to automatically wake up the iMac file server, so I didn't have to leave it running all the time. Again, this isn't too hard. First, pick up SleepWatcher, clearly written and packaged by someone who's never heard of dmg or a Makefile (but it works). Install wakeonlan, a tiny little PERL script that sends Wake-on-LAN magic packets. Then, add something to /etc/rc.wakeup like:

COUNT=0
while [ ${COUNT} -lt 10 ]; do
  /usr/local/sbin/wakeonlan 00:23:df:fe:c6:6d
  COUNT=$((COUNT + 1))
  sleep 1
done

Your path to wakeonlan, MAC address (of your fileserver) and packet count (and time) required for your network interface to come online may vary.

Hello, iPad?

It would be really great to be able to pull a Minority Report-style video transfer, moving streaming video seamlessly from a TV to the iPad and walking away with it. This is a pipe dream, however, until Apple decides to actually support streaming on the iPad. Seriously, Apple, I have to plug this thing in and copy the whole video to it to watch it?

(Wireless) network optimization, 2010 edition

2010-04-07T14:11:00.001-07:00

After getting the Internet connection all tuned up, it's time to talk network speed.

How fast do you need to go?

Talking about gigabit speeds around the office drew some incredulity. Most users seem to be used to talking about Internet connection speeds in the sub-10mbit range, so a 10mbit hub (which my new apartment came prewired with) and 802.11b (6mbit/s TCP) or at least 802.11g (~30mbit/s TCP) will pretty much suffice. Political arguments about the mess that is US last-mile Internet connections aside, however, there are expensive options at higher speeds. Some areas have FiOS (though Verizon has apparently stopped rolling it out), and Comcast has a 50mbit/s "Extreme" plan in my area for $115/month. DOCSIS 3 supports up to ~160mbit/s link speed. Broadband speeds don't obey Moore's law (mostly due to the enormous infrastructure investment required to deploy new tech), but we'll still probably see cable plans breaking the 100mbit/s barrier in 3 or 4 years max. In short, it's a gigabit ethernet (~700mbit/s in real life) and 802.11n (~150mbit/s with today's gear) world for the short- to medium-term.

Defining your users

My use cases for wireless at home divide pretty neatly into two categories: high-bandwidth, low-latency streaming to fixed points (Mac Minis hooked up to my TVs), and bursty-bandwidth, can-survive-momentary-latency clients that move around a lot (laptop, cellphone, iPad). I'd like both to be able to max out my Internet connection, but the video streaming needs to be able to do better inside my network (streaming from my iMac). This may get murkier once Apple get iTunes streaming to the iPad working.

The first hop

No amount of optimization on the wireless side is going to help if the cable modem to router hop can't push the full speed of the 'net connection (this presumes that they're two different devices for you). First, both should support GigE (I have a Motorola SB6120 and a D-Link DIR-855). It's harder than it should be to verify the connection speed between these two; in the end, I had to force the link on the router end to 1000mbit/s-only, then make sure it still connected.

My apartment isn't wired ideally, so the cable modem and router are in different places. The apartment has ethernet throughout, but it's only wired with 2 pairs (out of the 4 pairs in an RJ45 connector); that's only sufficient for 100mbit/s links. Kacirek brought over the toolkit and we appropriated some telephone wiring to serve as the extra two pairs, replacing the 10mbit hub with an ethernet coupler.

Going N-only

The 802.11b to 802.11g migration was a mess; networks effectively dropped back to all-B in the presence of even a single B device. G to N isn't as bad, but it's not great; 802.11 continues to accumulate backwards-compatibility hacks all over the place. However, I was surprised to find that every device except my old T60 supports N, including my Nexus One. It didn't ship with the support, and Google never indicated that an upgrade to it was forthcoming, but it must have snuck out with a firmware upgrade somewhere. After digging out an old 802.11n mini-PCI card that I bought years ago and upgrading the T60, I was able to switch from G/N to just N. This is probably a significant win, if you can manage to upgrade all your devices. If not, confining the older ones to 2.4GHz (leaving 5GHz to pure-N, rather than A/N) is probably your best bet.

There's N, then there's N

802.11n has to be one of the most consumer-confusing specs ever. N works by using multiple antennas to build virtual "spatial streams". For example, radio one has antennas 1A and 1B; radio two has 2A, 2B and 2C. The silicon supports 2 spatial channels, which get built between, e.g. 1A and 2B, 1B and 2C. These spatial channels are treated as separate links, even though they're on the same frequency. There are 16 possible antenna/radio configurations and 30 antenna/radio/spatial channel configurations. The configurations are abbreviated AxB:C (A: transmit radios/antennas, B: receive radios/antennas, C: processor-supported spatial channels). The spec goes up to 4x4:4. Unfortunately, this means that 3-antenna systems aren't necessarily 3-stream (and most sold today aren't). You can't have more streams than your lowest radio/antenna count, and your maximum speed is determined by your number of streams and frequency width. N can use 20MHz or 40MHz of radio spectrum. The DIR-855 I bought seems to be either 2x3:2 or 3x3:2; 300mbit/s max at 40MHz. It seems to be impossible to buy 3- or 4-stream consumer gear at the moment (and you need client gear to support it, so it wouldn't be too useful).

2.4GHz vs. 5GHz

802.11n makes the frequency choice even harder than it used to be. 2.4GHz is an overpopulated ghetto unless you live on double-digit acreage. It penetrates walls significantly better than 5GHz, but that's a blessing and a curse: you can use it from further away, but your neighbors interfere from further away. Even worse, at 40MHz, 802.11n takes 2 of the 3 non-overlapping 2.4GHz channels. That means that if you can see two or more neighboring access points, you're not getting full speed. The penetration advantages are significant, though: my iPad gets 6mbit/s link speed on 5GHz at the furthest point in my apartment from my access point. At 2.4GHz, it gets 26mbit/s.

Dual-band solutions help, but you have to be careful. Assign different SSIDs to your 2.4GHz and 5GHz networks, so you can force clients to one or the other. Put things like video streaming in 5GHz, where a neighbor download isn't likely to cause hiccups. Test your other devices at maximum range, and see whether you can live with the 5GHz signal level.

A little more range

If you'd like to squeak just a little more range out of your access point, either to be able to use 5GHz where you would've used 2.4GHz, or to be able to reach far-away spots with anything at all, consider replacement antennas. Higher-grade access points support them, and they'll buy you a little bit, though don't expect miracles. I picked up 3 of these, which help a bit without taking it to ridiculous extremes.

Other optimizations

Location, location, location: put your access point in the middle of your coverage area. It's the simplest thing you can do to get massive speed gains.

Make sure 802.11n short guard interval is enabled: without it, you lose about 10% of speed.

Plug it in: if you can wire it, do. This is especially true for things like home fileservers, as wiring things that you copy from to wireless effectively doubles your transfer speed and halves your latency.

Screw the neighbors: some access points have options to disable being friendly to other 802.11a/b/g devices.

Disable all the complex router filtering: turn off web filtering, try things without QoS, disable the "firewall" and "security" features. Usually processors in routers are pretty light-weight, and you can be wasting CPU time on faux-security that could be spent moving more packets faster.

Fix DNS: good chunks of interactive browsing time is spent waiting for DNS servers. Your router may be doing DNS relaying (running a local DNS server); try with and without it, to see if it sucks. If your ISP's DNS servers suck, try Google Public DNS; it's further from you network-wise, so it can't possibly be as fast as your ISP's DNS servers could be, but it's quite likely faster than they actually are.

Comcast hacking

2010-04-05T19:18:00.001-07:00

I finally gave up on my principled stand against traffic shaping and switched to Comcast when I moved. The 10x speed for the same price over Speakeasy might have had something to do with it. It's been an interesting road getting it working as I wanted, and here's the notes list:

D-Link modems don't work (well, at least). The one I got needed a custom firmware to work with Comcast according to D-Link's website, and the instructions for flashing the firmware included running a local TFTP server, then telnetting to the modem. Connection refused. Classy, guys. Took it back to Fry's, noticed that every single D-Link and Linksys cable modem box had a returned sticker on it. Bought a Motorola SB6120.
DOCSIS seems to support remote firmware flashing. The modem comes up with a firmware with the string "walledgarden" in the name, and all your connections get redirected to Comcast's activation page. The redirection is only DNS-level, though; switch to Google Public DNS servers on your router/DHCP server/computer and you can surf just fine.
Speed seems to be enforced per-account, not per-link. That means that they can't apply any speed package to you until you go through the activation wizard, which registers your modem's HFC-side MAC address with Comcast. The "walledgarden" speed actually appears to be Comcast's Ultra package (22/5mbps). That means that if you're not signed up for Ultra or Extreme (50/10), but chose Performance (12/2) or Blast! (16/2) instead, it's actually in your best interest to not activate.
If you have to activate, the technician workflow is way easier: no installing software that messes with your network settings, and it works in Linux.

More on the great wireless network project coming soon.

Fun with map visualizations

2010-03-26T23:00:00.000-07:00

I've been playing with Google's Chart Tools quite a bit lately.

I did some internal stuff at work (that unfortunately I can't show here) to visualize problems where you have lots of points, all connected by lines that you know the ideal length of, but where those lengths don't lay out properly in 2 dimensions, and figuring out where to put the points is hard anyway.

The most elegant solution seems to be a force-directed graph: pretend the lines are springs, randomize the initial point positions, and simulate physics iteratively. It's slow, and the algorithm is easy to screw up (don't forget friction, or they just orbit each other), but the results are impressive: consistent layouts that are rotation-agnostic each time you run the algorithm. I ended up rendering the results using the static scatter chart. Sadly, the URL syntax is arcane, and the error feedback is nonexistent.

Today, I got to build a visualization that went public:

We had lots of data from the fiber-to-the-home request-for-information site, and needed a way to visualize it. This is a geo map that uses markers. Unfortunately, the API limits you to 400 points, which wasn't enough, so I (at nmlorg's suggestion) screenshotted the map with 400 points at a time and stitched the results; hacky, but functional. We couldn't have used the direct rendering anyway, as it does one AJAX call to Google Maps to look up each point (we were passing in ZIP codes), so it takes ~10 minutes to render.

We considered static maps (custom icons would've been cool), but the URL length limit makes the effective point limit less than 400, and there's only so much stitching I'll put up with.

Karaoke project

2010-03-09T22:18:00.000-08:00

Friend and sometimes boss mikeyd had a friend ask him to help her with her entry into a karaoke contest. Mikey suggested that I could try out the video recording on the new camera, so I hauled out some of the old sound recording gear, bought some new mics, grabbed the camera and the tripod and headed to Avalon at Mission Bay's penthouse to film.

We shot 14 takes, of which 3 were usable. Top discard reasons:

Video exposure problems
Artist goofs
Background sound (other people in the room)

The internal mic on the camera is pretty much useless for anything but tourist shots, so I recorded audio on MicroTrack II, one mic into each channel. This gave pretty good (though not perfect) separation of the guitar and vocal tracks. When I got home, over several days, I cut the audio into multitrack mode with Soundtrack Pro, adding more vocal to the mix, a bit of stereo separation, fade in and fade out, and a bit of reverb to the vocals. I tried a some light auto-tune, but the guitar strumming picked up on the vocal track sounded really, really odd through the auto-tuner.

I used iMovie (I didn't have time for the Final Cut Pro learning curve) to cut the original audio off, sync the new audio track, trim the junk out of the video, normalize the audio volume, and export 720p to YouTube. Here are the 3 results:

Lessons:

Shooting in front of a window? Maybe it would look nicer as a night shot.
Leave plenty of time on either end of both sound and video; trying to sneak the last second out of the clip is lame.
Get a board to show take number to the camera. Say it clearly on the audio track. Stop and start between each take, even if they screw up the first note.
Think you've got 2 or 3 good takes out of 14? You probably need to shoot 30.
Put whoever's providing rhythm on a click track, even if you don't think you'll cut multiple videos together. Write the BPM on the cue board.

Photo of the day

2010-02-28T19:48:00.001-08:00

Finished jump 16 today. I'm finally landing the 220 (square foot) parachute without getting dragged along the ground, but I'm still not practicing what I should be in free fall (the last attempts at back flips have ended with me stuck on my back, fighting to get turned over again). After 3 jumps, I stopped and photographed the next load. Favorite picture:

Second (click and look at it in high res -- I love the grass lighting):

Pond swooping is supposed to look like this:

Not like this:

The whole series is here. Video of plane takeoff here.

Lessons:

The EyeFi card is slow (class 4 or less), making burst mode painful once I run out of buffer, especially shooting fast-paced stuff like parachute landings. Pro X2 may be better (and supports RAW), but not released yet.
No aperture or shutter speed changes in burst mode. Start shooting near sun, end shooting away, end pictures are really dark.
Auto-focus has trouble with crazy focus changes while shooting video. Try telling it what object to focus on first?
Pictures are way prettier on the big LED-backlit iMac screen at full size than at 400px on this blog post.

Confusing BIND with CNAMEs

2009-09-11T15:04:00.000-07:00

Given the zone:

subdomain IN NS nameserver

nameserver IN CNAME nameserver.otherserver

in a BIND server that is both recursive and authoritative, requests without RD (recursion desired) return the NS record, while requests with RD return SERVFAIL. Changing it to:

subdomain IN NS nameserver.otherserver

fixes the problem, though both resolution trees are perfectly valid.

The odd case of my mugging

2009-02-19T21:55:00.001-08:00

I think I'm writing this down just so I can keep track of it all:

I was mugged last September on Mountain Shadows Drive in Mountain View, less than a block from my house. At the time, I was walking home with take-out sushi. The two black guys who mugged me seemed new to the trade: they were confused when I asked them if I could just give them the cash: first they said yes, then they changed their minds when I only had $14. They got the cash, my credit cards, and my driver's license (what a pain that is to replace). They never showed a weapon; apparently, it's a lesser crime if they can manage the robbery without showing one, and I didn't have much incentive to fight.

The events after that are pieced together from credit card data and a police department that is very efficient and enthusiastic, but doesn't communicate well. Someone bought a tank of gas at the local shell station with one credit card. They tried at WalMart, but used the card linked to my ING feeder account at BoA with no money in it, so it was declined. The muggers apparently gave at least one card to a women named Charlotte Rapuet, who took it to Body Kneads and charged $200 to it. She got away with this, despite the obvious gender mismatch, because she knew the clerk, a woman named Yadira Hernandez.

Yadira was caught fairly easily: the credit card company led the police to the spa, where the security camera footage mysteriously didn't match any of the recorded transactions for the evening (she had switched the tape). She plead no contest, turned in Charlotte, and I watched her sentencing today: 12 days served, probation for 2 years, various fines and fees (court security fee?!?) and restitution to Body Kneads (who got stuck holding the bad credit card charge). She looks to be in her early twenties and apparently has one child and a very supportive family. The court waved the public defender fee.

Charlotte has refused to turn in the person who gave her the card, presumably one of the muggers (she made it to the spa within 2 hours of the mugging). Her plea hearing is 9am on March 9th. I intend to attend; this experience has, at the very least, been an interesting lesson in the workings of our justice system.

5-packet TCP connection?

2009-02-03T16:25:00.000-08:00

This seems to be valid:

1) C->S: SYN
2) S->C: SYN/ACK
3) C->S: FIN/ACK
4) S->C: FIN/ACK
5) C->S: ACK

A completely opened and closed TCP connection. Further, 2 and 3 can both contain data? Could you get a whole HTTP request/response in this conversation?