Hello Guys
I know this is basic and different people do that in different ways according to their needs; but I would like to know about the best practices to design a highly scalable real time enterprise databases for financial institutions.
When are we making transaction how the record is kept and tables are designed This is must not be basic ER solutions. Some experts thinks this must be blend of Transactional Database and analytical database (OLTP and OLAP). Whatever it it, the database must be designed to ensure a highly scalable,high performance and high throughput one.
Here is a sceniario. When millions of people are making transactions for their day-to-day saving accounts how the records are kept on the tables and how the tables are updated realtime. , when a transaction has been made their current balance is available at the next moment for next transaction or view., when a transaction has been made their current balance is available at the next moment for next transaction or view. I am thinking about thousands records per minutes. This is a solution for a financial industries so advance application features like page caching wont help me in this regards.
Reliability and scalability can be done both at the application software and hardware level. but improving a highly data-centric application performance is a challenge.
My objective is to know the best practices in software industries in tackling this issue.
Thanks for your co-operation in advance.
Regards
Tanvir
Email: shayer009@hotmail.com
Designing real time database for financial organization
MadJack McMad
AndyPham
I do quite a bit of high-volume DB work as well so I understand where you're coming from.
The two main things that I look for when designing a high-volume DB beyond separating OLTP and OLAP (that's a MUST), is horizontal partitioning and creating insert-only tables.
If you can partition based on something like customer region, keeping that low-volatility data in-memory on your application servers, you're all set in terms of horizontal partitioning.
Insert-only tables are a bit trickier to do. The idea is to change your data model in such a way that your high-volume transactions don't do updates, only inserts. In this way you decrease locking and contention which increases the number of threads that can hit the DB at the same time, thus increasing throughput and TPM (transactions per minute).
One other thing that you might want to consider is moving the data files of those high volume tables to higher performing storage like possibly a flash disk.
When combining all of these "optimizations", I've been able to get the high levels of throughput you mentioned - 10,000 TPM is not only achievable, but also not that expensive in terms of hardware.
Let me know how this works out.
_FlaYer
Hi Tanvir,
In addition to what Udi and Keith said you can also consider using a tiered approach for the data caching changes (transactionally) on the applicaiton servers as well as the master database. The result of this may be that a user continueing from transaction to transaction will see the updates - however connecting again later she may not see them as the cahnge have not rippled from the backend to all the intermidiate caches - depending on you situation you may not be able to make this tradeoff
Additionally you can take a look at in-memory databases - see for example TimesTen (http://www.oracle.com/database/timesten.html)
If you have (relatively) simple objects you may also consider Jini based solution like http://www.gigaspaces.com/ (they support C# as well)
Arnon
DevDiver
Hi Tanvir,
not sure but possibly here, http://msdn2.microsoft.com/en-us/architecture/ms191149.aspx, is the guidance you are finding out