The customer is always wrong

“The customer is always right” – that’s a saying that serious businesses actually say sometimes, with a serious and straight face. It’s one of those sayings rooted in good intentions and immediately polluted with the messy reality of the real world.

When you’re working in Database or even Network Engineering, the real answer is far more complicated and nuanced, but it’s much closer to “the customer is always wrong”. That is not to say that the Database or Network Engineer is right, either, though.

Usually the customer (in this case usually a Software Engineer) comes to us, or more likely pages us, with something like “the database is down”. Why? Because they got an error message like “The server has gone away” or “Too many connections” or “Host unreachable” or any number of error messages which are simultaneously too descriptive, and completely unhelpful. Such error messages are good at leading people astray.

For many reasons, not the least of which is that it’s impossible, error messages never say “Your query, yes the new one from ReallyExpensiveJob at line 52, added last Thursday, was really not a good idea” – that would be too simple. Instead the web page, or background job, or query quietly and efficiently consumes every resource available to it, or deadlocks some important process, or overwhelms the network, or any number of other secondary or tertiary symptoms. Maybe it even works fine most of the time but in some circumstances (say, when processing certain users’ data, or a specific shard) it goes absolutely wild.

More often than not, what that looks like both to the Software Engineer and the Database Engineer is that the “database is down”… because for all practical purposes, it is. Often though, the database is not really the problem. The database is the victim of some bad behavior: anywhere from a new bad query to an external end user clicking a button or a crawler/bot doing something.

When responding to these sorts of issues, in my experience, the priority is:

  1. Don’t make a bigger mess. Avoid taking any drastic actions to “fix” the underlying systems: don’t failover, don’t rebuild a host, don’t reboot.
  2. Provide first aid. Do what you need to: turn things off, block jobs, or even put the application in maintenance mode to give yourself breathing room.
  3. Find the root cause. Figure out what are primary symptoms and what are secondary symptoms.
  4. Patch temporarily. Get things mostly working again relatively quickly.
  5. Document the incident. Make sure future-you and your peers know what happened and what still needs to be done.
  6. Fix permanently. Implement a long-term solution.

Don’t make a bigger mess

When you log in and see that a particular thing is “broken”, it can be very tempting to try and immediately fix it. The graphs all show db73 is broken, it’s obvious that you should try replacing db73, right? The other folks responding in their own on-call rotations are likely also applying additional pressure for you to do something – anything – to fix it.

Resist the temptation to failover, rebuild, reboot, or restart things without a better understanding of the underlying problem. Sometimes doing so is the right answer, and you’ll feel stupid for not having done it sooner. More often than not, though, there are  a lot of symptoms to sort through to figure out what the root cause is.

Why not, though? A failover or reboot in the middle of an incident caused by what turns out to be a bad query can distract you, but it can also seriously exacerbate the actual problem. Changing key parts of your infrastructure in the middle of an incident may cause problems due to locks, cold caches, killed/broken queries, and just generally introduces more change to an unknown system state. Additionally, it may mask the problem (making you think you fixed the problem) because the bad actor itself gets temporarily broken/stopped by the change and takes some time to come back.

Provide first aid

Hopefully you have a few tools at your disposal to make things better without pushing new code or taking drastic measures in the underlying systems. These will usually be temporary measures to “stop the bleeding”: blocking specific kinds of jobs, IP blocks, locking specific users out, or even just putting the entire application or parts of it into maintenance mode.

The idea here is not to actually solve the problem, just to give yourself or other responders more breathing room and stop any escalation of harm. Don’t be afraid to take fairly extreme measures, so long as they are temporary and safe (and ideally well designed and documented). I promise you that users will not mind getting a maintenance page instead of a page that repeatedly times out loading.

Find the root cause

Now you’ve got some breathing room, it’s time to figure out what is going on. When things go terribly wrong though, pretty much every graph for every system will look “wrong”. You will need to systematically work through the available data, logs, and metrics to figure out what are primary symptoms and what are secondary symptoms.

Some examples of secondary symptoms which can easily confuse your ability to make sense of the data and cause you to misunderstand the problem are:

  • When the system is overloaded, every query may get slower, time out, or error. Those can be conflicting signals that send you on a wild goose chase.
  • When queries are slower, generally fewer of them will get processed, and they will “stack up” more, running concurrently. This will introduce entirely new behaviors.
  • When the database is overloaded or behaving badly, upstream systems such as web servers will behave differently and look different. The number of workers may increase. They may see more timeouts or even OOMs.
  • Conversely: Maybe the increase in workers is actually the problem and they are now overloading the databases? Confusing, isn’t it?
  • As you’re digging through logs and metrics, you’re likely to find a few things that have “always” been there and always been bad: bad queries, weird user behaviors, abusive bots, etc. Keep that in mind, and try to figure out whether something you’re seeing is new or whether it changed during the incident window.
  • The monitoring systems that provide all of this data sometimes themselves cause extra load or bad behaviors when the system is under load. This can be due to increased logging rates, the size of some frequently queried internal table, locking required to access internal data, or any number of other reasons.
  • Automatic retries in components of your systems can cause escalating or increasingly stacked concurrent loads. For example, a system may retry an operation repeatedly after a short timeout. Then what happens when the operation starts to time out repeatedly because of an unrelated problem? This can make it seem like a particular system is the root cause, when it’s really a secondary symptom.
  • Users will often make things worse. When a page doesn’t load or produces an error, users will often try it again, and again, and again. They’re impatient and tenacious. Don’t be afraid to lock them out while you work to fix things.

It can be really frustrating and challenging to figure out what is really going on when the system is overloaded and everything is broken. 

Patch temporarily

Okay, now you’ve identified what you think is the root cause.

Resist the temptation to dig into the real fix for that yet. Often, there’s a temporary solution to get the system back to “mostly” working: Perhaps you can block only a specific type of job rather than all jobs; or perhaps you can early-return an error for a specific URL rather than having the entire site in maintenance mode. Be creative, but keep things simple and temporary.

Making a clever temporary patch will also help you to validate that you’ve identified the root cause correctly before you spend time implementing the permanent fix. It sucks to spend time writing a permanent fix with thorough review and tests only to realize later that the problem was not what you understood, and your fix doesn’t help.

Document the incident

Make sure that you document everything that you did as part of this investigation to the extent possible, as soon as possible after the fire is out (or ideally even work with someone else during the incident to document it in real time):

  • Temporary patches or configuration changes that need to be reversed.
  • A proposal for the permanent fix, if you have one.
  • Interesting alternative theories about the problem, in case your final identified root cause turns out to be wrong.
  • Unrelated bad or broken things you found during your investigation.
  • Missing monitoring or alerting that would’ve identified the problem more efficiently.
  • Missing protections in the system that could’ve prevented the problem.

Fix permanently

