Re: Issues with ExecuteSQL and DBCPConnectionPool service in NiFi

[Paul Riddle <pa...@onyxpoint.com>]

Hello Boris, NiFi Team,

Thank you for the reply.  We do have a validation query, and the issue
persists.  What we are seeing is that we will have a successful query, and
then the connection will stay open to the database.  When the next query
goes through (beyond 5 minutes later), it will attempt to use that same
stale connection, and the NiFi thread will hang for 30-40 minutes.

We have a workaround modifying TCP keepalive settings for now, however, we
were wondering if there may be an additional setting in the
DBCPConnetionPoolService that we can add to guarantee that the connection
isn't kept open after the query completes.

As always, any assistance you can offer is greatly appreciated.

Regards,
Paul

On Fri, May 25, 2018 at 3:43 PM, Boris Tyukin <bo...@boristyukin.com> wrote:

> Hi Paul,
>
> have you tried to set validation query on the pool? something like SELECT
> 1 FROM dual
>
> I had a bunch of issues with our databases but after we started using
> validation query, all issues with disconnected sessions were gone.
>
> Give it a try if you have not done this yet
>
> On Fri, May 25, 2018 at 2:54 PM, Paul Riddle <pa...@onyxpoint.com>
> wrote:
>
>> NiFi Team,
>>
>> We are seeing issues in the DBCPConnectionPool service and ExecuteSQL
>> processor where threads are hanging from NiFi consistently.  The first set
>> of queries goes through without issue when the processor is initially
>> started.  After 5 minutes of inactivity, a second set of queries simply
>> hangs.
>>
>> Netstat on the server (netstat -nputwoc | grep 1521 ) shows that once we
>> restart the service,  the connection pool is cleared and the processor
>> continues to work as normal as long as there is activity within 5min ( that
>> consumes the connection pool) .
>>
>> After 5 min of inactive period the next attempt to make JDBC connection
>> uses the connection from the same old pool . It then appears that that pool
>> is not good to consume, and if used, causes threads to hang in NiFi
>> (consistently reproducible).
>>
>> There are no errors in the logs during this occurance, or bulletins
>> within NiFi.  The threads hang for over 30 minutes at that point.
>>
>>
>>
>> Other than default processor configs:
>> The max wait time on the processor is set to 30 seconds
>>
>>
>> Other than default controller service configs:
>> database driver- oracle.jdbc.driver.OracleDriver
>> max wait time- 500 ms
>> Max total connections- 8
>>
>> Any assistance you can provide is appreciated.
>>
>> Regards,
>> Paul
>>
>
>

Re: Issues with ExecuteSQL and DBCPConnectionPool service in NiFi

[Matt Burgess <ma...@apache.org>]

Paul,

We use Apache DBCP to pool the connections, and one of its major
features is to keep connections open for reuse, to avoid the overhead
of establishing new connections (and shutting them down) quickly. In
your case you are not creating/closing them quickly but rather every 5
mins. If they have gone idle or otherwise won't work, this is exactly
what the Validation Query is supposed to take care of, so I'm not sure
what's going on in your particular case. Since you are only querying
every 5 minutes, perhaps try a validation query that does actual work
(select * from myTable); that's usually not recommended but might work
in your case such that it would fail quickly and thus DBCP would get
you a new connection.

Alternatively, there are a number of properties you can set on the
data source (the managed DBCP object) [1] that we don't explicitly
expose as properties (such as "validationQueryTimeout"). You could try
setting those via user-defined properties on the DBCPConnectionPool.

Regards,
Matt

[1] https://commons.apache.org/proper/commons-dbcp/configuration.html

