Karl Katzke

Freshbooks 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 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.