If you’ve gotten this far, the world is no longer on fire, and you should have a little bit more time to implement a permanent and more proper fix. Ideally, someone else actually is responsible for that, so you can go back to sleep or the fix can wait for until Monday.

A guide to cleaning up in Seattle (and elsewhere)

One of the reasons we moved to Seattle in July, 2022 was to have more opportunities to contribute in building a community while living more sustainably. Anyone visiting Seattle even briefly would notice that there’s trash just everywhere. Shortly after moving here I started volunteering both by myself and with a few other groups such as Seattle Street Fixers cleaning up around Seattle.

My main focus has been in three basic areas:

  • Pedestrian accessibility and safety.
  • Bicycle, scooter, wheelchair, and other wheeled vehicle accessibility.
  • Environmental harm reduction.

Car infrastructure gets plenty of attention and funding, and I don’t care to contribute there, with the exception of the last point. I will pick up litter from car lanes and gutters to avoid it ending up in our waterways (or ending up on pedestrian or bike infrastructure).

Most of my work is in a few areas:

  • Cleaning up litter and hazards (e.g. broken glass, metal, etc.) on and around pedestrian and bike infrastructure.
  • Removing plant overgrowth and plant litter on and around pedestrian and bike infrastructure.
  • Cleaning up litter to keep it from migrating further into the environment and to improve the aesthetics of the city.

I have no interest in “cleaning up” the city by removing or harassing unhoused people, or otherwise “beautifying” the city by harming vulnerable people.

Sign up for Adopt-a-Street

The first step you’ll want to take is probably to sign up for Seattle Public Utilities (SPU) Adopt-a-Street program. SPU will provide most supplies you need for litter pickup, including bright yellow “Community Cleanup” trash bags, compost/leaf bags, trash grabbers, leaf grabbers, sharps containers, gloves, and vests. Most importantly though, they will pick up the collected trash and dispose of it at no cost. For most people, the cost of trash disposal in Seattle would make doing this as a volunteer infeasibly expensive. However if you’re working by bike, it would also be difficult to transport the heavy trash all the way to a transfer station, anyway. You don’t really have to “adopt” any particular street to participate in the program, but when you sign up you can mark your home street or similar.

Build and outfit a work-by-bike rig

I am going to list everything that I personally use for cleanup work, although I don’t bring everything every time. You should, of course, decide what kind of work you’d like to do; not everything here is required for most cleanup work. For any group cleanup, I usually bring pretty much everything I have and loan out tools and supplies to anyone who needs them.

Bike and trailer outfitted for cleanup work with a bucket strapped to the pannier carrier of the bike and the Burley Nomad trailer loaded with tools (not visible) and a flat shovel strapped to the Cargo Rack on its top.

This section is very work-by-bike centric, but most of this advice applies to anyone working by car or otherwise as well. Consider working by bike if you can, though. Sidewalks, bike lanes, trails, and other places are much more accessible by bike, and you can keep your tools and supplies much closer to where you’re working that way. It’s ideal!

Giant Explore E+ e-bike

The bike is by far the most expensive, and most personal component of the rig. I have a 2022 Giant Explore E+ e-bike, and I think it’s a good fit for this purpose. An electric bike of any sort is very helpful (and maybe even required) for getting around Seattle with a trailer loaded with tools or trash.

On any non-cargo bike, you’ll also almost certainly want to have a trunk bag and pannier carriers (or a rear rack). I find that I can strap a 5-gallon bucket to my rear rack very nicely and it doesn’t interfere with the trailer hitch (whereas a pannier on that side does).

I have also modified my bike with a Rohloff Speedhub internal gear hub, partly to have lower gears available for hill climbing with a heavy trailer, but that is certainly not required.

ABUS Pro Tectic 4960 lock

The ABUS Pro Tectic 4960 lock is a good “cafe lock” to quickly lock the rear wheel when you walk away from the bike to keep your bike safe from “grab and go” theft. On a cleanup rig, it’s handy to quickly lock the bike up without anything to lock it to (although a heavy trailer loaded with trash also discourages theft). However, a much more important (and invaluable) use for it is as a parking brake so that you can park the bike and trailer combination on a (reasonable) hill and be able to walk away from it as you pick up litter, and keep it from rolling away as you load the trailer.

Ortlieb Back-Roller waterproof panniers

I have a pair of Ortlieb Back-Roller waterproof panniers, and I keep the right-side one loaded up with various cleanup supplies so it’s usually ready to go. I generally carry the cleanup bags, extra gloves (for myself when I get my gloves wet or gross, and to lend out), a sharps container, extra straps and bungees, any any other small tools. I can also store extra clothing in the pannier, which is handy. They are waterproof so your supplies and clothes don’t get soaked.

Burley Nomad trailer with cargo rack

The Burley Nomad is probably the most versatile trailer for general cleanup work, especially with the Cargo Rack accessory. It is covered so that you can load it loosely with tools and not need to strap anything down inside it, which is great. It’s also nice to keep the rain off the contents, such as batteries and power tools that may not appreciate being left in the pouring rain. The cover may also keep passers-by from pilfering anything inside. The cargo rack accessory provides a nice flat (or angled) area with tubes that are handy to strap down trash bags, tools, and anything else you need to carry. While the Nomad trailer is very handy, its raw carrying capacity for trash and other junk is not that high.

I’ve modified my Nomad trailer so that I can tow the Flatbed trailer behind it, so that I can bring both trailers along.

Burley Flatbed trailer

The Burley Flatbed trailer is great for shuffling large amounts of heavy junk around. I can usually fit three fully loaded trash bags per trip on this trailer, and when correctly balanced, I can easily carry ~200 lbs (just don’t tell Burley, since the weight limit is only 100 lbs). The Flatbed trailer is, however, not great for carrying tools and supplies as you’ll have to strap everything down very carefully and tediously, or you’ll lose things on the street biking across town (ask me how I know).

Mr Tuffy tire liners

I use Mr Tuffy tire liners in my bike tires and all trailer tires. Especially since cleanup work necessarily means going to messy locations, and since Seattle has more than its fair share of road hazards anyway, these will keep you rolling most of the time instead of having to fix flats once a week. Hazards are mainly: broken glass, metal shards, broken-off needle tips, nails and screws, and sometimes thorns.


You need comfortable and durable work gloves, possibly waterproof ones. Always use them. Bring several pairs. I use Milwaukee Performance Work Gloves from the selection at Home Depot.

Five gallon bucket

For any litter picking, a bucket is practically a requirement. It’s a huge pain to carry a bag around while picking, it’s awkward to get the trash into the bag, and it will quickly get heavier than you want to carry. You’ll also inevitably drag it across some blackberry brambles and tear a huge hole in it, spilling all the tiny litter you just picked up. Pick the litter into a five-gallon bucket and dump the bucket into the bag each time it’s full. I typically use an orange Home Depot bucket, but I need to get an unbranded one and put some stickers on it.

