Скачать презентацию My SQL Empowers Mission Critical Financial Application Скачать презентацию My SQL Empowers Mission Critical Financial Application

c560bc159902d7a0dcc14408ee3c25ce.ppt

  • Количество слайдов: 49

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

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

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

Project Overview n. Target system  ü Front office support application for asset management companies Project Overview n. 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 n. 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

The main functions of the system Application functions  üFront-office position management üFund and capital The main functions of the system Application functions  üFront-office position management üFund and capital management üContract management üUser management Infrastructure functions ü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) 5

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

2. System requirements n. Basic requirements  üSmall start & high scalability ØIn terms of 2. System requirements n. Basic requirements  üSmall start & high scalability ØIn terms of cost ØIn terms of system configuration üConsideration for multi-customers n. Data Volume üThe number of funds: 2, 500+ üThe number of balances reported by account/brand: 250, 000+ üTransaction data volume: 1 TB+ n. 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

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

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

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

Server configuration overview Servers supporting the architecture as follows: Usage Role Presentation infrastructure Aggregation Server configuration overview Servers supporting the architecture as follows: Usage Role Presentation infrastructure Aggregation server ・Accepting client requests, and via the EMS server requesting the service group to deal with them ・Delivering real data on the server side to clients Messaging infrastructure EMS server ・Serving as a messaging infrastructure to enable smooth communications between servers which are loosely coupled Service group (In this project, DB function is available in each service server. ) Position management / ・Providing business logic relating to position fund management server management/fund management ・Using same configuration for business logic server and DB server Authentication server ・Providing an authentication function Other (key usages) File linkage server ・A server used for file linkage with external systems External EMS server ・A server used for real-time linkage with external systems Shared disk ・Storage for sharing business DB, authentication DB and others 11

Hardware configuration This program exclusively employs IA-based servers. Usage CPU Memory HDD capacity Presentation Hardware configuration This program exclusively employs IA-based servers. Usage CPU Memory HDD capacity Presentation infrastructure Aggregation server Dual core Xeon 5160 (3. 00 GHz) 4 GB 146 GB(RAID 1) Messaging infrastructure EMS server Service group (In this project, DB function is available in each service server. ) Position management / Quad core Xeon X 535 (2. 66 GHz) 8 GB fund management server 146 GB(RAID 1) Authentication server Dual core Xeon 5110 (1. 60 GHz) 4 GB 146 GB(RAID 1) File linkage server Dual core Xeon 5110 (1. 60 GHz) 4 GB 146 GB(RAID 1) External EMS server Dual core Xeon 5160 (3. 00 GHz) 4 GB 146 GB(RAID 1) Shared disk The 2 C 2 D (C: controller, D: disk enclosure) configuration with a capacity of 146 GB× 16 HDDs. Possible to increase capacity up to 25 TB (146 GB× 28 HDDs). By adding enclosures, can be up to 50 TB with 2 C 4 D (56 HDDs) 1238 GB (RAID 5) SAN switch - - Others - 12

Software configuration n. Presentation infrastructure Aggregation server Apache 2. 0. 54 Application In-company F/W Software configuration n. Presentation infrastructure Aggregation server Apache 2. 0. 54 Application In-company F/W Strus EMS JBoss AP Server 4. 0. 5 Client API e-out JDK 5. 0 Scal ration Red. Hat Linux ES 4. 0 onfigu c n. Messaging infrastructure EMS server External EMS server TIBCO EMS 4. 4. 0 Cluster middleware Red. Hat Linux ES 4. 0 + HA le-up on Sca gurati confi 13

Software configuration n. Service group infrastructure Position/fund management server Authentication server EMS Client API Software configuration n. Service group infrastructure Position/fund management server Authentication server EMS Client API Application In-company F/W JBoss AP Server 4. 0. 5 JDK 5. 0 Cluster middleware Red. Hat Linux ES 4. 0 My. SQL 5. 0. 40 + HA le-up Sca ation figur con 14

Screen shots n User registration screen 15 Screen shots n User registration screen 15

