The Second stress test involves, Transfering 6 millions of records from a Flat File, to a table in MySQL.
The first surprise of the test was that it had no drivers for mysql: ODI does not provide any default JDBC connector (in-built). Though MySQL is shown up as one of the technologies in Topology Manager, but when user tries to establish a physical connection, there is no pre-defined connector available in ODI.
1) You must download the driver from the official website.
2) Place this JAR file in the following 2 locations:
a. %ODI_HOME%\oracledi\agent\drivers. %ODI_HOME% is the location of complete installation directory for ODI under Oracle\MiddleWare.
b. <UserProfile>\Application Data\odi\oracledi\userlib.
Once past the hurdle of not having drivers for mysql. After downloading the jdbc from the MySQL site, and add it as a driver available. The results of this test are not satisfactory yet. It's working ok with sample files (same metadata but much lower amounts). I tried modifying the "Staging Area". But the transfer rate begins to fall and becomes endless. In small quantities, up to about 10,000 records, is impacted perfectly in the database. But terrible times.
It is clear that resources are minimal memory consumption is large, do not use ODBC (this is a plus, but that did not yield good results).
However, the case remains open, for recommendations on possible improvements. I am anxiously waiting for the run on the same job with 8 GB of RAM and multiple cores available for testing.
|Objective:||To measure elapsed time reading and writing 6 million rows, from Flat file, to a MySQL database. The DB run in other machine. The benefit es "Clear", only ODI run alone in the virtual machine. The negative point is the transfer of data through the network, using the generic drivers installed by VM.|
|Resources:||Virtual machine with: 2 GB RAM, ODI like main process over the virtual plataform. The resources used are anecdotal, today, Any production environment has enough processing power for current and future requirements. The objective here, is to build, to execute and to measure with the same environment (regardless of the limited resources)|
|Design & Run||
|Elapsed time (s)||
|Rows per sec (avg)||