Garbo Grabber collapsible litter reacher tool

I use the Garbo Grabber collapsible litter reacher (provided for free by SPU) for picking up litter. There are a lot of variations of trash grabbers available for sale, but the Garbo Grabber ones provided for free by SPU are nicer than the ones I bought from Home Depot. Whenever possible, don’t pick up trash by hand. Use a trash grabber. Needles can hide under or inside of trash. (Besides that, it’s often kinda gross, and you’ll want it at arms’ length anyway.)

Sharps container

If you’re litter picking in Seattle, you’re unfortunately absolutely going to find hypodermic needles (with blood and drug residues), razor blades, and other similar sharp things. You can get sharps containers from SPU with your other Adopt-a-Street supplies. If you’re comfortable doing so, I would encourage you to collect them and dispose of them properly to keep kids, dogs, and bike tires from finding them. In general, it’s not a big deal: bring a sharps container along, and use the trash grabber to pick up each needle individually and drop it into your sharps container. Once the container is full, dispose of it properly using the City of Seattle Sharps Collection Program.

If you’re not comfortable doing this, I understand. You can leave them in place, and ideally report them through the Find It Fix It app.

Hand broom and dustpan

A decent interlocking (for storage and keeping track of both parts) hand broom and dustpan is invaluable for cleaning up broken glass on sidewalks and bike paths. You probably don’t want to bring the one you use in the house, so it’s a good idea to get a dedicated one.

Flat square shovel

A flat square shovel is very useful for scraping up things from the sidewalk: mud, moss, etc., as well as sorting through trash on the ground (or even shoveling it up into a bag). I like this DeWalt 49-inch version, but anything you’re happy with works fine.

Rugg Leaf Scoops

A handy tool is the Rugg Leaf Scoops (provided for free by SPU), which allow you to pick up a large quantity of leaf litter for bagging. If you’re doing a substantial amount of leaf cleanup work, a pair of leaf grabbers like these is essential. Many people also find that a snow shovel is quite useful for this task.

DeWalt 60V MAX FlexVolt leaf blower

I have the DeWalt 60V MAX FlexVolt leaf blower and it’s pretty good. This is useful for blowing leaves, of course, but also sticks, seeds, and gravel. I use it a lot, and it almost always comes in handy.

DeWalt 20V MAX 22″ hedge trimmer

I have the DeWalt 20V MAX 22″ hedge trimmer for cutting back blackberries and similar. This is very useful for clearing overgrown sidewalks.

DeWalt 20V MAX reciprocating saw and blades

I have the DeWalt 20V MAX reciprocating saw for cutting trees and branches too large for the hedge trimmer. Usually when clearing overgrown sidewalks we’ll have to break this out at least a few times. It’s also useful for cutting up downed branches (or even entire trees) so that they can be moved. You’ll also need some decent pruning blades for it, such as these Diablo 9-inch pruning blades, but whatever works.

DeWalt 60V batteries

I have 7 of the DeWalt FlexVolt 20V/60V MAX 9.0 Ah battery and carry a few of them as needed in a separate bag. Especially if you intend to use the leaf blower for anything substantial, you’re going to need multiple batteries (each 9 Ah battery lasts about 15 minutes at full power). I got the specific tools that I did to maximize battery sharing; they all use the same batteries.

Collection of bungees and straps

You will need a bunch of bungees and straps to attach everything to the trailers, and to be able to move trash and other junk around efficiently without it falling off. There’s an art to strapping things down and you’ll get the hang of it eventually. I usually carry and use around 7-8 bungees of different lengths, and a couple of Husky 1 in x 8 ft lashing straps. (I’ve found that ratchet straps are too cumbersome to use for this purpose, but they do work.)

GearLight S400 Pro front and rear lights

I use the GearLight S400 Pro front and rear lights in various flashing modes when I have my bike parked on the sidewalk or near the bike path to notify oncoming people that I’m around and working. It also likely notifies potential thieves that I am around and the bike wasn’t just left there.

Find an area in need

I bike around Seattle every day to exercise, explore the city, and as my daily transportation, which means that I end up seeing a lot of areas up close. I’ll often mentally note an area in need to come back to later. When I am out doing one cleanup, I inevitably stumble upon several more. I keep a spreadsheet.

I am not usually looking for massive cleanups, but something where I can make a difference as an individual cleaning up solo, but that’s just me, as I am pre-planning-averse. I focus on areas where accessibility or safety is clearly compromised, or where a problem is likely to escalate. I often work in “less loved” neighborhoods such as SODO and Georgetown. I also try to keep my “home” streets such as Highland Park Way SW clean and accessible.

Find a community (optional, but fun!)

There are a few groups out there doing cleanup work, such as Seattle Street Fixers (@SEAStreetFixers on Twitter). It can be a lot of fun to meet like-minded people and do some work together to make a bigger difference. There are, however, groups which might be doing something in a way that you don’t agree with or doesn’t match your goals. Be sure the group matches your expectations and feel free to bail out of groups that go in a direction you don’t agree with.

Do the cleanup work

I usually start in the morning on weekends, load up the bike and head out to the work area. Upon arrival, park the bike to the side somewhere (but not far away), lock the cafe lock, come up with an overall plan for the day, and set to work. If I am primarily litter-picking I’ll usually walk with a five-gallon bucket in my left hand and trash grabber in my right, picking up as I go and dumping the bucket into bags. I’ll generally leave the bags as they get full (ideally in pairs though). As I walk, I’ll also move bulky items near to the curb/path so I can come back for them easily and not forget about them.

Bike and trailer outfitted for litter collection with a bucket strapped to the pannier holder, a pannier with supplies, and the Burley Flatbed trailer loaded with three large bags of collected litter and other items.

Once I’m done for the day (or sometimes mid-day as well), round up all the bags and other junk at a single collection point (more on that below). I usually shuttle bags and junk in several trips using the bike trailer until everything is rounded up in one place.

Create a pile and report your cleanup for pickup

If you’re working with Seattle Public Utilities Adopt-a-Street program, you can (and should!) report your cleanup on their website.

To make things as easy as possible and ensure a quick pickup, make a single pile of everything you collected, and I’ve found the following formula works well:

  • Do not block the sidewalk, bike lanes, crosswalks, or any other accessible infrastructure!
  • Choose a spot near a major intersection, but not at the intersection. Keep in mind that a potentially large truck will need to stop near the pile and load everything.
  • Place everything at the edge of the roadway so that it’s visible from both sides of the street and can be easily located.
  • Use the bright yellow Adopt-a-Street bags so that they are very visible, place them up front, and make it obvious to the driver that your pile is the one that was reported for Adopt-a-Street pickup.
  • Place everything on public property: planting strips, near tree wells, or on the sidewalk space (if possible without blocking anything).
  • If you must block or impair something, block the roadway rather than the sidewalk.
  • For the sake of cost efficiency in the Adopt-a-Street program, try to create just one pile (the bike trailer is nice for this because it’s really not painful to move things even quite long distances), but don’t kill yourself to do that. Just try not to leave five piles of two bags each for pickup… that will cost the city five times as much.
  • Make sure nothing can blow away: put or other heavy items on top of lighter items such as signs and cardboard.

