[resolved] OutOfMemoryError: GC overhead limit exceeded in simple MSSqBulkExec jb

[Anonymous]

I'm doing a simple, three component job - tFileInputExcel > tMap > MSSqBulkOutputExec.
The input file has just 11,923 rows (was just written by another TOSDI job) and the tMap has no processing except some row mapping.
The MSSqBulkOutputExec uses a just-retrieved repository definition for the database table and is set to append to the SQL table.
Execution starts out slow and slows to a crawl until it stops at row 5,287 with the following: (The temporary file, mssql_data.txt finishes with 5,206 rows written.)
Exception in thread "main" java.lang.Error: java.lang.OutOfMemoryError: GC overhead limit exceeded
at masterproviderdatabase.dhpl_all_insertlicenseesintompdproviders_0_1.DHPL_All_InsertLicenseesIntoMPDProviders.tFileInputExcel_1Process(DHPL_All_InsertLicenseesIntoMPDProviders.java:3830)
at masterproviderdatabase.dhpl_all_insertlicenseesintompdproviders_0_1.DHPL_All_InsertLicenseesIntoMPDProviders.runJobInTOS(DHPL_All_InsertLicenseesIntoMPDProviders.java:4012)
at masterproviderdatabase.dhpl_all_insertlicenseesintompdproviders_0_1.DHPL_All_InsertLicenseesIntoMPDProviders.main(DHPL_All_InsertLicenseesIntoMPDProviders.java:3877)
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.apache.xmlbeans.impl.schema.SchemaTypeImpl.createUnattachedNode(SchemaTypeImpl.java:1859)
disconnected
at org.apache.xmlbeans.impl.schema.SchemaTypeImpl.createElementType(SchemaTypeImpl.java:1021)
at org.apache.xmlbeans.impl.values.XmlObjectBase.create_element_user(XmlObjectBase.java:893)
at org.apache.xmlbeans.impl.store.Xobj.getUser(Xobj.java:1657)
at org.apache.xmlbeans.impl.store.Xobj.find_element_user(Xobj.java:2062)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getIs(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:269)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:64)
at masterproviderdatabase.dhpl_all_insertlicenseesintompdproviders_0_1.DHPL_All_InsertLicenseesIntoMPDProviders.tFileInputExcel_1Process(DHPL_All_InsertLicenseesIntoMPDProviders.java:2457)
... 2 more
I've done much bigger SQL outputs that this. I tied restarting Windows(7) to clear any cobwebs but no difference.
Any suggestions? Thanks!
UPDATE: I just replaced the MSSqBulkOutputExec then the tMap with a tLogRow, disconnecting them until I only have the FileInputExcel and the tLogRow - it still fails the same! ??
UPDATE: On the Advanced Settings tab of the FileInputExcel I found the Generation Mode field and set it to "Less memory consumed . . . " All set now.

[Anonymous]

Can you screenshot the page where youve added this and share it?

[Anonymous]

How did you manage to solve this problem? I now faced exactly the same task and do not understand how to solve it. Can you suggest a solution to your problem?

[Anonymous]

I had the same problem with an Excel file.
I think the original problem of this ticket came from reading the excel file.
With the latest versions of Talend (In 2013 it may have been different), you have an advanced option to manage memory differently (Component> Advanced option> Generation mode).
That solved my problem.

Version originale.

"J'ai eu le même problème avec un fichier Excel.
Je pense le problème d’origine de ce ticket venait de la lecture du fichier excel.
Avec les dernières versions de Talend (En 2013 c'était peut-être différent), tu as une option avancée permettant de gérer la mémoire différemment (Composant > option avancée > Mode de génération).
Cela a réglé mon problème."

[Anonymous]

Yes, this worked for me, Thank you

[Anonymous]

There is a tparallelize component which connects to 5 subjobs and processing data from core table to staging table in each subjob.

As this is processing huge amount of data from one core table to staging table like 30,00000.

Now it is showing error like below:

we arent allowed to increase JVM.

Could you please provide any other method to optimize query:

tParallelize_1 - GC overhead limit exceeded
java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.util.Arrays.copyOf(Arrays.java:3332)
at java.lang.String.<init>(String.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.replaceParameterMarkers(SQLServerConnection.java:5063)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:234)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2021)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:1967)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1858)
at mdm.daily_delta_9_0.Daily_Delta$1LimitBytesHelper_tMSSqlOutput_4.limitBytePart1(Daily_Delta.java:12266)
at mdm.daily_delta_9_0.Daily_Delta.tMSSqlInput_2Process(Daily_Delta.java:12322)
at mdm.daily_delta_9_0.Daily_Delta$3.run(Daily_Delta.java:8303)

[Anonymous]

There is a tparallelize component which connects to 5 subjobs and processing data from core table to staging table in each subjob.

As this is processing huge amount of data from one core table to staging table like 30,00000.

Now it is showing error like below:

we arent allowed to increase JVM.

Could you please provide any other method to optimize query:

tParallelize_1 - GC overhead limit exceeded
java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.util.Arrays.copyOf(Arrays.java:3332)
at java.lang.String.<init>(String.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.replaceParameterMarkers(SQLServerConnection.java:5063)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:234)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2021)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:1967)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1858)
at mdm.daily_delta_9_0.Daily_Delta$1LimitBytesHelper_tMSSqlOutput_4.limitBytePart1(Daily_Delta.java:12266)
at mdm.daily_delta_9_0.Daily_Delta.tMSSqlInput_2Process(Daily_Delta.java:12322)
at mdm.daily_delta_9_0.Daily_Delta$3.run(Daily_Delta.java:8303)