Hi All, To further debug this issue, I set the fetch size to 50 and Max rows per file to 100 as shown below image.
[image: image.png] I have set the initial heap size to 8GB and Max heap size to 15GB in the bootstrap.conf file. I started monitoring CPU and Memory with a 'top' command before starting the executeSQLRecord processor. *Initially there was about 48GB free memory* and CPU usage of NIFI was little. When the ExecuteSQLRecord is started, free memory size starts to reduce little by little. Until it was reduced to 35-36GB range CPU usage of NIFI was around 25%-30% and NIFI webui worked perfectly fine. But when free memory size reaches the 35GB-36GB range, NIFI's CPU usage increases to 1300% suddenly, and NIFI webui starts not to respond. It seems like Heap is filled within a short time and even though GC tries to clean the heap blocking all other threads, Further, a considerable amount of heap doesn't get cleaned as I noticed. I increased the heap size upto 50GB and the same issue occurs after some time. Note that, even though I have set batch size property to 1, I couldn't see even a single flowfile coming out before NIFI webUI starts not to respond. I have checked the GC.log file and it also shows the large heap growing. I have attached the GC log with this email. Could you please assist me to resolve this issue. Thanks & Regards, Vibhath On Wed, May 12, 2021 at 10:30 AM Vibhath Ileperuma < vibhatharunapr...@gmail.com> wrote: > Hi Matt, > > I have set the fetch size to 20000. I tried with small values like 100. > Got the same issue with the small sizes. > Further I limited the row count fetched by the sql by adding a limit. Then > I didn't got the issue even with large fetch sizes. > > Thank you. > > > On Mon, 10 May 2021, 8:05 pm Matt Burgess, <mattyb...@apache.org> wrote: > >> Vibath, >> >> What is the "Fetch Size" property set to? It looks like PostgreSQL >> will load all results if Fetch Size is set to zero [1]. Try setting it >> to 10000 or something like that, whatever doesn't use too much memory >> but doesn't slow down the performance too much. >> >> Regards, >> Matt >> >> [1] https://jdbc.postgresql.org/documentation/head/query.html >> >> On Mon, May 10, 2021 at 3:41 AM Vibhath Ileperuma >> <vibhatharunapr...@gmail.com> wrote: >> > >> > Hi Vijay, >> > >> > Eventhough that property is set to a postive value, same issue happens. >> Seems like NIFI first loads all the rows to RAM and write to multiple files >> when that property is set to a non-zero value. >> > On the other hand, if this property is set to a very small value, a >> large no of flow files can be generated at once. >> > >> > Thank you. >> > Best regards, >> > Vibhath >> > >> > On Mon, 10 May 2021, 9:00 am Vijay Chhipa, <vchh...@apple.com> wrote: >> >> >> >> Hi Vibhath, >> >> >> >> There is this property on the processor >> >> >> >> Max Rows Per Flow File >> >> >> >> >> >> Per docs: If the value specified is zero, then all rows are returned >> in a single FlowFile. >> >> Which seems to be what is happening in your case. >> >> >> >> >> >> >> >> On May 9, 2021, at 12:11 PM, Vibhath Ileperuma < >> vibhatharunapr...@gmail.com> wrote: >> >> >> >> executeSQLRecord >> >> >> >> >> >
GC.LOG
Description: Binary data