Don’t forget to take a few pictures, both for the cleanup/pickup report and to tell people about it! (And if there’s any question about the pickup location, you can refer to the location the picture was taken and any landmarks you can see in the photo.)

Here are a few exemplary locations:

A pile of bags and junk left for pickup at one of my typical locations on the planting strip near the intersection of Highland Park Way SW and W Marginal Way SW.
A pile of bags and and junk left for cleanup at the curb on sidewalk and tree well near the intersection of S Winthrop St and Martin Luther King Jr Way S.

Tell people about it!

Write on Twitter, Facebook, Instagram, etc., and tell people about the awesome work you’re doing. It helps to get some encouragement from others, of course, but it also encourages others to get out and help as well. Mention your local Adopt-a-Street agency, tag your city name, and engage with others to encourage engagement. I frequently post on Twitter as @jeremycole about my cleanup efforts around Seattle.

When you see someone, say something… or help!

If you’re out somewhere and see someone cleaning up, feel free to stop by and say “hi”, offer your support, or even offer a helping hand. It’s really great to hear kind words of support. If you’re in a car, don’t honk though, as it’s very startling to a pedestrian, and a car driving at 150% of the speed limit honking in support as they speed by isn’t exactly… encouraging.

Upgrading a Giant Explore E+ 2 with the Rohloff Speedhub 500/14

The drivetrain side of my Giant Explore E+ 2 converted to the Rohloff Speedhub 500/14 from the Alki shoreline in West Seattle with the downtown Seattle skyline in the distance.
The brake side of my Giant Explore E+ 2 converted to the Rohloff Speedhub 500/14 from the Alki shoreline in West Seattle with the downtown Seattle skyline in the distance.

In September 2022, I got a 2022 Giant Explore E+ 2 GTS e-bike, which has been great (a more thorough review is possibly overdue), and I have put more than 2,000 miles on it in those few months. It’s a practical bike for my purposes: okay for road riding, gravel/dirt and light off-road, and of course has pretty robust electric assistance for Seattle’s hills and just general riding comfort. I’ve made a point to ride every day “rain or shine” so have put a lot of miles on it in the rain and snow, and I often tow a trailer full of tools for community cleanup projects.

One of my main complaints with the bike, though, has been the gearing. It comes with a 1×9 derailleur arrangement, using a microSHIFT CS-H093 11-42T cassette and a 42T chainring. In my experience, the 1st gear isn’t quite low enough for such a heavy bike in a hilly place (especially with a heavy rider and a trailer), and simultaneously, the 9th gear isn’t quite high enough for long slight descents or flats. I also don’t love having 9 gears on a derailleur with only a single chainring gear, with some of the most common gears I use (1-2 for hill climbing and 7-8-9 for cruising and descents) necessarily at fairly extreme chain angles.

For those practical reasons as well as for technical fun and aesthetic reasons, I thought it would be fun to upgrade the bike to an internal gear hub. After some research and especially checking compatibility as much as I could in advance, I settled on the Rohloff Speedhub 500/14.

Compatibility of the Speedhub

There are a few variations of the Speedhub, but most of them are not compatible due to one or more reasons. Once you work through the dizzying array of variations there are really only a few choices to be made for this upgrade:

  • Axle style: quick release or threaded
  • Number of spokes: 32 or 36
  • Color: silver, black, or red

I opted for quick release for drop-in compatibility with my current setup with trailer hitch, a 36-spoke wheel (more below), and given the dark blue and black color theme of the Giant Explore E+ 2, I went with black color to match. You’ll of course need the disc brake style, since the bike has disc brakes.

Choices in gearing

The bike comes stock with a 42T chainring, which I ended up keeping. The Speedhub allows you to easily change the input sprocket, and they sell sprockets from 13T to 21T, with the default being 16T. Between the choice of chainring and the Speedhub’s input sprocket, a lot of different gear ratios are possible.

Since I had already put a couple thousand miles on the bike with its stock gearing, I understood its current performance pretty well. I started by calculating the gear ratios of the stock bike with its 42T chainring and 11-42T cassette: 1.00 in 1st gear up to 3.82 in 9th gear. Since I was hoping to get one lower gear and one higher gear, I’d be aiming for the new 1st gear to be around maybe 0.9 or lower, and the new 14th gear around 4.2 or higher (at least 10% change for each).

With the default 16T input sprocket, the ratio of 1st gear would be 0.73 (27% lower than the old 1st) and 14th gear would be 3.85 (basically the same as the old 9th). After some spreadsheeting, I opted for a 14T input sprocket, which provides a new 1st gear ratio of 0.84 (16% lower than the old 1st) and the new14th gear would be 4.40 (15% higher than the old 9th). Sounds great! The remaining 12 gears also give a lot more intermediate steps than the old 7 gears.

This is what the comparison of the old vs. new gear systems looks like (graph made with R):

Graph of gear ratios of gears 1-9 on microSHIFT CS-H093 compared to the Rohloff Speedhub 500/14 with 14T input sprocket.

Note: The Speedhub documentation prohibits some gear ratios depending on bike and rider weight, to limit torque input. They provide a gear calculator which can be used to compare different chainring and sprocket combinations.


The bike comes with disc brakes, of course, and all we’ll really need to do is get the correct disc brake rotor for the Speedhub to be compatible with the existing brake calipers. My bike was built using a Tektro HD-M275 with a 180mm rotor of 1.8mm thickness, so I used the 180mm x 1.8mm Rohloff brake rotor, and it fits and operates perfectly.


Since the Speedhub uses a twist shifter it can’t be installed with the stock grips; it needs a shorter right grip to make space. I got the Ergon GP1 based on availability, but the GP2 or GP3 would likely be a better choice for the aesthetics of the bike (and I already miss the bar ends).

One compatibility issue: wheel speed sensor

Once I received the hub I was able to do a quick compatibility check to make sure there were no major issues.

Rohloff Speedhub, torque arm, and brake rotor test fit into the Giant Explore E+ 2 dropout with the derailleur still installed.

