trafodion support investigation

[Eric Owhadi <er...@esgyn.com>]

Hi Fineracteers,

I have been looking at Trafodion integration requirement, and as pointed out by Vishwas, the first obstacle is on lack of flyway support.
However, I think this is no big deal, after I have seen that Cassandra had its own ways of dealing with schema version (CassandraJourney), and I can easily implement a TrafodionJourney. The advantage of this approach over flyway is that the flyway method have a drawback (at least from my quick overview of the features):
Let me explain: when doing a create table, then dropping columns, and then adding column, and dropping again, and adding again, some (most?) database, like Trafodion, will end up having an internal storage for that particular table not as optimized as it would be if the final table was defined without a succession of adding and dropping columns. I am not sure flyway allow defining/playing just the final end state for a given table, or if no matter what, you always have to successively play all versions one after the other.
I plan to support both successive replay of each version, and a direct jump to final version on a TrafodionJourney ecosystem. So that for fresh installs, we don't get a sub optimal storage format.
Thoughts?

Also I have another question:
The key advantage of going NewSQL is on horizontal scalability. I assume the reason I read that Fineract cn can support small bank, but not medium or large, is that the RDBMS for a single tenant will be the bottleneck?
I am guessing that transactions on the checking account must be the most stressed table? If so, I am wondering if at tenant configuration time, we can specify if the tenant we are creating is going to be "small/medium/big", so that DDL associated with stressful table can be adapted and "salted" across multiple servers of the DB cluster. So in a nutshell, I need the DDL to be parameterized based of tenant size. Is that already something accounted for?

Thanks in advance for the help,
Eric Owhadi

RE: trafodion support investigation

[Eric Owhadi <er...@esgyn.com>]

Hi Victor,
The proposal is to investigate using Trafodion (NewSQL) instead of both MariaDB/postgres(Classic RDBMS) and Cassandra (NoSQL).
Thanks for your insight on the fact that there is no single answer on what table will be stressed based on size of tenant. My problem is the following: when using a MPP (Massively Parallel) Database, the way horizontal scalability is achieved can require some hint at DDL time (For SALTING/PARTITIONING). If a specific table is a hot spot, one can easily kill it by spreading the table across multiple nodes. We may not have such a thing as a single table needing splitting across multiple nodes, and balancing may be good enough by having one table be on one node, while all other are spread across other nodes. Just wanted to be ready in case "SALTING" is needed, and provide this optional capability just in case.

There is no specific use case in mind, it is a general potential optimization applicable to all existing use cases supported by current and future microservices. Not sure if you saw the initial message I sent about the idea of using trafodion in fineract-cn sent on the dev list?

Again, thanks for the insight, very helpful,
Regards,
Eric


From: Victor Manuel Romero Rodriguez <vi...@fintecheando.mx>
Sent: Monday, June 17, 2019 9:59 AM
To: dev@fineract.apache.org
Subject: Re: trafodion support investigation

External

Hi Eric,

The proposal is to use Trafodion and MariaDB/Postgresql? Or how is the approach?

About doing a setup on the DDL for  "small/medium/big" tenant based on the tenant size, looks strange to me, adding a layer of complexity to existing functionality of NewSQL.

Big banks have different line of business, medium banks also. (Size... is very relative... it could be related to retail customers, assets, market cap, etc) .

Transaction tables are stressed depending on the products that the financial institution offers to its customers and also the delivery channels. I have worked for big banks and in the local core banking (Retail Banking and Wealth Management) running in a zOS, the bottleneck was not in the DB2 tables related to the customers, it was in a treasury account used for the interbank payments, because all the channels (ATMs, Branches, Mobile, Personal Banking, Corporate Banking) access that table for encrypting/decrypting the transactions and storing them for any rollback. On another line of business (Global Banking and Markets) the bottleneck is not in the customer accounts, they in fact do very few transaction but the tables used for the market data are stressed.

Having a clear use case will help to apply Trafodion.

Regards

Victor

On 6/14/19 9:06 a. m., Eric Owhadi wrote:
Hi Fineracteers,

