Understanding MySQL Internals (Paperback)

~ Sasha Pachev (Author)
Key Phrases: initialization module, command packet, logging module, The Storage Engine Interface Example, Core Classes, Length Description (more...)

Editorial Reviews

Product Description

Although MySQL's source code is open in the sense of being publicly available, it's essentially closed to you if you don't understand it. In this book, Sasha Pachev -- a former member of the MySQL Development Team -- provides a comprehensive tour of MySQL 5 that shows you how to figure out the inner workings of this powerful database. You'll go right to heart of the database to learn how data structures and convenience functions operate, how to add new storage engines and configuration options, and much more.

The core of Understanding MySQL Internals begins with an Architecture Overview that provides a brief introduction of how the different components of MySQL work together. You then learn the steps for setting up a working compilable copy of the code that you can change and test at your pleasure. Other sections of the book cover:

• Core server classes, structures, and API
• The communication protocol between the client and the server
• Configuration variables, the controls of the server; includes a tutorial on how to add your own
• Thread-based request handling -- understanding threads and how they are used in MySQL
• An overview of MySQL storage engines
• The storage engine interface for integrating third-party storage engines
• The table lock manager
• The parser and optimizer for improving MySQL's performance
• Integrating a transactional storage engine into MySQL
• The internals of replication

Understanding MySQL Internals provides unprecedented opportunities for developers, DBAs, database application programmers, IT departments, software vendors, and computer science students to learn about the inner workings of this enterprise-proven database. With this book, you will soon reach a new level of comprehension regarding database development that will enable you to accomplish your goals. It's your guide to discovering and improving a great database.

About the Author

Sasha Pachev graduated from the Brigham Young University in 1998 with a degree in Computer Science, worked on the MySQL development team in 2000-2002, and was the original developer of replication in MySQL. In 2003 he wrote his first book MySQL Enterprise Solutions. He currently lives in Provo, Utah with his wife Sarah and his five children working as an independent consultant with the emphasis on MySQL.

In spite of his great interest in computers, Sasha is equally passionate about distance running. He has won a number of marathons, has a personal best of 2:24:47, and is attempting to qualify for the US Olympic Trials. He feels it is important to help other runners reach their potential, and operates a web site http://FastRunningBlog.Com dedicated to that purpose.

Product Details

• Paperback: 256 pages
• Publisher: O'Reilly Media; 1 edition (April 10, 2007)
• Language: English
• ISBN-10: 0596009577
• ISBN-13: 978-0596009571
• Product Dimensions: 9 x 7 x 0.6 inches
• Shipping Weight: 15.2 ounces (View shipping rates and policies)
• Average Customer Review: 4.8 out of 5 stars  See all reviews (6 customer reviews)
• Amazon.com Sales Rank: #178,242 in Books (See Bestsellers in Books)

  Popular in these categories: (What's this?)

  #44 in	Books > Computers & Internet > Databases > Specific Databases > MySQL & mSQL
  #47 in	Books > Computers & Internet > Databases > Relational Databases
  #55 in	Books > Computers & Internet > Databases > Data Warehousing

Customer Reviews

Most Helpful Customer Reviews

11 of 11 people found the following review helpful:

5.0 out of 5 stars Excellent book, clearly explains MySQL internals, May 25, 2007

By	Adrien Lamothe - See all my reviews (REAL NAME)

MySQL is assuming increased importance as an enterprise database and has become the de-facto standard with web hosting companies. A number of books have appeared in recent years, covering various aspects of MySQL. In Understanding MySQL Internals, Sasha Pachev, a MySQL-focused consultant and former member of the MySQL development team, takes us on a tour of how MySQL works and how it is built.

Starting with an overview of MySQL history and architecture, the book quickly moves us through working with the source code to build and run the product in a debugger. Drawing on his deep experience with the software, Mr. Pachev explains the coding conventions used to develop MySQL and why you should use them when modifying the software yourself.

The author delineates the different functional "modules" (a term he coined himself to better explain the database, technically speaking there are no defined modules in MySQL's architecture) of MySQL and how they interrelate. Herein lies the key to how this book teaches the reader the vast intricacy of such a complex piece of software. The author recognizes the subject is huge and the source code changing. What he does in the book is serve as our tour guide, driving us through the various areas and explaining as much relevant information as he reasonably can. He constantly illustrates key pieces of source code and data structures; but perhaps more importantly he makes reference to the actual source files utilized by each functional component of the system, while encouraging us to explore further on our own. His approach is very much "top down".

As he takes us on this tour, the author explains many of the design decisions behind the various MySQL components, often imparting the historical perspective behind them. I appreciate how, throughout the book, the author shares some of his "inside information" about MySQL's development. There is also a section where he examines the code stability of each module and speculates on what the future may hold for each module. The author's writing style is clear and easy to read. I found Understanding MySQL Internals interesting and fun, and surprisingly easy to read for a book covering such a sophisticated piece of software. The author also does a good job explaining the engineering trade-offs of different MySQL configurations. Speaking of configuration, the book shows you how to add your own configuration option to the mysqld daemon.

Chapter 7, the largest chapter in the book, is 41 pages long. It covers the MySQL storage engine interface. This chapter explains how to integrate your own custom storage engine into MySQL and contains the source code for two custom storage engine examples (one for MySQL 4.1, the other for version 5.1). You can download this source code from O'Reilly's web site. Despite this one long chapter, the book is surprising short, only 234 pages.

I believe this book has value outside of MySQL. It does a good job showing how MySQL is essentially a well designed piece of software: a high-performance, reliable Unix server. The book also touches on the multi-platform aspects of MySQL design. Those of you designing other types of server software may benefit from studying how MySQL is constructed.

This book isn't for everyone, but if you are serious about MySQL in particular or database software design in general then this title certainly deserves a look.

2 of 2 people found the following review helpful:

5.0 out of 5 stars Provides excellent content for those working on MySQL, August 20, 2008

By	John Murtari "AKidsRight.Org" (Lyons, NY) - See all my reviews (REAL NAME)

Amazon Verified Purchase

I have been working on a project to make some changes to MySQL server
operation. Learned a lot through code review & trial and error! Recently
purchased this book and it is excellent! Wish we had it months ago.

The author knows what he's talking about. Provides a great overview of the
architecture, file and subsystem layout. Also provides great information on using the 'built in' debug capability of the server -- very useful!

I was expecting to be disappointed, I was not. The content was right
on the mark for those folks interested on working on server internals.

2 of 3 people found the following review helpful:

5.0 out of 5 stars Lift the magic veil, December 31, 2007

By	Ilya Grigorik (Toronto, ON, Canada) - See all my reviews (REAL NAME)

Amazon Verified Purchase

An excellent overview of the MySQL codebase, philosophy, and the design decisions that have made MySQL what it is today. Even though I have never touched the MySQL source before (it can be a daunting beast for the uninitiated), Sasha's explanations made me comfortable with the 'internals' and finally lifted the 'magic veil' off the engine. 'MySQL internals' has a perfect balance of high-level explanations, and low-level code samples to help you understand the inner workings of the database.

After reading this book you should be comfortable enough to tackle the source, and you'll also have deep understanding of the storage engines and how to configure them, how the query parser works, how the optimizer decides on the execution plan, and much more. This is a must read for any DBA, or a programmer who wants to see a great example of a high-performance, reliable UNIX server.