The stock bike uses a custom mounting location for the wheel speed sensor right next to the dropout on the left side of the bike, and uses a magnet mounted on the brake rotor to actuate it. Unfortunately that arrangement has two compatibility issues with the Speedhub:

  • The magnet is mounted to the rotor with a small carrier that expects the rotor to use the standard 6-hole mounting pattern, but Rohloff brake rotors use a 4-hole pattern with larger bolts. I knew this before getting all parts in, so I was expecting to need to do something about that, however, it didn’t end up mattering, because…
  • The sensor is mounted within range of the magnet on the mounting bolts on the brake rotor, which puts it very close to the dropout. The sensor itself has a clearance issue with the Speedhub (which is larger than most hubs), preventing the Speedhub from fitting into the dropouts at all unless the sensor is unmounted.

Not a big deal. The speed sensor is just a hall effect magnet sensor, it’s hermetically sealed, and it can pretty much go anywhere we can get a magnet to pass by it once per revolution of the wheel. There was enough slack in the sensor cable that I was able to move it back onto the chainstay, away from the dropout, and using a bit of custom shaped foam, I was able to mount it within range of a standard spoke magnet.

The speed sensor mounting location next to the dropout. A little too close, it turns out.
Speed sensor mounted on a bit of foam with zip ties, held in place with tape just to keep it from sliding on the chainstay.

I will probably custom design and 3D print a nicer looking and more permanent carrier for the sensor, but the foam part has been working nicely for me for a few hundred miles.

Building the wheel

The stock bike comes with 32-spoke Giant eX2 700c rims. Since I did this conversion at home and it was somewhat experimental (and might fail!), and I wanted to continue riding every day, I opted to build a completely new rim rather than re-use the current one. That way I could swap parts back to the derailleur at any time.

Since I would be buying all new parts, I could choose between the 32-hole and 36-hole Speedhub. I went for the 36-hole version partly due to availability, and partly because I felt like a 36-spoke wheel would be a better fit for my use: I’m heavy, the bike is heavy, and I tow heavy trailers. More spokes, more better. I also wanted to get a very sturdy/strong rim, and weight was not really a concern at all. Aesthetically, it should be black and not look terrible mixed with the stock rim on the front in case I want to keep that one.

I went with the Velocity Cliffhanger 700c 36-hole, disc brake, in black [from Northwest Bicycle]. I got Ritchey 700x19mm rim tape [from Northwest Bicycle].

The Speedhub documentation strongly suggested using specifically Sapim Race double-butted 2.0mm/1.8mm/2.0mm spokes [from Wheelbuilding Parts] and using the Speedhub documentation’s lookup table with the rim’s ERD I determined I would need 262mm length. Black, of course. I also got Sapim 12mm brass nipples to match.

After receiving all the parts, I built, tensioned, and trued the wheel myself with the fantastic assistance of Cory (@cyclegator) at the Center for Bicycle Repair on S Jackson. Rohloff specifies that only double (2x) cross lacing should be used, so that’s what we did.

Newly built wheel on a truing stand.

Be sure to check out Rohloff’s extensive documentation about wheel building for the Speedhub.


Since the Speedhub is technically “single gear”, you can use a single gear chain. They are supposed to be quieter and last longer due to having stiffer links since they don’t need to travel diagonally. I planned on keeping my old derailleur and its 9-speed chain ready to go in case something didn’t work, so I planned to get a new chain anyway. I chose the KMC e101, but due to a fulfillment error (which I didn’t notice until I was ready to install the chain) I received a completely different chain which was not compatible. Since everything was going well during my test fitting, I opted to shorten my old 9-speed chain instead. Although I received the new chain, I have been running just fine with the old 9-speed chain so far.

Installing everything

Installing the Speedhub was relatively easy for someone with moderate mechanical knowledge and ability. The instructions are excellent, although since they are generic to any bike, they are not very specific and require a bit of judgement. The build quality of everything from Rohloff is exceptional, and they provided a lot of spacers and extra hardware to ensure the installation should go well for almost any bike.

Installation in progress. You can actually see the incorrect chain I was sent in this photo. Oops.

This is the installation process I followed, more or less:

  1. Build the wheel with the new hub.
  2. Install the disc brake rotor on the hub.
  3. Install the torque arm on the hub.
  4. Remove the rear wheel.
  5. Unmount the tire from the old wheel and mount on the new wheel.
  6. Remove the chain completely.
  7. Remove the derailleur from the derailleur hanger, cut the cable end off, and remove the derailleur from the shifter cable.
  8. Remove the wheel speed sensor mounting screws and sensor cable retainer (leaving the sensor free to dangle out of the way)
  9. Install the chain tensioner on the derailleur hanger, using spacers as necessary to get the correct chain alignment.
  10. Loosen the disc brake caliper mounts to account for the slightly different position of the new brake rotor.
  11. Install the new wheel, shifting the brake calipers as necessary to align.
  12. Tighten the brake caliper mounts as necessary.
  13. Install the torque arm quick release on the chainstay using the provided instructions.
  14. Remove the old grips and shifter.
  15. Install the twist shifter and its control cables using the provided instructions.
  16. Route the control cables to the rear of the bike.
  17. Install the new grips, being careful to leave a bit of space between the shifter and the grip.
  18. Install the “external mech” box on the control cables, shortening the control cables if desired.
  19. Install the chain, shortening as necessary to achieve the correct tension.
  20. Fill the Speedhub with the provided oil.
  21. Adjust the adjustment screws for the control cables to achieve the tension you desire and make shifting feel good.
  22. Install the wheel speed sensor on the chainstay using an appropriate piece of foam or other material.
  23. Install the spoke magnet on a spoke in range of the wheel speed sensor and test that the speed is reported correctly.
  24. Cable manage and zip tie everything as desired.
  25. Done! Put the sticker on if desired.

Really not that bad, for such an impressive upgrade.

Issues and future improvements

I didn’t run into any issues of any substantial nature, actually (surprisingly). There are a few things that could be improved or which are not ideal. I’ll list them all here concisely:

  • The control cables are zip tied to the frame. I don’t mind the look, actually, and this is a work/practical bike rather than a show piece, but I may try to route them internally just for fun.
  • The control cables have an extra loop at the moment so that I have some extra length in case I want to route them internally, as above. If I decide not to route them internally, I should at least cut them shorter to remove the loop, which should improve the feel.
  • The wheel speed sensor is zip tied to a piece of foam. I intend to make a 3D printed part to hold it nicely and more permanently. When I do so, I’ll update to provide a link here.
  • I removed the derailleur, but not the shifter or shifter cable. The shifter is currently zip tied to the handlebars and out of the way, but I can remove it to clean things up even more.
  • The clearance between the 14T input sprocket and the chain tensioner mounted on the derailleur is very tight, and makes me think that with larger sprockets it actually wouldn’t be removable at all. I may look into whether it’s possible to get a slightly longer derailleur hanger to make wheel removal and installation a bit easier.

Parts list and prices

