Xaprb

My Dell laptop’s battery died (slowly). Thinking I was being clever, I bought a replacement part from a name brand instead of Dell. That one died, too, but much more spectacularly. If I buy another battery, this time from Dell, I’ll have spent more on batteries than on the laptop.

This stinks. It’s all around us, though: refrigerators that have non-serviceable compressors, so when they wear out you have to get a new refrigerator. Lamps with sealed non-replaceable switches that have crappy solder joints that corrode through after a little while, just due to the heat and current. And thousands upon thousands of items for which replacement parts are available, but ridiculously expensive.

So do I buy a new battery, or shell out twice as much money as the battery would cost and get a new laptop?

Optimizing Oracle Performance

Optimizing Oracle Performance. By Cary Millsap, O’Reilly 2003. Page count: about 375 pages with appendices. (Here’s a link to the publisher’s site.)

This is easily one of the best books I’ve ever read on performance optimization. I’ve just finished reading it for the second-and-a-half time in two weeks, and I very rarely read a book more than once. I’ve been telling a lot of people about it.

Despite the title, it is actually not about Oracle performance. It is a book on how to optimize a) any system, including a MySQL-based application b) the process of optimization itself. It is very, very good, and I highly recommend it to all database users. My bet is that most people will learn more by reading this book than by spending thousands of dollars on conferences and training, especially since a lot of what you’ll learn from those sources is wrong and harmful. So not only will you save money and time on learning, you’ll reap great rewards thereafter.

The book is organized into three sections. The first section explains a performance optimization methodology called Method R (for response time) that is designed to deterministically advance from identifying what needs to be optimized, to collecting diagnostic data, and then choosing the correct activities to optimize. Simply put, it is probably the clearest and most logical process I’ve ever seen for focusing on what matters. It is quite similar to our process at Percona, which we call Goal-Driven Performance Optimization. But Cary does a really good job explaining it. Cary told me that he wrote the book while simultaneously developing Method R training classes, and did dozens of classes before the book went to press. You can tell!

The second and third parts of the book are about putting the method into practice.

Cary shows many typical mistakes, such as focusing on ratios, working on things that “look bad,” ignorance of Amdahl’s Law, trying to draw conclusions about specific activities by examining system-wide information, and so on. He brings the focus back to response time again and again.

Another typical “tuning mistake” is not knowing when to quit. Cary shows how to know when further performance improvements, even if they’re possible, will not be economically justified. At the point where the gains don’t exceed the cost, you’re done. The system is optimized. Maybe it’s not performing as well as it could, but if it costs too much to get any more performance, it’s still optimal.

This is a book that backs up every assertion with references to source material, or even with a proof or derivation from first principles. It is significantly more rigorous than most books of this type. There’s a very good chapter on kernel timings that explains a ton of stuff about how processes work, measurement intrusion effect, quantization error, and so on.

There is a very good section on queueing theory, with excellent examples of using it to prove that a desired improvement is mathematically impossible and/or impractically expensive. There are also examples of how to use queueing theory to predict when a hardware upgrade will worsen your performance problems. (And lots of other examples of when “optimizing” something will actually make it worse, as for example improving performance of some task other than the one you’re interested in, thereby making it use and contend for more resources — and hurting performance of the task you wanted to improve.)

What is it NOT about? It’s not about how to write more efficient SQL — there is practically nothing in the book about SQL. It’s not about how everything inside Oracle works, though it does have one or two chapters that are mostly about how to apply the principles to Oracle through extended SQL trace files, a discussion that will transfer well to any other system that emits trace data. (Read these, or you’ll miss learning about quantization error and such!)

Highly, highly recommended.

Someone recently posted this to an email list as a sample of an interesting SHOW INNODB STATUS output:

mysql> SHOW ENGINE INNODB STATUS\G
          _______  _______
|\     /|(  ____ \(  ____ \
| )   ( || (    \/| (    \/
| |   | || (_____ | (__
| |   | |(_____  )|  __)
| |   | |      ) || (
| (___) |/\____) || (____/\
(_______)\_______)(_______/

 _______  _______  _______ _________ _______  _______  _______  _______
(  ____ )(  ___  )(  ____ \\__   __/(  ____ \(  ____ )(  ____ \(  ____ \
| (    )|| (   ) || (    \/   ) (   | (    \/| (    )|| (    \/| (    \/
| (____)|| |   | || (_____    | |   | |      | (____)|| (__    | (_____
|  _____)| |   | |(_____  )   | |   | | ____ |     __)|  __)   (_____  )
| (      | |   | |      ) |   | |   | | \_  )| (\ (   | (            ) |
| )      | (___) |/\____) |   | |   | (___) || ) \ \__| (____/\/\____) |
|/       (_______)\_______)   )_(   (_______)|/   \__/(_______/\_______) 

I thought it was worth trying out, so I gave it a shot:

mysql> use postgres
ERROR 1049 (42000): Unknown database 'postgres'

Clearly I just need to create the database. Short work:

mysql> create database postgres;
Query OK, 1 row affected (0.00 sec)

mysql> use postgres
Database changed

So now I’m using Postgres. I still feel like I’m missing something, though. It feels a lot like reading XKCD comics. Where’s the tooltip?