You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Mike Thomsen <mi...@gmail.com> on 2023/09/01 22:00:54 UTC

NiFi hanging during large sql query

I have a three node cluster with an executesqlrecord processor with primary execution only. The sql it runs is a straight forward select on a table with about 44m records. If I leave it running, after about 10 min the node becomes unresponsive and leaves the cluster. The query runs just fine in jetbrains data grip on that postgresql server, so I don’t think it’s anything weird with the db or query. Any ideas about what could be causing this? Even with a high limit like 5m records the query doesn’t lock up the NiFi node.

Sent from my iPhone

Re: NiFi hanging during large sql query

Posted by Mike Thomsen <mi...@gmail.com>.
I don't think so.

On Sat, Sep 2, 2023 at 11:36 AM Mark Payne <ma...@hotmail.com> wrote:

> Thanks for sharing the solution Mike. Is there something we need to update
> in nifi to prevent this from biting others?
>
> Thanks
> Mark
>
> Sent from my iPhone
>
> On Sep 2, 2023, at 9:48 AM, Joe Witt <jo...@gmail.com> wrote:
>
> 
> Nice.  Gald you found it.
>
> On Sat, Sep 2, 2023 at 5:07 AM Mike Thomsen <mi...@gmail.com>
> wrote:
>
>> It was the PostgreSQL JDBC driver. If you don't paginate the query
>> aggressively, it will try to load a significant chunk of the table into
>> memory rather than just pulling chunks, even with fetchSize set low.
>>
>> On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mi...@gmail.com>
>> wrote:
>>
>>> I have a three node cluster with an executesqlrecord processor with
>>> primary execution only. The sql it runs is a straight forward select on a
>>> table with about 44m records. If I leave it running, after about 10 min the
>>> node becomes unresponsive and leaves the cluster. The query runs just fine
>>> in jetbrains data grip on that postgresql server, so I don’t think it’s
>>> anything weird with the db or query. Any ideas about what could be causing
>>> this? Even with a high limit like 5m records the query doesn’t lock up the
>>> NiFi node.
>>>
>>> Sent from my iPhone
>>
>>

Re: NiFi hanging during large sql query

Posted by Matt Burgess <ma...@apache.org>.
When you said "fetchSize set low", I assume you mean non-zero, a zero
will fetch all the rows at once. How did you paginate your query with
ExecuteSQLRecord? I was going to suggest GenerateTableFetch in front
to paginate the queries for you, but it definitely seems like we
should be able to do or configure something that will make PG happy.

Regards,
Matt

On Sat, Sep 2, 2023 at 11:36 AM Mark Payne <ma...@hotmail.com> wrote:
>
> Thanks for sharing the solution Mike. Is there something we need to update in nifi to prevent this from biting others?
>
> Thanks
> Mark
>
> Sent from my iPhone
>
> On Sep 2, 2023, at 9:48 AM, Joe Witt <jo...@gmail.com> wrote:
>
> 
> Nice.  Gald you found it.
>
> On Sat, Sep 2, 2023 at 5:07 AM Mike Thomsen <mi...@gmail.com> wrote:
>>
>> It was the PostgreSQL JDBC driver. If you don't paginate the query aggressively, it will try to load a significant chunk of the table into memory rather than just pulling chunks, even with fetchSize set low.
>>
>> On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mi...@gmail.com> wrote:
>>>
>>> I have a three node cluster with an executesqlrecord processor with primary execution only. The sql it runs is a straight forward select on a table with about 44m records. If I leave it running, after about 10 min the node becomes unresponsive and leaves the cluster. The query runs just fine in jetbrains data grip on that postgresql server, so I don’t think it’s anything weird with the db or query. Any ideas about what could be causing this? Even with a high limit like 5m records the query doesn’t lock up the NiFi node.
>>>
>>> Sent from my iPhone

Re: NiFi hanging during large sql query

Posted by Mark Payne <ma...@hotmail.com>.
Thanks for sharing the solution Mike. Is there something we need to update in nifi to prevent this from biting others?

Thanks
Mark

Sent from my iPhone

On Sep 2, 2023, at 9:48 AM, Joe Witt <jo...@gmail.com> wrote:


Nice.  Gald you found it.

On Sat, Sep 2, 2023 at 5:07 AM Mike Thomsen <mi...@gmail.com>> wrote:
It was the PostgreSQL JDBC driver. If you don't paginate the query aggressively, it will try to load a significant chunk of the table into memory rather than just pulling chunks, even with fetchSize set low.

