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
hardware.
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.
Copyright © 2000-2009
1:49 pm
Nice Rich! Multi-colored charts are definitely helpful
9:46 pm
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
7:11 am
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!
7:43 am
Here Here
8:57 am
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.
9:50 am
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!
11:05 am
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.
12:34 pm
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).
3:38 pm
You Freshbooks Guys are my Hero !!
Mike M for President in 2008
10:26 pm
[...] ran into an interesting bit about selecting a MySQL Table Cache size: The problem was caused by our new configuration of MySQL. The usual approach to setting MySQL’s [...]
7:51 am
You guys simply rock…….!
3:01 pm
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.
4:30 pm
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.
2:10 pm
So that explains the slowness.
4:20 pm
thanks for new. explanations of complicated issues
4:21 pm
bencede so that explains.
4:22 pm
kelebek live Just kidding. Seriously though
9:43 am
thank you very much for this article
2:04 pm
What software do you use for that testing?
2:07 pm
Adam: The graph above is from AlertSite, who we use for performance monitoring.
2:27 pm
Thanks Rich!
2:29 pm
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.
9:08 pm
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 (http://openquery.com.au)
MySQL training & expertise
9:47 am
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.
Cheers,
Momchil
3:45 pm
Momchil,
Thanks, I’ll take a look at those two.
Best regards,
Adam
3:33 am
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.
3:34 am
Selenium is indeed a great tool for automated regression tests. It has a solid community and support base and lots of QA su deposu departments use it since it is free and at the same time provides a decent set of features.
6:00 pm
Keep up the good works. it’s good to be making business with a company who cares about its client’s curiosity.
6:01 pm
[...] ran into an interesting bit about selecting a MySQL Table Cache size: The problem was caused by our new configuration of MySQL. prefabrik evler The usual approach to setting MySQL’s [...]
12:22 pm
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.
8:07 am
I am grateful to you for this great content.
2:22 am
Keep up the good works. it’s good to be making business with a company who cares about its client’s curiosity.
8:22 am
Keep up the good works. it’s good to be making business with a company who cares about its client’s curiosity.
5:25 am
Seriously though, this is a great improvement & we appreciate the openness with your clients. Saç ekimi and saç ekimi merkezi
6:25 am
it’s good to be making business with a company who cares about its client’s curiosity.
4:06 pm
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.
3:52 am
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
3:53 am
thanks my friend
3:55 am
Momchil,
Thanks, I’ll take a look at those two.
3:55 am
thanks good article
3:56 am
So that explains the slowness.
3:57 am
nice stie thanks
3:57 am
very nice work
2:42 am
thanks a lot 3
2:44 am
thank you
very good article
2:50 am
very good work
nice cars
2:52 am
thank you
very nice wedding rings
6:39 am
thank you
nice Adwokats