Screen shots n Front position management screen 16 Screen shots n Front position management screen 16

Screen shots n Transaction progress management screen 17 Screen shots n Transaction progress management screen 17

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

First thoughts Commercial product ?   My. SQL ?   19 First thoughts Commercial product ?   My. SQL ?   19

Thoughts on “commercial product” n. Points: pros üBoth in and outside the company it Thoughts on “commercial product” n. 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 n. 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

Thoughts on My. SQL n. Points: pros üExpectations from simple architecture of My. SQL: Thoughts on My. SQL n. Points: pros üExpectations from simple architecture of My. SQL: Ø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. n. 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

Final decision “My. SQL” n. Reasons behind adopting My. SQL meets basic requirements of Final decision “My. SQL” n. Reasons behind adopting My. SQL meets basic requirements of this project. ØMy. SQL enables a small start both in investment costs and systems scale. ØMy. SQL 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 My. SQL’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

Comments in executive review n. Suggestions in internal design review meeting (1) Review by Comments in executive review n. Suggestions in internal design review meeting (1) Review by My. SQL professional for system design is required, because team did not have much experience of using My. SQL 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. n. 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 My. SQL. Ø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

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

System design/operation design n. Multi-users support üAdopting the Inno. DB storage engine to handle System design/operation design n. Multi-users support üAdopting the Inno. DB storage engine to handle transaction data üSingle My. SQL 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 My. SQL DB for Company A DB for Company B DB for Company N Tablespace (single file viewed from the OS) 25

Backup with “Snapshot” n. Backup operations (Step 1) üAdopting “Snapshot” feature of the storage Backup with “Snapshot” n. 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. Storage management server Position/fund management server NFS media server Snapshot command Unmount Shared storage DB for Company A Snapshot operation performed DB for Company B Tablespace Snapshot Tablespace 26

Backup with “Snapshot” n. Backup operations (Step 2) (2) When Snapshot operation is done, Backup with “Snapshot” n. 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. Storage management server Position/fund management server Available Mount NFS media server Mount Shared storage DB for Company A DB for Company B Copied tablespace Tablespace 27

Backup with “Snapshot” n. Backup operations (Step 3) (3)The backup server issues a backup Backup with “Snapshot” n. 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. NFS media server Backup command Backup server Mount Shared storage Tape library Copied tablespace 28

Infrastructure test n. Evaluation of features üThe basic features as a DBMS meets the Infrastructure test n. Evaluation of features üThe basic features as a DBMS meets the conditions of practical use. üNew features of My. SQL 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 My. SQL server and won’t make proper fail-over or cause unstable condition. 29

Performance tests n. Evaluation of performance üDoing benchmark tests to find out basic performance Performance tests n. Evaluation of performance üDoing benchmark tests to find out basic performance of My. SQL server.  Tests included Import, Export and Create Index performance. üUsing production environment to find “real” performance. Tests Purposes - To obtain basic performance values for full-table scan in My. SQL Select (FULL Scan) - To find the difference of execution time and resources usage on: data size; concurrency; existence of cache in Inno. DB buffer pool. - To obtain basic performance values in index search in My. SQL Select (Index Scan) - To find the difference of execution time and resources usage on: data size; concurrency; existence of cache in Inno. DB buffer pool. Insert - To obtain basic performance values in data insert in My. SQL - To find the difference of execution time and resources usage on: data size; concurrency; existence of cache in Inno. DB buffer pool. 30

Performance tests n. Select (FULL Scan) performance üData size: 1 GB, 5 GB üConcurrency: Performance tests n. Select (FULL Scan) performance üData size: 1 GB, 5 GB üConcurrency: 1, 4, 8, and 16 üRebooting OS before each test case üIssuing two sets of queries in a row per concurrency Ø 1 st : Right after booting the My. SQL server Ø 2 nd: 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

