High Performance MySQL: Optimization, Backups, Replication, and Load Balancing

by Derek J. Balling and Jeremy D. Zawodny

Book Overview

As users come to depend on MySQL, they find that they have to deal with issues of reliability, scalability, and performance--issues that are not well documented but are critical to a smoothly... This description may be from another edition of this product.

• Format: Paperback
• Language: English
• ISBN: 0596003064
• ISBN13: 9780596003067
• Release Date: April 2004
• Publisher: O'Reilly Media
• Length: 276 Pages
• Weight: 1.15 lbs.
• Dimensions: 0.8" x 7.2" x 8.7"

Customer Reviews

Outstanding

The second edition of this book is far superior to any documentation or other books on MySQL that I have read. The content is extremely useful (see the table of contents), the writing is excellent and the explanations and diagrams are so lucid and elegant. The workarounds and other nifty pieces of advice are also valuable. I often couldn't put it down, which is unbelievable for a database text!

Turbo Charging MySQL

Well, the first thing you want to do when you finish a book like this is go on and on about how impressed you are. This is one of the best database technology books I've read, and the best one on MySQL. Digging into the book I would say I was an intermediate DBA. With 12 years experience on Oracle I was a seasoned DBA. And although I've used MySQL for about 10 years, I had not used all of the high end or newest 5.0 and 5.1 features. After reading this book, or while reading it, you'll be ready to dig into everything from MySQL master-master replication (not to be confused with multi-master), creating a logging server, optimizing your query cache, or even using some of the Google MySQL patches to add some much needed but missing feature to MySQL. The book is organized pretty well. Keep in mind that this is not a beginner book. If you're looking for more general across the board MySQL book, I'd recommend the APress Pro MySQL by Kruckenberg and Pipes Pro MySQL (Expert's Voice in Open Source). It is also very good, but hits more of the beginning topics (as well as some advanced ones). So given the intermediate to advanced audience, this book dives right into benchmarking and profiling at the beginning. Queries... those pesky SQL commands that you send to your database. They're so important to performance, yet so sadly misunderstood. This book devotes two chapters to the topic, one about schema and index optimization, and one about query performance. These two work together. You need to understand indexing to make best use of them, and how to write good queries to get only the data you need. The indexing chapter hit on index types supported by MyISAM, and ones for InnoDB. It talked about rebuilding, and when it's important, and statistics, and how they are different across the different storage engines. And this is a key point. Going into this book with my Oracle background, I had a lot of questions about how the optimizing engine aka the cost-based optimizer, works and interacts with the storage engines. It's all laid out here in clear detail. It was pretty obvious that these others are closely involved with the actual database development, and/or interviewed some of them to get the information correct. This is something I've had a hard time finding in other books, and really key to understanding how to optimize and tune queries. Where does the query cache sit, when and how are queries parsed, when does the optimizer pickup statistics, and how does it use them. You'll learn all the ins and outs of the explain facility, which you'll of course need to know to tune queries. The next chapter on advanced features covered the query cache in detail, how to set it up, how to tune it, and how to monitor it. The chapter also covers UDFs, cursors, stored procedures, views, full-text searching, merge tables, partitioning and so on. One other topic it really investigated was distributed (XA) transactions. You might at first think the

Good book overall, but may grow obsolete to MySQL5.

I mainly bought this book so that I could get some insight into 'advanced' Storage and Replication techniques w/ MySQL. Jeremy provided some pretty detailed and easy to understand examples, with decently comprehensive descriptions which did help answer some of the questions I had. I'd suggest this book to anyone who wants to understand the principles of Storage and Replication techniques in MySQL4. This book is definately a kick in the right direction, but does not take you too far, so I'd say this is for intermediate users. MySQL5 has many new storage and replication features not mentioned in this book, some of which resolve a lot of the 'problematic' storage and replication issues that this book discusses, thus making SOME of the content irrelevent (or obsolete) to MySQL5. However, the overall principles remain the same, and can be applied to either version. If you're using MySQL4, then this book is for you! If you're using MySQL5, you may want to wait for a revised edition of this book. I sure hope Jeremy is working on a revised version for MySQL5! *hint*hint* =)

A must for MySQL Administrators

If you interact with MySQL on a regular basis, High Performance MySQL should be the next book that you read. High Performance MySQL does a great job at covering techniques on benchmarking your current configuration and how to increase performance at 3 major levels: 1) database architecture, 2) server tuning, and 3) scaling horizontally (with multiple servers). Database architecture is where it really begins. Zawodny and Balling did a great job explaining the different storage engines along with their advantages and disadvantages, working with transactions, how to get the most of your database through indexing and how to optimize query performance. Zawodny and Balling also did an excellent job on covering server tuning. It just wasn't a turtorial on 'this is how you should modify your configuration files.' The authors whent into great detail in explaining different hardware configurations, what to look for in RAID configurations and different filesystems, and how to solve various bottlenecks. For the most part, the authors reserved a complete 60 pages of the book for Replicaiton and Load Balancing configurations. The authors provided several scenarios to choose from along with the advantages and disadvantages of each.

Well explained MySQL concepts

As an Oracle DBA, I was looking for a book that can help me better understand MySQL core concepts and differences compared to my primary working platform. I was looking for a book with the flavor of Oracle Concepts Manual. I partially read official MySQL reference manual but didn't find (get?) all the answers (nor I really enjoyed reading it - sorry AB doc. team!). I think this book filled my knowledge gap on MySQL perfectly. Actually, this book could easily bear different title, such as "MySQL concepts guide" or something like that. You'll probably read the book in a couple of days, thanks to the author's clear writing style. Let me give you an example. Some technical topics are difficult to explain in a few sentences, like the one on letter I (Isolation) from ACID rules for 'safe' transactions. Just compare the explanation on "phantom reads" from this book with the one you'll find in Oracle Concepts Manual (freely available on-line from OTN). Now, which one did you understand on the first pass? ;-)Thanks to clear and short explanations, right from the beginning of the book, I learned some important technical facts about MySQL that I could easily put in perspective with my Oracle background. For example:-"All InnoDB tables have primary keys"-"InnoDB tables are similar to Oracle index-organized tables."-"MySQL will only ever use one index per table per query!"-"MySQL doesn't cache rows for MyISAM tables, only indexes...as opposed to InnoDB"-"...counts are very fast on MyISAM tables and slow on InnoDB tables..."Obviously the chapters that I liked the most in this book are the ones that covers core things very well: "2. Storage Engines", "4. Indexes", "5. Query Performance" and "10. Security". All other chapters are fine but not essential for my current use of MySQL (like the excellent chapter on replication where it's obvious that Jeremy poses vast practical experience with replication from his workplace at Yahoo!). The only complaint that I have is the one on "Storage Engines" chapter. In my opinion multi storage engine architecture is the most important advantage of MySQL over all other database vendors products. I wish author's went a little deeper with the details (and thanks but no thanks, I don't want to read source code ;-), especially InnoDB engine is not covered enough (hmm...or maybe it's just me, after all :-). Overall this book is highly recommended to all DBAs, the existing MySQL DBAs as well as to all others that work with other RDBMS and want a fast way to pick the most important technical nuances of MySQL.