Xaprb

Understanding MySQL Internals

Understanding MySQL Internals. By Sasha Pachev, O’Reilly 2007. Page count: about 227 pages. (Here’s a link to the publisher’s site).

I should have read this book a long time ago, and it’s my loss that I didn’t. Although the title makes it sound like it should only benefit those who’ll be changing the MySQL server’s own code, that’s not true. To the contrary, at least parts of this book should be required reading for DBAs and developers who use MySQL, after they gain a moderate level of familiarity with how to use the server.

The book does indeed start off with a few chapters on the source code, how to work with it, and the core structures that make up the MySQL server at the source code level. However, even these topics hold value for users such as DBAs. If you don’t know how the server really works, you are lost when you are faced with a problem or asked to understand some behavior. Peter Zaitsev refers to this as “X-Ray Vision,” something a good DBA or consultant needs. I think the first few chapters of this book are a great way to develop that X-Ray Vision into MySQL.

The next couple of chapters are on the client/server API, configuration variables, and thread-based architecture. Although the first is probably not a core competency for DBAs, the others are. I sure wish I’d had the client/server protocol chapter handy when I was working with the protocol, though. It is variously more useful than, and a good supplement to, the internals document on the MySQL wiki.

The following chapters cover the storage engine interface, the server-level lock manager and how it interacts with the storage-engine locking, and the parser and optimizer. These are absolutely core knowledge for DBAs and developers in my opinion. The server/storage-engine division is one of the things that makes MySQL different from other databases, and is mandatory to understand deeply. This applies equally well to the rest of the chapters, which cover the parser, optimizer, various storage engines (as opposed to just the server’s interface to them), transactions, and replication. Mandatory, every one.

What’s missing? I found that the book is kind of funny in one major way. It doesn’t talk much about MySQL 5.0. Instead, it delves into 4.x and 5.1. Most of the new features in 5.0 are not mentioned at all. Stored procedures, the INFORMATION_SCHEMA, triggers, and so on are absent, as are most discussions of changes to the optimizer and so forth. Some 5.0 topics are covered: index merge, for example. But by and large, there’s not a lot of coverage here. The 5.1-specific topics are those such as the new storage engine API and row-based binary logging. Events are not covered, nor are changes to other types of logging. Honestly, I feel this is appropriate in a book this size; the stuff that hasn’t changed since 4.x days is more important to understand.

There’s little discussion of exactly how certain features work, such as the different sorting algorithms. But that’s OK. These are covered pretty well by the MySQL manual, and even by my own book High Performance MySQL 2nd Edition. I think some other major topics might be missing, but I can’t quite think of them now.

The book is really well written. I expected it to be dry but it’s not at all. It’s actually engaging and interesting. I also found a curious thing happening as I read: I became more aware of how much legacy cruft there is inside MySQL, and how much that has contributed to various shortcomings. This made me actually feel sad, and made me yearn for the bright pure clean exciting vision that Drizzle strives towards. But at the same time I kind of felt nostalgic, kind of fell a little more in love with MySQL, for its strengths and for the countless hours of work and the really monumental genius that it embodies, warts and all. It was quite a cognitive dissonance experience, to tell the truth!

For those who have any inclination to reading it, I’d say: do it. It’ll benefit you a lot more than you think. And if possible, do it with a copy of the MySQL source code available and actually take the time to look through it and explore the things Sasha suggests. I read this book on an airplane, far from a computer, and I need to read it again and look at source code as I do so. I am positive I’ll get more than twice as much benefit from this second reading than I did from the first. I say that because I have a shin-deep exposure to the MySQL source code myself, so I knew just enough about it to recognize that I really would get a lot more from going and looking at the code Sasha cross-referenced. It was a bit like speaking Spanish without a dictionary, but having had a few weeks of intensive instruction ten years ago. I remembered some things well, other things just hazily.

Overall, this book is easily a high 4 stars on a scale of 5, and again, anyone seriously using MySQL should have it.

The best overall method of performance optimization is optimization for response time. Users care about response time, not load average or cache hit ratios. The job of a system is to accept some request and do the required work, and deliver a result. The time elapsed between the request and the result is the response time.

Methods of Response Time Optimization

Not all optimization methods are created equal. Here are a few I see commonly.

• No method. Most people simply have no method of performance optimization at all. They just look for things that look “bad” and try to make them look “better.” In the MySQL database world, the classic example is trying to improve a cache hit ratio. This is utter folly, and doesn’t become any less stupid no matter how many times it is taught and repeated.
• Server Load Reduction. One step up from that is to try to understand what work the database is performing and discover what part of that work consumes the most response time, then improve that to lower the load on the database server. This is better, but still not a logical way to optimize response time for the end user. Imagine that you’ve built your scaling strategy around archiving and purging unnecessary data — a very sensible strategy. Most well-designed archiving and purging jobs are terribly inefficient, for a reason: they are designed to consume resources that are not needed by anything else, so they don’t interfere with anything else. Archiving a billion rows from a table is best done in nibbles, not in billion-row chunks. The nibbles are going to be slow. If you measure the entire system and find out where the response time goes, you’re almost guaranteed to find these jobs are a top offender. And yet they don’t matter at all, because they have no impact on the user’s response time. Server load reduction is a shotgun approach that sometimes yields results, because it’s easy to aim a shotgun.
• Method R, or Goal-Driven Performance Optimization. Two methods I know of that are based in sound thinking are Cary Millsap’s Method R and Peter Zaitsev’s Goal-Driven Performance Optimization. Cary wrote an excellent book about his method, and I recommend buying that book and reading it at least twice. These methods are guaranteed to truly optimize the system in question: they will produce the best possible performance improvements with the least possible cost, and they have a termination condition that is satisfied when further improvements are either not possible or cost more than they are worth. A system that has been subjected to one of these methods can be confidently called “fully optimized.”

