topic Re: tRedshiftOutput Extend Insert breaks in Talend Studio

tRedshiftOutput Extend Insert breaks

Anonymous — Sat, 16 Nov 2024 11:47:28 GMT

In trying to evaluate the option to use Extend Insert to boost performance I've tried different setting Number of Rows per Insert. The test hold a 250k rows and 7 columns in a MySQL, which is not much. And I'm fetching it and inserting it into a Redshift DB.
When changing the Extend Insert's Number of Rows to 5000 rows it breaks when the first 5000 rows are fetched from the source and gives me:
"Exception in component tRedshiftOutput_1
org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
at..."

I'm of course looking for much higher Number of Rows to evaluate if tRedshiftOutput is even usable.
Any suggestions on what's causing this error?
/Geuder
PS. TOS BD 5.3.1 running on Windows

Re: tRedshiftOutput Extend Insert breaks

Anonymous — Fri, 03 Jan 2014 12:02:35 GMT

Hi geuder,
It looks like there is some issue at the DB end processing so much data at a time may be some configuration issue or may be some network bandwidth problem. Also check if there is some restriction on data transmission over certain limit(Firewall issue).

--
vinod_talend

Re: tRedshiftOutput Extend Insert breaks

Anonymous — Fri, 03 Jan 2014 14:40:44 GMT

Thanks you.
I'm currently running this job locally and on a "vacation-grade" network, so that could be the reason.
I'll run the test again when I'm on a sturdier network.
/Geuder

Re: tRedshiftOutput Extend Insert breaks

Anonymous — Sun, 05 Jan 2014 22:06:11 GMT

Hi,
ran the job on a broader bandwidth and got the same crash at 5000 rows per insert.
So that leaves, I guess:
- configuration issue
- data transmission restrictions/limits
Any suggestions on where to look or test these out?
/Geuder

Re: tRedshiftOutput Extend Insert breaks

Anonymous — Sun, 05 Jan 2014 23:04:10 GMT

Any ideas of where to look for more information regarding this?
Perhaps in any of the STL-tables?
STL tables for logging
STL_AGGR
STL_BCAST
STL_CONNECTION_LOG
STL_DDLTEXT
STL_DIST
STL_DELETE
STL_ERROR
STL_EXPLAIN
STL_FILE_SCAN
STL_HASH
STL_HASHJOIN
STL_INSERT
STL_LIMIT
STL_LOAD_COMMITS
STL_LOAD_ERRORS
STL_LOADERROR_DETAIL
STL_MERGE
STL_MERGEJOIN
STL_NESTLOOP
STL_PARSE
STL_PROJECT
STL_QUERY
STL_QUERYTEXT
STL_REPLACEMENTS
STL_S3CLIENT
STL_S3CLIENT_ERROR
STL_SCAN
STL_SESSIONS
STL_SORT
STL_SSHCLIENT_ERROR
STL_STREAM_SEGS
STL_TR_CONFLICT
STL_UNDONE
STL_UNIQUE
STL_UNLOAD_LOG
STL_USERLOG
STL_UTILITYTEXT
STL_VACUUM
STL_WARNING
STL_WINDOW
STL_WLM_ERROR
STL_WLM_QUERY

Re: tRedshiftOutput Extend Insert breaks

Anonymous — Sun, 05 Jan 2014 23:32:02 GMT

The server configuration parameter setting statement_timeout
(Aborts any statement that takes over the specified number of milliseconds)
is set to 0 (meaning: turns off limitation)
So it's not that making the:
"Exception in component tRedshiftOutput_1
org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
at..."