Oracle TimesTen 을이용한 DBMS 성능최적화구축방안 UNIONE I&C 김윤복대표 2007.2
회사소개 회사명 유니원아이앤씨 유니원아이앤씨 주 소 서울시서울시영등포구영등포구여의도동여의도동 44-22 44-22 호성빌딩호성빌딩본관본관 9F 9F (www.unioneinc.co.kr) (www.unioneinc.co.kr) 전화번호 02-780-0039 02-780-0039 팩스번호 02-780-2503 02-780-2503 주요업무 Oracle Oracle 성능성능컨설팅컨설팅 / 유지보수 / 유지보수,, H/W H/W 납품납품 / 유지보수 / 유지보수,, 대외업무시스템대외업무시스템개발개발 2
TimesTen #1 IMDB market share 네트워크장비제조업체의상위 9 개중 7 새사 Timesten 을사용해제품생산 전세계에서가장유명한 Telecom Billing SYSTEM 이 Timesten 을사용 미국내무선 911 콜의 80% 가 Timesten 을통해서연결 유럽상위무선통신사업자 5 개중 4 개가 Timesten 을사용 검증된솔루션 세계에서가장큰 Call Center 에서 Timesten 을사용 세계 2위규모의항공사에서 Timesten 을사용 전세계 1500 개이상의기업에서 TimesTen 을사용하고있습니다. 3
TimesTen Option TimesTen IMDB Real-Time Database System Real-time data management for applications requiring exceptional performance Network Replication option Real-Time Database System with Replication Real-time data management, and replication, for applications requiring exceptional performance, with the failover, recovery, and evolution of databases with minimal downtime Network Cache option Real-Time Dynamic Data Cache Real-time relational processing of selected data from an Oracle database, including automatic data loading and update synchronization Network B Cache DataServer DataServer DataServer Typical Uses: Network Equipment & Systems Value-Added Services Reference Data Primary Subscriber(s) Typical Uses: Network Equipment & Systems Value-Added Services Reference Data Typical Uses: Application Server Acceleration Enterprise App Acceleration Application-Tier Data Caching 4
TimesTen Oracle DB VS TimesTen Database Characteristic Applications Data Model Optimization Target Applications Typical deployment Response Times Data Capacity Administration Oracle Database 10g Mission-critical Relational Disk-centric All Database tier (Backoffice server) Milliseconds (to seconds) Tens of terabytes DBAs & Sys Admins Oracle TimesTen In-Memory Database Mission-critical Relational Memory-centric OLTP, some DSS Application tier (Embedded in application) Microseconds (to milliseconds) Tens of gigabytes Unattended operation 5
TimesTen Preformance(READ) Comparative Throughput TimesTen vs RDBMS IBM eserver pseries 670, 12-CPU 1.1 GHz POWER4 AIX 5.2, 64-bit Transactions Per Second 350,000 300,000 250,000 200,000 150,000 100,000 50,000 0 @ 2 CPUs @ 4 CPUs @ 12 CPUs 12,158 23,577 TimesTen on 2 CPUs outperforms a RDBMS on 12 CPUs 54,856 12 CPUs 71,225 2 CPUs 130,506 4 CPUs 314,076 12 CPUs Best Case RDBMS Read TT Read 6
TimesTen Preformance(READ & WRITE) 7
TimesTen 을이용한 DBMS 성능최적화방안 DB 성능진단 SQL과 Table 추출 Oracle TimesTen 구축 Application 수정 부하테스트 DBMS 성능진단 SQL 과 Object 추출 전체적인 DBMS 와어플리케이션성능파악 DBMS 와어플리케이션의성능저하원인분석 DBMS 성능최적화을위한방안도출 Oracle TimesTen 에캐쉬 (Cache) 할테이블추출 Oracle TimesTen 에서처리할 SQL Statement 추출 Oracle TimesTen 구축 Oracle TimesTen 구성방식선정 Application 수정 Oracle TimesTen 에서처리할수없는경우 (Stored Procedure Function/Procedure) 성능부하테스트 Oracle TimesTen 구성전 / 후성능부하테스트를통한검증 Oracle TimesTen + Unione I&C 8
적용사례 증권
Home Trading System B 증권 Oracle TimesTen을이용한 Home Trading System (Before) Home Trading System - HP rp7640 875MHz * 4 - Memory 6GB 시세 Data C-ISAM DB 사용 시세 Chart Data 시세 Chart Data 문제점 - 각Chart(Tick Chart, 일봉, 월봉 ) 수신속도저하 - 시세폭주종목시세누락 - C-ISAM 장애시 Recovery 속도지연 Active C-ISAM C-ISAM Active 증권전산시세 Data 10
Home Trading System B 증권 Oracle TimesTen을이용한 Home Trading System (After) Home Trading System - HP rp7640 875MHz * 4 - Memory 6GB - MMDB Memory 4GB 사용 시세 Data TimesTen MMDB 적용 효과 - 기존 Server 증설없이성능극대화 - 손쉬운 Application Migration - 완벽한실시간이중화시스템구축 - 사용자응답속도향상으로고객경쟁력강화 - SQL 문장을이용한유지보수편의성증가 시세 Chart Data 시세 Chart Data Active TimesTen TimesTen Replication 증권전산시세 Data Active 11
Home Trading System B 증권 Oracle TimesTen 적용후성능비교 HTS Transaction 시세 Data Throughput Processing TimesTen/DataServer / 5.1 6.1 IBM HP eserver rp7410 875MHz pseries2cpu 670, 12 -CPU 64bit 1.1 C Application GHz POWER4 총 570,000 AIX 5.2, 건 64 처리 -bit 120 100 80 60 C-ISAM TimesTen 40 20 30,000 Writes Per Second 380,000 Reads Per Second 0 UPDATE INSERT SELECT 12
FIX Trading System S 증권 Oracle TimesTen을이용한 FIX Trading System FIX Trading System - SUN Ultra 60 300MHz * 1 - Memory 512MB - MMDB Memory 200MB 사용 Trader 주문, 체결 / 시세 Data TimesTen MMDB 적용 효과 - 기존 Server 증설없이성능극대화 - 손쉬운 Application Migration - 완벽한실시간이중화시스템구축 - 빠른응답속도와안정성을제공한주문처리속도개선 - 시스템을통한단위시간당집중도소화 Active TimesTen Replication 증권전산 Trading Data TimesTen StandBy 증권전산 BASE 21 13
적용사례 대학
DBMS 성능진단 K 대학 수강신청업무시점에서의 SQL Statement 성능분석 SQL 문장별자원사용비중도를분석한것으로가장높은응답시간을기록한 TOP SQL이전체어플리케이션누적응답시간의 19.24% 를사용하였으며, 특히 Latch 대기시간이가장많았음을알수있다. Top Reource SQL 누적응답시간 ( 초 ) CPU( 초 ) Latch 비중도 Select a.std_no.. 128,234 53,697 74,533 19.24% Select decode(e.lt.. 57,996 10,699 34,040 8.7% Select b.open_shyr.. 36,230 22,915 31,700 5.4% 15
수강신청 K 대학 Oracle TimesTen을이용한수강신청시스템 조회요청 / 응답 DML 요청 / 응답 Wep Application Srv IBM HTTP Srv WebSphere TimeSten Cache for Oracle (SQL*Net) Commit 수행시 App Srv MMDB Cache for oracle Domain #1 에의해 TimeSten으 Oracle 10g R2 MMDB를통한 SELECT 조회로 CPU : 1.2Ghz * 4 변경된데이터를자동즉시저장 Mem : 8G Wep Application Srv IBM HTTP Srv WebSphere TimeSten App Srv MMDB Wep Application Srv IBM HTTP Srv WebSphere TimeSten App Srv MMDB JDBC(DML) 수강신청시스템 - 인기과목수강신청폭주로인한 DB 서버 Performance 감소 - 일부조회 DATA를 MMDB 에상주시켜 Oracle Database 부하감소 16
적용사례 해외
JP Morgan Program Trading System 사용용도 Reference data lookups Order processing Event publishing 처리성능 350 transactions per second 10X increase over J2EE object caching 800 txns/sec, 1mil orders/day 50 reference data lookups/sec Institutional Investors Pre-Trade Validation Order Prep and Release Post-Trade Allocation WAS Msg Bus Active Orders, Inquiries, Notifications Standby 시스템구성 16-CPU* Server (plus hot-standby) Solaris with Java applications 2-20 Gigabyte 64-bit TimesTen 기대효과 High-End Txn Proc in Java/J2EE Commercial, standards-based Aged Order Data Shared Global Data Reference Data Global Order Repository Historical Reporting 18
LEHMAN Brothers 채권 Trading System 사용용도 Event capture (trade orders) Order processing (trade matching) Event publishing (trader alerts and closed orders) 처리성능 1,000 fixed-income trades/hr (30/sec peak) 20 trader alerts/sec 시스템구성 4-CPU Server (plus hot-standby) per location Sun/Solaris O/S with C++ applications 2 Gigabyte TimesTen 기대효과 Fast order execution Trader alerting Orders, Inquiries, Notifications Active TimesTen Trader Alerts XLA Application Closed Order Data Tibco Messaging Global Order Repository Internal Trading Desks ( different geography or class of instrument ) TimesTen Standby Active TimesTen XLA Application Trader Alerts Aggregated Reporting TimesTen Standby 19
Oracle TimesTen 구성방안 Centralized vs Distributed
Centralized TimesTen 구성 ( 2 ) ( 3 ) ( 1 ) ( 6 ) Oracle TimesTen WAS ( 5 ) ( 4 ) ( 1 ) HTTP Request ( 2 ) SELECT 조회 ( 3 ) SELECT 결과리턴 ( 4 ) DML 수행 (PassThrough SQL) ( 5 ) DML 결과데이터반영과 AutoRefresh(Oracle RDBMS > TimesTen) Oracle 10g 21
Distributed TimesTen 구성 WAS ( 1 ) ( 2 ) WAS ( 3 ) ( 4 ) Oracle 10g ( 5 ) ( 1 ) HTTP Request WAS ( 2 ) SELECT 조회 ( 3 ) SELECT 결과리턴 ( 4 ) DML 수행 (PassThrough SQL) ( 5 ) DML 결과데이터반영과 AutoRefresh(Oracle RDBMS > TimesTen) 22
Centralized vs Distributed 비교 Centralized Configuration Distributed Configuration Installation 동일시스템에 Oracle RDBMS 와 TimesTen 를설치함 WAS 서버에 TimesTen 를설치함 Auto Refresh 오라클 RDBMS 에변경된데이터 (Commited Data) 를가장빠른속도로 TimesTen 에반영됨. 오라클 RDBMS 에변경된데이터 (Commited Data) 가매우빠른속도로 WAS 에설치된모든 TimesTen 에반영됨 Performance Availability 만약 3 개 Node 의 WAS 에서활성트랜잭션이증가한다면 DBMS 시스템의자원경합에의한속도저하가간헐적으로발생할수있음. TimesTen 장애발생시 TimesTen 도입전과동일한형태의처리가보장되나어플리케이션의성능을기대할수없음. 활성트랜잭션이증가하여도 WAS 안에존재하는개별 TimesTen 에서처리하므로안정적인어플리케이션속도를보장함 3 개의 WAS 에서운용되는 TimesTen 중일부가장애가발생하더라도나머지정상운영되는 TimesTen 에의해어플리케이션의성능을유지할수있음. 23
Oracle TimesTen 사용장점
Oracle TimesTen 사용장점 기존 Oracle RDBMS 와연동용이 Direct Mode 운영으로인한타 MMDBM 보다월등한성능보장 손쉬운마이그레이션 Real Time 속도향상 25
감사합니다.