Data Stores Under the Hood: MySQL
This writeup capture internals of the MySQL data store. The information is collected with limited research. Some of the content could be opiniated.
Relational Data Stores
MySQL
- Engine
- Default is InnoDB engine. Other engines listed here could be used.
- Storage & Retrival
- Best used to view current snapshot of data tied to the application. Most suitable of Online Transaction processing(OLTP).
- Storage Structure
- Uses B+ trees storage structure. In B+ trees, a node can have multiple children. Each non-leaf node carries only the key. The leaf nodes has the key and pointer to actual data in disk. For ease of traversal, the leaf nodes are connected as a sequential linked list. The non-leaf nodes act as an hierarchy of indexes for faster & accurate search.
- Row / column oriented
- Used as a row oriented database. A row typically has a primary key column. May contain foreign key references to other tables.
- Indices
- Primary key index is called clustered index. Secondary indices are supported.
- Materialized Views
- No in-built support for Materialized views. Can be built at the application layer.
- Encoding & Encrypting
- Supported Encoding
- Text
- Schema
- Applciation defined schema with primary key and foreign key relations.
- Replication
- Single Leader Replication
- Asynchronous replication is the default. Semi-synchronous replication can be enabled. Under Semi-syncronous replication, the leader waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction.
The replication format can be either Statement Based Replication(SBR) or Row Based Replication(RBR).
The method of replication is the binary log file based replication (Default). In a log file based replication, the leader writes updates and changes as “events” to a append only binary log. Replicas are configured to read the binary log from the source and to execute the events in the binary log. Each replica keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed.
Alternate method of replication is Global Transaction Identifier (GTID) based replication. No log file is used here and GTIDs are preserved between leader & replica. - Multi Leader Replication
- Supported under Group replication. Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.
Different configurations are exposed to handle group recovery, concurrency & availability. 3rd party solution like Tungsten Replicator could be used. - Leader or Follower Failure
- 3rd party tools like Master High Availability could be used for automatic failover recovery.
- Multi Version Concurrency Control (MVCC)
- InnoDB supports MVCC. It keeps information about old versions of changed rows to support concurrent updates and rollback.
- Consistency Guarentee
- Supports Eventual Consistency among replicas due to asynchronous replication. Other possible stronger consistency configuration are listed here.