I fixed a big Technorati bug today. As the database has grown (it is now tracking over 135,000 blogs every hour) I've grown concerned about its performance. Sometimes queries would come back very quickly, and sometimes the site seemed incredibly bogged down - almost useless, even for simple queries. The worst part of it was that these performance slowdowns happened at infrequent intervals, and they seemed to be getting worse as the database grew. I fretted that I was facing a worst-case scenario - that I simply needed more RAM or disk spindles to increase the speed of the site. Even with the optimizations I did a few weeks ago, performance had slowed again. Even worse, a number of daily housekeeping chores were taking longer and longer to complete, which meant that (a) the system load was higher than it needed to be because of these extra tasks running in the background, and (b) I was in danger of allowing the data to get out-of-date, and one of the things I like the best about Technorati is the freshness of its data feeds.
This got me thinking about a great book called Zen and the Art of Motorcycle Maintenance by Robert Pirsig. This is one of my all-time favorite books. Like Thoreau's Walden, it is one of those books that I keep taking down every few years and rereading. In it, Pirsig talks about the relationship of Science, Art, Engineering, and Zen, and the enormous rift in our culture created when so many people rely on technology but so few understand it. He tells this story with analogies to motorcycle maintenance in particular, all the while discussing the scientific method - observation, hypothesis, experimentation, and analysis.
Sometimes we observe a problem and immediately jump to a conclusion - oh, that's happening because I just disconnected the battery, no wonder the lights don't work. Sometimes, we need to compare the problem with our mental map of how things should work, and then use logical deduction to figure out what is going wrong - think of Click and Clack's uncanny diagnostic powers on Car Talk; they can often figure out someone's problem just by knowing the make and model of car (mental map) and then by asking some probing questions (does it squeal when you're in neutral?) can often figure out what is wrong with a car within a sound-bite interval, along with witty comments.
But sometimes you're stuck. You can't figure out why something is going wrong. Your usual swami-like powers are just not clicking when it comes to this problem. So you get frustrated. This is a very dangerous time - Pirsig calls this a gumption trap opportunity. Because you're stuck, you search for answers, and are willing to take blind chances in order to fix the problem expeditiously. That's usually right about when all hell breaks loose, and you really start to fuck things up. So Pirsig recommends breaking out the big artillery, the gumption trap killer, the big monster: The scientific method. In Pirsig's words:
When I think of formal scientific method an image sometimes comes to mind of an enormous juggernaut, a huge bulldozer...slow, tedious lumbering, laborious, but invincible. It takes twice as long, five times as long, maybe a dozen times as long as informal mechanic's techniques, but you know in the end you're going to get it. There's no fault isolation problem in motorcycle maintenance that can stand up to it. When you've hit a really tough one, tried everything, racked your brain and nothing works, and you know that this time Nature has really decided to be difficult, you say, ``Okay, Nature, that's the end of the nice guy,'' and you crank up the formal scientific method.That's where I was at with regard to Technorati's performance. Things were too unpredictable, and I couldn't figure out why there were problems - only that a problem did indeed exist. So, I broke out the scientific method. The first thing I did was to state the problem and to start observing.
For this you keep a lab notebook. Everything gets written down, formally, so that you know at all times where you are, where you've been, where you're going and where you want to get. In scientific work and electronics technology this is necessary because otherwise the problems get so complex you get lost in them and confused and forget what you know and what you don't know and have to give up. In cycle maintenance things are not that involved, but when confusion starts it's a good idea to hold it down by making everything formal and exact. Sometimes just the act of writing down the problems straightens out your head as to what they really are.
The logical statements entered into the notebook are broken down into six categories: (1) statement of the problem, (2) hypotheses as to the cause of the problem, (3) experiments designed to test each hypothesis, (4) predicted results of the experiments, (5) observed results of the experiments and (6) conclusions from the results of the experiments. This is not different from the formal arrangement of many college and high-school lab notebooks but the purpose here is no longer just busywork. The purpose now is precise guidance of thoughts that will fail if they are not accurate.
The real purpose of scientific method is to make sure Nature hasn't misled you into thinking you know something you don't actually know. There's not a mechanic or scientist or technician alive who hasn't suffered from that one so much that he's not instinctively on guard. That's the main reason why so much scientific and mechanical information sounds so dull and so cautious. If you get careless or go romanticizing scientific information, giving it a flourish here and there, Nature will soon make a complete fool out of you. It does it often enough anyway even when you don't give it opportunities. One must be extremely careful and rigidly logical when dealing with Nature: one logical slip and an entire scientific edifice comes tumbling down. One false deduction about the machine and you can get hung up indefinitely.
MySQL is the database I've been using to backend the Technorati link data. It has a great reputation - robust, fast, and it has nearly all of the features you'd expect in a SQL database. Lots of people use it, and its code is open source, which means that its bugs are few and far-between. I also know it pretty well, so I was unafraid to make it Technorati's backbone. I dug into the MySQL manuals, and found an interesting log file configuration parameter - the "log-slow-queries" configuration file. By turning this on, I started to collect a log of all of the queries that took a long time to process - observations for my scientific method log book. I also delved deep into MySQL's analysis tool, called the "EXPLAIN" command. Using it, I could find out why a certain query was taking a long time; was it hogging the CPU? Was it chewing through disk accesses? Was it not using a database index? This was my experimental playground. Given enough observations (slow queries), I could run them through my test scenarios (individual explanations) and see what happened as I performed experiments on the database.
The first thing that I found out is that MySQL locks the entire database table when it does an INSERT or an UPDATE on a table. What that means is that all queries into the database are locked out while the Technorati spider is adding newly refreshed blogs into the database. I found that by batching INSERTs and UPDATES and by using MySQL's LOW_PRIORITY flag, I could significantly reduce the latency of database queries - which meant that interactive performance of the site rose. Good news!
Unfortunately, that didn't entirely solve the problem. I kept seeing some really slow database calls show up in the slow-queries log, often taking anywhere from 60-180 seconds to complete. That's unacceptable, most people will just click reload on their browser, which sends off ANOTHER query, loading down the database even further. Other people will just get frustrated with it and will go elsewhere. Not good.
After I had a week's worth of slow query data, I sat down with it and looked for patterns. Something niggled at my brain. I looked more closely. Then I had it. Almost all of the slow queries came from people requesting information on sites that had an underscore in the domain name or in the URL. In other words, people were looking for the link cosmos for sites like "http://p_o_l_e_c_a_t.blogspot.com", and the queries were taking forever to execute. What I remembered is that the underscore is a character that has a special meaning in MySQL queries - it is a wildcard character, which means it can stand for any character in the alphabet. So when doing a search on the URL above, instead of making one database query, MySQL was actually making tens of thousands of queries, trying out each alternative of the wildcard. All I needed to do was to tell MySQL not to treat the underscore as a special character anymore, and it just might solve the performance problem.
Lo and behold, it did. Previously, a search on "http://p_o_l_e_c_a_t.blogspot.com" took 82 seconds and searched through 180,785 rows of the database. Now, it takes less than a hundredth of a second and searches through 31 rows. All of a sudden, Technorati started firing on all cylinders again. Since a small number of queries were no longer hogging the database, all of the remaining queries got more of a chance to run, and executed even more quickly. Response time has returned to acceptable levels. I am a happy man, and even though I had to pull out the enormous bulldozer of the scientific method, wait a week for some decent observations, and spend time pulling my hair out trying to figure it out on my own, my faith is unshaken and I stand victorious.