On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mi...@gmail.com>> wrote:
I have a three node cluster with an executesqlrecord processor with primary execution only. The sql it runs is a straight forward select on a table with about 44m records. If I leave it running, after about 10 min the node becomes unresponsive and leaves the cluster. The query runs just fine in jetbrains data grip on that postgresql server, so I don’t think it’s anything weird with the db or query. Any ideas about what could be causing this? Even with a high limit like 5m records the query doesn’t lock up the NiFi node.

Sent from my iPhone

Re: NiFi hanging during large sql query

Posted by Joe Witt <jo...@gmail.com>.
Nice.  Gald you found it.

On Sat, Sep 2, 2023 at 5:07 AM Mike Thomsen <mi...@gmail.com> wrote:

> It was the PostgreSQL JDBC driver. If you don't paginate the query
> aggressively, it will try to load a significant chunk of the table into
> memory rather than just pulling chunks, even with fetchSize set low.
>
> On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mi...@gmail.com>
> wrote:
>
>> I have a three node cluster with an executesqlrecord processor with
>> primary execution only. The sql it runs is a straight forward select on a
>> table with about 44m records. If I leave it running, after about 10 min the
>> node becomes unresponsive and leaves the cluster. The query runs just fine
>> in jetbrains data grip on that postgresql server, so I don’t think it’s
>> anything weird with the db or query. Any ideas about what could be causing
>> this? Even with a high limit like 5m records the query doesn’t lock up the
>> NiFi node.
>>
>> Sent from my iPhone
>
>

Re: NiFi hanging during large sql query

Posted by Mike Thomsen <mi...@gmail.com>.
It was the PostgreSQL JDBC driver. If you don't paginate the query
aggressively, it will try to load a significant chunk of the table into
memory rather than just pulling chunks, even with fetchSize set low.

On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mi...@gmail.com> wrote:

> I have a three node cluster with an executesqlrecord processor with
> primary execution only. The sql it runs is a straight forward select on a
> table with about 44m records. If I leave it running, after about 10 min the
> node becomes unresponsive and leaves the cluster. The query runs just fine
> in jetbrains data grip on that postgresql server, so I don’t think it’s
> anything weird with the db or query. Any ideas about what could be causing
> this? Even with a high limit like 5m records the query doesn’t lock up the
> NiFi node.
>
> Sent from my iPhone

Re: NiFi hanging during large sql query

Posted by Mike Thomsen <mi...@gmail.com>.
The resolution was to manually paginate the query statement with the
appropriate postgres syntax and set the fetchSize to 250 instead of 0,
which is unlimited.

On Mon, Sep 11, 2023 at 8:54 AM <e-...@gmx.fr> wrote:

>
> Hello Mike,
>
> Could you please give me the details about the resolution ?
> Have you change something in the processor or just changing the sql
> command ?
>
> Regards
>
> *Envoyé:* samedi 2 septembre 2023 à 00:00
> *De:* "Mike Thomsen" <mi...@gmail.com>
> *À:* users@nifi.apache.org
> *Objet:* NiFi hanging during large sql query
> I have a three node cluster with an executesqlrecord processor with
> primary execution only. The sql it runs is a straight forward select on a
> table with about 44m records. If I leave it running, after about 10 min the
> node becomes unresponsive and leaves the cluster. The query runs just fine
> in jetbrains data grip on that postgresql server, so I don’t think it’s
> anything weird with the db or query. Any ideas about what could be causing
> this? Even with a high limit like 5m records the query doesn’t lock up the
> NiFi node.
>
> Sent from my iPhone
>
>
>

Re: NiFi hanging during large sql query

Posted by e-...@gmx.fr.

Hello Mike,



Could you please give me the details about the resolution ?

Have you change something in the processor or just changing the sql command ?



Regards



**Envoy e:** samedi 2 septembre 2023 a 00:00  
**De:**  "Mike Thomsen" <mi...@gmail.com>  
**À :** users@nifi.apache.org  
**Objet:**  NiFi hanging during large sql query

I have a three node cluster with an executesqlrecord processor with primary
execution only. The sql it runs is a straight forward select on a table with
about 44m records. If I leave it running, after about 10 min the node becomes
unresponsive and leaves the cluster. The query runs just fine in jetbrains
data grip on that postgresql server, so I don't think it's anything weird with
the db or query. Any ideas about what could be causing this? Even with a high
limit like 5m records the query doesn't lock up the NiFi node.  
  
Sent from my iPhone