book

Understanding MySQL Internals

Name: Understanding MySQL Internals
Author: Sasha Pachev
ISBN: 9780596009571

by Sasha Pachev

April 2007

Intermediate to advanced

254 pages

8h 9m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Preface
How This Book Is OrganizedWho This Book Is ForConventions Used in This BookUsing Code ExamplesComments and QuestionsSafari® EnabledAcknowledgments
1. MySQL History and Architecturel
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. Nuts and Bolts of Working with the MySQL Source Code
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. Core Classes, Structures, Variables, and APIs
3.1. THD3.2. NET3.3. TABLE3.4. Field3.5. Utility API Calls3.6. Preprocessor Macros3.7. Global Variables
4. Client/Server Communication
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. Configuration Variables
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. Thread-Based Request Handling
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. The Storage Engine Interface
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. Concurrent Access and Locking
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. Parser and Optimizer
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. Storage Engines
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. Transactions
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. Replication
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
About the Author
Colophon
Copyright

Content preview from Understanding MySQL Internals

Chapter 10. Storage Engines

In this chapter we will discuss the most prominent storage engines within MySQL in more detail. Unfortunately, due to the large number of different storage engines and the complexity that some possess, we are not able to examine each one in sufficient detail on the code level. Indeed, storage engines like MyISAM and InnoDB each deserve their own thousand-page book. However, I will provide pointers to the source for those who would like to learn more.

Different storage engines have different capabilities. Table 10-1 contains a comparison of different storage engines.

Table 10-1. MySQL storage engine comparison

MyISAM	InnoDB	Memory	Merge	NDB	Archive	Federated
Transactions	No	Yes	No	No	Yes	No	No
Indexing	B-tree, R-tree, full text	B-tree	Hash, B-tree	B-tree, R-tree	Hash, B-tree	None	Depends on the remote table engine
Storage	Local disk	Local disk	RAM	Local disk	Remote and local duster nodes	Local disk	Remote MySQL server instance
Caching	Key cache	Key and data cache	N/A	Same as MyISAM	Key and data cache	None	Depends on the remote table engine
Lacking	Table	Row	Table	Table	Row	Row	Relies on the remote table engine
Foreign keys	No	Yes	No	No	No	No	Depends on the remote table engine

Shared Aspects of Architecture

While there is a great degree of freedom in the implementation of a storage engine, all storage engines must integrate with the main MySQL server code. As a result they have a few things in common. Aside from having to support the basic concepts of tables residing in a database, records, columns, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596009577Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Understanding MySQL Internals

by Sasha Pachev

Chapter 10. Storage Engines

Shared Aspects of Architecture

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.