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. When taking a sharp checkpoint, the database flushes all dirty pages of committed transactions to disk and update the MasterRecord to be the lastest committed LSN. Checkpoint behaves the same way as MySQL and is not modified by ClearDB

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, where the system reclaims unnecessary blocks at the end of the page. ClearDB's doesn't change compression from MySQL.

Concurrency Control

Two-Phase Locking (Deadlock Detection)

ClearDB doesn't change concurrency control from MySQL. 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.

Data Model

Relational

ClearDB's underlying MySql database uses a relational data model that is not modified by ClearDB.

Foreign Keys

Supported

The build-in MySQL natively supports foreign key constraints.

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. ClearDB doesn't modify the behavior of indexes from MySQL.

Joins

Nested Loop Join Hash Join

ClearDB's change from MySQL doesn't involve 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.

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. Query execution is not affected by ClearDB's change.

Query Interface

SQL Stored Procedures

ClearDB doesn't change the query interface of the underlying MySQL. MySQL natively supports SQL and stored procedures for query executions.

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.

Storage Model

N-ary Storage Model (Row/Record)

InnoDB uses a row storage structure that supports 4 row formats: Redundant, Compact, Dynamic, Compressed. It is not affected by the change from MySQL to ClearDB.

System Architecture

Shared-Nothing

ClearDB is a cloud-based database-as-a-service version of MySQL. It wraps multiple software stacks around MySQL 5.5 and 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 .