I have been looking at Trafodion integration requirement, and as pointed out by Vishwas, the first obstacle is on lack of flyway support.
However, I think this is no big deal, after I have seen that Cassandra had its own ways of dealing with schema version (CassandraJourney), and I can easily implement a TrafodionJourney. The advantage of this approach over flyway is that the flyway method have a drawback (at least from my quick overview of the features):
Let me explain: when doing a create table, then dropping columns, and then adding column, and dropping again, and adding again, some (most?) database, like Trafodion, will end up having an internal storage for that particular table not as optimized as it would be if the final table was defined without a succession of adding and dropping columns. I am not sure flyway allow defining/playing just the final end state for a given table, or if no matter what, you always have to successively play all versions one after the other.
I plan to support both successive replay of each version, and a direct jump to final version on a TrafodionJourney ecosystem. So that for fresh installs, we don't get a sub optimal storage format.
Thoughts?

Also I have another question:
The key advantage of going NewSQL is on horizontal scalability. I assume the reason I read that Fineract cn can support small bank, but not medium or large, is that the RDBMS for a single tenant will be the bottleneck?
I am guessing that transactions on the checking account must be the most stressed table? If so, I am wondering if at tenant configuration time, we can specify if the tenant we are creating is going to be "small/medium/big", so that DDL associated with stressful table can be adapted and "salted" across multiple servers of the DB cluster. So in a nutshell, I need the DDL to be parameterized based of tenant size. Is that already something accounted for?

Thanks in advance for the help,
Eric Owhadi

Re: trafodion support investigation

[Victor Manuel Romero Rodriguez <vi...@fintecheando.mx>]

Hi Eric,

The proposal is to use Trafodion and MariaDB/Postgresql? Or how is the 
approach?

About doing a setup on the DDL for  "small/medium/big" tenant based on 
the tenant size, looks strange to me, adding a layer of complexity to 
existing functionality of NewSQL.

Big banks have different line of business, medium banks also. (Size... 
is very relative... it could be related to retail customers, assets, 
market cap, etc) .

Transaction tables are stressed depending on the products that the 
financial institution offers to its customers and also the delivery 
channels. I have worked for big banks and in the local core banking 
(Retail Banking and Wealth Management) running in a zOS, the bottleneck 
was not in the DB2 tables related to the customers, it was in a treasury 
account used for the interbank payments, because all the channels (ATMs, 
Branches, Mobile, Personal Banking, Corporate Banking) access that table 
for encrypting/decrypting the transactions and storing them for any 
rollback. On another line of business (Global Banking and Markets) the 
bottleneck is not in the customer accounts, they in fact do very few 
transaction but the tables used for the market data are stressed.

Having a clear use case will help to apply Trafodion.

Regards

Victor

On 6/14/19 9:06 a. m., Eric Owhadi wrote:

> Hi Fineracteers,
>
> I have been looking at Trafodion integration requirement, and as 
> pointed out by Vishwas, the first obstacle is on lack of flyway support.
>
> However, I think this is no big deal, after I have seen that Cassandra 
> had its own ways of dealing with schema version (CassandraJourney), 
> and I can easily implement a TrafodionJourney. The advantage of this 
> approach over flyway is that the flyway method have a drawback (at 
> least from my quick overview of the features):
>
> Let me explain: when doing a create table, then dropping columns, and 
> then adding column, and dropping again, and adding again, some (most?) 
> database, like Trafodion, will end up having an internal storage for 
> that particular table not as optimized as it would be if the final 
> table was defined without a succession of adding and dropping columns. 
> I am not sure flyway allow defining/playing just the final end state 
> for a given table, or if no matter what, you always have to 
> successively play all versions one after the other.
>
> I plan to support both successive replay of each version, and a direct 
> jump to final version on a TrafodionJourney ecosystem. So that for 
> fresh installs, we don’t get a sub optimal storage format.
>
> Thoughts?
>
> Also I have another question:
>
> The key advantage of going NewSQL is on horizontal scalability. I 
> assume the reason I read that Fineract cn can support small bank, but 
> not medium or large, is that the RDBMS for a single tenant will be the 
> bottleneck?
>
> I am guessing that transactions on the checking account must be the 
> most stressed table? If so, I am wondering if at tenant configuration 
> time, we can specify if the tenant we are creating is going to be 
> “small/medium/big”, so that DDL associated with stressful table can be 
> adapted and “salted” across multiple servers of the DB cluster. So in 
> a nutshell, I need the DDL to be parameterized based of tenant size. 
> Is that already something accounted for?
>
> Thanks in advance for the help,
>
> Eric Owhadi
>