For the last day or two we’ve received a number of reports that FreshBooks was running far slower than it ever had in the past. Given that this weekend we’d had some downtime to upgrade our infrastructure, things running slowly the first business day after that was the last thing we expected.
But there’s good news: after a long day of checking every corner, we managed to track down the problem to an unexpected bottleneck in MySQL. Clearing up that bottleneck has let us tap the full speed of our new
Among other measurements, we monitor site performance by measuring the amount of time it takes to log in and send an invoice from various locations around the world. In the graph below you can see how eliminating that bottleneck has helped performance significantly:
FreshBooks is now running faster than ever. Thanks to those of you who struggled through yesterday with us. We are now pleased to introduce you to our new hardware and the fastest running FreshBooks experience to date – enjoy!
For those curious about what happened, there’s some details under the cut.
The problem was caused by our new configuration of MySQL. The usual approach to setting MySQL’s table cache size is to make it large enough that it can hold all of your tables open, but we have too many tables for that to be practical. Instead, we decided to make it big, to improve the odds that tables we need were already in the cache. And since we had new, bigger hardware with more RAM and faster disks, we decided to cache even more tables.
Unfortunately, while opening tables is fast in MySQL, the speed at which it can close tables gets slower and slower as the table cache grows. Linearly, in fact. And since the cache will always end up full, the speed that MySQL can close tables becomes the speed that MySQL can open tables. And to exacerbate the problem, opening and closing tables in MySQL uses a global lock, so only one thread can be opening or closing tables at a time, regardless of how many threads are executing in parallel.
Once we understood this, we realized that if the cache is going to be full, it needs to be kept as small as possible. We reduced it to the minimum we could get away with without having to reopen tables in the middle of one session interacting with FreshBooks. And now that closing tables is faster, everything’s faster.