Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: performance issue with DB to DB data transport

  1. #1
    Join Date
    Nov 2008
    Posts
    13

    Default performance issue with DB to DB data transport

    There is a very simple data transport from one database to another. This is to check on the performance tuning required to implement our integration interfaces. The test was performed w/o any transformations. This simple table to table copy from database A to database B for a table with 4.3 million records takes around 6 minutes of time.

    I tried with multiple copies on target table step but there is no improvement in the time. The sum of throughput on those multiple threads is same as single thread (12K per sec).
    The commit size is 20K, rows in rowset if 20K, number of copies to start on target is 6. Any higher value in the rowset/commit size gives JVM memory error. Increased the memory setup in spoon.bat to OPT=-Xmx1024m. Gives spoon startup error if this value is set to > 1024.

    The machine has 128GB memory with 16 processors, so there is no issue on the hardware. I have been asked to make this transform as fast as it could.

    Please suggest any more configurations possible to improve this performance time. I tried creating 2 table inputs with different WHERE clause to select diff set of records from same table and this helped. But is there any way create multiple threads on table input? and harness the power of the hardware to make this transport faster?

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    What's the database? What JVM are you using (32-bit, 64-bit)?
    Not that it matters that much, the operation you describe uses next to no memory, 32MB would be sufficient I guess.

    11k rows/s is not that unusual so you might be reaching the limits of the target database.
    Bulk loading might help actually. I've reached around 40k rows/s on my laptop with PostgreSQL for example.

    Parallel loading only helps if your database can cope with it. Oracle for example is lousy at it, causing deadlock errors all over the place, especially if you have indexes on the table.
    That brings me to the next subject: see if you have any indexes on the table during the load. Depending on the size of the table and the size of the load you might want to drop the index(es) and re-create it afterwards.
    etc, etc.

    Matt

  3. #3
    Join Date
    Nov 2008
    Posts
    13

    Default

    We are using Oracle DB, Windows server 2003 machine with 64 bit, Java version 1.6 but not sure about JVM 32/64. Java was installed by someone else in our team. Task mgr shows "javaw.exe *32" consuming 990MB. Does this mean it is 32 bit?

    I tried with the bulk loader transform, but it helped a little, giving through of 14K per sec with 100K rowset and 100K commit.

    There are indexes on the table and I will try the load again after dropping those indexes.

    I am using JDBC native driver for DB connection. Could not use OCI due to error (no ocijdbc10 in java.library.path).
    Is there any performance improvement possible using OCI driver?

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    No, performance is usually a bit lower with OCI. It needs to go through another layer.

    Increasing the rowset size usually has a negative impact on performance since it lowers parallelism in the transformation.
    Setting the commit / batch size to such a high number is also not guaranteed to work faster. When running with parallel Table Outputs, try lowering that value as well.

    OCI: you need the same JDBC driver version from Oracle as the Net8 client. (see the docs)

    Another option wrt to the indexes: load it in another table without indexes in multiple threads (an index is an item reducing parallelism) and launch an "insert into targetTable select * from tempTable" afterwards.

    Take care,
    Matt

  5. #5
    DEinspanjer Guest

    Default

    First, listen to Matt, he knows more about Oracle and Kettle than I could ever hope to.

    I was thinking that you might be able to improve your throughput using data partitioning. I think the success of that would depend greatly on whether Oracle could handle parallel load streams by doing some sort of segment locking instead of locking the whole table.

    Attached is a very simple example I wrote that partitions the data by three known ID values and uses those partition IDs in the where clause to allow three streams to run in parallel. If you didn't know the partition IDs before hand, I don't know if you could work up some way to make this partitioning example dynamic.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2008
    Posts
    13

    Default

    Index removal did not help.
    Is there any way to improve the table reader?

    I tried creating 3 different table inputs with same table and manipulating the WHERE clause to get different set of records. All 3 inputs are going to the same output table. This imrpoved the peformance time.

    I think partitioning should help on the similar lines. But I am looking for any config on the input reader. Setting multiple copies of table input (change number of copied to start) is not helping here but was useful in case of flat file input. Could this option work with table input?

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Interesting idea you have there Daniel: using the same partitioning system for the Oracle target table as you do in Kettle.
    Combine that with Local Oracle indexes (CREATE LOCAL INDEX IDX_FOO_BAR ON FOO(BAR) etc) and you should in theory scale the thing.
    The inserter threads should in theory work completely in parallel in that case and Oracle should also scale that way.

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Oracle is typically bloody fast in reading data, I've seen it put out 150k rows/s for a single Table Input step.

    Well to answer your question: perhaps you can simply try to partition manually to test the performance difference:

    SELECT * FROM FOO where mod(id,2)=0

    and

    SELECT * FROM FOO where mod(id,2)=1

    That would do a full table scan on the source system though, I'm not sure this is the best way.

    Mmm, how about this : calculate the min(id) and a max(id) so you know the range.
    Then split the difference and run in 2 or 3 queries:

    SELECT * FROM FOO where id>=0 and id<100
    SELECT * FROM FOO where id>=100 and id<200
    etc.

    Depending on the success of the tests I'm sure you could automate that. (jobs, result rows, etc, etc)

  9. #9
    Join Date
    Nov 2008
    Posts
    13

    Default

    Manual partitioning has helped, 3 table input reads on the same table with ID checks as follows:
    1) SELECT
    * FROM TAB_A WHERE ID <= 'S'
    2) SELECT * FROM TAB_A WHERE ID > 'S'and ID < 'T'

    3) SELECT * FROM TAB_A WHERE ID >= 'T'

    All 3 inputs are writing to the same output table. But the performance time on this case was around 3 min (50% improvement).


  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Interesting. That might actually be caused by a limitation on the source system.
    I know that Oracle uses a single thread to handle a single request.
    So now you're probably using multiple threads over there.

  11. #11
    Join Date
    Nov 2008
    Posts
    13

    Default

    Let me correct the record, the 3 table input with same target table in single transform took 4.7 minutes. But the job with 3 different transforms running in parallel (each transform has input and out table with specific WHERE clause) took 2.8 minutes.

  12. #12
    DEinspanjer Guest

    Default

    Were you able to apply my data partitioning example attached above to your case? If you see improved performance with three parallel transforms then it should help you simplify that case.

  13. #13
    Join Date
    Nov 2008
    Posts
    13

    Default

    I wanted to try yhe partitioning approach but unable to define the partitions with the WHERE clause.
    SELECT * FROM FOO where id>=0 and id<100
    SELECT * FROM FOO where id>=100 and id<200

    Could I use partitioning method as MOD Partitional? It is accepting just the field name? Could you please point to some link showing steps required to setup these partitions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.