Query Execution

Tuple-at-a-Time Model

MySQL follows an iterative processing mode and subqueries are optimized with materialization by storing subquery result in a temporary table

Concurrency Control

Two-Phase Locking (Deadlock Detection)

MySQL uses a combination of Multiversion Concurrency Control (MVCC) and row-level locking to achieve better concurrency. Old versions of rows are stored in a rollback segment which could later be used to rollback changes. Rollback segments are partitioned into the insert and update undo logs for different types of rollbacks.

Compression

Naïve (Page-Level)

InnoDB supports multiple compression algorithms which include Zlib and LZ4. When writing a page to disk, data were first compressed and then written to disk. Then empty blocks will be released by hole punching mechanism.

Storage Architecture

Disk-oriented

On a higher level, ClearDB uses cloud-based durable storage. On a lower level, ClearDB MySQL uses InnoDB as the default storage engine and supports multiple other storage engines: MyISAM, Memory (Heap Engine), Merge, Archive, Federated, and NDB.

Query Interface

SQL Stored Procedures

ClearDB MySQL natively supports SQL and stored procedures for query executions.

Indexes

B+Tree Hash Table R-Tree

MySQL support multiple types of indexes. Most MySQL tables stored on disks use B-trees. Spatial data uses R-tree for indexes. MySQL also uses hash indexes for memory tables.

Foreign Keys

Supported

The build-in MySQL natively supports foreign key constraints.

Data Model

Relational

Checkpoints

Fuzzy

It uses both Fuzzy and Sharp checkpoint. It takes a sharp checkpoint when the database is performing shut down and uses fuzzy checkpoint during normal database operations.

Joins

Nested Loop Join Hash Join

MySQL employs both nested loop join as well as hash join algorithms. Nested loop joins are used by default and hash join was used when the join has an equi-join condition and no indexes were used. Block nested loop, as well as Batched Key Access Joins, are used to improve join performance.

Storage Model

N-ary Storage Model (Row/Record)

InnoDB uses a row storage structure that supports 4 row formats: Redundant, Compact, Dynamic, Compressed.

System Architecture

Shared-Nothing

ClearDB is a cloud-based database-as-a-service for MySQL that wraps multiple software stacks around MySQL 5.5 to achieve 100% uptime. It runs multiple geo-separated database clusters to reduce the chance of total database failures. It uses shared-nothing architecture, master-master replication, and proprietary routing technology to achieve automatically handle failed nodes. Database replications are done in an asynchronous manner and are realtime. In the event of failure, the SQL routing algorithm will automatically redirect the query to the secondary master and bring the master back when the node recovers.

ClearDB performs daily backup where the backups are compressed and encrypted and will be replicated to master nodes.

ClearDB leveraged a secure configuration from MySQL, and SSL was used for network security .