Some aspects of Natural Load Testing's site have never really performed that well. The site was under incredibly rapid development so I didn't want to invest too much time in making performance tweaks on code or even architecture that might change in the next week.

No joke, we've re-written our load tester at least three times. Two complete re-writes in the same language, then we switched languages to get better threading performance

I think we hit some sort of limit in the past week or two, and things got much worse. Pages that used to be snappy jumped up to taking 10 seconds to load. Clearly something needed to be done.

When our load tester runs, we generate a run id, here's an example: 21_20111116_4ec4802b9e7b8_000016. That's the user id of whomever launched the test, the date, a unique identifier, and the id of the spawned worker. I wanted something unique, and easy to track if anything went wrong, and that certainly fit the bill.

Unfortunately it also left me executing plenty of queries along the lines of: SELECT min(`test_results`.`timestamp`)AS `min`, max(`test_results`.`timestamp`)AS `max`, sum(`test_results`.`response_time`) / 1000 AS `diff`, count(`test_results`.`run_id`)AS `completed_requests`, `test_results`.`run_id`, sum(`response_size`) as `total_transferred`, max(`test_results`.`response_time`) AS `longest` FROM `test_results` WHERE `test_results`.`run_id` LIKE('21_20111116_4ec4802b9e7b8_%') GROUP BY `test_results`.`run_id` ORDER BY `test_results`.`run_id` ASC one key problem there is the WHERE `test_results`.`run_id` LIKE('21_20111116_4ec4802b9e7b8_%'). Even indexed, that query has a hard time. I tried a few optimization techniques with different index types or other options, but the query was still hurting.

The solution was to break up that run_id into its components: the run id, and the worker id. This wasn't quite as pain free as it could have been (user IDs with different lengths forced me to go through this in several iterations) but I'm quite happy with the results. MySQL is performing a HASH index on the new run_id column, which seems perfect for this situation (there's no web interface to do anything other than an exact lookup on that column).

Results

Before: 10 rows in set (11.49 sec)
After: 10 rows in set (0.00 sec)
Sometimes you need to look further than simple indexes and query optimizations to solve problems.