Timeout and Connection Issues in ADF with Azure Managed Postgres: Npgsql Exception

Question

I’m currently engaged with ADF and Azure Managed Postgres, and I’ve been encountering a persistent issue where look-ups and query-sourced copy activities consistently time out around the 35-second mark.

Failures happened on 'Source' side. 'Type=Npgsql.NpgsqlException,Message=Exception while reading from stream,Source=Npgsql,''Type=System.IO.IOException,Message=Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.,Source=System,''Type=System.Net.Sockets.SocketException,Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond,Source=System,'

After reviewing the error message, which indicates a Npgsql Exception, I referred to Npgsql’s documentation and adjusted the connection string by setting the Timeout and CommandTimeout to 60 seconds. It’s worth noting that Internal Timeout defaults to CommandTimeout.

However, even with these changes, the queries still experience timeouts at approximately 35 seconds. I’m curious whether this might be attributed to a socket problem within the Azure Managed Instance, leading to the timeout issue that subsequently affects Npgsql. I would greatly appreciate any assistance or insights on this matter.

Answer 1

Hi ClarissaJacquline,

Thanks for using Microsoft Q&A.
The error message you are seeing indicates that the lookup and copy activities are timing out because they are unable to read data from the Azure Managed Postgres instance. This could be due to a number of factors, including:

• A network issue between the Azure Data Factory and the Azure Managed Postgres instance
• Timeout setting on the Azure Managed Postgres instance.
• A resource issue on the Azure Managed Postgres instance.

To troubleshoot the issue, you can try the following:

• You can try increasing the timeout value by adding Timeout=600;CommandTimeout=0 to the connection string in the linked service. Timeout parameter is used to establish the connection, while the CommandTimeout parameter is the timeout for the command itself (in seconds, 0 means infinity).
• Check the resource utilization of the Azure Managed Instance to ensure that it is not overloaded or experiencing high CPU usage. This can cause the queries to time out or fail. You can use Azure Monitor to monitor the resource utilization of the Azure Managed Instance and identify any performance issues.
• If the queries are complex or involve large amounts of data, you can try optimizing the queries or breaking them down into smaller to improve performance.
• Check if there are any firewall rules that could be blocking the connection between ADF and Azure Managed Postgres. Ensure that the firewall rules are correctly configured to allow the connection.

You can find more information about the other parameters and details that you can use in the Npgsql documentation

We hope that these steps help you resolve the issue. If you have any further questions or concerns, please don't hesitate to ask.