Dp agent keeps going down when ever we run a query on Virtual table. How can we resolve this?

[srujan_gannamaneni]

Hi Everyone

SDI Dp agent keeps going down when ever we run a query on Virtual table which is pointed to a table with 250 Million records. How can we resolve this?

Adapter used: Camel JDBC Adapter

Source system: MySQL ( No time out limit set at server level)

Thanks and Regards

Srujan

[fedaros]

Hi Srujan,

More info needed.

Do you have additional information from logs ?

Which kind of SELECT are you perform SELECT * without WHERE ? a SELECT with key provokes same behavior ?
Others SQL/tables on same server works fine ?

Regards, Fernando Da Rós

[srujan_gannamaneni]

Hi Fernando

It was a count(field) query with filter on date field filtering for past 3 years. This table is having quite many rec compared to other tables. Tables having 2-10 mill are returning o/p for similar queries.

Also one major issue, I observed is timestamp columns. Timestamp in MySQL seems to allow 0000-00-00 00:00:00 but I don't think HANA accepts this as far as I know. But this can be handled by creating a view at SQL db level by creating a view for sure but is there any other way. I know this point is not related to Agent issue. But any suggestion on this would help me knock out one of the issues I am facing.

Thanks and Regards

Srujan Gannamaneni

[yogananda]

srujan.gannamaneni

1. Check the polling interval of the Camel JDBC adapter. If it is too low, increase the polling interval to reduce the load on the system.

2. Check if the query is optimized to return only the needed data.

3. Increase the memory allocated to the Dp Agent.

4. If possible, try running the query as an asynchronous job with a scheduled task and allow it to run in the background.

5. Check the network latency and make sure it is optimized.

6. Check the system load and make sure it is not too high.

[srujan_gannamaneni]

Thank you Yogi

I highlighted Basis to check few properties like framework fetch sizes, jdbc timeout and jdbc communication time out as seemed quite low compared to the time query was taking through MySQL workbench.

For point 2, it was count(field) with filter on date for past three years.

3. I will definitely request for more memory allocation. Can you suggest where can that be done? I never tried it myself 😅

5. This is definitely one of my prob. DPagent is not sitting in the network of MySQL server. It is sitting on some other location where they seem to have opened ports for communication. And I don't know how much I can persuade on changing that. But will definitely try again.

6. System load was low when we tried. It is dpagent which is going down. Source and target are performing normal

[jmuiruri]

Greetings srujan.gannamaneni,

I would recommend you check the dpagent logs, the clues as to why this happened should be present there.

Best Regards,

Joseph

[srujan_gannamaneni]

Thank you Joseph

Let me do this first thing Monday and see if I can find something concrete there.