Speedhub 500/14 CC DB 36hRohloff68022$1,409.80
Speedhub rotor, 180mm x 1.8mmRohloff8287$89.00
Speedhub splined sprocket, 14TRohloff8542$21.00
Rim, Cliffhanger 700c, Disc, Black, 36hVelocity4000-62236$93.60
Rim Tape, 700 x 19mmRitchey48340847001$10.75
36 x Spoke, Race Double Butted, 262mm, BlackSapim$37.80
36 x Spoke Nipple, Brass 12mm, BlackSapim$7.92
Spoke Magnet for BDU2XX BDU3XXBosch2100160$14.66
Chain, e101KMCCN10226$40.51
Grips, GP1Ergon42410205$39.95


The bike runs and rides great! I have said that this is the gear system the bike should have had from the factory. Overall, I am very happy with the look and function of all aspects of the upgrade. It will be even better with a custom mount for the wheel speed sensor which would get rid of the only “bodge”. I may also attempt to route the control cables internally in the frame (and remove the shifter cable). In case I want to do that, I left an extra loop in the control cables to take up the extra slack rather than trimming them, but even so, shifting works fine and feels fine. I suspect it’ll feel even better once that loop is taken out.

The new 1st gear is especially great for towing a trailer uphill. I haven’t made much use of the new 14th gear, as often 12th or 13th is more then enough. In the future I may consider moving to a 15T or even 16T input sprocket to get an even lower 1st gear, but I am very happy with things as they are now.

Rohloff Speedhub and chain tensioner.
Rohloff Speedhub tension arm, “external mech” and control cables, and disc brake rotor.

If you attempt an upgrade/conversion and found this information useful, let me know!

I learned to read at 20 years old

[Note: I shared this post on Twitter and there has been a fair bit of discussion about it there. Please feel free to re-share if you find it interesting or you learned something, and I’m happy to answer questions or read your comments there, or in the blog post comments here, as well. Thanks for your support and kind words!]

If you’ve worked with me, read my technical writing, my public Twitter or Facebook posts, or read any of my posts here in the past, you would probably not consider me illiterate. (I hope, hah.) In fact you probably read the title of this post and thought it must be clickbait – but it’s not. It’s true. I was not able to read traditional books or anything else substantial until I was 20 years old.

I am dyslexic. I didn’t know it until adulthood.

In the past two decades I have worked in some exciting and incredible places and accomplished a lot. From small startups in the early days of the web, to being an early employee of and shaping the future of MySQL, moving to Silicon Valley to work at Yahoo!, running my own technical consulting company, expending inordinate blood, sweat, and tears to keep Twitter alive in its pivotal years, working in the heart of the beast at Google, and now helping the world’s entrepreneurs be successful at Shopify – I’ve been around a bit, and I’ve been incredibly lucky to have been given many opportunities to excel.

Disclaimer: This is my story, my situation. It’s deeply personal, of course, but due to human biology, and also human experiences, how dyslexia is experienced and dealt with varies a lot for everyone. Some of this probably applies to nearly everyone who is dyslexic, but no doubt some of it is my own quirks, or just wrong. I don’t really like writing about myself, and some of this is embarrassing to talk about publicly, but I am hopeful that this will help others struggling with dyslexia even if they don’t know it yet.

My insurmountable struggle with school

Growing up, my family moved a lot.

I was never in the same city, and thus the same school system, for more than a handful of years. I barely remember half of the schools I attended. I never had any long term relationships with any school staff or anyone that cared to understand what the reasons for my struggling actually were. Most of my teachers thought I was either lazy or stupid, or gave up on me. I was overweight, and in later years nerdy, and almost always (due to moving a lot) the “weird new kid”. I was bullied relentlessly by other kids.

Starting at a very young age, once I started in school, it was clear that something was wrong with me. I struggled with the most basic subjects in elementary school. I failed at, or failed to complete most of my work. It was a combination of struggling with the material itself (especially anything involving reading or writing), and boredom. I hated pretty much every single minute of school and did everything I could to avoid it. I was once (in third grade?) dragged inside the school–literally kicking and screaming–by the school principal.

I struggled through elementary and middle school, nearly failing several grades along the way. Then came high school, for which I was horribly unprepared, and I quickly realized I was in deep trouble.

Entering ninth grade, I attended a “year round” high school in Kentucky. This school had a quite unusual and somewhat experimental 4 x 90 minute daily schedule, which meant completing four full courses during the first half of the year, and starting an entirely new set of four courses after winter break. I did reasonably well.

Halfway through tenth grade, my family moved again, to another state, to a different school system – one which used a more traditional course schedule. Since we moved mid-year, this meant that I had completed half of my courses, but not yet started the other half, while the students I was joining at the new school were halfway done with all of their courses. Since the new school was in a different state, there were also slightly different credit requirements for graduation… leading to a very unpleasant school year of chaos.

Eleventh grade was a struggle as well, and afterwards, it was clear I was not going to graduate high school on time – I had failed two English courses, for one, so I would end up needing to take 10th, 11th, and 12th grade English all at the same time in my senior year, or repeat the 11th grade. I decided I had had enough. I dropped out of high school and took the GED test (after I moved states, on my own, to another state where it was legal for me to do so) to get a high school equivalent diploma.

I had planned to attend DeVry University and get a degree in Computer Science, but (long story short) it was not what I had hoped for. I struggled with depression (that I only now recognize), and I ended up attending only two semesters at DeVry before leaving and working full time instead. I’ve been working ever since.

Reading is not reading

How did I get all the way to nearly finishing high school–in the United States–while being functionally illiterate? There’s a lot to blame on the American school systems for that, and I won’t get into that here. However, one of the biggest challenges is that: reading is not reading per se.

All throughout school it would be hard to say, at any time, that I “couldn’t read”. I could read the instructions to problems, I could read aloud (mostly), I could read the board (untreated eyesight problems aside), and it would not have necessarily been obvious that, despite all of that, I could not read in the traditional sense: I could not read books, long articles, essays, etc. This meant that most academic knowledge was beyond my reach (and, to be honest, some still is).

It turns out that being “able to read” is not a singular thing where you either can or can’t – where you’re either literate or illiterate. It’s always been easy for me to read short passages, single sentences, instructions, short word problems, etc., but it was impossible for me to read books, long paragraphs, or anything really longer than half a page or so.

Writing is not writing, either. Throughout school, I really struggled with handwriting, producing only incredibly messy scribble with letters out of order, or backwards, and without space between words. To this day, unless I really concentrate on writing neatly, it’s the same.

I also struggled with math in school… especially with showing my work, rather than with understanding the math. I could not write well, but I innately understood the problems and the math behind them. I tended towards doing calculations entirely in my head, or writing scribbled “notes” (to offload my short term memory) rather than full solutions. I often used non-traditional ways of working out problems. (This is still how I do a lot of math.)

I learned that there is nothing that irritates an American math teacher more than not showing your work in the way they want you to. It doesn’t matter if you get the right answer. It didn’t matter whether I was excited about learning it.

