I have customers that need to pull large (> 500k rows) result sets from a data warehouse (it's on DB2 UDB 9.7, on AIX servers).
I've found that we can only pull about 300-400k rows (depending on the number of columns, or the size of the columns) before the tool slows down to a crawl.
I figured that this was due to Java heap management, so I opened PMR 22184,442,000 about it.
So, on my RHEL 6 machine (which runs 4.1 at the moment), I performed the following steps to enable large pages for the VMM.
- put these lines in /etc/sysctl.conf & invoked sysctl -p
vm.nr_hugepages = 1280
kernel.shmall = 68719476736
kernel.shmmax = 68719476736
- added -Xlp parm to eclipse.ini
- changed these lines in eclipse.ini
Even after enabling large pages like this, and increasing the heap, when the tool pulls > 300k rows, these things seem to happen:
- the query runs and pulls the data into memory.
- "Closing JDBC connection" appears in the status window. This message stays there for a long time, or sometimes forever.
- The eclipse process physical memory footprint grows slowly up to the limit in -Xmx.
- Tens of minutes go by with the tool frozen while the heap grows.
- nothing appears in the workspace folder .metadata/.plugins/org.eclipse.datatools.sqltools.result
- If I am lucky enough to not exhaust the heap, the result set tab eventually appears, and I can use the tool. Also a file shows up in the workspace folder .metadata/.plugins/org.eclipse.datatools.sqltools.result. It's as if the tool won't write the file until it gets done messing with the java heap.
- If I do exhaust the heap, the JVM may crash, or I'll see a java.lang.outofmemory error appear in a dialog box.
Does anyone know how to fix this? Is it because the result set itself is a java object?
Would any changes to -Xgcpolicy:gencon be of use?
My customers are very unhappy with this behavior.
On the Windows x86 platform, they can't pull 100k rows without making the heap grow to 1024M, then the same behavior described above happens.