topic Re: Postgres data reading stream performance & memory used in Talend Studio

Postgres data reading stream performance & memory used

Anonymous — Sat, 16 Nov 2024 09:58:55 GMT

Using Big data enterprise edition. Help me with the below job scenario.
PostgresInput------------------------------------>File
All the ETL transformation was written as SQL query and called inside postgressql input component. SQL output was written to a file in the same job.
For triggering the job, we are using ETL server. Postgres DB was present in different server.
Following questions hits my mind.
a)Ran the job with datasize around 4 gb. Job failed, if i allocate JVM less that 4gb. Talend keeps all the data(4gb) in memory(RAM). It look like pipeline doesn't work in this case, or postgres pipeline streaming doesn't work?
If some bytes of data was read, it should write the data in file and release the buffer. right?
I'm pushing all the load to DB, which was present in different server. ETL server is used only for I/O process. Why it occupies more RAM in ETL server? Am i missing anything? Please suggest.
Thanks

Re: Postgres data reading stream performance & memory used

vapukov — Sun, 19 Mar 2017 20:37:39 GMT

on Advanced settings - checkbox "Use Cursor"
after adjust number of rows to send
for final performance affect 2 "variables":

number of rows
size of data
You can play with both - reduce number of rows for bigger data (BLOB, TEXT column) or increase it

Re: Postgres data reading stream performance & memory used

Anonymous — Mon, 20 Mar 2017 08:08:08 GMT

Thanks.
I can find the cursor option.
We are using Enterprise database Postgres(EDB) instead of open source.What is the difference between these tpostgressql and tpostgresqlplus component?

Re: Postgres data reading stream performance & memory used

vapukov — Mon, 20 Mar 2017 14:51:15 GMT

don't know, I use only OpenSource version of Postgres, so not familiar with EDB features

Re: Postgres data reading stream performance & memory used

Anonymous — Tue, 21 Mar 2017 14:04:13 GMT

Thanks. I have set Cursor as 100000. Found the huge improvement in performance.
With 1GB of JVM, job completed succesfully without any memory issues.
In one case, reading 1 million records from postgres, but i found only 0.2 million were read and job completed successfully. What would be the issue?
Thought because of AUTOCOMMIT set to TRUE. But it was enabled in CODE.