MySQL Performance Blog

Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here.

The “common sense” approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL common sense however does not always works – we’ve seen performance issues with large query_cache_size also sort_buffer_size and read_buffer_size may not give you better performance if you increase them. I found this also applies to some other buffers.
[read more...]

A final update on training for this year – registration for InnoDB/XtraDB training in Chicago (8th December) and Atlanta (10th December) are now open.

While in Atlanta I’ll be giving a talk at the Atlanta PHP User Group on Optimizing MySQL Performance (details to be posted to their website shortly).  If you’re in Chicago and would like me to speak at your group on 7th-8th December, let me know!

I forwarded on a list of questions about PBXT to Paul McCullagh today.  While Paul’s busy answering them, I’d like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.

I wrote a post a while back that said why you don’t want to shard.  In that post that I tried to explain that hardware advances such as 128G of RAM being so cheap is changing the point at which you need to shard, and that the (often omitted) operational issues created by sharding can be painful.

What I didn’t mention was that if you’ve established that you will need to eventually shard, is it better to just get it out of the way early?  My answer is almost always no. That is to say I disagree with a statement I’ve been hearing recently; “shard early, shard often”.  Here’s why:

• There’s an order of magnitude better performance that can be gained by focusing on query/index/schema optimization.  The gains from sharding are usually much lower.
• If you shard first, and then decide you want to tune query/index/schema to reduce server count, you find yourself in a more difficult position – since you have to apply your changes across all servers.

Or to phrase that another way:
I would never recommend sharding to a customer until I had at least reviewed their slow query log with mk-query-digest and understood exactly why each of the queries in that report were slow.  While we have some customers who have managed to create their own tools for shard automation, it’s always easier to propose major changes to how data is stored before you have a cluster of 50+ servers.