book

Understanding MySQL Internals

by Sasha Pachev

April 2007

Intermediate to advanced

254 pages

8h 9m

English

O'Reilly Media, Inc.

Read now

Unlock full access

How This Book Is OrganizedWho This Book Is ForConventions Used in This BookUsing Code ExamplesComments and QuestionsSafari® EnabledAcknowledgments
1.1. MySQL History1.2. MySQL Architecture1.2.1. Core Modules1.2.2. Interaction of the Core Modules1.2.3. Detailed Look at the Core Modules1.2.3.1. Server Initialization Module1.2.3.2. Connection Manager1.2.3.3. Thread Manager1.2.3.4. Connection Thread1.2.3.5. User Authentication Module1.2.3.6. Access Control Module1.2.3.7. Parser1.2.3.8. Command Dispatcher1.2.3.9. Query Cache Module1.2.3.10. Optimizer1.2.3.11. Table Manager1.2.3.12. Table Modification Modules1.2.3.13. Table Maintenance Module1.2.3.14. Status Reporting Module1.2.3.15. Abstracted Storage Engine Interface (Table Handler)1.2.3.16. Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)1.2.3.17. Logging Module1.2.3.18. Replication Master Module1.2.3.19. Replication Slave Module1.2.3.20. Client/Server Protocol API1.2.3.21. Low-Level Network I/O API1.2.3.22. Core API
2.1. Unix Shell2.2. BitKeeper2.3. Preparing the System to Build MySQL from BitKeeper Tree2.4. Building MySQL from BitKeeper Tree2.5. Building from Source Distribution2.6. Installing MySQL into a System Directory2.7. Source Code Directory Layout2.8. Preparing the System to Run MySQL in a Debugger2.9. Debugger-Guided Source Tour2.10. Basics of Working with gdb2.11. Finding Things in the Source2.12. Interesting Breakpoints and Variables2.13. Making a Source Modification2.14. Coding Guidelines2.14.1. Stability2.14.2. Portability2.14.3. Performance2.14.4. Style and Ease of Integration2.15. Keeping Your BitKeeper Repository Up to Date2.16. Submitting a Patch
3.1. THD3.2. NET3.3. TABLE3.4. Field3.5. Utility API Calls3.6. Preprocessor Macros3.7. Global Variables
4.1. Protocol Overview4.2. Packet Format4.3. Relationship Between MySQL Protocol and OS Layer4.4. Authenticating Handshake4.4.1. Authentication Protocol Security4.4.2. Protocol Capabilities Bit Mask4.5. Command Packet4.6. Server Responses4.6.1. Data Field4.6.2. OK Packet4.6.3. Error Packet4.6.4. EOF Packet4.6.5. Result Set Packets
5.1. Configuration Variables Tutorial5.1.1. Configuration File and Command-Line Options5.1.2. Internals of the Configuration Option Parsing5.1.3. Example of Adding a New Configuration Option5.2. Interesting Aspects of Specific Configuration Variables5.2.1. big-tables5.2.2. concurrent-insert5.2.3. core-file5.2.4. default-storage-engine5.2.5. delay-key-write5.2.6. ft_stopword_file5.2.7. innodb_buffer_pool_size5.2.8. innodb_flush_log_at_trx_commit5.2.9. innodb_file_per_table5.2.10. innodb_lock_wait_timeout5.2.11. innodb_force_recovery5.2.12. init-file5.2.13. key_buffer_size5.2.14. language5.2.15. log5.2.16. log-bin5.2.17. log-isam5.2.18. log-slow-queries5.2.19. max_allowed_packet5.2.20. max_connections5.2.21. max_heap_table_size5.2.22. max_join_size5.2.23. max_sort_length5.2.24. myisam-recover5.2.25. query_cache_type5.2.26. read_buffer_size5.2.27. relay-log5.2.28. server-id5.2.29. skip-grant-tables5.2.30. skip-stack-trace5.2.31. slave-skip-errors5.2.32. sort_buffer_size5.2.33. sql-mode5.2.34. table_cache5.2.35. temp-pool5.2.36. transaction-isolation
6.1. Threads Versus Processes6.1.1. Advantages of Using Threads6.1.2. Disadvantages of Using Threads6.1.3. Advantages of Using Forked Processes6.1.4. Disadvantages of Using Forked Processes6.2. Implementation of Request Handling6.2.1. Structures, Variables, Classes, and API6.2.2. Execution Walk-Through6.3. Thread Programming Issues6.3.1. Standard C Library Calls6.3.2. Mutually Exclusive Locks (Mutexes)6.3.3. Read-Write Locks6.3.4. Synchronization6.3.5. Preemption
7.1. The handler Class7.1.1. handlerton7.2. Adding a Custom Storage Engine to MySQL7.2.1. Integration Instructions for Version 4.17.2.2. Integration Instructions for Version 5.1

8.1. Table Lock Manager8.1.1. Read Lock Request8.1.2. Write Lock Request8.1.2.1. Storage engine interaction with the table lock manager8.1.3. InnoDB Locking8.1.3.1. Lock types8.1.3.2. Record locking8.1.3.4. Dealing with deadlocks
9.1. Parser9.1.1. Lexical Scanner9.1.2. Grammar Rules Module9.1.3. Parse Tree9.2. Optimizer9.2.1. Basics of the Optimizer Algorithm9.2.2. Using EXPLAIN to Understand the Optimizer9.2.2.1. Understanding the output of EXPLAIN9.2.2.2. Select types9.2.2.3. Record access types9.2.2.4. Extra field9.2.3. Range Optimizer9.2.3.1. Range9.2.3.2. Index_merge9.2.3.3. Range_desc9.2.3.4. Fulltext9.2.3.5. ROR_intersect9.2.3.6. ROR_union9.2.3.7. Group_min_max9.2.4. Subquery Optimization9.2.5. Core Optimizer Classes and Structures9.2.5.1. JOIN9.2.5.2. JOIN_TAB9.2.5.3. select_result9.2.6. SELECT Parse Tree9.2.6.1. Execution of a SELECT on the code level
10.1. Shared Aspects of Architecture10.1.1. MyISAM10.1.2. MyISAM Architecture10.1.2.1. Datafile10.1.2.2. Index file10.1.3. MyISAM Key Types10.1.3.1. B-tree keys10.1.3.2. Full-text keys10.1.3.4. Spatial keys10.2. InnoDB10.3. Memory (Heap)10.4. MyISAM Merge10.5. NDB10.6. Archive10.7. Federated
11.1. Overview of Transactional Storage Engine Implementation11.2. Implementing the handler Subclass11.3. Defining the handlerton11.4. Working with the Query Cache11.5. Working with the Replication Binary Log11.6. Avoiding Deadlocks
12.1. Overview12.2. Statement-Based Versus Row-Based Replication12.3. Two-Threaded Slave12.4. Multi-Master12.5. SQL Commands to Help Understand Replication12.6. Binary Log Format12.7. Creating a Custom Replication Utility

Content preview from Understanding MySQL Internals

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 onMySQL.In spite of his great interest in computers, Sasha is equallypassionate 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.