When enabled, the feature puts a drop-down box next to your email address at the top of the page which lets you toggle between accounts. So far, this only works for Gmail, Google Calendar, Google Sites, Google Reader, Google Voice, App Engine and Google Code. Visiting an unsupported service will switch you to your default account, and signing out of any account will log out all the others.
Multiple sign-in isn't perfect yet. Google says it doesn't work on mobile devices and there is a handful of other issues: the Google Calendar gadget doesn't work properly in Gmail, you can no longer use offline Gmail or offline Google Calendar, and the "note in Reader" bookmarklet only works for the default account. Nevertheless, it still beats repeatedly retyping your credentials.