Through much of my school years I mostly believed my teachers: I must be really lazy, or stupid. Something must be wrong with me.

A fortuitous parallel thread in my life: computers

As you are probably aware by now (or if you know me at all), I got involved in computers very early in my life, as well as (luckily) fairly early in the life of computing itself. I was 8 years old when we got my first computer in 1989: a yard sale IBM PCjr for $100, ostensibly for the family. When we got it, it was already quite outdated and limited (having been sold new about five years earlier). It came with IBM PC DOS (of course), cartridge BASIC, and most critically, the whole collection of original manuals.

Computer, operating system, and programming language documentation is somewhat unique: It tends to be composed of small fragments, each describing a single device, program, command, function, etc. Reading them, I realized very quickly that I could read just fine, and comprehend them. (In fact, I really understood them very well even for my age, and could visualize the way things worked spatially–due to dyslexia–but I didn’t know that yet.) Using the results of what I learned from those manuals, I also discovered that I really enjoyed making the computer do what I wanted.

Having full-time access to a computer for the first time, I realized that when I typed something the computer would make the symbols I wanted in the right order, and the right way around, with no effort, and I could easily edit what I had typed to fix letter swaps and typos. It didn’t take any of my mental energy to remember the right shapes or orientation for the letters or any of my patience to make myself draw them. It didn’t sap all of my confidence trying to write something and messing it up over and over. I didn’t realize it at the time, but this was life changing.

From that moment on, I’ve never stopped using and learning about computers.

The Gift of Dyslexia

About 18 years ago–I’m not entirely sure how or why–I read something about dyslexia in an article or on a web site. I had heard about dyslexia before, of course, but had a perception of it as a learning disability – something they talk about in school, but surely not something I had. After all, I was 20 years old, successfully working with computers, reading documentation, writing a thousand-page manual for MySQL, reading and writing emails every day…

I did some quick research, and on May 4, 2002, on a whim, I bought a book from Amazon that would change my entire perception of myself, and completely change the last 18 years of my life since then: The Gift of Dyslexia: Why Some of the Smartest People Can’t Read… and How They Can Learn (now available as a second edition) by Ronald D. Davis.

In The Gift of Dyslexia, Mr. Davis explains some of the workings of the dyslexic mind: the learning “disability” parts of course, but also the distinct advantages that come with it, primarily exceptional spatial reasoning and automatic three-dimensional visualization. In his description in Chapter 1, The Underlying Talent, Mr. Davis says:

The mental function that causes dyslexia is a gift in the truest sense of the word: a natural ability, a talent. It is something special that enhances the individual.

Dyslexics don’t all develop the same gifts, but they do have certain mental functions in common. Here are the basic abilities all dyslexics share:

  1. They can utilize the brain’s ability to alter and create perceptions (the primary ability).
  2. They are highly aware of the environment.
  3. They are more curious than average.
  4. They think mainly in pictures instead of words.
  5. They are highly intuitive and insightful.
  6. They think and perceive multidimensionally (using all the senses).
  7. They can experience thought as reality.
  8. They have vivid imaginations.

These eight basic abilities, if not suppressed, invalidated, or destroyed by parents or the educational process, will result in two characteristics: higher-than-normal intelligence and extraordinary creative abilities. From these the true gift of dyslexia can emerge–the gift of mastery.

I realized quickly that I saw in myself a lot of the advantages of dyslexia, and I immediately recognized the challenges as well. The book goes on to describe several methods to overcome most of the reading challenges associated with dyslexia. It’s not perfect, and I didn’t follow all of its advice, but it set me on a definite path to conquer my own dyslexic reading problems, as well as to understand and embrace the advantages it brought me.

At the time, I was traveling full time for work, flying all over the world, and during that travel I read a lot of basically trash material, as practice. In particular, I read the entire series of John Grisham novels because they were formatted well for me (see below for more on that), they were easy reads, perhaps most importantly they were readily available at every airport book shop so I could pick up a new one after finishing each one. I had to consciously work to apply the methods I learned and make it through these books, but it trained enough of my brain well enough, so that I could read other more difficult material more easily.

Finally, at 20 years old, I learned to read.

What is/was dyslexia like for me?

Before re-learning to read (and even now, if I am tired or too distracted), trying to read a page of text was a challenge: The letters are a jumble. The words are scrambled. I subconsciously read the words in the adjacent lines of text as I am reading. I jump ahead or re-read behind. I read parts of the opposite page. My imagination spins with ideas about what I read. By the time I get to the end of a paragraph, I have no idea what I’ve just read, so I have to read it again. And again. And again.

Aside from the most obvious problems with reading (which I’ve largely overcome), I have many ongoing struggles:

  • My handwriting is atrocious. Unless I am very careful (slow) and methodical, letters will be poorly spaced, mis-ordered, and otherwise pretty unreadable.
  • I can’t take good notes. Since I cannot write by hand quickly enough, or listen and/or think at the same time, handwritten notes are pretty much impossible. I can type to take notes, but that makes the kinds of quick annotations and illustrations that good note-takers usually do very hard.
  • My memory is poor. This is not because of an actual lack of memory, per se, but rather the mechanism by which I form memories. It is more physical and spatial than most. If you just tell me something, it’s gone instantly. I cannot remember singular facts without context. That includes dates, names, amounts, addresses… pretty much anything.
  • I have no sense of order. I will mix up anything that can be flipped or is ambiguous, such as left/right, north/south, east/west, function parameters in programming… the arguments to ln. It doesn’t matter how recently I’ve been told or how recently I’ve used that information.
  • I have no sense of direction. My wife will attest that no matter how many times I’ve been somewhere, or how easy it is to get there, I will get lost. This is mostly because of the previous point about my sense of order.

