JMeter JDBC sampler fails on large ResultSet

Question

In my JMeter 3.2 runned test I have a JDBC sampler that is setup like follows:

<JDBCSampler enabled="true" guiclass="TestBeanGUI" testclass="JDBCSampler" testname="query1">
  <stringProp name="dataSource">jdbcConfig_tpcds</stringProp>
  <stringProp name="query">${__FileToString(sql/query1.sql)}</stringProp>
  <stringProp name="queryArguments"/>
  <stringProp name="queryArgumentsTypes"/>
  <stringProp name="queryType">Select Statement</stringProp>
  <stringProp name="resultVariable"/>
  <stringProp name="variableNames"/>
  <stringProp name="queryTimeout"/>
  <stringProp name="resultSetHandler">Count Records</stringProp>
</JDBCSampler>

The query tests db performance and returns very large ResultSet (~10M records). I don't care about result itself so resultSetHandler option is set to Count Records so my expectation is that I will get a row count and ResultSet will be closed. However at the end of this long query when db is done JMeter fails with OutOfMemoryError: Java heap space error which most likely due to Java trying to handle the monstrous ResultSet.

Is there anything I can do using stock JDBC sampler or do I need to write my own?

I cannot limit the ResultSet by adding LIMIT to the query, etc. since it's a performance query

P.S. This does not look good since after looking at the stacktrace it's pretty obvious that regardless of value of resultSetHandler JMeter code still loops through entire ResultSet and tries to suck the complete set into memory

2017/05/05 00:07:42 ERROR - jmeter.threads.JMeterThread: Test failed! java.lang.OutOfMemoryError: Java heap space
    at java.util.Arrays.copyOf(Arrays.java:3332)
    at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124)
    at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448)
    at java.lang.StringBuilder.append(StringBuilder.java:136)
    at java.lang.StringBuilder.append(StringBuilder.java:131)
    at org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.getStringFromResultSet(AbstractJDBCTestElement.java:548)
    at org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.execute(AbstractJDBCTestElement.java:175)
    at org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.sample(JDBCSampler.java:89)

Answer 1

The solutions are in:

• increase JVM heap for JMeter in particular and ensure you are following JMeter performance tuning tips in general
• modify your query to return the number of rows only
• implement your own code for executing SQL queries using i.e. JSR223 Sampler

Answer 2

Here's 2 files you need to put in the JAR and add to JMeter/lib/ext. This will give you custom JDBC sampler that will have "Discard Records" option. With that option enabled only first record will be pulled and then the result set will be closed. You can disable that 1st row pulling by commenting out code

package test.jmeter.protocol.jdbc.sampler;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler;
import org.apache.jmeter.samplers.SampleResult;
import org.apache.jmeter.threads.JMeterVariables;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DiscardResultsJDBCSampler extends JDBCSampler {
    private static final long serialVersionUID = 3528634569296356066L;
    private static final Logger logger = LoggerFactory.getLogger(DiscardResultsJDBCSampler.class);
    static final String SELECT = "Select Statement";
    static final String RS_DISCARD_RECORDS = "Discard Records";

    @Override
    protected byte[] execute(final Connection conn, final SampleResult sample)
            throws SQLException, IOException, UnsupportedOperationException {
        if (SELECT.equals(getQueryType()) && RS_DISCARD_RECORDS.equals(getResultSetHandler()))
            return processCountOnly(conn, sample);
        return super.execute(conn, sample);
    }

    private byte[] processCountOnly(final Connection conn, final SampleResult sample)
            throws SQLException, UnsupportedEncodingException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.setFetchSize(1);
            stmt.setQueryTimeout(getIntegerQueryTimeout());
            ResultSet rs = null;
            try {
                final String query = getQuery();
                logger.info("Calling: " + query);
                rs = stmt.executeQuery(query);
                logger.info("Got result set, processing");
                final ResultSetMetaData meta = rs.getMetaData();
                final StringBuilder sb = new StringBuilder();
                final int numColumns = meta.getColumnCount();
                for (int i = 1; i <= numColumns; i++) {
                    sb.append(meta.getColumnLabel(i));
                    if (i == numColumns) {
                        sb.append('\n');
                    } else {
                        sb.append('\t');
                    }
                }

                final JMeterVariables jmvars = getThreadContext().getVariables();
                final String[] varNames = getVariableNames().split(",");
                final String resultVariable = getResultVariable().trim();
                List<Map<String, Object>> results = null;
                if (resultVariable.length() > 0) {
                    results = new ArrayList<>();
                    jmvars.putObject(resultVariable, results);
                }
                logger.info("Goint to call rs.next()");
                if (rs.next()) {
                    logger.info("Processing first record");
                    Map<String, Object> row = null;
                    for (int i = 1; i <= numColumns; i++) {
                        Object o = rs.getObject(i);
                        if (results != null) {
                            if (row == null) {
                                row = new HashMap<>(numColumns);
                                results.add(row);
                            }
                            row.put(meta.getColumnLabel(i), o);
                        }
                        if (o instanceof byte[]) {
                            o = new String((byte[]) o, ENCODING);
                        }
                        sb.append(o);
                        if (i == numColumns) {
                            sb.append('\n');
                        } else {
                            sb.append('\t');
                        }
                        if (i <= varNames.length) { // i starts at 1
                            final String name = varNames[i - 1].trim();
                            if (name.length() > 0) { // Save the value in the variable if present
                                jmvars.put(name + "_" + 0, o == null ? null : o.toString());
                            }
                        }
                    }
                }
                final String firstRow = sb.toString();
                logger.info("First row results: " + firstRow);
                sample.latencyEnd();
                return firstRow == null ? new byte[0] : firstRow.getBytes(ENCODING);
            } finally {
                logger.info("Done with result set, cleaning up and closing the result set");
                close(rs);
            }
        } finally {
            logger.info("Done with the statement, cleaning up and closing");
            close(stmt);
            logger.info("All finished, exiting");
        }
    }

}

And bean info file

package test.jmeter.protocol.jdbc.sampler;

import java.beans.PropertyDescriptor;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.jmeter.protocol.jdbc.JDBCTestElementBeanInfoSupport;

public class DiscardResultsJDBCSamplerBeanInfo extends JDBCTestElementBeanInfoSupport {

    /**
     * @param beanClass
     */
    public DiscardResultsJDBCSamplerBeanInfo() {
        super(DiscardResultsJDBCSampler.class);

        // Add "Discard Records" option
        final PropertyDescriptor p = property("resultSetHandler");
        String[] tags = (String[]) p.getValue(TAGS);
        tags = ArrayUtils.add(tags, DiscardResultsJDBCSampler.RS_DISCARD_RECORDS);
        p.setValue(TAGS, tags);
    }

}