1
|
- Bill Bartlett
- Software Analytics, Inc.
|
2
|
- Will review Party City’s “Intranet & Enterprise Reporting” system
- Oracle9i Application Server (9iAS)
- Portal, Reports Server, custom portlets
- Network and infrastructure
- 24x7 operation
- In production since September 2001
- Used by corporate, stores and vendors
|
3
|
- Overview
- Business & Technical Needs
- Solution
- Infrastructure
- Custom Application
- Monitoring
- Lessons Learned
- Future
|
4
|
- Audience Background
- DBMS: 8i or 9i?
- Using 9iAS?
- Using 9iAS Release 1 or Release 2?
- Using Portal?
- Using Reports Server?
- Master/slave or single server?
|
5
|
- America’s largest party goods chain
- Today: > 470 Stores (235 Company-owned stores, 240 franchise stores
in United States and Puerto Rico)
- Expected annual growth: 25-30 corporate stores, 25+ franchise stores
- Emphasis on technology to promote efficiency
|
6
|
- Full-service Solution Provider
|
7
|
- Secure
- Also: granular, flexible, dynamic security
- Scalable
- Robust / Reliable
- Access to real-time data
- Reports: On demand, scheduled, event-based
- Easy to use / Minimal training required
- Easy distribution of company information
|
8
|
- Database size & activity
- Currently using same databases for OLTP and reports
- Database used for price lookups in stores, so cannot impact database
performance [much]
- Need to access multiple databases
- Limited WAN bandwidth to stores
|
9
|
- Databases:
- Oracle 8i (8.1.7.1.0)
- 64 bit AIX version
- 750 GB primary database on a 12 CPU RS/6000 with 24 GB RAM
|
10
|
- 9iAS Release 1, Reports 6i (for now…)
- Production environment:
- 6 server master/slave report server cluster
- 2 node master report server cluster (Windows 2000 Advanced Server +
Oracle Fail Safe)
- 4 report slave servers
- 4 server web farm, load balanced via content switch (Windows 2000
Servers)
- Redundant & distributed connections
- QA environment identical to production
|
11
|
|
12
|
|
13
|
|
14
|
- Web application
- Currently 9iAS Release 1
- Originally 9iAS v1.0.2.1 / Portal 3.0.8 / Reports6i patch 4
- Several interim updates
- Now 9iAS v1.0.2.2.2 / Portal 3.0.9 / Reports6i patch 11
- 9iAS Release 2 in 1Q2003 ?
|
15
|
- 9iAS components: Portal, SSO, Reports
- 98% custom code (9iAS PDK / JPDK)
- Portlet providers: both database and web
- PL/SQL procedures & packages
- PSP (PL/SQL Pages)
- Java: JSP, Servlets, EJB
- HTML, DHTML, JavaScript, XML, XMLHTTP
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
|
24
|
|
25
|
|
26
|
|
27
|
|
28
|
|
29
|
- Tools:
- WhatsUp Gold (www.ipswitch.com)
- Compaq Insight Manager
- CA Unicenter
- Oracle Enterprise Manager
- Custom application pages
- Screens show different information based on security
- Primarily JSP / servlets / EJB wrapped around Windows Resource Kit
utilities or HttpURLConnection’s
- Accessed both from inside application and from other monitoring tools
and sites
|
30
|
|
31
|
|
32
|
- 9iAS Overall
- Reports
- Report Server
- Apache
- Environment
- Disaster Recovery
|
33
|
- API provides TREMENDOUS amount of flexibility, but you’ll need to go
exploring
- “API spelunking”
- Documented API’s
- Semi-documented API’s (OK to use?)
- Private API’s: YGYBYF (use only as a last resort)
- JPDK doesn’t have everything yet; may need to call the PL/SQL API’s
|
34
|
- Extract all data up front (via functions)
- Do NOT go back to database on each line
- Line-by-line can crush your DBMS
- Difficult to detect
- Database sessions appear to be inactive
- Report servers show low activity
- Rapidly refresh database session information to see tiny but
frequently repeated activity
- Use packet sniffer (Network Monitor, Sniffer, etc.) to monitor the
network
|
35
|
- Load test each report
- If you don’t do this, your users will !
- Assume the worst-case scenario: All available report engines will
simultaneously run this report with its worst-possible criteria
- 17 Pentium III-based report engines can easily overwhelm a 12 CPU, 64
bit RS/6000
- However, report server will ensure that all reports in the queue do
eventually get processed
|
36
|
- Upgrade to Reports Patch 11
- Security & scalability fixes added post Patch 10, especially in
integration of Reports & Content Areas
- Determine the proper number of report engines
- Too few: “run immediately” reports will fail
- Too many: Can overwhelm the database server
- Changing number of report engines may require restarting the report
cluster
|
37
|
- Need multiple ways to look at report queue
- Might want users or helpdesk to look at queue
- May be times when one method is inaccessible
- Queue Manager application is the best way
- RWCGI60 output is almost as good
- RW_SERVER_QUEUE is not always current (but it’s good enough for most
users)
- Worst case: Can use the Report Server log file
|
38
|
- RepServer.ORA: traceoption=portalpush|all
- Useful when diagnosing Reports / Portal interaction problems
- Don’t kill a report from the database until it has first been killed
from the report server
- Otherwise this will crash the engine that was running the report,
sometimes in bad ways
|
39
|
- Monitor for cluster failovers
- If slave cannot start properly, the master will detect the problem, and
fail the cluster over. The new master will attempt to restart, fail,
and fail back to the original node, and so on.
- Not a problem if slave is offline, only if online but unable to start
report service
- False alerts are sometimes generated if an engine on the master crashes
|
40
|
- Upgrade to 9iAS v1.0.2.2.2 !
- Increase all “timeout” and “retry” settings
- JServ.conf: ApJServRetryAttempts 30, ApJServVMTimeout 20
- zone.properties: servlet.page.initArgs=requesttime=60
- Change JServ port to 8008
- JServ.conf: ApJServDefaultPort 8008
- jserv.properties: port=8008
- Monitor servlet service; restart web server if this goes down
- Much better in v1.0.2.2.2
|
41
|
- Have identical environments in development, QA and production
- Same report server configuration: cluster/single
- Same load balancing method
- Similar database size & content
- If only single report server in development, cannot test clustering,
Fail Safe, failover, report job preservation, etc.
|
42
|
- With proper preparation, this is easy
- Document all cluster details (especially disk signatures)
- Use dumpcfg.exe to get the disk signature
- Clustering solutions for lab
- “Poor Man’s Clustering”
- www.SoftwareAnalytics.com for details
|
43
|
- TracePlus Web Detective (www.sstinc.com)
- Sniffer (www.nai.com)
- Or Windows Network Monitor
- VMWare GSX Server (www.vmware.com)
- DBArtisan (www.embarcadero.com)
- Or any favorite client tool that can rapidly refresh session
information
- Sysinternals tools (www.sysinternals.com)
- Windows Resource Kit
|
44
|
- 9iAS Release 2
- Portal 9.0.2
- Reports 9i
- Forms 9i ?
- Expand use of content areas (WebDAV, workflow, greatly expanded
content)
- Additional “Application” portlets
- Quick Entry Department Inventory, QE Purchase Order
- OEM !
|
45
|
- In production since 9/2001
- Has become “business-critical”
- 1400 users
- 116 reports available (in several variations)
- 190,000 Reports run in the past 11 months
- Report size: 1 – 71,000 pages
|
46
|
- Vendor access to reports
- On-line Training Documentation
- Centralized document management
- Reduce hard-to-find network-stored documents
- Eliminating MS Exchange Folder storage
- Eliminating daily & weekly emails
|
47
|
|
48
|
- Bill Bartlett
bbartlett@SoftwareAnalytics.com
- Software Analytics, Inc.
http://www.SoftwareAnalytics.com
|