1 / 49

MySQL Empowers Mission Critical Financial Application ~ MySQL Case study in financial industry ~

Read this MySQL case study in the financial industry by Ryusuke Kajiyama, MySQL Senior Evangelist, to learn about the project background, system requirements, architecture, DBMS selection, detailed design, and benchmark.

patricar
Download Presentation

MySQL Empowers Mission Critical Financial Application ~ MySQL Case study in financial industry ~

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL Empowers Mission Critical Financial Application ~ MySQL Case study in financial industry ~ Ryusuke Kajiyama MySQL Senior Evangelist, APAC Sun Microsystems / MySQL

  2. Agenda 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 2

  3. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 3

  4. Project Overview • Target system • Front office support application for asset management companies (investment trusts/investment advisors, etc.) • System for fund managers • software-as-a-service model application • The purposes of systematization • Timely front positions understanding and fund plannings • To enhance the ability to manage financial products in accordance • To integrate information from various sources, both in and outside the company • To focus on core operations as business efficiency improves 4

  5. The main functions of the system Application functions • Front-office position management • Fund and capital management • Contract management • User management • Data integration via messaging infrastructure (used for linkage with systems of other companies) • Client application development framework • (tailored for the individual needs of fund managers) Infrastructure functions 5

  6. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 6

  7. 2. System requirements • Basic requirements • Small start & high scalability • In terms of cost • In terms of system configuration • Consideration for multi-customers • Data Volume • The number of funds: 2,500+ • The number of balances reported by account/brand: 250,000+ • Transaction data volume: 1TB+ • Function requirements • Advanced user interface • Graphs and other GUI components as well as a high operability of drag and drop, and other functions • Publishing infrastructure for data to be reflected in real-time • Real-time/batch data linkage between systems • Messaging/file transfer 7

  8. 2. System requirements • Performance requirements (online) • Number of transactions per second: 200/second • Response time: 3 seconds • Fault-tolerance requirements • Avioidng single failure point (Server/network duplexing) • If server down: recovery time within 10 minutes • Operational requirements • Weekdays: 6:00-20:00 (online) • Other days: application batch/infrastructure batch • All maintenance operations are automated 8

  9. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 9

  10. System configuration overview Presentation Infrastructure ------ ------ ------ Screen layout definition Screen - data map Data set definition Data load Data cache Orchestration Infrastructure Messaging infrastructure Routing Publishing Database linkage Workflow definition Flow control System evidence / system audit Service component group Database service group Position management Fund management OMS Brand Benchmark Account Company Compliance Risk management Market Characteristics 10

  11. Server configuration overview Servers supporting the architecture are as follows: 11

  12. Hardware configuration This program exclusively employs IA-based servers. 12

  13. Software configuration • Presentation infrastructure Aggregation server Application Apache 2.0.54 In-company F/W Strus EMS Client API Scale-out configuration JBoss AP Server 4.0.5 JDK 5.0 RedHat Linux ES 4.0 • Messaging infrastructure EMS server External EMS server TIBCO EMS 4.4.0 Scale-up + HA configuration Cluster middleware RedHat Linux ES 4.0 13

  14. Software configuration • Service group infrastructure Position/fund management server Authentication server Application In-company F/W MySQL 5.0.40 EMS Client API JBoss AP Server 4.0.5 JDK 5.0 Cluster middleware Scale-up + HA configuration RedHat Linux ES 4.0 14

  15. Screen shots • User registration screen 15

  16. Screen shots • Front position management screen 16

  17. Screen shots • Transaction progress management screen 17

  18. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 18

  19. First thoughts Commercial product ? MySQL ? 19

  20. Thoughts on “commercial product” • Points: pros • Both in and outside the company it has produced proven results: • Stable product quality • Many engineers with know-how are in SIer • More know-how of application and system design in SIer • The product architecture can accommodate a large-scale project • Points: cons • Cost (1) • Software license fees/software maintenance fees are required. • Cost (2) • Higher spec hardware may be needed to run heavy DBMS. • Possible higher hardware costs • Cost (3) • While existing know-how can be applied, designing and implementation tend to be time consuming 20

  21. Thoughts on MySQL • Points: pros • Expectations from simple architecture of MySQL: • Easier in designing and implementations phase • Fewer errors in designing and implementations • Less time for designing and implementations • Low costs • No software license fee; only an inexpensive maintenance fee • Easy designing means SE cost reductions can be expected. • Points: cons • Proven results are relatively limited in business systems • Main proven results are in reference systems. • No proven results with NRI’s internal use in update systems. • It is uncertain whether the requirements and practical use conditions of this project would be met. • Limited design know-how • Not much experience in HA design • Difficult to find technical experts in other div of NRI 21

  22. Final decision “MySQL” • Reasons behind adopting MySQL MySQL meets basic requirements of this project. • MySQL enables a small start both in investment costs and systems scale. • MySQL is flexible to expand both in terms of investment costs and scale. * Cost advantage is outstanding, including preparing dev environment. Rapid system design and implementation • Ease of expansion meets requirements in this multi-user model system. • Simplicity of MySQL’s implementation process is impressive. Advanced technical support from NRI’s OSS support team • Quick trouble shooting can be expected. • Many experienced engineers went through number of tough projects Challenging something new!! • Using only proven technology will rust knowledge and solution capability 22

  23. Comments in executive review • Suggestions in internal design review meeting (1) Review by MySQL professional for system design is required, because team did not have much experience of using MySQL in mission critical and high transactional environment. (2) Feasibility test should be conducted using real environment. (3) If the system malfunctioned, backup plans should be discussed for worst case scenarios. • Response measures based on the suggestions For (1) • The lack of design know-how was complemented by support from NRI’s OSS Support team and MySQL. • Followed by a DBMS-related professional review For (2) • A simple prototype application was developed to confirm that at least there was no problem with function/performance. For (3) • As a backup plan, in the case of the system being found infeasible, replace with a commercial product. 23

  24. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 24

  25. System design/operation design • Multi-users support • Adopting the InnoDB storage engine to handle transaction data • Single MySQL server instance to be shared for multiple companies,not running multiple instances for each companies on the single server • To reduce daily operational tasks, use partitions (backup, monitoring) • Minimizing downtime through server • Storing data/index in the common tablespace file MySQL DB for Company A DB for Company B DB for Company N Tablespace (single file viewed from the OS) 25

  26. Backup with “Snapshot” • Backup operations (Step 1) • Adopting “Snapshot” feature of the storage device 【Processing steps】 (1) Storage management server issues a Snapshot command. A new tablespace is cloned in storage device. NFS media server Storage management server Position/fund management server Snapshot command Unmount Shared storage Snapshot operation performed Tablespace Tablespace DB for Company B DB for Company A Snapshot 26

  27. Backup with “Snapshot” • Backup operations (Step 2) (2) When Snapshot operation is done, the position/fund management server mounts the shared storage, in preparation for making service available; in addition, in preparation for backup, the NFS media server mounts the copy area. NFS media server Storage management server Position/fund management server Available Mount Mount Shared storage Tablespace Copied tablespace DB for Company B DB for Company A 27

  28. Backup with “Snapshot” • Backup operations (Step 3) (3)The backup server issues a backup command to the NFS media server and executes “LAN-free backup” to tape library is performed. Backup server NFS media server Backup command Mount Tape library Shared storage Copied tablespace 28

  29. Infrastructure test • Evaluation of features • The basic features as a DBMS meets the conditions of practical use. • New features of MySQL 5.0 was not used in this project. e.g. view, stored procedure and trigger • Avoiding the use of brand-new features to reduce risks: the application can be implemented without such features • Preparing for performance tuning: View will not be used • Facilitating operations and maintenance: Avoiding using stored procedure and trigger • After series of tests, stability of products, ease of restore + recovery and simplicity of restore + recovery process met our requirements. • TIPS: When configuring HA cluster, do NOT use `mysqld_safe` shell script to start the server. -> In case of failure , both mysqld_safe and clustering tool will try to restart MySQL server and won’t make proper fail-over or cause unstable condition. 29

  30. Performance tests • Evaluation of performance • Doing benchmark tests to find out basic performance of MySQL server. Tests included Import, Export and Create Index performance. • Using production environment to find “real” performance. 30

  31. Performance tests • Select (FULL Scan) performance • Data size: 1GB, 5GB • Concurrency: 1, 4, 8, and 16 • Rebooting OS before each test case • Issuing two sets of queries in a row per concurrency • 1st : Right after booting the MySQL server • 2nd: After a 60-second interval following the first query • The SQL statement issued is shown below. Full table scan is forced by the HINT • SELECT SUM(gid) FROM http_auth IGNORE INDEX (primary key) ; • To assess the execution time, the date command was executed before and after processing and the difference was measured. 31

  32. Performance tests • Select (FULL Scan) performance : results • Data transfer rate of storage : 14.5[MB/sec]. • With 5GB, data transfer rate was accelerated to 59.7[MB/sec] with smart storage controller. • No difference was found relating to concurrency, probably because of one-table access. • The effectiveness of the cache mechanism was confirmed. 32

  33. Performance test • Select (FULL Scan) performance : results • CPU utilization (1GB, 1 concurrency pattern) • In the 1st trial, the process proved to be I/O bound. • In the 2nd trial, CPU utilization proved to be close to zero. The 2nd trial The 1st trial 33

  34. Performance test • Select (FULL Scan) performance : results • Available memory size (1GB, 1 concurrency pattern) • In the 1st trial, approximately 1.4 GB memory was used, probably because the retrieval data was cached. • In the 2nd trial, no change was found. The 2nd trial The 1st trial 34

  35. Performance test • Select (FULL Scan) performance : results • Disk read size: 1GB, 1 concurrency pattern • In the 1st trial, a disk read was found to have occurred with an average of approx 15[MB/s]. • In the 2nd trial, no disk read was found. The 2nd trial The 1st trial 35

  36. Performance test • Select (Index Scan) performance • Data size: 1GB, 5GB • Concurrency: 1, 4, 8, and 16 • Rebooting OS before each test case • Issuing two sets of queries in a row per concurrency • 1st : Right after booting the MySQL server • 2nd: After a 60-second interval following the first query • The SQL statement issued is shown below. Executing the program by specifying a unique and random number for the WHERE statement • SELECT * FROM http_auth WHERE uid ＝ ‘xxx’ ; • To assess the execution time, the date command was executed before and after processing and the difference was measured. 36

  37. Performance test • Select (Index Scan) performance : results • Retrieval performance is processed at the high speed of 10[ms/sec]. A 5GB item of data is processed at a speed of 20-30 [ms/sec]. • An increase in concurrency results in almost no decrease in retrieval speeds. • It was confirmed that the cache mechanism functions effectively. 37

  38. Performance test • Select (Index Scan) performance : results • CPU utilization (1GB, 1 concurrency pattern) • In the 1st trial, it was found that only a minimal amount of resources was required to complete the process. • In the 2nd trial, CPU utilization proved to be close to zero. The 2nd Trial The 1st Trial 38

  39. Performance test • Select (Index Scan) performance : results • Available memory size (1GB, 1 concurrency pattern) • In the 1st trial, a memory of approx. 11MB was utilized. Index and retrieved data are considered to have been cached. • In the 2nd trial, no reduction in free memory and no increase in cache was found. The 2nd trial The 1st trial 39

  40. Performance test • Select (Index Scan) performance : results • Disk read size (1GB, 1 concurrency pattern) • In the 1st trial, a disk read was found to have occurred (approx. 13[KB/s] on average). • In the 2nd trial, no disk read was found. The 1st trial The 2nd trial 40

  41. Performance test • Inserts performance • Data size: 1GB, 5GB • Concurrency: 1, 5, and 10 • Rebooting OS before each test case • Inserting one record = 1KB • In the case of 5 or 10 multiplexes, a specified data size is to be built in throughout the entire number of processes operated in parallel. Example) in the case of a 5 GB data size • 1 concurency ：1 thread (5GB/thread) Insert • 5 concurency ：5 threads (1GB/thread) Inserts • 10 concurency ：10 threads (500MB/thread) Inserts • To assess the execution time, the date command was executed before and after processing and the difference was measured. 41

  42. Performance test • Inserts performance : results • Insert performance of 450-480[processes/second] with single thread.In 5GB test case, 500+ [processes/second] • Particularly noteworthy was that regardless of multiplex or DB size, there was no change in throughputs. Although in this test, commit processing was performed for each insert, the performance in this method is considered to be reaching the processing limit. 42

  43. Performance test • Inserts performance : results • CPU utilization (1GB, 1 concurrency pattern) • An average CPU utilization is 13% with a maximum utilization of 37%. • It was confirmed that programmatically, a delayed write occurred even after the completion of the Insert process. It can be assumed that the MySQL system performs commit processing. Delayed write 43

  44. Performance test • Inserts performance : results • Available memory size: 1GB, 1 concurrency pattern • It is considered that through the Insert processing, the data for Insert was cached. A reduction of approx. 1GB due to caching 44

  45. Performance test • Inserts performance : results • Disk read/write size: 1GB, 1 concurrency pattern • Few disk reads occurred. • An average disk read speed was 14[MB/s] with maximum 17[MB/s]. • The bottleneck can be caused by the disk I/O to the Binlog. • It was confirmed that a delayed write occurred in disk I/O. Actual DB is updated in bulk? 45

  46. 1. The project background 2. System requirements 3. System architecture 4. DBMS selection 5. Detailed design and benchmark 6. Post project review 46

  47. Benefits of using MySQL • No license fees for MySQL • Minimum investment: Low initial investment costs in this service delivery business model is important • In most of projects, license fee of DBMS is relatively large portion in hardware and software costs. • No additional fees: No fee for options, No uplift • Lower cost of expansion: No additional fee for more CPUs, memory or clients • Stable and high product quality • MySQL showed high data processing performance not only in data reference application, but also in writing application. • With no product deficiency detected during development and production phase. • Easy to confirm “real” performance in early stage, because MySQL does not require high spec servers and testing on dev server shows similar results on production server 47

  48. Conclusion • MySQL is more than cheap & easy-to-use! • There were no technical problems in functions of MySQL • Stable and high product quality • High quality technical support from Sun • Time to use MySQL in mission critical • Now we are seeing more use of MySQL in multiple industryincluding Telco, Finance, Manufacturing, Healthcare and Government. • MySQL is now better than commercial products in both quality and performance. • With the accumulation of best-practice and know-how, better professional services and technical support are available. “Do feasibility tests before jump into project with MySQL.” “You can find many systems which you don’t haveto pay expensive “DBMS Tax” and you can get a lot of benefits with understanding MySQL more.” 48

  49. MySQL Empowers Mission Critical Financial Application ~ MySQL Case study in financial industry ~ Ryusuke Kajiyama rkajiyama@mysql.com MySQL Senior Evangelist, APAC Sun Microsystems / MySQL

More Related