Now we’re flying!

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.

Share this article
Share on FacebookTweet about this on TwitterShare on LinkedIn
  • Randy

    Nice Rich! Multi-colored charts are definitely helpful 😉

  • Joe

    I really appreciate the honest explanation and enjoy actually hearing the technical reason behind the problem. Most companies these days try to pass the blame off to someone else, and if they do admit to it, they don’t tell you WHAT caused the problem.

    Hearing such a detailed and technical breakdown helps my confidence in FreshBooks because not only are you honest, but it shows me that you really know your systems and learned something at the same time. Plus I learned something too :)

  • Jeff Courteau

    Nice job, and I appreciate it too when people are honest like that, it’s good to be making business with a company who cares about its client’s curiosity 😉

    Keep up the good works!

  • Tm Mahdi

    Here Here 😀

  • Leon

    As a potential customer this explanation is a very refreshing change to the usual “it was the hardware” excuse carted out by most suppliers. Freshbooks is easy to use AND now its super quick. But as a techie myself I can’t help saying that MS SQL Server wouldn’t have had this problem ;)…. There would be many others.

  • Rich Lafferty

    Leon: One of our developers formerly worked on DB2 at IBM. I bet you can imagine some of the opinions he’s had on MySQL in the last couple of days! :-)

  • Tom

    Hey, how can I become one of the folks that’s a green line on the chart?? Just kidding. Seriously though, this is a great improvement & we appreciate the openness with your clients.

  • Rich Lafferty

    Oh, sorry, I could have labeled that chart better! The purple line is the relevant one — the time it takes to complete sending a new invoice.

    The green line is DNS request time, and yellow is “first byte”. Purple is “full content”.

    This chart’s just from one of the monitoring locations. Multiple locations is useful when we’re seeing different results from different places, but when we know it’s at our end we just pay attention to one (New York, in this case).

  • Joe Vaccone

    You Freshbooks Guys are my Hero !!
    Mike M for President in 2008

  • Pingback: MySQL Cache Optimization » Karl Katzke | PHP, Puppies, and other Geekery()

  • francis

    You guys simply rock…….!

  • John Saxon

    I found this claymation site that really helps give clear explanations of complicated issues.I know colored charts can help, but what if freshbooks was to implement a animation video that would help even the most elementary internet user gain a grasp of your technology. Just a thought.

  • David Ledgerwood

    We’re a leadership development consultancy and I just wanted to take a chance to applaud your transparent handling of this issue. As others have said I can count on one hand the number of times a vendor has just explained the bare technical details to me. It’s much appreciated. I continue to be a big fan.

  • Adam

    What software do you use for that testing?

  • Rich Lafferty

    Adam: The graph above is from AlertSite, who we use for performance monitoring.

  • Adam

    Thanks Rich!

  • Adam

    While I’m asking, do you use Selenium or another front end testing tool for regression testing? We’re looking for something other than Selenium.

  • Arjen Lentz

    table_cache is for MyISAM tables, I’m quite surprised you’re using MyISAM as your main storage engine – you mentioned invoicing, too.
    MyISAM is not ACID compliant, and is not that safe with crash recovery (depends somewhat on the rest of the config, it’s not absolute anyway).
    Therefore, with modern machines with enough RAM and such, InnoDB is generally recommended as the default storage engine.

    With MyISAM you’re also relying on the disk cache for row-level caching, and of course there’s the table-level locking for updates/deletes; all this affects performance, in the latter case for reasons of concurrency bottlenecks.

    Regards, Arjen.
    Director, Open Query (
    MySQL training & expertise

  • Momchil

    Hey Adam,

    Selenium is indeed a great tool for automated regression tests. It has a solid community and support base and lots of QA departments use it since it is free and at the same time provides a decent set of features.

    If you would like to use something “more professional”, two good options are HP’s QTP (quick test pro) or Borland’s SilkTest. Those will definitely cost you money, but many would argue they are worth every penny.



  • Adam

    Thanks, I’ll take a look at those two.

    Best regards,

  • satis egitimi

    thank for the article, well done!

  • Webmarker

    Hello, great article – thanks!

  • Farid

    good article my friend

  • Marketingsen

    Very nice article. Thank you guys!

  • beolex

    Keep up the good works!

  • Köln | Dialog SEO Agentur

    Congrats from Cologne, Germany! Keep up the good work and keep on flying!

  • Mevin Muller

    Thank you great post, nice diagrams.

  • Vidalı Kompresör

    great post from interesting article

  • Feuerwehr Spiele

    Hello, very good article – thanks!

  • Tafelfolie

    Thank you very Much … I’ll use it

  • Mutfak Modelleri

    good article from you its really good story

  • Cicek Sepeti

    great article and nice site with good informations

  • lobur

    thanks.good article from you its really good story

  • Ernst

    Been using freshbooks a long time now. I remember this update, you guys have been making freshbooks working even better since. Keep up the good work!

  • vi da nam

    […]They also revealed that we will be staying at the Pearl Fiji. It is surrounded by water and it offers one of Fiji’s longest stretches of beach. One of our favourite things to do is to take a sunset walk after a day of adventure.[…]

  • Lüks Araç Kiralama

    You can rent a car from us.