I07_Jordan - International Informix Users Group

candlewhynotData Management

Jan 31, 2013 (4 years and 6 months ago)

242 views

Jordan Bruce

I07


Open Source and IDS

Major Canadian Retail Drugstore’s
Prescription For Success

Tuesday 3
rd

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 #
Elapsed Time
IDS 9.30
IDS 10.00
12

Query

Performance ‘ The Data’

#
Test Case
IDS 9.30
IDS 10.00
% Gain
1
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
Elapsed Time (ms)
Before
After
14

OS Performance

Unixware vs. Linux
0
5000
10000
15000
20000
G3 + UW7.1.2
G3 + UW7.1.4
G4 + UW7.1.2
G4 + Linux
stack
Server / OS
Score (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

jbruce@hotmail.com

Session I
07

Case Study: Major Canadian Retail
Drugstore’s Prescription For Success