On Thu, Jun 28, 2018 at 9:11 AM Paul Riddle <pa...@onyxpoint.com> wrote:
>
> Hello NiFi Team,
>
> Can you provide any guidance on ExecuteSQL in combination with DBCPConnectionPool controller service not releasing connections to a remote database after a successful query?  Is this intended behavior, a mis-configuration on our end, or a bug?  We have a workaround as mentioned in the previous email to close TCP settings at the OS level, but were hoping that we could resolve this within the processor or controller service.  Any help you can provide is greatly appreciated.
>
> Regards,
> Paul
>
> On Fri, Jun 8, 2018 at 8:24 AM, Paul Riddle <pa...@onyxpoint.com> wrote:
>>
>> Hello Boris, NiFi Team,
>>
>> Thank you for the reply.  We do have a validation query, and the issue persists.  What we are seeing is that we will have a successful query, and then the connection will stay open to the database.  When the next query goes through (beyond 5 minutes later), it will attempt to use that same stale connection, and the NiFi thread will hang for 30-40 minutes.
>>
>> We have a workaround modifying TCP keepalive settings for now, however, we were wondering if there may be an additional setting in the DBCPConnetionPoolService that we can add to guarantee that the connection isn't kept open after the query completes.
>>
>> As always, any assistance you can offer is greatly appreciated.
>>
>> Regards,
>> Paul
>>
>> On Fri, May 25, 2018 at 3:43 PM, Boris Tyukin <bo...@boristyukin.com> wrote:
>>>
>>> Hi Paul,
>>>
>>> have you tried to set validation query on the pool? something like SELECT 1 FROM dual
>>>
>>> I had a bunch of issues with our databases but after we started using validation query, all issues with disconnected sessions were gone.
>>>
>>> Give it a try if you have not done this yet
>>>
>>> On Fri, May 25, 2018 at 2:54 PM, Paul Riddle <pa...@onyxpoint.com> wrote:
>>>>
>>>> NiFi Team,
>>>>
>>>> We are seeing issues in the DBCPConnectionPool service and ExecuteSQL processor where threads are hanging from NiFi consistently.  The first set of queries goes through without issue when the processor is initially started.  After 5 minutes of inactivity, a second set of queries simply hangs.
>>>>
>>>> Netstat on the server (netstat -nputwoc | grep 1521 ) shows that once we restart the service,  the connection pool is cleared and the processor continues to work as normal as long as there is activity within 5min ( that consumes the connection pool) .
>>>>
>>>> After 5 min of inactive period the next attempt to make JDBC connection uses the connection from the same old pool . It then appears that that pool is not good to consume, and if used, causes threads to hang in NiFi (consistently reproducible).
>>>>
>>>> There are no errors in the logs during this occurance, or bulletins within NiFi.  The threads hang for over 30 minutes at that point.
>>>>
>>>>
>>>>
>>>> Other than default processor configs:
>>>> The max wait time on the processor is set to 30 seconds
>>>>
>>>>
>>>> Other than default controller service configs:
>>>> database driver- oracle.jdbc.driver.OracleDriver
>>>> max wait time- 500 ms
>>>> Max total connections- 8
>>>>
>>>> Any assistance you can provide is appreciated.
>>>>
>>>> Regards,
>>>> Paul
>>>
>>>
>>
>

Re: Issues with ExecuteSQL and DBCPConnectionPool service in NiFi

[Paul Riddle <pa...@onyxpoint.com>]

Hello NiFi Team,

Can you provide any guidance on ExecuteSQL in combination with
DBCPConnectionPool controller service not releasing connections to a remote
database after a successful query?  Is this intended behavior, a
mis-configuration on our end, or a bug?  We have a workaround as mentioned
in the previous email to close TCP settings at the OS level, but were
hoping that we could resolve this within the processor or controller
service.  Any help you can provide is greatly appreciated.

Regards,
Paul

On Fri, Jun 8, 2018 at 8:24 AM, Paul Riddle <pa...@onyxpoint.com>
wrote:

> Hello Boris, NiFi Team,
>
> Thank you for the reply.  We do have a validation query, and the issue
> persists.  What we are seeing is that we will have a successful query, and
> then the connection will stay open to the database.  When the next query
> goes through (beyond 5 minutes later), it will attempt to use that same
> stale connection, and the NiFi thread will hang for 30-40 minutes.
>
> We have a workaround modifying TCP keepalive settings for now, however, we
> were wondering if there may be an additional setting in the
> DBCPConnetionPoolService that we can add to guarantee that the connection
> isn't kept open after the query completes.
>
> As always, any assistance you can offer is greatly appreciated.
>
> Regards,
> Paul
>
> On Fri, May 25, 2018 at 3:43 PM, Boris Tyukin <bo...@boristyukin.com>
> wrote:
>
>> Hi Paul,
>>
>> have you tried to set validation query on the pool? something like SELECT
>> 1 FROM dual
>>
>> I had a bunch of issues with our databases but after we started using
>> validation query, all issues with disconnected sessions were gone.
>>
>> Give it a try if you have not done this yet
>>
>> On Fri, May 25, 2018 at 2:54 PM, Paul Riddle <pa...@onyxpoint.com>
>> wrote:
>>
>>> NiFi Team,
>>>
>>> We are seeing issues in the DBCPConnectionPool service and ExecuteSQL
>>> processor where threads are hanging from NiFi consistently.  The first set
>>> of queries goes through without issue when the processor is initially
>>> started.  After 5 minutes of inactivity, a second set of queries simply
>>> hangs.
>>>
>>> Netstat on the server (netstat -nputwoc | grep 1521 ) shows that once we
>>> restart the service,  the connection pool is cleared and the processor
>>> continues to work as normal as long as there is activity within 5min ( that
>>> consumes the connection pool) .
>>>
>>> After 5 min of inactive period the next attempt to make JDBC connection
>>> uses the connection from the same old pool . It then appears that that pool
>>> is not good to consume, and if used, causes threads to hang in NiFi
>>> (consistently reproducible).
>>>
>>> There are no errors in the logs during this occurance, or bulletins
>>> within NiFi.  The threads hang for over 30 minutes at that point.
>>>
>>>
>>>
>>> Other than default processor configs:
>>> The max wait time on the processor is set to 30 seconds
>>>
>>>
>>> Other than default controller service configs:
>>> database driver- oracle.jdbc.driver.OracleDriver
>>> max wait time- 500 ms
>>> Max total connections- 8
>>>
>>> Any assistance you can provide is appreciated.
>>>
>>> Regards,
>>> Paul
>>>
>>
>>
>