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 2. Nuts and Bolts of Working with the MySQL Source Code

Much can be learned about MySQL by studying its source. Monty Widenius, the creator of MySQL, once half-jokingly remarked that the source is the ultimate documentation. Indeed, assuming that the hardware and the compiler are functioning properly, the software will do exactly what the source tells it to. However, understanding the source of a complex program such as MySQL can be a challenge. The purpose of this chapter is to give you a head start in your study of the source.

Unix Shell

Although MySQL runs on a number of different platforms, you will find it easier to study the source if you get an account on some Unix-like system, such as Linux, FreeBSD, Mac OS X, or Solaris. If you do not have a preference to start with, I recommend Linux. It could be either a remote server, or running on your desktop. The examples in this chapter assume you are logged in to a Unix command shell, and that your shell is Bourne-compatible to some degree. One way to get such a shell is to execute:

	/bin/sh

right after you log in.

BitKeeper

MySQL developers use BitKeeper (http://www.bitmover.com) for source revision control. A BitKeeper repository containing MySQL source code is publicly available with read-only access. Although MySQL source code can also be obtained by downloading a compressed archive, using BitKeeper offers a number of advantages:

You get the most recent source version and can stay up to date with all the developments on a daily ...

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