[rt-users] mysql DB engine ndbdcluster

[Alex Vandiver]

On Wed, 14 Dec 2016 09:42:42 -0500
Mike Diehl <mdiehlenator at gmail.com> wrote:
> At the risk of picking a fight, I'd like to understand this a bit better.

Happy to explain more -- and my instinct may have been wrong on one
count; see below.


> As long as the database supports minimum functions, such as transactions, 
> joins, datatypes, etc., why should an application care about the underlying 
> storage engine?

Because distributed databases have different properties around
atomicity and data locality than single-host databases.  Applications
running atop such databases need to be built to accommodate these
correctness and performance properties.


The biggest issue is that of transaction isolation[1] -- not all
transactions are equal.  RT assumes that a database transaction gives it
"repeatable read" isolation from other transactions.  This isolation
level, the default for InnoDB tables[2], means that essentially, upon
the first read, a snapshot of the state of the database is taken, and
it provides strong guarantees that regardless how long the transaction
is open, all queries within it will return consistent data[3].

I believe it likely (though I cannot prove, offhand) that RT assumes
repeatable read isolation semantics -- and NDB only offers "read
committed" isolation, which admits the possibility of different
results for the same query run twice within the same transaction.

However, upon writing this, it occurs to me that Postgres' default
isolation level is _also_ "read committed."[4]  Thus any possible race
conditions that might show up under NDB are also possible under
Postgres.  I'd need to do some close analysis to determine if this
means that Postgres is open to data corruption, or if both are safe
because the queries RT runs do not care about repeatable-read
semantics.

So NDB may actually be fine on this front.



The other property concerns data locality, and is purely a performance
constraint.  NDB stores data across a cluster of data notes,
optionally with replication, which are queried by other hosts that
serve as SQL nodes[5].  This means that joining data across tables
cannot be done in-memory, but instead may incur network-level
latencies to match up the data between data nodes -- meaning poor
query performance.

MySQL Cluster 7.2 (equivalent to MySQL 5.5) does provide some tricks to
prevent this performance hit[6], but it's not clear that those
optimizations will be able to be applied to RT's queries, as not all of
the column types match between tables.  It also doesn't get you all
of the way to InnoDB join performance.  Finally, the tables may also
need explicit hinting in order to partition the data to give any sort of
locality across the hosts.

On the other hand, if you're deploying an NDB cluster, you may already
have the MySQL DBAs on-hand to attend to those.  I've never heard of
an NDB deploy, discovering the correct partitioning scheme would be
all uncharted territory.



NDB clusters also don't support FULLTEXT indexes[7], though that's
clearly only an optional feature for RT.


Pescoller, consider me curious to hear back if you actually deploy RT
against and NDB cluster in production, and the performance
characteristics you see compared to single-host InnoDB.
 - Alex



[1] https://en.wikipedia.org/wiki/Isolation_(database_systems)
[2] https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
[3] Repeatable read nominally opens up the possibility of "phantom
reads" where range queries can return inconsistent data from one query
to another; however, InnoDB uses some clever locking tricks to prevent
them.
[4] https://www.postgresql.org/docs/9.1/static/transaction-iso.html#XACT-READ-COMMITTED
[5] http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html
[6] http://mysqlhighavailability.com/70x-faster-joins-with-aql-in-mysql-cluster-7-2/
[7] https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations-syntax.html