Understanding MySQL Internals

Understanding MySQL Internals

by Sasha Pachev
Released April 2007
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596009571

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book 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.

Publisher resources

View/Submit Errata

Table of contents

  1. Dedication
  2. A Note Regarding Supplemental Files
  3. Preface
    1. How This Book Is Organized
    2. Who This Book Is For
    3. Conventions Used in This Book
    4. Using Code Examples
    5. Comments and Questions
    6. Safari® Enabled
    7. Acknowledgments
  4. 1. MySQL History and Architecturel
    1. 1.1. MySQL History
    2. 1.2. MySQL Architecture
      1. 1.2.1. Core Modules
      2. 1.2.2. Interaction of the Core Modules
      3. 1.2.3. Detailed Look at the Core Modules
        1. 1.2.3.1. Server Initialization Module
        2. 1.2.3.2. Connection Manager
        3. 1.2.3.3. Thread Manager
        4. 1.2.3.4. Connection Thread
        5. 1.2.3.5. User Authentication Module
        6. 1.2.3.6. Access Control Module
        7. 1.2.3.7. Parser
        8. 1.2.3.8. Command Dispatcher
        9. 1.2.3.9. Query Cache Module
        10. 1.2.3.10. Optimizer
        11. 1.2.3.11. Table Manager
        12. 1.2.3.12. Table Modification Modules
        13. 1.2.3.13. Table Maintenance Module
        14. 1.2.3.14. Status Reporting Module
        15. 1.2.3.15. Abstracted Storage Engine Interface (Table Handler)
        16. 1.2.3.16. Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)
        17. 1.2.3.17. Logging Module
        18. 1.2.3.18. Replication Master Module
        19. 1.2.3.19. Replication Slave Module
        20. 1.2.3.20. Client/Server Protocol API
        21. 1.2.3.21. Low-Level Network I/O API
        22. 1.2.3.22. Core API
  5. 2. Nuts and Bolts of Working with the MySQL Source Code
    1. 2.1. Unix Shell
    2. 2.2. BitKeeper
    3. 2.3. Preparing the System to Build MySQL from BitKeeper Tree
    4. 2.4. Building MySQL from BitKeeper Tree
    5. 2.5. Building from Source Distribution
    6. 2.6. Installing MySQL into a System Directory
    7. 2.7. Source Code Directory Layout
    8. 2.8. Preparing the System to Run MySQL in a Debugger
    9. 2.9. Debugger-Guided Source Tour
    10. 2.10. Basics of Working with gdb
    11. 2.11. Finding Things in the Source
    12. 2.12. Interesting Breakpoints and Variables
    13. 2.13. Making a Source Modification
    14. 2.14. Coding Guidelines
      1. 2.14.1. Stability
      2. 2.14.2. Portability
      3. 2.14.3. Performance
      4. 2.14.4. Style and Ease of Integration
    15. 2.15. Keeping Your BitKeeper Repository Up to Date
    16. 2.16. Submitting a Patch
  6. 3. Core Classes, Structures, Variables, and APIs
    1. 3.1. THD
    2. 3.2. NET
    3. 3.3. TABLE
    4. 3.4. Field
    5. 3.5. Utility API Calls
    6. 3.6. Preprocessor Macros
    7. 3.7. Global Variables
  7. 4. Client/Server Communication
    1. 4.1. Protocol Overview
    2. 4.2. Packet Format
    3. 4.3. Relationship Between MySQL Protocol and OS Layer
    4. 4.4. Authenticating Handshake
      1. 4.4.1. Authentication Protocol Security
      2. 4.4.2. Protocol Capabilities Bit Mask
    5. 4.5. Command Packet
    6. 4.6. Server Responses
      1. 4.6.1. Data Field
      2. 4.6.2. OK Packet
      3. 4.6.3. Error Packet
      4. 4.6.4. EOF Packet
      5. 4.6.5. Result Set Packets
  8. 5. Configuration Variables
    1. 5.1. Configuration Variables Tutorial
      1. 5.1.1. Configuration File and Command-Line Options
      2. 5.1.2. Internals of the Configuration Option Parsing
      3. 5.1.3. Example of Adding a New Configuration Option
    2. 5.2. Interesting Aspects of Specific Configuration Variables
      1. 5.2.1. big-tables
      2. 5.2.2. concurrent-insert
      3. 5.2.3. core-file
      4. 5.2.4. default-storage-engine
      5. 5.2.5. delay-key-write
      6. 5.2.6. ft_stopword_file
      7. 5.2.7. innodb_buffer_pool_size
      8. 5.2.8. innodb_flush_log_at_trx_commit
      9. 5.2.9. innodb_file_per_table
      10. 5.2.10. innodb_lock_wait_timeout
      11. 5.2.11. innodb_force_recovery
      12. 5.2.12. init-file
      13. 5.2.13. key_buffer_size
      14. 5.2.14. language
      15. 5.2.15. log
      16. 5.2.16. log-bin
      17. 5.2.17. log-isam
      18. 5.2.18. log-slow-queries
      19. 5.2.19. max_allowed_packet
      20. 5.2.20. max_connections
      21. 5.2.21. max_heap_table_size
      22. 5.2.22. max_join_size
      23. 5.2.23. max_sort_length
      24. 5.2.24. myisam-recover
      25. 5.2.25. query_cache_type
      26. 5.2.26. read_buffer_size
      27. 5.2.27. relay-log
      28. 5.2.28. server-id
      29. 5.2.29. skip-grant-tables
      30. 5.2.30. skip-stack-trace
      31. 5.2.31. slave-skip-errors
      32. 5.2.32. sort_buffer_size
      33. 5.2.33. sql-mode
      34. 5.2.34. table_cache
      35. 5.2.35. temp-pool
      36. 5.2.36. transaction-isolation
  9. 6. Thread-Based Request Handling
    1. 6.1. Threads Versus Processes
      1. 6.1.1. Advantages of Using Threads
      2. 6.1.2. Disadvantages of Using Threads
      3. 6.1.3. Advantages of Using Forked Processes
      4. 6.1.4. Disadvantages of Using Forked Processes
    2. 6.2. Implementation of Request Handling
      1. 6.2.1. Structures, Variables, Classes, and API
      2. 6.2.2. Execution Walk-Through
    3. 6.3. Thread Programming Issues
      1. 6.3.1. Standard C Library Calls
      2. 6.3.2. Mutually Exclusive Locks (Mutexes)
      3. 6.3.3. Read-Write Locks
      4. 6.3.4. Synchronization
      5. 6.3.5. Preemption
  10. 7. The Storage Engine Interface
    1. 7.1. The handler Class
      1. 7.1.1. handlerton
    2. 7.2. Adding a Custom Storage Engine to MySQL
      1. 7.2.1. Integration Instructions for Version 4.1
      2. 7.2.2. Integration Instructions for Version 5.1
  11. 8. Concurrent Access and Locking
    1. 8.1. Table Lock Manager
      1. 8.1.1. Read Lock Request
      2. 8.1.2. Write Lock Request
        1. 8.1.2.1. Storage engine interaction with the table lock manager
      3. 8.1.3. InnoDB Locking
        1. 8.1.3.1. Lock types
        2. 8.1.3.2. Record locking
        3. 8.1.3.4. Dealing with deadlocks
  12. 9. Parser and Optimizer
    1. 9.1. Parser
      1. 9.1.1. Lexical Scanner
      2. 9.1.2. Grammar Rules Module
      3. 9.1.3. Parse Tree
    2. 9.2. Optimizer
      1. 9.2.1. Basics of the Optimizer Algorithm
      2. 9.2.2. Using EXPLAIN to Understand the Optimizer
        1. 9.2.2.1. Understanding the output of EXPLAIN
        2. 9.2.2.2. Select types
        3. 9.2.2.3. Record access types
        4. 9.2.2.4. Extra field
      3. 9.2.3. Range Optimizer
        1. 9.2.3.1. Range
        2. 9.2.3.2. Index_merge
        3. 9.2.3.3. Range_desc
        4. 9.2.3.4. Fulltext
        5. 9.2.3.5. ROR_intersect
        6. 9.2.3.6. ROR_union
        7. 9.2.3.7. Group_min_max
      4. 9.2.4. Subquery Optimization
      5. 9.2.5. Core Optimizer Classes and Structures
        1. 9.2.5.1. JOIN
        2. 9.2.5.2. JOIN_TAB
        3. 9.2.5.3. select_result
      6. 9.2.6. SELECT Parse Tree
        1. 9.2.6.1. Execution of a SELECT on the code level
  13. 10. Storage Engines
    1. 10.1. Shared Aspects of Architecture
      1. 10.1.1. MyISAM
      2. 10.1.2. MyISAM Architecture
        1. 10.1.2.1. Datafile
        2. 10.1.2.2. Index file
      3. 10.1.3. MyISAM Key Types
        1. 10.1.3.1. B-tree keys
        2. 10.1.3.2. Full-text keys
        3. 10.1.3.4. Spatial keys
    2. 10.2. InnoDB
    3. 10.3. Memory (Heap)
    4. 10.4. MyISAM Merge
    5. 10.5. NDB
    6. 10.6. Archive
    7. 10.7. Federated
  14. 11. Transactions
    1. 11.1. Overview of Transactional Storage Engine Implementation
    2. 11.2. Implementing the handler Subclass
    3. 11.3. Defining the handlerton
    4. 11.4. Working with the Query Cache
    5. 11.5. Working with the Replication Binary Log
    6. 11.6. Avoiding Deadlocks
  15. 12. Replication
    1. 12.1. Overview
    2. 12.2. Statement-Based Versus Row-Based Replication
    3. 12.3. Two-Threaded Slave
    4. 12.4. Multi-Master
    5. 12.5. SQL Commands to Help Understand Replication
    6. 12.6. Binary Log Format
    7. 12.7. Creating a Custom Replication Utility
  16. About the Author
  17. Colophon
  18. Copyright

Product information

  • Title: Understanding MySQL Internals
  • Author(s): Sasha Pachev
  • Release date: April 2007
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596009571