Performance tests n. Select (FULL Scan) performance : results Data size Concurrenc y Trial Performance tests n. Select (FULL Scan) performance : results Data size Concurrenc y Trial Execution time (hh: mm: dd) 1 GB 1 1 st 0: 01: 37 2 nd 0: 00 4 1 st 0: 01: 37 2 nd 0: 00 8 1 st 0: 01: 36 2 nd 0: 00 üData transfer rate of storage : 14. 5[MB/sec]. 16 1 st 0: 01: 39 üWith 5 GB, data transfer rate was accelerated to 59. 7[MB/sec] 2 nd 0: 00 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

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

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

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

Performance test n. Select (Index Scan) performance üData size: 1 GB, 5 GB üConcurrency: Performance test n. Select (Index Scan) performance üData size: 1 GB, 5 GB üConcurrency: 1, 4, 8, and 16 üRebooting OS before each test case üIssuing two sets of queries in a row per concurrency Ø 1 st : Right after booting the My. SQL server Ø 2 nd: 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

Performance test n. Select (Index Scan) performance : results Data size 1 GB Concurrency Performance test n. Select (Index Scan) performance : results Data size 1 GB Concurrency Trial Execution time (hh: mm: dd) 1 1 st 0: 01 0: 00: 00 1 st 0: 00: 01 2 nd 16 0: 00 2 nd 8 0: 01 2 nd 4 1 st 0: 00 Ave. throughputs Ave. response (TPS) time (ms/process) 100 - 10 - 400 - 10 - 800 - 1600 - 10 - üRetrieval performance is processed at the high speed of 10[ms/sec]. A 5 GB 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

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

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

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

Performance test n. Inserts performance üData size: 1 GB, 5 GB üConcurrency: 1, 5, Performance test n. Inserts performance üData size: 1 GB, 5 GB üConcurrency: 1, 5, and 10 üRebooting OS before each test case üInserting one record = 1 KB ü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 (5 GB/thread) Insert   5 concurency : 5 threads (1 GB/thread) Inserts  10 concurency : 10 threads (500 MB/thread) Inserts üTo assess the execution time, the date command was executed before and after processing and the difference was measured. 41

Performance test n. Inserts performance : results Data Concurrency size 1 GB 1 5 Performance test n. Inserts performance : results Data Concurrency size 1 GB 1 5 10 Execution time (sec. ) Throughputs/ Ave. throughputs Total throughputs thread per threads (TPS) (processes/second) 452. 1 2212 452. 1 96. 0 2089 95. 9 478. 7 96. 0 95. 8 47. 8 2095 47. 8 477. 2 47. 8 47. 7 üInsert performance of 450 -480[processes/second] with single thread. In 5 GB 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

Performance test n. Inserts performance : results üCPU utilization (1 GB, 1 concurrency pattern) Performance test n. Inserts performance : results üCPU utilization (1 GB, 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 My. SQL system performs commit processing. Delayed write 43

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

Performance test n. Inserts performance : results üDisk read/write size: 1 GB, 1 concurrency Performance test n. Inserts performance : results üDisk read/write size: 1 GB, 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

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

Benefits of using My. SQL n. No license fees for My. SQL üMinimum investment: Benefits of using My. SQL n. No license fees for My. SQL ü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 n. Stable and high product quality üMy. SQL 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 My. SQL does not require high spec servers and testing on dev server shows similar results on production server 47

Conclusion n. My. SQL is more than cheap & easy-to-use! üThere were no technical Conclusion n. My. SQL is more than cheap & easy-to-use! üThere were no technical problems in functions of My. SQL üStable and high product quality üHigh quality technical support from Sun n. Time to use My. SQL in mission critical üNow we are seeing more use of My. SQL in multiple industry including Telco, Finance, Manufacturing, Healthcare and Government. üMy. SQL 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 My. SQL. ” “You can find many systems which you don’t have to pay expensive “DBMS Tax” and you can get a lot of benefits with understanding My. SQL more. ” 48

My. SQL Empowers Mission Critical Financial Application ~ My. SQL Case study in financial My. SQL Empowers Mission Critical Financial Application ~ My. SQL Case study in financial industry ~ Ryusuke Kajiyama [email protected] com My. SQL Senior Evangelist, APAC Sun Microsystems / My. SQL