jordan bruce i07 – open source and ids major canadian retail drugstore’s prescription for...
TRANSCRIPT
Jordan Bruce
I07 – Open Source and IDS
Major Canadian Retail Drugstore’s Prescription For Success
Tuesday 3rd October 2006 • 15:15 – 16:15
2
Major Canadian Retail Drugstore's Prescription For Success
Migrating to IDS 10.00 on LINUX SuSe Sles9
3
Jordan Bruce, Sr. Database Administrator
• 8 Years DBA Experience• Former President of Ontario Canada Informix User Group• Lead Senior DBA at Canada’s leading retail drugstore• Database Administration Experience with IDS, Oracle, and SQL
Server• Currently Administering over 1000 IDS Instances across Canada
for one of the countries largest retailers
4
Case Study of Large Scale IDS 10 Migration:
• Going From SCO to Open Source Linux
• What Was Involved With Our Migration of Over 100 Server Sites
• Performance Gains Achieved With New IDS 10 features
5
Who We Are…
• Canada’s Largest/Leading Drugstore Group
• Operating Since 1962
• 30,000 Employees
• 1000 Retail Drug Stores Nation Wide
• 7 Billion in Total Revenue for 2005
6
What is our Pharmacy Application?
• Allows Critical Health Information to be Securely Accessed by Healthcare Professionals In-House Developed J2EE Pharmacy Application
• GUI Front-End
• Replaces Old Legacy Pharmacy Application
• Uses JMS for inter process Communication
7
Factors leading To Migration
• Slow Performance of Pharmacy Application
• Insufficient Support on SCO
• EOL for Current Platform
• Increased Stability on IDS 10.00
• Simplified Administration on IDS 10.00
• Open Source OS Licensing Cost
• No further migration from IDS 9.30 on current platform
• No future support on current platform for Application Server (WebLogic 7.1)
8
Migrating From…
• OS: SCO OpenUnix 8.0 • Also known as Unixware 7.1.2 with LKP
• Hardware: Proliant ML370 G
• Network: 100Mbps
• Application Server: BEA Weblogic 7 SP 1
• RDBMS: Informix IDS 9.30 UC7
9
Migrating To…
• OS: SUSE LINUX Enterprise Server 9 (i586) • Kernel 2.6.5-7.155.29-bigsmp• glibc-2.3.3-98.38
• Hardware: IBM e326 AMD 1U
• Network: 100Mbps
• Application Server: BEA Weblogic 8.1 SP4
• RDBMS: Informix IDS 10.00UC1
10
Improved Performance with IDS
• IDS 10.00 UC1 vs 9.30 UC7 - 8.82%
• Configurable Page Size – 34.97%• 4kb page size for I/O intensive DBSpaces• 16kb page size for temporary DBSpaces
• Prepared Statement Cache - 16%
• DS_NONPDQ_QUERY_MEM
• OPTCOMPIND
11
Query Performance
49% improvement for queries
0
10
20
30
40
50
60
70
80
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Query #
Ela
psed
Tim
e
IDS 9.30
IDS 10.00
12
Query Performance ‘ The Data’# Test Case IDS 9.30 IDS 10.00 % Gain1 1.1 Load tx Table (without indices) 63.27 19.84 69%2 2.1 Query 1 (no index) - 1% selection 4.2 2.49 41%3 2.2 Query 2 (no index) - 10% selection 2.81 1.82 35%4 2.3 Query 3 (no index) - Join tx1,rx 2.8 1.72 39%5 2.4 Query 4 (no index) - Join B 2.66 1.74 35%6 2.5 Query 5 (no index) - Aggergate Function 3.38 2.42 28%7 2.6 Query 6 (no index) - Aggergate Function/Group By 3.11 2.12 32%8 2.5 Query 7 (no index) - Aggergate Function/Group By 4.48 3.51 22%9 5.1 - Create Indices 31.36 26.07 17%
10 2.1 Query 1 (with index) - 1% selection 1.72 0.76 56%11 2.2 Query 2 (with index) - 10% selection 1.08 0.09 92%12 2.3 Query 3 (with index) - Join tx1,rx 1.03 0.04 96%13 2.4 Query 4 (with index) - Join B 1.03 0.1 90%14 2.5 Query 5 (with index) - Aggergate Function 2.99 0.01 100%15 2.6 Query 6 (with index) - Aggergate Function/Group By 2.79 1.97 29%16 2.5 Query 7 (with index) - Aggergate Function/Group By 3.72 3.18 15%17 3.1 Load tx Table (with index) 74.78 45.97 39%
Overall 49%
13
Application Performance
Roughly a ~62% Improvement for Application
0
200
400
600
800
1000
1200
1400
1600
1800
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35
Application Function
Ela
psed
Tim
e (
ms)
Before
After
14
OS PerformanceUnixware vs. Linux
0
5000
10000
15000
20000
G3 + UW7.1.2 G3 + UW7.1.4 G4 + UW7.1.2 G4 + Linuxstack
Server / OS
Sco
re (
ms)
Roughly a ~39% performance gain
15
Ease of Administration
• External Optimizer Directives
• Reorganizations Made Simple By Renaming DBSpace
• Single User Mode
16
Security
• DBCREATE_PERMISSION
• Secure Environment Check
17
Increased Stability
• Uninterrupted Service at all 114 sites across Canada since implementation
18
Higher Availability
• Point-in-Time Table Restore
• FAST_RESTART_PHYSLOG and FAST_RESTART_CKPT_FUZZYLOG
• Online Index Build
19
Benefits
• Migration from IDS 9.30UC7 to 10.00UC1 –
0 problems• Seamless Migration• End Users Extremely Pleased With Results
• No effort required for Stored Procedures / Triggers
• Weblogic using latest J2EE Standards
20
Discover – In-House Asset Management System• Developed using shell scripting, Expect, XML,
Java, Informix, JavaScript, PHP, ADOdb for PHP, HTML and CSS.
• Web Application is hosted on a Linux server using Apache.
• Store search web interface – provides info by store number
• Management reports web interface – provides quantified info about all stores
21
Discover – Store Search Screenshot
22
Discover Web – Why use PHP?• PHP (http://www.php.net) – Server-side Scripting Language
• Obvious Benefits of Open Source - It’s Free• Quick Development• Platform Independent• Plenty of Other Open-Source Tools for PHP that can be Used
• PHP in Combination with Apache Remove of the Need for an Application Server.
• Not Forced into OOP • If Business Logic is Managed Well by the Database Design,
the Application Does Not Need to be Complex for Simple Display/Search Capabilities
23
Discover Web – Why use ADOdb?
• ADOdb (http://adodb.sourceforge.net/) • One of the Fastest Open Source Database Abstraction
Library for PHP (and Python). • Licensed Using BSD and LGPL. • Providing Portability Over a Variety of Databases Such
as MySQL, PostgreSQL, Interbase, Firebird, Informix, Oracle, MS SQL, Access, DB2, etc.
• Full PHP5 Support
24
Discover – Sample code using ADOdb
• ADOdb Database Connection$dbdriver = ‘informix’;
$db = ADONewConnection($dbdriver);
$db->PConnect($server, $user, $password, $database);
25
Jordan Bruce
Session I07
Case Study: Major Canadian Retail Drugstore’s Prescription For Success