Transcript I07_Jordan

I07 – Open Source and IDS
Major Canadian Retail Drugstore’s
Prescription For Success
Jordan Bruce
Tuesday 3rd October 2006 • 15:15 – 16:15
Major Canadian Retail Drugstore's
Prescription For Success
Migrating to IDS 10.00 on LINUX SuSe Sles9
2
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
3
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
4
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
5
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
6
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)
7
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
8
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
9
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
10
Query Performance
80
70
Elapsed Time
60
50
IDS 9.30
40
IDS 10.00
30
20
10
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Query #
49% improvement for queries
11
16
17
Query Performance ‘ The Data’
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Test Case
1.1 Load tx Table (without indices)
2.1 Query 1 (no index) - 1% selection
2.2 Query 2 (no index) - 10% selection
2.3 Query 3 (no index) - Join tx1,rx
2.4 Query 4 (no index) - Join B
2.5 Query 5 (no index) - Aggergate Function
2.6 Query 6 (no index) - Aggergate Function/Group By
2.5 Query 7 (no index) - Aggergate Function/Group By
5.1 - Create Indices
2.1 Query 1 (with index) - 1% selection
2.2 Query 2 (with index) - 10% selection
2.3 Query 3 (with index) - Join tx1,rx
2.4 Query 4 (with index) - Join B
2.5 Query 5 (with index) - Aggergate Function
2.6 Query 6 (with index) - Aggergate Function/Group By
2.5 Query 7 (with index) - Aggergate Function/Group By
3.1 Load tx Table (with index)
IDS 9.30
63.27
4.2
2.81
2.8
2.66
3.38
3.11
4.48
31.36
1.72
1.08
1.03
1.03
2.99
2.79
3.72
74.78
IDS 10.00
19.84
2.49
1.82
1.72
1.74
2.42
2.12
3.51
26.07
0.76
0.09
0.04
0.1
0.01
1.97
3.18
45.97
Overall
12
% Gain
69%
41%
35%
39%
35%
28%
32%
22%
17%
56%
92%
96%
90%
100%
29%
15%
39%
49%
Application Performance
1800
1600
1400
Elapsed Time (ms)
1200
1000
Before
After
800
600
400
200
0
1
3
5
7
9
11
13
15
17
19
21
23
25
27
29
31
33
35
Application Function
Roughly a ~62% Improvement for Application
13
OS Performance
Unixware vs. Linux
Score (ms)
20000
15000
10000
5000
0
G3 + UW7.1.2 G3 + UW7.1.4 G4 + UW7.1.2
G4 + Linux
stack
Server / OS
Roughly a ~39% performance gain
14
Ease of Administration
• External Optimizer Directives
• Reorganizations Made Simple By Renaming
DBSpace
• Single User Mode
15
Security
• DBCREATE_PERMISSION
• Secure Environment Check
16
Increased Stability
• Uninterrupted Service at all 114 sites across
Canada since implementation
17
Higher Availability
• Point-in-Time Table Restore
• FAST_RESTART_PHYSLOG and
FAST_RESTART_CKPT_FUZZYLOG
• Online Index Build
18
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
19
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
20
Discover – Store Search Screenshot
21
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
22
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
23
Discover – Sample code using ADOdb
• ADOdb Database Connection
$dbdriver = ‘informix’;
$db = ADONewConnection($dbdriver);
$db->PConnect($server, $user, $password,
$database);
24
Session I07
Case Study: Major Canadian Retail
Drugstore’s Prescription For Success
Jordan Bruce
[email protected]
25