Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Input Table of MySQL taking too much time.

  1. #1

    Default Input Table of MySQL taking too much time.

    My MySQL input table has 2 left joins having 250 Millions records on 230 Millions and 250 M on 36 M.
    I am already using index for the all the joins.
    And on this input I am using one database lookup with input 110 M records.
    And I am getting this error just after 45 min of the start of the job:
    org.pentaho.di.core.exception.KettleDatabaseException:Couldn't get row from result set
    Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
    at org.pentaho.di.core.database.Database.getRow(Database.java:2546)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2516)
    at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:148)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    I have already raised the net_write_timeout to 259200 seconds(3 days).

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    This doesn't look like a Kettle problem to me.
    So long, and thanks for all the fish.

  3. #3

    Default

    The thing is previously I was using stream lookup instead of database lookup and after the loading of the data from main source, the lookup fails as it is not getting the data from Mysql Input Table.
    Basically, the lookup is failing as it has to wait for the Input table for a longer time.
    Last edited by sanjay_singh; 10-23-2017 at 05:20 AM.

  4. #4

    Default

    I have one more question regarding lookup stream. After taking the full scan of the lookup table, for how long will it wait for the input stream?

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Not sure if I understand at all.
    We use a lookup step if the lookup keys represent a small set.
    You say your lookup table has 110 million rows - that's hardly a small set.
    I would use a Merge-Join in such a situation.

  6. #6

    Default

    I have one more doubt. Can I use 2-3 consecutive stream lookups into an output table?

  7. #7
    Join Date
    Aug 2016
    Posts
    290

    Default

    Quote Originally Posted by sanjay_singh View Post
    I have one more doubt. Can I use 2-3 consecutive stream lookups into an output table?
    No, you can only use stream lookup into a stream. What you do with that stream (table output or other) is irrelevant. And you can have any number of consecutive stream lookups. It's not an input step so they can stack.

Tags for this Thread

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.