On Wed, 14 Dec 2016 09:42:42 -0500 Mike Diehl <mdiehlena...@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 --------- RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017