You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by e-...@gmx.fr on 2024/01/04 13:26:18 UTC
Hardware requirement for NIFI instance
Hello all,
Who could help me to determine the cpu/memory need for nifi instance to fetch
the data from Postgresql hosted in google ?
We got this error :
==> Error : executesql.error.message
**Ran out of memory retrieving query results.**
The procesor ExecuteSQL has this config : Set Auto Commit ==> false
driver Jar to use : postgresql-42.7.1.jar
Java version : jdk-11.0.19
**Table information :**
rows number : 14958836
fields number : 20
Linux Rocky8
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
**CPU(s): 2**
On-line CPU(s) list: 0,1
Thread(s) per core: 2
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
BIOS Vendor ID: Google
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
Stepping: 7
CPU MHz: 2800.286
BogoMIPS: 5600.57
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 33792K
NUMA node0 CPU(s): 0,1
**Memory : 8GB**
Thanks for you helps
Minh
RE: Hardware requirement for NIFI instance
Posted by "stephen.hindmarch.bt.com via users" <us...@nifi.apache.org>.
Minh,
As you are pulling data from the database, and you have no control of how many rows there might be in your result set, I would look at paging the data out so that you can control the maximum size of the result set held in memory.
Looking at the ExecuteSQL processor I would say that "Fetch Size" and "Max Rows Per Flow File" are the properties you should be dealing with. Try setting them to a reasonable batch size, like 1000 or 10,000 at first, and then see how that performs.
Steve Hindmarch
From: Isha Lamboo <is...@virtualsciences.nl>
Sent: Thursday, January 4, 2024 3:30 PM
To: users@nifi.apache.org
Subject: RE: Hardware requirement for NIFI instance
You don't often get email from isha.lamboo@virtualsciences.nl<ma...@virtualsciences.nl>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
We've had this occur when executing complex queries and/or queries on large tables in ExecuteSQL.
We typically try out some values of Max Rows Per Flow File and Fetch Size (both to the same value) in the range of 1000, 10k, 50k, 100k to make it work without memory issues. Changing the Output Batch Size value may also be needed, but I looked up one example from recent memory and it still had the default of 0. I'd try a value of 1 to test, but the other settings are likely the main cause.
Regards,
Isha
Van: Etienne Jouvin <la...@gmail.com>>
Verzonden: donderdag 4 januari 2024 14:48
Aan: users@nifi.apache.org<ma...@nifi.apache.org>
Onderwerp: Re: Hardware requirement for NIFI instance
You don't often get email from lapinoujoujou@gmail.com<ma...@gmail.com>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Hello.
I also think the problem is more about the processor, I guess ExecuteSQL.
Should play with batch configuration and commit flag to commit intermediate FlowFile.
The out of memory exception makes me believe the full table is retrieved, and if it is huge the FlowFile content is very large.
Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com>> a écrit :
It should be memory efficient so I think this is likely a configuration aspect of your processor. Can you share the configuration for all properties?
As a side note: if NiFi ran out of memory, you'd always want to restart it because you are never sure what's the state of the JVM after an OOME.
Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr>> a écrit :
Hello all,
Who could help me to determine the cpu/memory need for nifi instance to fetch the data from Postgresql hosted in google ?
We got this error :
==> Error : executesql.error.message
Ran out of memory retrieving query results.
The procesor ExecuteSQL has this config : Set Auto Commit ==> false
driver Jar to use : postgresql-42.7.1.jar
Java version : jdk-11.0.19
Table information :
rows number : 14958836
fields number : 20
Linux Rocky8
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 2
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
BIOS Vendor ID: Google
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
Stepping: 7
CPU MHz: 2800.286
BogoMIPS: 5600.57
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 33792K
NUMA node0 CPU(s): 0,1
Memory : 8GB
Thanks for you helps
Minh
RE: Hardware requirement for NIFI instance
Posted by Isha Lamboo <is...@virtualsciences.nl>.
We've had this occur when executing complex queries and/or queries on large tables in ExecuteSQL.
We typically try out some values of Max Rows Per Flow File and Fetch Size (both to the same value) in the range of 1000, 10k, 50k, 100k to make it work without memory issues. Changing the Output Batch Size value may also be needed, but I looked up one example from recent memory and it still had the default of 0. I'd try a value of 1 to test, but the other settings are likely the main cause.
Regards,
Isha
Van: Etienne Jouvin <la...@gmail.com>
Verzonden: donderdag 4 januari 2024 14:48
Aan: users@nifi.apache.org
Onderwerp: Re: Hardware requirement for NIFI instance
You don't often get email from lapinoujoujou@gmail.com<ma...@gmail.com>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Hello.
I also think the problem is more about the processor, I guess ExecuteSQL.
Should play with batch configuration and commit flag to commit intermediate FlowFile.
The out of memory exception makes me believe the full table is retrieved, and if it is huge the FlowFile content is very large.
Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com>> a écrit :
It should be memory efficient so I think this is likely a configuration aspect of your processor. Can you share the configuration for all properties?
As a side note: if NiFi ran out of memory, you'd always want to restart it because you are never sure what's the state of the JVM after an OOME.
Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr>> a écrit :
Hello all,
Who could help me to determine the cpu/memory need for nifi instance to fetch the data from Postgresql hosted in google ?
We got this error :
==> Error : executesql.error.message
Ran out of memory retrieving query results.
The procesor ExecuteSQL has this config : Set Auto Commit ==> false
driver Jar to use : postgresql-42.7.1.jar
Java version : jdk-11.0.19
Table information :
rows number : 14958836
fields number : 20
Linux Rocky8
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 2
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
BIOS Vendor ID: Google
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
Stepping: 7
CPU MHz: 2800.286
BogoMIPS: 5600.57
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 33792K
NUMA node0 CPU(s): 0,1
Memory : 8GB
Thanks for you helps
Minh
Re: Hardware requirement for NIFI instance
Posted by Juan Pablo Gardella <ga...@gmail.com>.
Agree on using sane defaults.
On Fri, Jan 5, 2024 at 11:52 AM Mark Payne <ma...@hotmail.com> wrote:
> Thanks for following up. That actually makes sense. I don’t think Output
> Batch Size will play a very big role here. But Fetch Size, if I understand
> correctly, is essentially telling the JDBC Driver “Here’s how many rows you
> should pull back at once.” And so it’s going to buffer all of those rows
> into memory until it has written out all of them.
>
> So if you set Fetch Size = 0, it’s going to pull back all rows in your
> database into memory. To be honest, I cannot imagine a single scenario
> where that’s desirable. We should probably set the default to something
> reasonable like 1,000 or 10,000 at most. And in 2.0, where we have the
> ability to migrate old configurations we should automatically change any
> config that has Fetch Size of 0 to the default value.
>
> @Matt Burgess, et al., any concerns with that?
>
> Thanks
> -Mark
>
>
> On Jan 5, 2024, at 9:45 AM, e-sociaux@gmx.fr wrote:
>
> So after some tests, here the result perhaps could help someone.
>
> With nifi (2CPU / 8Go Ram)
>
> I have tested with these couples properties :
>
> > 1 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 100000
>
> > 2 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 200000
>
> > 2 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 400000
> and started 5 executeSQL in the same time
>
> The 5 processors work perfectly and receive 5 avro files with same size.
> And during the test, the memory is stable and the Web UI works perfectly
>
>
> FAILED TEST "OUT OF MEMORY" if the properties are :
>
> > 1 executeSQL with "select * from table"
> Output Batch Size : 0
> Fetch Size : 0
> Regards
>
>
> *Envoyé:* vendredi 5 janvier 2024 à 08:12
> *De:* "Matt Burgess" <ma...@apache.org>
> *À:* users@nifi.apache.org
> *Objet:* Re: Hardware requirement for NIFI instance
> You may not need to merge if your Fetch Size is set appropriately. For
> your case I don't recommend setting Max Rows Per Flow File because you
> still have to wait for all the results to be processed before the
> FlowFile(s) get sent "downstream". Also if you set Output Batch Size
> you can't use Merge downstream as ExecuteSQL will send FlowFiles
> downstream before it knows the total count.
>
> If you have a NiFi cluster and not a standalone instance you MIGHT be
> able to represent your complex query using GenerateTableFetch and use
> a load-balanced connection to grab different "pages" of the table in
> parallel with ExecuteSQL. Those can be merged later as long as you get
> all the FlowFiles back to a single node. Depending on how complex your
> query is then it's a long shot but I thought I'd mention it just in
> case.
>
> Regards,
> Matt
>
>
> On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
> <pi...@gmail.com> wrote:
> >
> > You can merge multiple Avro flow files with MergeRecord with an Avro
> Reader and an Avro Writer
> >
> > Le jeu. 4 janv. 2024 à 22:05, <e-...@gmx.fr> a écrit :
> >>
> >> And the important thing for us it has only one avro file by table.
> >>
> >> So it is possible to merge avro files to one avro file ?
> >>
> >> Regards
> >>
> >>
> >> Envoyé: jeudi 4 janvier 2024 à 19:01
> >> De: e-sociaux@gmx.fr
> >> À: users@nifi.apache.org
> >> Cc: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >>
> >> Hello all,
> >>
> >> Thanks a lot for the reply.
> >>
> >> So for more details.
> >>
> >> All the properties for the ExecuteSQL are set by default, except "Set
> Auto Commit: false".
> >>
> >> The sql command could not be more simple than "select * from
> ${db.table.fullname}"
> >>
> >> The nifi version is 1.16.3 and 1.23.2
> >>
> >> I have also test the same sql command in the another nifi (8 cores/ 16G
> Ram) and it is working.
> >> The result is the avro file with 1.6GB
> >>
> >> The detail about the output flowfile :
> >>
> >> executesql.query.duration
> >> 245118
> >> executesql.query.executiontime
> >> 64122
> >> executesql.query.fetchtime
> >> 180996
> >> executesql.resultset.index
> >> 0
> >> executesql.row.count
> >> 14961077
> >>
> >> File Size
> >> 1.62 GB
> >>
> >> Regards
> >>
> >> Minh
> >>
> >>
> >> Envoyé: jeudi 4 janvier 2024 à 17:18
> >> De: "Matt Burgess" <ma...@apache.org>
> >> À: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >> If I remember correctly, the default Fetch Size for Postgresql is to
> >> get all the rows at once, which can certainly cause the problem.
> >> Perhaps try setting Fetch Size to something like 1000 or so and see if
> >> that alleviates the problem.
> >>
> >> Regards,
> >> Matt
> >>
> >> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com>
> wrote:
> >> >
> >> > Hello.
> >> >
> >> > I also think the problem is more about the processor, I guess
> ExecuteSQL.
> >> >
> >> > Should play with batch configuration and commit flag to commit
> intermediate FlowFile.
> >> >
> >> > The out of memory exception makes me believe the full table is
> retrieved, and if it is huge the FlowFile content is very large.
> >> >
> >> >
> >> >
> >> >
> >> > Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <
> pierre.villard.fr@gmail.com> a écrit :
> >> >>
> >> >> It should be memory efficient so I think this is likely a
> configuration aspect of your processor. Can you share the configuration for
> all properties?
> >> >> As a side note: if NiFi ran out of memory, you'd always want to
> restart it because you are never sure what's the state of the JVM after an
> OOME.
> >> >>
> >> >> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
> >> >>>
> >> >>>
> >> >>> Hello all,
> >> >>>
> >> >>> Who could help me to determine the cpu/memory need for nifi
> instance to fetch the data from Postgresql hosted in google ?
> >> >>>
> >> >>> We got this error :
> >> >>> ==> Error : executesql.error.message
> >> >>> Ran out of memory retrieving query results.
> >> >>>
> >> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
> >> >>> driver Jar to use : postgresql-42.7.1.jar
> >> >>> Java version : jdk-11.0.19
> >> >>>
> >> >>> Table information :
> >> >>> rows number : 14958836
> >> >>> fields number : 20
> >> >>>
> >> >>> Linux Rocky8
> >> >>>
> >> >>> Architecture: x86_64
> >> >>> CPU op-mode(s): 32-bit, 64-bit
> >> >>> Byte Order: Little Endian
> >> >>> CPU(s): 2
> >> >>> On-line CPU(s) list: 0,1
> >> >>> Thread(s) per core: 2
> >> >>> Core(s) per socket: 1
> >> >>> Socket(s): 1
> >> >>> NUMA node(s): 1
> >> >>> Vendor ID: GenuineIntel
> >> >>> BIOS Vendor ID: Google
> >> >>> CPU family: 6
> >> >>> Model: 85
> >> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
> >> >>> Stepping: 7
> >> >>> CPU MHz: 2800.286
> >> >>> BogoMIPS: 5600.57
> >> >>> Hypervisor vendor: KVM
> >> >>> Virtualization type: full
> >> >>> L1d cache: 32K
> >> >>> L1i cache: 32K
> >> >>> L2 cache: 1024K
> >> >>> L3 cache: 33792K
> >> >>> NUMA node0 CPU(s): 0,1
> >> >>>
> >> >>> Memory : 8GB
> >> >>>
> >> >>> Thanks for you helps
> >> >>>
> >> >>> Minh
> >>
> >>
>
>
>
>
>
Re: Hardware requirement for NIFI instance
Posted by e-...@gmx.fr.
Hello Mark,
I shared the screenshot about ExecuteSQL properties.
This 3 properties together work very well for PostGRESQL in the NIFI with
2CPU/8Gb.
Without the properties "Set Auto Commit" to FALSE, the NIFI stuck and crash.
Thanks a lot for help
Minh
**Envoy e:** vendredi 5 janvier 2024 a 15:52
**De:** "Mark Payne" <ma...@hotmail.com>
**À :** "users@nifi.apache.org" <us...@nifi.apache.org>
**Objet:** Re: Hardware requirement for NIFI instance
Thanks for following up. That actually makes sense. I don't think Output Batch
Size will play a very big role here. But Fetch Size, if I understand
correctly, is essentially telling the JDBC Driver "Here's how many rows you
should pull back at once." And so it's going to buffer all of those rows into
memory until it has written out all of them.
So if you set Fetch Size = 0, it's going to pull back all rows in your
database into memory. To be honest, I cannot imagine a single scenario where
that's desirable. We should probably set the default to something reasonable
like 1,000 or 10,000 at most. And in 2.0, where we have the ability to migrate
old configurations we should automatically change any config that has Fetch
Size of 0 to the default value.
@Matt Burgess, et al., any concerns with that?
Thanks
-Mark
> On Jan 5, 2024, at 9:45 AM, e-sociaux@gmx.fr wrote:
>
>
>
> So after some tests, here the result perhaps could help someone.
>
>
>
> With nifi (2CPU / 8Go Ram)
>
>
> I have tested with these couples properties :
>
>
>
> > 1 executeSQL with "select * from table"
>
> Output Batch Size : 1
>
> Fetch Size : 100000
>
>
>
> > 2 executeSQL with "select * from table"
>
> Output Batch Size : 1
>
> Fetch Size : 200000
>
>
>
> > 2 executeSQL with "select * from table"
>
> Output Batch Size : 1
>
> Fetch Size : 400000
>
> and started 5 executeSQL in the same time
>
>
>
> The 5 processors work perfectly and receive 5 avro files with same size.
>
> And during the test, the memory is stable and the Web UI works perfectly
>
>
>
>
>
> FAILED TEST "OUT OF MEMORY" if the properties are :
>
>
>
> > 1 executeSQL with "select * from table"
>
> Output Batch Size : 0
>
> Fetch Size : 0
>
> Regards
>
>
>
>
>
> **Envoy e:** vendredi 5 janvier 2024 a 08:12
> **De:** "Matt Burgess" <ma...@apache.org>
> **À :** users@nifi.apache.org
> **Objet:** Re: Hardware requirement for NIFI instance
>
> You may not need to merge if your Fetch Size is set appropriately. For
> your case I don't recommend setting Max Rows Per Flow File because you
> still have to wait for all the results to be processed before the
> FlowFile(s) get sent "downstream". Also if you set Output Batch Size
> you can't use Merge downstream as ExecuteSQL will send FlowFiles
> downstream before it knows the total count.
>
> If you have a NiFi cluster and not a standalone instance you MIGHT be
> able to represent your complex query using GenerateTableFetch and use
> a load-balanced connection to grab different "pages" of the table in
> parallel with ExecuteSQL. Those can be merged later as long as you get
> all the FlowFiles back to a single node. Depending on how complex your
> query is then it's a long shot but I thought I'd mention it just in
> case.
>
> Regards,
> Matt
>
>
> On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
> <pi...@gmail.com> wrote:
> >
> > You can merge multiple Avro flow files with MergeRecord with an Avro
> Reader and an Avro Writer
> >
> > Le jeu. 4 janv. 2024 a 22:05, <e-...@gmx.fr> a ecrit :
> >>
> >> And the important thing for us it has only one avro file by table.
> >>
> >> So it is possible to merge avro files to one avro file ?
> >>
> >> Regards
> >>
> >>
> >> Envoye: jeudi 4 janvier 2024 a 19:01
> >> De: e-sociaux@gmx.fr
> >> À: users@nifi.apache.org
> >> Cc: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >>
> >> Hello all,
> >>
> >> Thanks a lot for the reply.
> >>
> >> So for more details.
> >>
> >> All the properties for the ExecuteSQL are set by default, except "Set
> Auto Commit: false".
> >>
> >> The sql command could not be more simple than "select * from
> ${db.table.fullname}"
> >>
> >> The nifi version is 1.16.3 and 1.23.2
> >>
> >> I have also test the same sql command in the another nifi (8 cores/ 16G
> Ram) and it is working.
> >> The result is the avro file with 1.6GB
> >>
> >> The detail about the output flowfile :
> >>
> >> executesql.query.duration
> >> 245118
> >> executesql.query.executiontime
> >> 64122
> >> executesql.query.fetchtime
> >> 180996
> >> executesql.resultset.index
> >> 0
> >> executesql.row.count
> >> 14961077
> >>
> >> File Size
> >> 1.62 GB
> >>
> >> Regards
> >>
> >> Minh
> >>
> >>
> >> Envoye: jeudi 4 janvier 2024 a 17:18
> >> De: "Matt Burgess" <ma...@apache.org>
> >> À: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >> If I remember correctly, the default Fetch Size for Postgresql is to
> >> get all the rows at once, which can certainly cause the problem.
> >> Perhaps try setting Fetch Size to something like 1000 or so and see if
> >> that alleviates the problem.
> >>
> >> Regards,
> >> Matt
> >>
> >> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com>
> wrote:
> >> >
> >> > Hello.
> >> >
> >> > I also think the problem is more about the processor, I guess
> ExecuteSQL.
> >> >
> >> > Should play with batch configuration and commit flag to commit
> intermediate FlowFile.
> >> >
> >> > The out of memory exception makes me believe the full table is
> retrieved, and if it is huge the FlowFile content is very large.
> >> >
> >> >
> >> >
> >> >
> >> > Le jeu. 4 janv. 2024 a 14:37, Pierre Villard
> <pi...@gmail.com> a ecrit :
> >> >>
> >> >> It should be memory efficient so I think this is likely a
> configuration aspect of your processor. Can you share the configuration for
> all properties?
> >> >> As a side note: if NiFi ran out of memory, you'd always want to
> restart it because you are never sure what's the state of the JVM after an
> OOME.
> >> >>
> >> >> Le jeu. 4 janv. 2024 a 17:26, <e-...@gmx.fr> a ecrit :
> >> >>>
> >> >>>
> >> >>> Hello all,
> >> >>>
> >> >>> Who could help me to determine the cpu/memory need for nifi instance
> to fetch the data from Postgresql hosted in google ?
> >> >>>
> >> >>> We got this error :
> >> >>> ==> Error : executesql.error.message
> >> >>> Ran out of memory retrieving query results.
> >> >>>
> >> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
> >> >>> driver Jar to use : postgresql-42.7.1.jar
> >> >>> Java version : jdk-11.0.19
> >> >>>
> >> >>> Table information :
> >> >>> rows number : 14958836
> >> >>> fields number : 20
> >> >>>
> >> >>> Linux Rocky8
> >> >>>
> >> >>> Architecture: x86_64
> >> >>> CPU op-mode(s): 32-bit, 64-bit
> >> >>> Byte Order: Little Endian
> >> >>> CPU(s): 2
> >> >>> On-line CPU(s) list: 0,1
> >> >>> Thread(s) per core: 2
> >> >>> Core(s) per socket: 1
> >> >>> Socket(s): 1
> >> >>> NUMA node(s): 1
> >> >>> Vendor ID: GenuineIntel
> >> >>> BIOS Vendor ID: Google
> >> >>> CPU family: 6
> >> >>> Model: 85
> >> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
> >> >>> Stepping: 7
> >> >>> CPU MHz: 2800.286
> >> >>> BogoMIPS: 5600.57
> >> >>> Hypervisor vendor: KVM
> >> >>> Virtualization type: full
> >> >>> L1d cache: 32K
> >> >>> L1i cache: 32K
> >> >>> L2 cache: 1024K
> >> >>> L3 cache: 33792K
> >> >>> NUMA node0 CPU(s): 0,1
> >> >>>
> >> >>> Memory : 8GB
> >> >>>
> >> >>> Thanks for you helps
> >> >>>
> >> >>> Minh
> >>
> >>
>
>
>
>
Re: Hardware requirement for NIFI instance
Posted by Phillip Lord <ph...@gmail.com>.
Agreed on updating defaults... I've seen this play out before.
On Fri, Jan 5, 2024 at 10:10 AM Mark Payne <ma...@hotmail.com> wrote:
> Thanks for following up. That actually makes sense. I don’t think Output
> Batch Size will play a very big role here. But Fetch Size, if I understand
> correctly, is essentially telling the JDBC Driver “Here’s how many rows you
> should pull back at once.” And so it’s going to buffer all of those rows
> into memory until it has written out all of them.
>
> So if you set Fetch Size = 0, it’s going to pull back all rows in your
> database into memory. To be honest, I cannot imagine a single scenario
> where that’s desirable. We should probably set the default to something
> reasonable like 1,000 or 10,000 at most. And in 2.0, where we have the
> ability to migrate old configurations we should automatically change any
> config that has Fetch Size of 0 to the default value.
>
> @Matt Burgess, et al., any concerns with that?
>
> Thanks
> -Mark
>
>
> On Jan 5, 2024, at 9:45 AM, e-sociaux@gmx.fr wrote:
>
> So after some tests, here the result perhaps could help someone.
>
> With nifi (2CPU / 8Go Ram)
>
> I have tested with these couples properties :
>
> > 1 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 100000
>
> > 2 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 200000
>
> > 2 executeSQL with "select * from table"
> Output Batch Size : 1
> Fetch Size : 400000
> and started 5 executeSQL in the same time
>
> The 5 processors work perfectly and receive 5 avro files with same size.
> And during the test, the memory is stable and the Web UI works perfectly
>
>
> FAILED TEST "OUT OF MEMORY" if the properties are :
>
> > 1 executeSQL with "select * from table"
> Output Batch Size : 0
> Fetch Size : 0
> Regards
>
>
> *Envoyé:* vendredi 5 janvier 2024 à 08:12
> *De:* "Matt Burgess" <ma...@apache.org>
> *À:* users@nifi.apache.org
> *Objet:* Re: Hardware requirement for NIFI instance
> You may not need to merge if your Fetch Size is set appropriately. For
> your case I don't recommend setting Max Rows Per Flow File because you
> still have to wait for all the results to be processed before the
> FlowFile(s) get sent "downstream". Also if you set Output Batch Size
> you can't use Merge downstream as ExecuteSQL will send FlowFiles
> downstream before it knows the total count.
>
> If you have a NiFi cluster and not a standalone instance you MIGHT be
> able to represent your complex query using GenerateTableFetch and use
> a load-balanced connection to grab different "pages" of the table in
> parallel with ExecuteSQL. Those can be merged later as long as you get
> all the FlowFiles back to a single node. Depending on how complex your
> query is then it's a long shot but I thought I'd mention it just in
> case.
>
> Regards,
> Matt
>
>
> On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
> <pi...@gmail.com> wrote:
> >
> > You can merge multiple Avro flow files with MergeRecord with an Avro
> Reader and an Avro Writer
> >
> > Le jeu. 4 janv. 2024 à 22:05, <e-...@gmx.fr> a écrit :
> >>
> >> And the important thing for us it has only one avro file by table.
> >>
> >> So it is possible to merge avro files to one avro file ?
> >>
> >> Regards
> >>
> >>
> >> Envoyé: jeudi 4 janvier 2024 à 19:01
> >> De: e-sociaux@gmx.fr
> >> À: users@nifi.apache.org
> >> Cc: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >>
> >> Hello all,
> >>
> >> Thanks a lot for the reply.
> >>
> >> So for more details.
> >>
> >> All the properties for the ExecuteSQL are set by default, except "Set
> Auto Commit: false".
> >>
> >> The sql command could not be more simple than "select * from
> ${db.table.fullname}"
> >>
> >> The nifi version is 1.16.3 and 1.23.2
> >>
> >> I have also test the same sql command in the another nifi (8 cores/ 16G
> Ram) and it is working.
> >> The result is the avro file with 1.6GB
> >>
> >> The detail about the output flowfile :
> >>
> >> executesql.query.duration
> >> 245118
> >> executesql.query.executiontime
> >> 64122
> >> executesql.query.fetchtime
> >> 180996
> >> executesql.resultset.index
> >> 0
> >> executesql.row.count
> >> 14961077
> >>
> >> File Size
> >> 1.62 GB
> >>
> >> Regards
> >>
> >> Minh
> >>
> >>
> >> Envoyé: jeudi 4 janvier 2024 à 17:18
> >> De: "Matt Burgess" <ma...@apache.org>
> >> À: users@nifi.apache.org
> >> Objet: Re: Hardware requirement for NIFI instance
> >> If I remember correctly, the default Fetch Size for Postgresql is to
> >> get all the rows at once, which can certainly cause the problem.
> >> Perhaps try setting Fetch Size to something like 1000 or so and see if
> >> that alleviates the problem.
> >>
> >> Regards,
> >> Matt
> >>
> >> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com>
> wrote:
> >> >
> >> > Hello.
> >> >
> >> > I also think the problem is more about the processor, I guess
> ExecuteSQL.
> >> >
> >> > Should play with batch configuration and commit flag to commit
> intermediate FlowFile.
> >> >
> >> > The out of memory exception makes me believe the full table is
> retrieved, and if it is huge the FlowFile content is very large.
> >> >
> >> >
> >> >
> >> >
> >> > Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <
> pierre.villard.fr@gmail.com> a écrit :
> >> >>
> >> >> It should be memory efficient so I think this is likely a
> configuration aspect of your processor. Can you share the configuration for
> all properties?
> >> >> As a side note: if NiFi ran out of memory, you'd always want to
> restart it because you are never sure what's the state of the JVM after an
> OOME.
> >> >>
> >> >> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
> >> >>>
> >> >>>
> >> >>> Hello all,
> >> >>>
> >> >>> Who could help me to determine the cpu/memory need for nifi
> instance to fetch the data from Postgresql hosted in google ?
> >> >>>
> >> >>> We got this error :
> >> >>> ==> Error : executesql.error.message
> >> >>> Ran out of memory retrieving query results.
> >> >>>
> >> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
> >> >>> driver Jar to use : postgresql-42.7.1.jar
> >> >>> Java version : jdk-11.0.19
> >> >>>
> >> >>> Table information :
> >> >>> rows number : 14958836
> >> >>> fields number : 20
> >> >>>
> >> >>> Linux Rocky8
> >> >>>
> >> >>> Architecture: x86_64
> >> >>> CPU op-mode(s): 32-bit, 64-bit
> >> >>> Byte Order: Little Endian
> >> >>> CPU(s): 2
> >> >>> On-line CPU(s) list: 0,1
> >> >>> Thread(s) per core: 2
> >> >>> Core(s) per socket: 1
> >> >>> Socket(s): 1
> >> >>> NUMA node(s): 1
> >> >>> Vendor ID: GenuineIntel
> >> >>> BIOS Vendor ID: Google
> >> >>> CPU family: 6
> >> >>> Model: 85
> >> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
> >> >>> Stepping: 7
> >> >>> CPU MHz: 2800.286
> >> >>> BogoMIPS: 5600.57
> >> >>> Hypervisor vendor: KVM
> >> >>> Virtualization type: full
> >> >>> L1d cache: 32K
> >> >>> L1i cache: 32K
> >> >>> L2 cache: 1024K
> >> >>> L3 cache: 33792K
> >> >>> NUMA node0 CPU(s): 0,1
> >> >>>
> >> >>> Memory : 8GB
> >> >>>
> >> >>> Thanks for you helps
> >> >>>
> >> >>> Minh
> >>
> >>
>
>
>
>
>
Re: Hardware requirement for NIFI instance
Posted by Mark Payne <ma...@hotmail.com>.
Thanks for following up. That actually makes sense. I don’t think Output Batch Size will play a very big role here. But Fetch Size, if I understand correctly, is essentially telling the JDBC Driver “Here’s how many rows you should pull back at once.” And so it’s going to buffer all of those rows into memory until it has written out all of them.
So if you set Fetch Size = 0, it’s going to pull back all rows in your database into memory. To be honest, I cannot imagine a single scenario where that’s desirable. We should probably set the default to something reasonable like 1,000 or 10,000 at most. And in 2.0, where we have the ability to migrate old configurations we should automatically change any config that has Fetch Size of 0 to the default value.
@Matt Burgess, et al., any concerns with that?
Thanks
-Mark
On Jan 5, 2024, at 9:45 AM, e-sociaux@gmx.fr wrote:
So after some tests, here the result perhaps could help someone.
With nifi (2CPU / 8Go Ram)
I have tested with these couples properties :
> 1 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 100000
> 2 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 200000
> 2 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 400000
and started 5 executeSQL in the same time
The 5 processors work perfectly and receive 5 avro files with same size.
And during the test, the memory is stable and the Web UI works perfectly
FAILED TEST "OUT OF MEMORY" if the properties are :
> 1 executeSQL with "select * from table"
Output Batch Size : 0
Fetch Size : 0
Regards
Envoyé: vendredi 5 janvier 2024 à 08:12
De: "Matt Burgess" <ma...@apache.org>
À: users@nifi.apache.org
Objet: Re: Hardware requirement for NIFI instance
You may not need to merge if your Fetch Size is set appropriately. For
your case I don't recommend setting Max Rows Per Flow File because you
still have to wait for all the results to be processed before the
FlowFile(s) get sent "downstream". Also if you set Output Batch Size
you can't use Merge downstream as ExecuteSQL will send FlowFiles
downstream before it knows the total count.
If you have a NiFi cluster and not a standalone instance you MIGHT be
able to represent your complex query using GenerateTableFetch and use
a load-balanced connection to grab different "pages" of the table in
parallel with ExecuteSQL. Those can be merged later as long as you get
all the FlowFiles back to a single node. Depending on how complex your
query is then it's a long shot but I thought I'd mention it just in
case.
Regards,
Matt
On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
<pi...@gmail.com> wrote:
>
> You can merge multiple Avro flow files with MergeRecord with an Avro Reader and an Avro Writer
>
> Le jeu. 4 janv. 2024 à 22:05, <e-...@gmx.fr> a écrit :
>>
>> And the important thing for us it has only one avro file by table.
>>
>> So it is possible to merge avro files to one avro file ?
>>
>> Regards
>>
>>
>> Envoyé: jeudi 4 janvier 2024 à 19:01
>> De: e-sociaux@gmx.fr
>> À: users@nifi.apache.org
>> Cc: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>>
>> Hello all,
>>
>> Thanks a lot for the reply.
>>
>> So for more details.
>>
>> All the properties for the ExecuteSQL are set by default, except "Set Auto Commit: false".
>>
>> The sql command could not be more simple than "select * from ${db.table.fullname}"
>>
>> The nifi version is 1.16.3 and 1.23.2
>>
>> I have also test the same sql command in the another nifi (8 cores/ 16G Ram) and it is working.
>> The result is the avro file with 1.6GB
>>
>> The detail about the output flowfile :
>>
>> executesql.query.duration
>> 245118
>> executesql.query.executiontime
>> 64122
>> executesql.query.fetchtime
>> 180996
>> executesql.resultset.index
>> 0
>> executesql.row.count
>> 14961077
>>
>> File Size
>> 1.62 GB
>>
>> Regards
>>
>> Minh
>>
>>
>> Envoyé: jeudi 4 janvier 2024 à 17:18
>> De: "Matt Burgess" <ma...@apache.org>
>> À: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>> If I remember correctly, the default Fetch Size for Postgresql is to
>> get all the rows at once, which can certainly cause the problem.
>> Perhaps try setting Fetch Size to something like 1000 or so and see if
>> that alleviates the problem.
>>
>> Regards,
>> Matt
>>
>> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com> wrote:
>> >
>> > Hello.
>> >
>> > I also think the problem is more about the processor, I guess ExecuteSQL.
>> >
>> > Should play with batch configuration and commit flag to commit intermediate FlowFile.
>> >
>> > The out of memory exception makes me believe the full table is retrieved, and if it is huge the FlowFile content is very large.
>> >
>> >
>> >
>> >
>> > Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com> a écrit :
>> >>
>> >> It should be memory efficient so I think this is likely a configuration aspect of your processor. Can you share the configuration for all properties?
>> >> As a side note: if NiFi ran out of memory, you'd always want to restart it because you are never sure what's the state of the JVM after an OOME.
>> >>
>> >> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
>> >>>
>> >>>
>> >>> Hello all,
>> >>>
>> >>> Who could help me to determine the cpu/memory need for nifi instance to fetch the data from Postgresql hosted in google ?
>> >>>
>> >>> We got this error :
>> >>> ==> Error : executesql.error.message
>> >>> Ran out of memory retrieving query results.
>> >>>
>> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>> >>> driver Jar to use : postgresql-42.7.1.jar
>> >>> Java version : jdk-11.0.19
>> >>>
>> >>> Table information :
>> >>> rows number : 14958836
>> >>> fields number : 20
>> >>>
>> >>> Linux Rocky8
>> >>>
>> >>> Architecture: x86_64
>> >>> CPU op-mode(s): 32-bit, 64-bit
>> >>> Byte Order: Little Endian
>> >>> CPU(s): 2
>> >>> On-line CPU(s) list: 0,1
>> >>> Thread(s) per core: 2
>> >>> Core(s) per socket: 1
>> >>> Socket(s): 1
>> >>> NUMA node(s): 1
>> >>> Vendor ID: GenuineIntel
>> >>> BIOS Vendor ID: Google
>> >>> CPU family: 6
>> >>> Model: 85
>> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>> >>> Stepping: 7
>> >>> CPU MHz: 2800.286
>> >>> BogoMIPS: 5600.57
>> >>> Hypervisor vendor: KVM
>> >>> Virtualization type: full
>> >>> L1d cache: 32K
>> >>> L1i cache: 32K
>> >>> L2 cache: 1024K
>> >>> L3 cache: 33792K
>> >>> NUMA node0 CPU(s): 0,1
>> >>>
>> >>> Memory : 8GB
>> >>>
>> >>> Thanks for you helps
>> >>>
>> >>> Minh
>>
>>
Re: Hardware requirement for NIFI instance
Posted by e-...@gmx.fr.
So after some tests, here the result perhaps could help someone.
With nifi (2CPU / 8Go Ram)
I have tested with these couples properties :
> 1 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 100000
> 2 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 200000
> 2 executeSQL with "select * from table"
Output Batch Size : 1
Fetch Size : 400000
and started 5 executeSQL in the same time
The 5 processors work perfectly and receive 5 avro files with same size.
And during the test, the memory is stable and the Web UI works perfectly
FAILED TEST "OUT OF MEMORY" if the properties are :
> 1 executeSQL with "select * from table"
Output Batch Size : 0
Fetch Size : 0
Regards
**Envoy e:** vendredi 5 janvier 2024 a 08:12
**De:** "Matt Burgess" <ma...@apache.org>
**À :** users@nifi.apache.org
**Objet:** Re: Hardware requirement for NIFI instance
You may not need to merge if your Fetch Size is set appropriately. For
your case I don't recommend setting Max Rows Per Flow File because you
still have to wait for all the results to be processed before the
FlowFile(s) get sent "downstream". Also if you set Output Batch Size
you can't use Merge downstream as ExecuteSQL will send FlowFiles
downstream before it knows the total count.
If you have a NiFi cluster and not a standalone instance you MIGHT be
able to represent your complex query using GenerateTableFetch and use
a load-balanced connection to grab different "pages" of the table in
parallel with ExecuteSQL. Those can be merged later as long as you get
all the FlowFiles back to a single node. Depending on how complex your
query is then it's a long shot but I thought I'd mention it just in
case.
Regards,
Matt
On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
<pi...@gmail.com> wrote:
>
> You can merge multiple Avro flow files with MergeRecord with an Avro Reader
> and an Avro Writer
>
> Le jeu. 4 janv. 2024 a 22:05, <e-...@gmx.fr> a ecrit :
>>
>> And the important thing for us it has only one avro file by table.
>>
>> So it is possible to merge avro files to one avro file ?
>>
>> Regards
>>
>>
>> Envoye: jeudi 4 janvier 2024 a 19:01
>> De: e-sociaux@gmx.fr
>> À: users@nifi.apache.org
>> Cc: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>>
>> Hello all,
>>
>> Thanks a lot for the reply.
>>
>> So for more details.
>>
>> All the properties for the ExecuteSQL are set by default, except "Set Auto
Commit: false".
>>
>> The sql command could not be more simple than "select * from
${db.table.fullname}"
>>
>> The nifi version is 1.16.3 and 1.23.2
>>
>> I have also test the same sql command in the another nifi (8 cores/ 16G
Ram) and it is working.
>> The result is the avro file with 1.6GB
>>
>> The detail about the output flowfile :
>>
>> executesql.query.duration
>> 245118
>> executesql.query.executiontime
>> 64122
>> executesql.query.fetchtime
>> 180996
>> executesql.resultset.index
>> 0
>> executesql.row.count
>> 14961077
>>
>> File Size
>> 1.62 GB
>>
>> Regards
>>
>> Minh
>>
>>
>> Envoye: jeudi 4 janvier 2024 a 17:18
>> De: "Matt Burgess" <ma...@apache.org>
>> À: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>> If I remember correctly, the default Fetch Size for Postgresql is to
>> get all the rows at once, which can certainly cause the problem.
>> Perhaps try setting Fetch Size to something like 1000 or so and see if
>> that alleviates the problem.
>>
>> Regards,
>> Matt
>>
>> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com>
wrote:
>> >
>> > Hello.
>> >
>> > I also think the problem is more about the processor, I guess ExecuteSQL.
>> >
>> > Should play with batch configuration and commit flag to commit
intermediate FlowFile.
>> >
>> > The out of memory exception makes me believe the full table is retrieved,
and if it is huge the FlowFile content is very large.
>> >
>> >
>> >
>> >
>> > Le jeu. 4 janv. 2024 a 14:37, Pierre Villard
<pi...@gmail.com> a ecrit :
>> >>
>> >> It should be memory efficient so I think this is likely a configuration
aspect of your processor. Can you share the configuration for all properties?
>> >> As a side note: if NiFi ran out of memory, you'd always want to restart
it because you are never sure what's the state of the JVM after an OOME.
>> >>
>> >> Le jeu. 4 janv. 2024 a 17:26, <e-...@gmx.fr> a ecrit :
>> >>>
>> >>>
>> >>> Hello all,
>> >>>
>> >>> Who could help me to determine the cpu/memory need for nifi instance to
fetch the data from Postgresql hosted in google ?
>> >>>
>> >>> We got this error :
>> >>> ==> Error : executesql.error.message
>> >>> Ran out of memory retrieving query results.
>> >>>
>> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>> >>> driver Jar to use : postgresql-42.7.1.jar
>> >>> Java version : jdk-11.0.19
>> >>>
>> >>> Table information :
>> >>> rows number : 14958836
>> >>> fields number : 20
>> >>>
>> >>> Linux Rocky8
>> >>>
>> >>> Architecture: x86_64
>> >>> CPU op-mode(s): 32-bit, 64-bit
>> >>> Byte Order: Little Endian
>> >>> CPU(s): 2
>> >>> On-line CPU(s) list: 0,1
>> >>> Thread(s) per core: 2
>> >>> Core(s) per socket: 1
>> >>> Socket(s): 1
>> >>> NUMA node(s): 1
>> >>> Vendor ID: GenuineIntel
>> >>> BIOS Vendor ID: Google
>> >>> CPU family: 6
>> >>> Model: 85
>> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>> >>> Stepping: 7
>> >>> CPU MHz: 2800.286
>> >>> BogoMIPS: 5600.57
>> >>> Hypervisor vendor: KVM
>> >>> Virtualization type: full
>> >>> L1d cache: 32K
>> >>> L1i cache: 32K
>> >>> L2 cache: 1024K
>> >>> L3 cache: 33792K
>> >>> NUMA node0 CPU(s): 0,1
>> >>>
>> >>> Memory : 8GB
>> >>>
>> >>> Thanks for you helps
>> >>>
>> >>> Minh
>>
>>
Re: Hardware requirement for NIFI instance
Posted by Matt Burgess <ma...@apache.org>.
You may not need to merge if your Fetch Size is set appropriately. For
your case I don't recommend setting Max Rows Per Flow File because you
still have to wait for all the results to be processed before the
FlowFile(s) get sent "downstream". Also if you set Output Batch Size
you can't use Merge downstream as ExecuteSQL will send FlowFiles
downstream before it knows the total count.
If you have a NiFi cluster and not a standalone instance you MIGHT be
able to represent your complex query using GenerateTableFetch and use
a load-balanced connection to grab different "pages" of the table in
parallel with ExecuteSQL. Those can be merged later as long as you get
all the FlowFiles back to a single node. Depending on how complex your
query is then it's a long shot but I thought I'd mention it just in
case.
Regards,
Matt
On Thu, Jan 4, 2024 at 1:41 PM Pierre Villard
<pi...@gmail.com> wrote:
>
> You can merge multiple Avro flow files with MergeRecord with an Avro Reader and an Avro Writer
>
> Le jeu. 4 janv. 2024 à 22:05, <e-...@gmx.fr> a écrit :
>>
>> And the important thing for us it has only one avro file by table.
>>
>> So it is possible to merge avro files to one avro file ?
>>
>> Regards
>>
>>
>> Envoyé: jeudi 4 janvier 2024 à 19:01
>> De: e-sociaux@gmx.fr
>> À: users@nifi.apache.org
>> Cc: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>>
>> Hello all,
>>
>> Thanks a lot for the reply.
>>
>> So for more details.
>>
>> All the properties for the ExecuteSQL are set by default, except "Set Auto Commit: false".
>>
>> The sql command could not be more simple than "select * from ${db.table.fullname}"
>>
>> The nifi version is 1.16.3 and 1.23.2
>>
>> I have also test the same sql command in the another nifi (8 cores/ 16G Ram) and it is working.
>> The result is the avro file with 1.6GB
>>
>> The detail about the output flowfile :
>>
>> executesql.query.duration
>> 245118
>> executesql.query.executiontime
>> 64122
>> executesql.query.fetchtime
>> 180996
>> executesql.resultset.index
>> 0
>> executesql.row.count
>> 14961077
>>
>> File Size
>> 1.62 GB
>>
>> Regards
>>
>> Minh
>>
>>
>> Envoyé: jeudi 4 janvier 2024 à 17:18
>> De: "Matt Burgess" <ma...@apache.org>
>> À: users@nifi.apache.org
>> Objet: Re: Hardware requirement for NIFI instance
>> If I remember correctly, the default Fetch Size for Postgresql is to
>> get all the rows at once, which can certainly cause the problem.
>> Perhaps try setting Fetch Size to something like 1000 or so and see if
>> that alleviates the problem.
>>
>> Regards,
>> Matt
>>
>> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com> wrote:
>> >
>> > Hello.
>> >
>> > I also think the problem is more about the processor, I guess ExecuteSQL.
>> >
>> > Should play with batch configuration and commit flag to commit intermediate FlowFile.
>> >
>> > The out of memory exception makes me believe the full table is retrieved, and if it is huge the FlowFile content is very large.
>> >
>> >
>> >
>> >
>> > Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com> a écrit :
>> >>
>> >> It should be memory efficient so I think this is likely a configuration aspect of your processor. Can you share the configuration for all properties?
>> >> As a side note: if NiFi ran out of memory, you'd always want to restart it because you are never sure what's the state of the JVM after an OOME.
>> >>
>> >> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
>> >>>
>> >>>
>> >>> Hello all,
>> >>>
>> >>> Who could help me to determine the cpu/memory need for nifi instance to fetch the data from Postgresql hosted in google ?
>> >>>
>> >>> We got this error :
>> >>> ==> Error : executesql.error.message
>> >>> Ran out of memory retrieving query results.
>> >>>
>> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>> >>> driver Jar to use : postgresql-42.7.1.jar
>> >>> Java version : jdk-11.0.19
>> >>>
>> >>> Table information :
>> >>> rows number : 14958836
>> >>> fields number : 20
>> >>>
>> >>> Linux Rocky8
>> >>>
>> >>> Architecture: x86_64
>> >>> CPU op-mode(s): 32-bit, 64-bit
>> >>> Byte Order: Little Endian
>> >>> CPU(s): 2
>> >>> On-line CPU(s) list: 0,1
>> >>> Thread(s) per core: 2
>> >>> Core(s) per socket: 1
>> >>> Socket(s): 1
>> >>> NUMA node(s): 1
>> >>> Vendor ID: GenuineIntel
>> >>> BIOS Vendor ID: Google
>> >>> CPU family: 6
>> >>> Model: 85
>> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>> >>> Stepping: 7
>> >>> CPU MHz: 2800.286
>> >>> BogoMIPS: 5600.57
>> >>> Hypervisor vendor: KVM
>> >>> Virtualization type: full
>> >>> L1d cache: 32K
>> >>> L1i cache: 32K
>> >>> L2 cache: 1024K
>> >>> L3 cache: 33792K
>> >>> NUMA node0 CPU(s): 0,1
>> >>>
>> >>> Memory : 8GB
>> >>>
>> >>> Thanks for you helps
>> >>>
>> >>> Minh
>>
>>
Re: Hardware requirement for NIFI instance
Posted by Pierre Villard <pi...@gmail.com>.
You can merge multiple Avro flow files with MergeRecord with an Avro Reader
and an Avro Writer
Le jeu. 4 janv. 2024 à 22:05, <e-...@gmx.fr> a écrit :
> And the important thing for us it has only one avro file by table.
>
> So it is possible to merge avro files to one avro file ?
>
> Regards
>
>
> *Envoyé:* jeudi 4 janvier 2024 à 19:01
> *De:* e-sociaux@gmx.fr
> *À:* users@nifi.apache.org
> *Cc:* users@nifi.apache.org
> *Objet:* Re: Hardware requirement for NIFI instance
>
> Hello all,
>
> Thanks a lot for the reply.
>
> So for more details.
>
> All the properties for the ExecuteSQL are set by default, except "Set Auto
> Commit: false".
>
> The sql command could not be more simple than "*select * from
> ${db.table.fullname}*"
>
> The nifi version is 1.16.3 and 1.23.2
>
> I have also test the same sql command in the another nifi (8 cores/ 16G
> Ram) and it is working.
> The result is the avro file with 1.6GB
>
> The detail about the output flowfile :
>
> executesql.query.duration
> 245118
> executesql.query.executiontime
> 64122
> executesql.query.fetchtime
> 180996
> executesql.resultset.index
> 0
> executesql.row.count
> 14961077
>
> File Size
> 1.62 GB
>
> Regards
>
> Minh
>
>
> *Envoyé:* jeudi 4 janvier 2024 à 17:18
> *De:* "Matt Burgess" <ma...@apache.org>
> *À:* users@nifi.apache.org
> *Objet:* Re: Hardware requirement for NIFI instance
> If I remember correctly, the default Fetch Size for Postgresql is to
> get all the rows at once, which can certainly cause the problem.
> Perhaps try setting Fetch Size to something like 1000 or so and see if
> that alleviates the problem.
>
> Regards,
> Matt
>
> On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com>
> wrote:
> >
> > Hello.
> >
> > I also think the problem is more about the processor, I guess ExecuteSQL.
> >
> > Should play with batch configuration and commit flag to commit
> intermediate FlowFile.
> >
> > The out of memory exception makes me believe the full table is
> retrieved, and if it is huge the FlowFile content is very large.
> >
> >
> >
> >
> > Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <
> pierre.villard.fr@gmail.com> a écrit :
> >>
> >> It should be memory efficient so I think this is likely a configuration
> aspect of your processor. Can you share the configuration for all
> properties?
> >> As a side note: if NiFi ran out of memory, you'd always want to restart
> it because you are never sure what's the state of the JVM after an OOME.
> >>
> >> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
> >>>
> >>>
> >>> Hello all,
> >>>
> >>> Who could help me to determine the cpu/memory need for nifi instance
> to fetch the data from Postgresql hosted in google ?
> >>>
> >>> We got this error :
> >>> ==> Error : executesql.error.message
> >>> Ran out of memory retrieving query results.
> >>>
> >>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
> >>> driver Jar to use : postgresql-42.7.1.jar
> >>> Java version : jdk-11.0.19
> >>>
> >>> Table information :
> >>> rows number : 14958836
> >>> fields number : 20
> >>>
> >>> Linux Rocky8
> >>>
> >>> Architecture: x86_64
> >>> CPU op-mode(s): 32-bit, 64-bit
> >>> Byte Order: Little Endian
> >>> CPU(s): 2
> >>> On-line CPU(s) list: 0,1
> >>> Thread(s) per core: 2
> >>> Core(s) per socket: 1
> >>> Socket(s): 1
> >>> NUMA node(s): 1
> >>> Vendor ID: GenuineIntel
> >>> BIOS Vendor ID: Google
> >>> CPU family: 6
> >>> Model: 85
> >>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
> >>> Stepping: 7
> >>> CPU MHz: 2800.286
> >>> BogoMIPS: 5600.57
> >>> Hypervisor vendor: KVM
> >>> Virtualization type: full
> >>> L1d cache: 32K
> >>> L1i cache: 32K
> >>> L2 cache: 1024K
> >>> L3 cache: 33792K
> >>> NUMA node0 CPU(s): 0,1
> >>>
> >>> Memory : 8GB
> >>>
> >>> Thanks for you helps
> >>>
> >>> Minh
>
>
>
Re: Hardware requirement for NIFI instance
Posted by e-...@gmx.fr.
And the important thing for us it has only one avro file by table.
So it is possible to merge avro files to one avro file ?
Regards
**Envoy e:** jeudi 4 janvier 2024 a 19:01
**De:** e-sociaux@gmx.fr
**À :** users@nifi.apache.org
**Cc:** users@nifi.apache.org
**Objet:** Re: Hardware requirement for NIFI instance
Hello all,
Thanks a lot for the reply.
So for more details.
All the properties for the ExecuteSQL are set by default, except "Set Auto
Commit: false".
The sql command could not be more simple than " **select * from
${db.table.fullname}**"
The nifi version is 1.16.3 and 1.23.2
I have also test the same sql command in the another nifi (8 cores/ 16G Ram)
and it is working.
The result is the avro file with 1.6GB
The detail about the output flowfile :
executesql.query.duration
245118
executesql.query.executiontime
64122
executesql.query.fetchtime
180996
executesql.resultset.index
0
executesql.row.count
14961077
File Size
1.62 GB
Regards
Minh
**Envoy e:** jeudi 4 janvier 2024 a 17:18
**De:** "Matt Burgess" <ma...@apache.org>
**À :** users@nifi.apache.org
**Objet:** Re: Hardware requirement for NIFI instance
If I remember correctly, the default Fetch Size for Postgresql is to
get all the rows at once, which can certainly cause the problem.
Perhaps try setting Fetch Size to something like 1000 or so and see if
that alleviates the problem.
Regards,
Matt
On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com> wrote:
>
> Hello.
>
> I also think the problem is more about the processor, I guess ExecuteSQL.
>
> Should play with batch configuration and commit flag to commit intermediate
> FlowFile.
>
> The out of memory exception makes me believe the full table is retrieved,
> and if it is huge the FlowFile content is very large.
>
>
>
>
> Le jeu. 4 janv. 2024 a 14:37, Pierre Villard <pi...@gmail.com> a
> ecrit :
>>
>> It should be memory efficient so I think this is likely a configuration
aspect of your processor. Can you share the configuration for all properties?
>> As a side note: if NiFi ran out of memory, you'd always want to restart it
because you are never sure what's the state of the JVM after an OOME.
>>
>> Le jeu. 4 janv. 2024 a 17:26, <e-...@gmx.fr> a ecrit :
>>>
>>>
>>> Hello all,
>>>
>>> Who could help me to determine the cpu/memory need for nifi instance to
fetch the data from Postgresql hosted in google ?
>>>
>>> We got this error :
>>> ==> Error : executesql.error.message
>>> Ran out of memory retrieving query results.
>>>
>>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>>> driver Jar to use : postgresql-42.7.1.jar
>>> Java version : jdk-11.0.19
>>>
>>> Table information :
>>> rows number : 14958836
>>> fields number : 20
>>>
>>> Linux Rocky8
>>>
>>> Architecture: x86_64
>>> CPU op-mode(s): 32-bit, 64-bit
>>> Byte Order: Little Endian
>>> CPU(s): 2
>>> On-line CPU(s) list: 0,1
>>> Thread(s) per core: 2
>>> Core(s) per socket: 1
>>> Socket(s): 1
>>> NUMA node(s): 1
>>> Vendor ID: GenuineIntel
>>> BIOS Vendor ID: Google
>>> CPU family: 6
>>> Model: 85
>>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>>> Stepping: 7
>>> CPU MHz: 2800.286
>>> BogoMIPS: 5600.57
>>> Hypervisor vendor: KVM
>>> Virtualization type: full
>>> L1d cache: 32K
>>> L1i cache: 32K
>>> L2 cache: 1024K
>>> L3 cache: 33792K
>>> NUMA node0 CPU(s): 0,1
>>>
>>> Memory : 8GB
>>>
>>> Thanks for you helps
>>>
>>> Minh
Re: Hardware requirement for NIFI instance
Posted by e-...@gmx.fr.
Hello all,
Thanks a lot for the reply.
So for more details.
All the properties for the ExecuteSQL are set by default, except "Set Auto
Commit: false".
The sql command could not be more simple than " **select * from
${db.table.fullname}**"
The nifi version is 1.16.3 and 1.23.2
I have also test the same sql command in the another nifi (8 cores/ 16G Ram)
and it is working.
The result is the avro file with 1.6GB
The detail about the output flowfile :
executesql.query.duration
245118
executesql.query.executiontime
64122
executesql.query.fetchtime
180996
executesql.resultset.index
0
executesql.row.count
14961077
File Size
1.62 GB
Regards
Minh
**Envoy e:** jeudi 4 janvier 2024 a 17:18
**De:** "Matt Burgess" <ma...@apache.org>
**À :** users@nifi.apache.org
**Objet:** Re: Hardware requirement for NIFI instance
If I remember correctly, the default Fetch Size for Postgresql is to
get all the rows at once, which can certainly cause the problem.
Perhaps try setting Fetch Size to something like 1000 or so and see if
that alleviates the problem.
Regards,
Matt
On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com> wrote:
>
> Hello.
>
> I also think the problem is more about the processor, I guess ExecuteSQL.
>
> Should play with batch configuration and commit flag to commit intermediate
> FlowFile.
>
> The out of memory exception makes me believe the full table is retrieved,
> and if it is huge the FlowFile content is very large.
>
>
>
>
> Le jeu. 4 janv. 2024 a 14:37, Pierre Villard <pi...@gmail.com> a
> ecrit :
>>
>> It should be memory efficient so I think this is likely a configuration
aspect of your processor. Can you share the configuration for all properties?
>> As a side note: if NiFi ran out of memory, you'd always want to restart it
because you are never sure what's the state of the JVM after an OOME.
>>
>> Le jeu. 4 janv. 2024 a 17:26, <e-...@gmx.fr> a ecrit :
>>>
>>>
>>> Hello all,
>>>
>>> Who could help me to determine the cpu/memory need for nifi instance to
fetch the data from Postgresql hosted in google ?
>>>
>>> We got this error :
>>> ==> Error : executesql.error.message
>>> Ran out of memory retrieving query results.
>>>
>>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>>> driver Jar to use : postgresql-42.7.1.jar
>>> Java version : jdk-11.0.19
>>>
>>> Table information :
>>> rows number : 14958836
>>> fields number : 20
>>>
>>> Linux Rocky8
>>>
>>> Architecture: x86_64
>>> CPU op-mode(s): 32-bit, 64-bit
>>> Byte Order: Little Endian
>>> CPU(s): 2
>>> On-line CPU(s) list: 0,1
>>> Thread(s) per core: 2
>>> Core(s) per socket: 1
>>> Socket(s): 1
>>> NUMA node(s): 1
>>> Vendor ID: GenuineIntel
>>> BIOS Vendor ID: Google
>>> CPU family: 6
>>> Model: 85
>>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>>> Stepping: 7
>>> CPU MHz: 2800.286
>>> BogoMIPS: 5600.57
>>> Hypervisor vendor: KVM
>>> Virtualization type: full
>>> L1d cache: 32K
>>> L1i cache: 32K
>>> L2 cache: 1024K
>>> L3 cache: 33792K
>>> NUMA node0 CPU(s): 0,1
>>>
>>> Memory : 8GB
>>>
>>> Thanks for you helps
>>>
>>> Minh
Re: Hardware requirement for NIFI instance
Posted by Matt Burgess <ma...@apache.org>.
If I remember correctly, the default Fetch Size for Postgresql is to
get all the rows at once, which can certainly cause the problem.
Perhaps try setting Fetch Size to something like 1000 or so and see if
that alleviates the problem.
Regards,
Matt
On Thu, Jan 4, 2024 at 8:48 AM Etienne Jouvin <la...@gmail.com> wrote:
>
> Hello.
>
> I also think the problem is more about the processor, I guess ExecuteSQL.
>
> Should play with batch configuration and commit flag to commit intermediate FlowFile.
>
> The out of memory exception makes me believe the full table is retrieved, and if it is huge the FlowFile content is very large.
>
>
>
>
> Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com> a écrit :
>>
>> It should be memory efficient so I think this is likely a configuration aspect of your processor. Can you share the configuration for all properties?
>> As a side note: if NiFi ran out of memory, you'd always want to restart it because you are never sure what's the state of the JVM after an OOME.
>>
>> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
>>>
>>>
>>> Hello all,
>>>
>>> Who could help me to determine the cpu/memory need for nifi instance to fetch the data from Postgresql hosted in google ?
>>>
>>> We got this error :
>>> ==> Error : executesql.error.message
>>> Ran out of memory retrieving query results.
>>>
>>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>>> driver Jar to use : postgresql-42.7.1.jar
>>> Java version : jdk-11.0.19
>>>
>>> Table information :
>>> rows number : 14958836
>>> fields number : 20
>>>
>>> Linux Rocky8
>>>
>>> Architecture: x86_64
>>> CPU op-mode(s): 32-bit, 64-bit
>>> Byte Order: Little Endian
>>> CPU(s): 2
>>> On-line CPU(s) list: 0,1
>>> Thread(s) per core: 2
>>> Core(s) per socket: 1
>>> Socket(s): 1
>>> NUMA node(s): 1
>>> Vendor ID: GenuineIntel
>>> BIOS Vendor ID: Google
>>> CPU family: 6
>>> Model: 85
>>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>>> Stepping: 7
>>> CPU MHz: 2800.286
>>> BogoMIPS: 5600.57
>>> Hypervisor vendor: KVM
>>> Virtualization type: full
>>> L1d cache: 32K
>>> L1i cache: 32K
>>> L2 cache: 1024K
>>> L3 cache: 33792K
>>> NUMA node0 CPU(s): 0,1
>>>
>>> Memory : 8GB
>>>
>>> Thanks for you helps
>>>
>>> Minh
Re: Hardware requirement for NIFI instance
Posted by Etienne Jouvin <la...@gmail.com>.
Hello.
I also think the problem is more about the processor, I guess ExecuteSQL.
Should play with batch configuration and commit flag to commit intermediate
FlowFile.
The out of memory exception makes me believe the full table is retrieved,
and if it is huge the FlowFile content is very large.
Le jeu. 4 janv. 2024 à 14:37, Pierre Villard <pi...@gmail.com>
a écrit :
> It should be memory efficient so I think this is likely a configuration
> aspect of your processor. Can you share the configuration for all
> properties?
> As a side note: if NiFi ran out of memory, you'd always want to restart it
> because you are never sure what's the state of the JVM after an OOME.
>
> Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
>
>>
>> Hello all,
>>
>> Who could help me to determine the cpu/memory need for nifi instance to
>> fetch the data from Postgresql hosted in google ?
>>
>> We got this error :
>> ==> Error : executesql.error.message
>> *Ran out of memory retrieving query results.*
>>
>> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
>> driver Jar to use : postgresql-42.7.1.jar
>> Java version : jdk-11.0.19
>>
>> *Table information :*
>> rows number : 14958836
>> fields number : 20
>>
>> Linux Rocky8
>>
>> Architecture: x86_64
>> CPU op-mode(s): 32-bit, 64-bit
>> Byte Order: Little Endian
>> *CPU(s): 2*
>> On-line CPU(s) list: 0,1
>> Thread(s) per core: 2
>> Core(s) per socket: 1
>> Socket(s): 1
>> NUMA node(s): 1
>> Vendor ID: GenuineIntel
>> BIOS Vendor ID: Google
>> CPU family: 6
>> Model: 85
>> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
>> Stepping: 7
>> CPU MHz: 2800.286
>> BogoMIPS: 5600.57
>> Hypervisor vendor: KVM
>> Virtualization type: full
>> L1d cache: 32K
>> L1i cache: 32K
>> L2 cache: 1024K
>> L3 cache: 33792K
>> NUMA node0 CPU(s): 0,1
>>
>> *Memory : 8GB*
>>
>> Thanks for you helps
>>
>> Minh
>>
>
Re: Hardware requirement for NIFI instance
Posted by Pierre Villard <pi...@gmail.com>.
It should be memory efficient so I think this is likely a configuration
aspect of your processor. Can you share the configuration for all
properties?
As a side note: if NiFi ran out of memory, you'd always want to restart it
because you are never sure what's the state of the JVM after an OOME.
Le jeu. 4 janv. 2024 à 17:26, <e-...@gmx.fr> a écrit :
>
> Hello all,
>
> Who could help me to determine the cpu/memory need for nifi instance to
> fetch the data from Postgresql hosted in google ?
>
> We got this error :
> ==> Error : executesql.error.message
> *Ran out of memory retrieving query results.*
>
> The procesor ExecuteSQL has this config : Set Auto Commit ==> false
> driver Jar to use : postgresql-42.7.1.jar
> Java version : jdk-11.0.19
>
> *Table information :*
> rows number : 14958836
> fields number : 20
>
> Linux Rocky8
>
> Architecture: x86_64
> CPU op-mode(s): 32-bit, 64-bit
> Byte Order: Little Endian
> *CPU(s): 2*
> On-line CPU(s) list: 0,1
> Thread(s) per core: 2
> Core(s) per socket: 1
> Socket(s): 1
> NUMA node(s): 1
> Vendor ID: GenuineIntel
> BIOS Vendor ID: Google
> CPU family: 6
> Model: 85
> Model name: Intel(R) Xeon(R) CPU @ 2.80GHz
> Stepping: 7
> CPU MHz: 2800.286
> BogoMIPS: 5600.57
> Hypervisor vendor: KVM
> Virtualization type: full
> L1d cache: 32K
> L1i cache: 32K
> L2 cache: 1024K
> L3 cache: 33792K
> NUMA node0 CPU(s): 0,1
>
> *Memory : 8GB*
>
> Thanks for you helps
>
> Minh
>