Response time in queued systems

Method R looks at where a system consumes time and sorts the biggest consumers to the top in a profile, then works on those first. Amdahl’s Law guarantees that this is the best way to improve the system’s performance.

Although the approach is correct, it doesn’t mean it’s easy. It might be easy if a system is stable. But unstable systems, those suffering from queueing delay (usually characterized by response time with a high standard deviation, a.k.a. “spikes” or “blips”), are much harder to optimize. The queries that are performing badly can no longer be assumed to be the source of the performance problem. Instead, they might be “good” queries that are the victim of something else happening.

Unstable systems suffer from a) dependencies between requests, and b) statistical variations in request arrival time, which causes queueing. The classic case is lock contention. Suppose someone goes to your OLTP database and runs an ad-hoc query against the table of invoice line items, and locks the table. Normally that table has specific, fast, well-indexed queries against it, but as soon as the ad-hoc query locks it, the queries instantly pile up and “look bad.” The system becomes an unstable train wreck. Alas, database servers such as MySQL typically don’t give the DBA enough information to blame the problem on a source.

Internal contention inside the database software itself is another potential cause of queueing. I can no longer remember the number of times I’ve disabled the query cache in MySQL and solved a system’s random freezes. Typically, the only way to prove that the query cache mutex is the source of the problem is to take a backtrace in GDB. A complex piece of software without good instrumentation is pretty difficult to troubleshoot in conditions like this.

And that brings me back to Method R. I can see that the queries are suffering from unstable performance, but I cannot see directly how to optimize the system because it is un-instrumented. Unfortunately, falling back to system load optimization is often the best that can be done, in terms of maximum optimization with minimal cost. An expert can do this with as much rigor as possible, and hopefully with good knowledge of the system’s internals can find the source of the problem quickly, but it’s still a much harder problem.

And this is why it is a crime to write un-instrumented software: because when an un-instrumented system starts to get overloaded, it is very hard to determine the source of performance problems.

I’ve been going through some old stuff that got thrown into a big cardboard box during my move in September. In particular there are a lot of papers from my desk — old notes I’d scratched down, legal pads full of notes taken during phone conversations, and so on.

Reading through these old conversations brings them back to me. There are notes from my first “non-interview” with Alan Rimm-Kaufman of The Rimm-Kaufman Group. (That non-interview led to me leave my previous job and join his company.) And lots and lots of notes on conversations I’ve had with others at the Rimm-Kaufman Group. And there’s a napkin with scribblings from when Peter and Vadim and I were riding in a car last April — the first time we’d gotten to talk alone together since I joined Percona, although I’d gotten to know them somewhat during an intense writing project that spanned a year.

One of the things that’s really neat about reading through notes from the past 3.5 years is seeing what changes over that time. For example, I’m seeing things I was working on that I’m not even involved in anymore. But even more striking is what doesn’t change at all.

To put it briefly, their focus on values is completely consistent over time — I see refinements, but the deepest core is constant. Their values come from who they fundamentally are, and they don’t change. Reading these notes from our conversations is just like reading notes I might have written a few days ago. This is a real source of inspiration and gratitude to me.

Here are some samples, slightly paraphrased because I’m expanding my shorthand back into real sentences:

• I believe that people are best at what they enjoy doing. Employees having fun and making money provide a unique value to customers.
• The best way to deliver value is to match your pay to the benefits of what you deliver, so there is only one motive and it’s aligned to the customer’s interests.
• Your career should be what you love.
• Open communications. There should never be a punishment for speaking what you feel.
• Non-experts have a lot to teach us.
• You should have a stake in the outcome.

I won’t say which quote came from whom. It doesn’t matter — any of them could have said these things. And a lot of people say such things, but the fruits of their labors bear witness to their true belief in these principles. This just supports me in my own convictions. I hope I provide good support in return.

From these conversations I have turned down many paths I might not have otherwise taken: reading Let My People Go Surfing and countless other books, for example. One of the things I love about this cycle of reinforcement is seeing the businesses follow this leadership, both internally and externally. And when I feel like I’m not aligned with my own values, I can have a conversation with one of the people whom I know share my values, and they help me find where the disconnect is and get back on track again.

Here’s an example: a a recent post on pricing from Peter, and one from Alan from a while ago. They both understand that the pricing model drives the behavior towards the customer, and they’ve both aligned with the customer’s benefit. It’s completely consistent because it’s truly who they are.

One of the other neat things about working with such people is their intense practicality. I am sometimes motivated so much towards what I feel is right, that I need their help to see a clear answer to the question “how much does this matter? What’s a good balance?” They help me to stay more centered.

These people have been and continue to be sources of inspiration and strength for me. Thanks!