Re: [rt-users] mysql DB engine ndbdcluster
Hi Reinhold, whats the goal of doing this? Not sure with ndbcluster, but we do it with percona cluster and this does very well. Torsten -Ursprüngliche Nachricht- Von: rt-users [mailto:rt-users-boun...@lists.bestpractical.com] Im Auftrag von Reinhold Pescoller Gesendet: Dienstag, 13. Dezember 2016 10:29 An: rt-users Betreff: [rt-users] mysql DB engine ndbdcluster Hi, Is there some possibilty to change the default db engine from innodb to ndbdcluster in rt4? thanks - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017 Kühne + Nagel (AG & Co.) KG Rechtsform: Kommanditgesellschaft, Bremen HRA 21928, USt-IdNr.: DE 812773878. Geschäftsleitung Kühne + Nagel (AG & Co.) KG: Dr. Hansjörg Rodi (Vors. ), Martin Brinkmann, Matthias Heimbach, Jan-Hendrik Köstergarten, Nicholas Minde, Michael Nebel, Lars Wedel, Matthias Weiner. Persönlich haftende Gesellschafterin: Kühne & Nagel A.G., Rechtsform: Aktiengesellschaft nach luxemburgischem Recht, HR-Nr.: B 18745, Geschäftsführendes Verwaltungsratsmitglied: Karl Gernandt. Geschäftsleitung Region Zentral- und Osteuropa: Dr. Hansjörg Rodi (Vors.), Thierry Held, Uwe Hött, Richard Huhn, Holger Ketz, Jan-Hendrik Köstergarten, Jan Kunze, Michael Nebel, Mustafa Sener. Wir arbeiten ausschließlich auf Grundlage der Allgemeinen Deutschen Spediteurbedingungen 2016 (ADSp 2016). Die ADSp 2016 beschränken in Ziffer 23 die gesetzliche Haftung für Güterschäden in Höhe von 8,33 SZR/kg je Schadenfall bzw. je Schadenereignis auf 1 Million bzw. 2 Millionen Euro oder 2 SZR/kg, je nachdem, welcher Betrag höher ist, und bei multimodalen Transporten unter Einschluss einer Seebeförderung generell auf 2 SZR/kg. Den vollständigen Text der ADSp 2016 übersenden wir Ihnen gerne auf Anfrage und können Sie auch unter http://www.kuehne-nagel.com einsehen. - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017
Re: [rt-users] mysql DB engine ndbdcluster
On Wed, 14 Dec 2016 09:42:42 -0500 Mike Diehl 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
Re: [rt-users] mysql DB engine ndbdcluster
At the risk of picking a fight, I'd like to understand this a bit better. As long as the database supports minimum functions, such as transactions, joins, datatypes, etc., why should an application care about the underlying storage engine? Are you trying to imply that ndbcluster simply isn't production-ready? If so, then end of conversation. Otherwise, what are some of the issues the OP will have to look forward to? Thanks. Mike. On Tuesday, December 13, 2016 09:05:11 PM Alex Vandiver wrote: > On Tue, 13 Dec 2016 12:25:37 +0100 > > Pescoller Reinhold wrote: > > Thanks for your informations. > > > > I tried to do so but rt give me an error that innodb is required and > > that I should upgrade my tables. > > Have I to change this direct in the code? > > lib/RT/Handle.pm: > https://github.com/bestpractical/rt/blob/095caac2a4b4fc7baba0d7878a79f8b4865 > 79854/lib/RT/Handle.pm#L291 > > I'll reiterate that while RT may appear to work in trivial > conditions, you're setting yourself up for a world of both poor > performance and nasty race condition bugs. You get to keep all of the > pieces when it bursts into flames in production -- NDB is in no way a > supported, suggested, or sane backing engine for RT. > - Alex > - > RT 4.4 and RTIR training sessions, and a new workshop day! > https://bestpractical.com/training * Los Angeles - January 9-11 2017 -- Mike Diehl Diehlnet Communications, LLC. Sales: (800) 254-6105 Voice: (505) 903-5700 Fax: (505) 903-5701 - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017
Re: [rt-users] mysql DB engine ndbdcluster
On Tue, 13 Dec 2016 12:25:37 +0100 Pescoller Reinhold wrote: > Thanks for your informations. > > I tried to do so but rt give me an error that innodb is required and > that I should upgrade my tables. > Have I to change this direct in the code? lib/RT/Handle.pm: https://github.com/bestpractical/rt/blob/095caac2a4b4fc7baba0d7878a79f8b486579854/lib/RT/Handle.pm#L291 I'll reiterate that while RT may appear to work in trivial conditions, you're setting yourself up for a world of both poor performance and nasty race condition bugs. You get to keep all of the pieces when it bursts into flames in production -- NDB is in no way a supported, suggested, or sane backing engine for RT. - Alex - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017
Re: [rt-users] mysql DB engine ndbdcluster
On Tue, 13 Dec 2016 10:28:41 +0100 Reinhold Pescoller wrote: > Is there some possibilty to change the default db engine from innodb to > ndbdcluster in rt4? RT assumes REPEATABLE READ isolation; you may encounter subtle and difficult to diagnose bugs under READ COMMITTED isolation, which is all that NDB supports. Performance of joins is generally not great under NDB, and RT assumes that joins do not incur a significant cost penalty. In short, you _might_ be able to get it to run simply by adjusting the table types, but I only expect it to get you into trouble down the road. - Alex - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Los Angeles - January 9-11 2017
