|
This stress test involves, Transfer 6 millions of records from a Flat File, to a table in ORACLE. |
|
It is noticeable the performance achieved by using a generic plugin (which connection is via ODBC) compared to a proprietary plugin. In test 2 for DS vs. MySQL(IBM DS 8.1) Stress Test 2: Flat to MySQL (RELOAD), even with the new architecture implemented, times were around 10 minutes loading the data. After several runs, this test achieved an excellent time of 1.2 minutes. (The chart below shows the times obtained from the "relationship" between ETL tool and data repositories).
TIMES:
In order to improve the times successive touches were done in the Commit Size in DS up to the point, where the variation, generates no changes in the performance.
APT_ORAUPSERT_COMMIT_ROW_INTERVAL -APT_ORAUPSERT_COMMIT_TIME_INTERVAL. These two environment variables work together to specify how oftentarget rows are committed to write to Oracle. Commits are made whenever the time interval period has passed or therow interval is reached, whichever comes first. By default, commits aremade every 2 seconds or 5000 rows.
Another change that was analyzed is thetable partitioning by one of the fields, in which dispersion is similar.
|
ARCHITECTURE: Environment: Infraestructure composed of 3 nodes 1) ESXi 5.0: a) Physical Datastore 1: VMETL DS(10GB RAM - 2 Cores * 2 Sockets) b) Physical Datastore 2: VM Database ServerOracle (6GB RAM - 2 Cores * 2 Sockets) 2) Monitor Performance: VM Monitor ESXi + SQL Server 2008 (with 4 GB RAM) 3) Operator ETL: ESXi Client (with 4 GB RAM) |
 |
CASE 1: COMMIT SIZE 25.000 Rows
| Objective: |
- To measure elapsed time reading and writing 6 million rows, from Flat file, to Oracle Database. - Analyze use of the resources. |
| ETL Tool | IBM Datastage 8.1 |
| Rows: | 6.024.000 M |
| Columns: | 37Columns |
|
Structure: (Metadata) |
|
| Design & Run |
|
| LOG |
Log:
|
| Elapsed time(s) | 120Secs |
| Rows p/s(avg) |
50.200 r/s avg |
|
How to Improve Performance |
-Adjust the parameters: - COMMIT Size |
USE OF RESOURCES:VM IBM DATASTAGE 8.1
| TOTAL |
Important:MemorySwap: 0 CPU/Datastore:CPU Usage Mhz / Datastore usage between 15:12-15:15 Menmory:After several executions, the memory consumption remains stable in 6,8 GB
|
| CPU: |
CPU Monitoring, "Passive and Active state" in different executions.Last Execution:15:12-15:15
|
| Memory: |
Memory Monitoring:Last Execution:15:12-15:15
|
| Network |
Network Monitoring:Last Execution:15:12-15:15
|
| DataST |
|