In re-learning to read, I realized that there are several important factors that go into reading successfully. For me, these are the most important things to understand:

  1. I must always carefully choose the physical layout of the material. The choice of font (and size), and the way a book is typeset, including the size of the margins are all important. Using an e-Reader such as Amazon Kindle helps enormously with this, as for most content I am able to choose the layout features myself. For physical books, I can glance at a few pages and quickly tell if I’ll be able to read it or not. As for me:
    • I prefer simple sans serif fonts without much decoration and with clear letter shapes.
    • I tend towards larger font sizes, as it minimizes distraction from adjacent letters/lines. Tiny text on wide pages is the worst.
    • Perhaps most importantly: Any longer text must be full justified. I find the ragged edges on the right side of a page of left justified text to be so distracting that my reading speed is less than half and I will struggle to get through anything substantial. I realize some people hate full justify, and I’m sorry.
  2. I have to evaluate the writing style of the author. This is a bit harder to describe, and varies a lot by the subject of the material. In general though, for me:
    • I avoid unnecessarily flowery writing styles. Beautiful descriptions of scenes that don’t directly apply to what is happening distract me greatly and I will get lost.
    • A well laid out logical explanation from top-down is usually best. If the author gets mired in the low-level details too early or too often, I will get lost.
    • Illustrations, especially high level or architectural ones help me “set the scene” so that my brain isn’t spending time creating potentially wrong visualizations as I read.
    • Starting with a clear overview or background of what I’m about to read, with definitions, helps a lot.
    • Keeping each paragraph reasonably-sized and on topic helps. Many really short paragraphs (like one sentence) back to back are hard for me to read. Really long paragraphs are impossible for me to read – I get lost in the sea of words.
  3. I always “read aloud” in my mind. For me there is no difference really between reading aloud and reading to myself, except for vocalizing what I am reading. I have the same internal monologue either way, and for me, it’s the only way I can read. (I know this differs with different dyslexics.) This also means that my reading speed is approximately the same (slow) either way.
  4. I use my “mind’s eye” to read. In The Gift of Dyslexia, the author describes fixing in place your “mind’s eye” (the point from which you visualize things) and not letting it wander. This is true for me, and I have to consciously choose to do this while reading text, rather than allowing my mind (and thus my eyes) to wander around the page as it normally would. Imagine my reading as like following the “ball” in karaoke. This is the part that fails first when I am too tired while trying to read.
  5. It’s critical for me to avoid distractions and noise. Until reading The Gift of Dyslexia I really didn’t understand how much simple distractions actually affected me, but they do. I frequently use noise-canceling headphones even without any music playing, just to eliminate the normal house or office noise.

When writing (typing, of course): All of the above applies, but I also have to re-read my writing many times, at least dozens. That’s why it may seem polished to you, the reader. I can’t always write well immediately, but after reading and revising so many times, it probably reads well.

When reviewing someone else’s writing: I will usually take a “style” pass to fix up the spacing, style, typos, font choices, etc., before reviewing. This is not because I’m OCD about style (although I am, as well) but rather that I literally cannot comprehend the content of the writing until I’ve first made it consumable to me. I have to fix those things, or I will trip over them so badly that my reading comprehension of the actual content can be zero. This is a “me” problem, not a you problem.

My dyslexic advantages

I’ve described all of the challenges of being dyslexic, but there are also many advantages.

One of the key things I understood after reading The Gift of Dyslexia and doing subsequent research on dyslexia in general is that the reading challenges associated with dyslexia largely stem from differences in the way the dyslexic person’s brain works. These differences produce many challenges with traditional school especially around reading and writing, but they are fundamentally a beneficial difference for many other aspects of life. So I’ll describe what I see as my “dyslexic advantages”; these are subjective based on my own experience, as I can’t feel or understand how other people’s brains work. For me, though:

  • I visualize in three dimensions. This is the thing that causes so much trouble with reading, because it’s automatic and sometimes uncontrollable, but it’s a very useful skill to have, otherwise. I innately visualize my entire life a lot like a CAD model and mentally rotate objects to see them from other angles. At a glance, I can immediately understand how parts can be joined together, how to assemble or disassemble things, what might be inside a component, etc. This is based partly on imagination, thus it’s not always correct, but it’s largely automatic and effortless.
  • I understand connections. I have an innate sense of the interconnectedness and flow of different systems or components. This applies to computer hardware, electrical systems, plumbing, software, and pretty much any other system. I can sense both how things work and how they or their connections or dependencies can fail. (This does not always extend to making small engines run, though.)
  • I sense correlations. If you’ve worked with me digging through graphs trying to find the source of some bad database traffic, you’ve probably sensed this. I often can’t explain it, but I have an (sometimes almost supernatural) ability to look at a handful of graphs and mentally sort through the noise and figure out causes vs. effects vs. bystanders.

These dyslexic advantages have proven very useful in my professional career working as a computer programmer or working on databases in various web or similar companies. They allow me to understand complex software and systems architectures very easily. I have a good idea how to successfully assemble new systems. In my hobbies and spare time they have allowed me to build very complex devices and electronics for personal projects, to help kids build robots, and to learn to fly airplanes.

Why am I sharing this? Why now?

Through my childhood and my young adult life I was both unaware of my challenges (and dyslexia itself), and ashamed/embarrassed of what those meant. In the intervening years as I came to fully understand my dyslexia and embrace it, I have talked to some people about it, but not very many. To be honest, I am still somewhat embarrassed by it.

Because I could never read during the formative years of my young life, I didn’t read any of the usual books that people generally read. I never developed any love of reading for “fun” as opposed to learning by reading. Even now I don’t really read fiction, I read non-fiction about politics, biographies, and technical topics.

I know there are millions of kids struggling in the same way that I did, without any sort of support network for them. I hope that sharing my story helps at least one struggling child, or helps a parent, teacher, or mentor understand what might be happening. Or… maybe you’re another adult reading this, and it triggers a flash of realization for you.

Reconsidering access paths for index ordering… a dangerous optimization… and a fix!

MySQL has had an interesting optimization for years now1, which has popped up from time to time: in certain circumstances, it may choose to use an index that is index-wise less efficient, but provides the resulting rows in order, to avoid a filesort of the result.

What does this typically look like in production? A query that seems simple and easy takes much longer than it should, sometimes. (Perhaps in production, the query gets killed by pt-kill or exceeds the max_execution_time provided.) The query could be very simple indeed:

SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1

There’s an index on other_id, and running the query with an appropriate USE INDEX, the query is fast. Even weirder, changing the query to use LIMIT 10 causes it to run lightning-fast! If anything, the LIMIT 1 should be faster… so, what gives?

Looking at the EXPLAIN, you may notice that the LIMIT 1 version is using access type index on the PRIMARY key (in this case id), whereas the LIMIT 10 version is using ref on a secondary key. Access type index means a full-index scan… on the primary key… which is the clustered key… which is a full table scan.

The optimization is hoping that the LIMIT n with a small enough limit will allow execution to be completed early, without scanning many rows, once the LIMIT is satisfied. This hope is often misplaced: there is no guarantee that there will be any matching rows in the first m rows of the table when ordered by the unwisely-chosen index. Hope is not a strategy.

Although the underlying issue had been reported several times already, under various circumstances, since there were so many individual bugs reported, I filed a new bug with a summary of the situation… and a patch: MySQL Bug 97001.

On MySQL Bug 97001, I proposed a solution (and provided a patch, submitted to Oracle under the NDA) introducing a new optimizer_switch flag named reconsider_index_for_order (defaulting to on to duplicate the current behavior). Although this optimization might benefit some queries, it’s too dependent on the actual data in the involved tables, so it’s not a good candidate for a general optimizer feature in my opinion. Maybe the default could eventually be off allowing users to opt into this optimization when they want it but providing compatible default behavior.

1 The underlying optimization actually appears to be have roots in some very old code, most importantly probably one specific commit in 5.1.