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 functioning site. This book is an insider's guide to these little understood topics.Author Jeremy Zawodny has managed large numbers of MySQL servers for mission-critical work at Yahoo!, maintained years of contacts with the MySQL AB team, and presents regularly at conferences. Jeremy and Derek have spent months experimenting, interviewing major users of MySQL, talking to MySQL AB, benchmarking, and writing some of their own tools in order to produce the information in this book.In High Performance MySQL you will learn about MySQL indexing and optimization in depth so you can make better use of these key features. You will learn practical replication, backup, and load-balancing strategies with information that goes beyond available tools to discuss their effects in real-life environments. And you'll learn the supporting techniques you need to carry out these tasks, including advanced configuration, benchmarking, and investigating logs.Topics include:
A review of configuration and setup options
Storage engines and table types
Backup and Recovery
Chapter 1 Back To Basics
Binary Versus Compiled-From-Source Installations
The SHOW Commands
Chapter 2 Storage Engines (Table Types)
Locking and Concurrency
Selecting the Right Engine
The Storage Engines
Chapter 3 Benchmarking
The Importance of Benchmarking
Chapter 4 Indexes
Indexes and Table Types
Chapter 5 Query Performance
Query Processing Basics
Optimizer Features and Oddities
Identifying Slow Queries
Influencing MySQL with Hints
Stupid Query Tricks
Chapter 6 Server Performance Tuning
Chapter 7 Replication
Under the Hood
Administration and Maintenance
The Future of Replication
Chapter 8 Load Balancing and High Availability
Load Balancing Basics
Chapter 9 Backup and Recovery
Considerations and Tradeoffs
Tools and Techniques
Rolling Your Own Backup Script
Chapter 10 Security
The Grant Tables
Grant and Revoke
Operating System Security
MySQL in a chrooted Environment
Appendix A The SHOW STATUS and SHOW INNODB STATUS Commands
Jeremy Zawodny and his two cats moved from Northwest Ohio to Silicon Valley in late 1999 so he could work for Yahoo!--just in time to witness the .com bubble bursting first-hand. He's been at Yahoo!® ever since, helping to put MySQL and other Open Source technologies to use in fun, interesting, and often very big ways. Starting with the popular and high-traffic Yahoo! Finance site, he worked to make MySQL part of the site's core infrastructure in large batch operations as well as real-time feed processing and serving content directly on the site. He then helped to spread "the MySQL religion" to numerous other groups within Yahoo!, including News, Personals, Sports, and Shopping. Nowadays he acts as Yahoo!'s MySQL guru, working with Yahoo!'s many engineering groups to get the most out of their MySQL deployments.
In 2000, he began writing for Linux Magazine and continues to do so today as a columnist and contributing editor. After over a year of active participation on the MySQL mailing list, he got the idea to write a book about MySQL. (How hard could it be, really?) You can still find him answering questions on the list today. Since 2001, Jeremy has been speaking about MySQL at various conferences (O'Reilly's Open Source Conference, PHPCon, The MySQL User Conference, etc.) and user groups in locations as far away as Bangalore, India. His favorite topics are performance tuning, replication, clustering, and backup/recovery. In more recent times, he's rediscovered his love of aviation, earning a Private Pilot Glider license in early 2003. Since then he's spent far too much of his free time flying gliders out of Hollister, California and Truckee, near Lake Tahoe. He hopes to soon earn his Commercial Pilot license and then go on to become a certified flight instructor someday. Occasional MySQL consulting also helps to pay for his flying addiction.
Jeremy rambles almost daily about technology and life in general on his weblog: www.jeremy.zawodny.com/blog/
Derek J. Balling has been a Linux system administrator since 1996. He has helped build and maintain server infrastructure for companies like Yahoo, and institutions like Vassar College. He has also written articles for The Perl Journal and a number of online magazines, and served on the Program Committee for the 2008 LISA (Large Installation System Administration) Conference. He is currently employed as the Data Center Manager for Answers.com.
When not working on computer-related issues, Derek enjoys spending time with his wife Debbie, and their posse of animals (four cats and a dog). He also makes his opinion known on current events or whatever is annoying him lately on his blog at http://blog.megacity.org/.
Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of High Performance MySQL: Optimization, Backups, Replication, and Load Balancing, is a sparrow hawk (Accipiter nisus), a small woodland member of the falcon family found in Eurasia and North Africa. Sparrow hawks have a long tail and short wings; males are bluish-grey with a light brown breast, and females are more brown-grey and have an almost fully white breast. Males are normally somewhat smaller (11 inches) than females (15 inches).Sparrow hawks live in coniferous woods and feed on small mammals, insects, and birds. They nest in trees and sometimes on cliff ledges. At the beginning of the summer, the female lays 4 to 6 white eggs, blotched red and brown, in a nest made in the boughs of the tallest tree available. The male feeds the female and their young.Like all hawks, the sparrow hawk is capable of bursts of high speed in flight. Whether soaring or gliding, the sparrow hawk has a characteristic flap-flap-glide action; its large tail enables the hawk to twist and turn effortlessly in and out of cover. Mary Anne Weeks Mayo was the production editor and proofreader, and Leanne Soylemez was the copyeditor for High Performance MySQL: Optimization, Backups, Replication, and Load Balancing. Emily Quill and Claire Cloutier provided quality control. Jamie Peppard and Mary Agner provided production assistance. John Bickelhaupt wrote the index.Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.David Futato designed the interior layout. This book was converted by Joe Wizda to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9 and Adobe Photoshop6. The tipand warning icons were drawn by Christopher Bing. This colophon was compiled by Mary Anne Weeks Mayo.
I have read this book completely and i think it is a awesome book. It presents even complex topic in simple and easy to understand manner. I have no dba expreince before reading this book. I have been doing some mysql querying but not much. Now i know a lot to manage mysql server, set up relication, troubleshoot performance issues, etc. But i think that they could have gone to some more depth rather than presenting in somewhat high level.
Bottom Line Yes, I would recommend this to a friend
Realy useful book, let understand DB structure in deep details and create high perfomance, reliable Data Bases. Will be usefull for both developers and DBA. Detailed description of such important topics like indexing and caching make it a perfect handbook for those who want to wring out the maximum perfomance. The book requires good understanding of MySQL in whole and intended for expirienced users. But some chapters may be helpfull for beginers as well. Written in a good style the book abounds in tips and workarounds. Information presented based on huge expirience of the writers.
Bottom Line Yes, I would recommend this to a friend
High Performance MySQL opens with a quick chapter describing various MySQL installation options including binary packages and compiling from source code. The next chapter describes the different storage engines available in MySQL and why you would want to choose one over another. This is the book's first hint of what you can do to improve database performance: picking the right table type.
Skilled software developers use benchmarking to gain insight into how their software is performing and database benchmarking should be part of that strategy. A brief chapter introduces some database benchmarking strategies and tools.
Chapters on indexing and query performance follow, describing some of the most important skills to master. Following that, replication and load balancing are discussed. Backup and recovery options are covered, as is the topic of security.
A somewhat thin trio of goodies rounds off the book in the appendices: the SHOW STATUS / SHOW INNODB STATUS commands, the mytop tool, and the phpMyAdmin user interface.
Overall, I found many possible areas for performance improvement that I had not thought of before. At 278 pages, a lot of material is crammed into this book. It's a quick read and if you're a MySQL admin or a developer using MySQL, you're bound to find something useful here!
A must for those that use MySQL on a regular basis
Comments about oreilly High Performance MySQL:
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.
High Performance MySQL is a good look at the issues faced by DBAs in high performance environments..
MySQL books to date have tended to be reviews of basic installation and configuration. This books assumes you have covered that ground already, a welcome departure.
The book takes an intermedite-level approach to topics such as indexing, replication, backup, and tuning.
On the positive side, the book is engaging and very readable. Although the material is, by nature, theoretical, the authors have taken effort to attach real-world examples to the concepts they are covering.
On the negative side, there were a few typos and grammatical errors in the book, such as a diagram referring to the 'NBD' storage engine (should read BDB). Perhaps one more trip through the editing desk would have been helpful. However, the material itself is comprehensive and accurate, and is not affected by these minor slips.
Overall, the books is recommended for DBAs who do non-trivial work with MySQL.