Month: July 2015

Resolved: rundeck out of memory errors

~ tatroc2015 ~ Leave a comment

I recently resolved an issue where rundeck would fail to run a Talend ETL job. Because rundeck and Talend both use the Java JVM I was unsure where the message was bubbling up from. After increasing -Xmx in the Talend job and rerunning the job still failed. I then increased -Xmx in /etc/rundeck/profile which resulted in the job completing successfully.

Below is the error message I received in the rundeck console. 

Failed dispatching to node localhost: java.lang.OutOfMemoryError: Java heap space
14:10:16			Execution failed: 8155: [Workflow result: , step failures: {1=Dispatch failed on 1 nodes: [localhost: Unknown: java.lang.OutOfMemoryError: Java heap space]}, Node failures: {localhost=[Unknown: java.lang.OutOfMemoryError: Java heap space]}, flow control: Continue, status: failed

Increased the JVM memory settings from 1024m to 6144m.
vim /etc/rundeck/profile

RDECK_JVM="$RDECK_JVM -Xmx6144m -Xms256m -XX:MaxPermSize=256m -server"
#RDECK_JVM="$RDECK_JVM -Xmx1024m -Xms256m -XX:MaxPermSize=256m -server"

How to handle bulk UPDATE statements in large tables

~ tatroc2015 ~ Leave a comment

Sometimes we are required to do mass database UPDATES. On very large tables this can become a problem. For example, I recently had a table with 80 million records in it which required a mass UPDATE. Instead of running one large UPDATE statement it is better to break the transactions into smaller batches. This will reduce the locking incurred on the table in question. 

WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
     UPDATE TOP(1000) dbo.tbl_shipments
     SET ship_zone = 99999
     WHERE ship_zone = 0;
    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:00.200'
  END -- WHILE
GO

There is a really good article at http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes which goes into depth about the issue. Although this article is focused on deletes it also applies to UPDATE statements.