Informatica

PowerCenter® 7 Architecture and Performance Tuning
Erwin Dral
Sales Consultant

1

Agenda
° PowerCenter Architecture ° Performance tuning step-by-step ° Eliminating Common bottlenecks

2

PowerCenter Architecture: Engine-based & Metadata-driven
Client Tools
Windows Metadata Exchange
Erwin Designer 2000 Power Designer Heterogeneous CWM ODBC

Workflow Manager

Workflow MonitorRepository Manager

Designer
ODBC

Metadata Reporter

TCP/IP JDBC Heterogeneous Targets Oracle API, SQL*Loader MS SQL Server, BCP Sybase, IQ Load Informix DB2 UDB, Autoloader Teradata fload, tpump, fload, tpump MainFrame , mpumpERP ODBC SAS Flat File RealTime XML Remote Files PowerConnect PowerCenter Server Engine Buffers

Sources
Oracle MS SQL Server Sybase Informix DB2 UDB ODBC FlatFile XML MainFrame VSAM/COBOL ERP Copybook

ODBC

Repository Server Repository Agent Native

Sources Metadata Repository
TCP/IP

Targets

SAS RealTime Remote Files

Native ODBC

GDR

Native ODBC

PowerConnect

UNIX, Windows
Key Data Metadata

Reader

DTM

Writer

3

Introducing PowerExchange
On-Demand Data Access through Changed Data Capture
MainframeReal-time
AS/400, HP3000

Change
Relational

Batch
File Formats, EAI

Change

Bulk

4

PowerCenter Environment
Disk Disk Disk Disk Disk Disk Disk
PowerCenter

Disk DBMS OS Disk Disk Disk Disk Disk Disk

LAN/WAN

° ° °

This is a multi-vendor, multi-system environment There are many components involved − Operating systems, databases, networks, I/O, PowerCenter Performance isdetermined by THE SLOWEST COMPONENT (the bottleneck) − Usually need to monitor performance in several places − Usually need to monitor outside PowerCenter

5

Server Architecture - Memory
° The PowerCenter Server utilizes two main processes
− Load Manager process (pmserver) − Session process (DTM)

° The Load Manager process is a continuous listener process designed to handle tasks such assession start, scheduling, error reporting, email, etc.
− Configured using the using the Load Manager Shared Memory

parameter

− Set value to approximately 200K bytes per session multiplied by

the max number of concurrent sessions

6

Server Architecture - Memory
° The DTM process uses shared memory to handle tasks such as reading, data transformation and writing ° Two sessionparameters control the DTM memory allocation
− DTM Buffer Pool Size − Buffer Block Size

° DTM pipeline threads overlap when possible

Reader

Transformation Engine

Writer

7

Server memory runtime
° Example

8

Server Architecture - Memory
° DTM Buffer Pool Size controls the total amount of memory used to buffer rows internally by the reader and writer − This sets the total numberof blocks available − The optimal value is about 25MB − If the block size is 64K, then you get 25M/64K = 390 blocks ° Buffer Block Size controls the size of the blocks that move in the pipeline − Optimum size depends on the row size being processed − 64KB ≈ 64 rows of 1KB − 128KB ≈ 128 rows of 1KB

9

Server Architecture – DTM Parameters
The Session Task parameters control the processingpipeline and are found on the Properties and Config Object tabs

10

Server Architecture - Threads
Assume a mapping with an Aggregator, a Rank, and other transformations in a session with two partitions. Pre and Post session commands would add one thread each. Load Manager

DTM Master Thread Mapping Thread

Transformation Transformation Thread Thread Rank Threads

Reader Thread ReaderThread Thread Writer Thread Writer Thread Thread

Transformation Thread Transformation Thread

Transformation Thread Transformation Thread Aggregator Threads

Process Memory

11

Performance tuning step-by-step
1. Determine Batch window 2. Measure Until elapsed time < batch window

HINTS:

5. Run sessions 4. Make ONE change

3. Determine bottleneck

•Write down a log of every...