JMeter JDBC采样器在大型ResultSet上失败

Question

在我的JMeter 3.2运行测试中，我有一个JDBC采样器，其设置如下：

<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>

该查询测试数据库性能并返回非常大的 ResultSet（约 10M 条记录）。我不关心结果本身，所以 resultSetHandler 选项设置为 Count Records，所以我的期望是我会得到一个行计数，ResultSet 将关闭。然而，在这个长查询结束时，当数据库完成时，JMeter会失败，并出现OutOfMemoryError：Java堆空间错误，这很可能是由于Java试图处理可怕的ResultSet。

我可以使用股票JDBC采样器做什么，或者我需要自己写吗？

我不能通过将LIMIT添加到查询等来限制ResultSet，因为它是一个性能查询

P、 这看起来不太好，因为在查看stacktrace之后，很明显，不管＜code＞resultSetHandler的值如何，＜/code＞JMeter代码仍然循环通过整个ResultSet，并试图将整个集合吸入内存

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

这是您需要放入JAR并添加到JMeter/lib/ext2的2个文件。这将为您提供具有“丢弃记录”选项的自定义JDBC采样器。启用该选项后，只会拉出第一条记录，然后关闭结果集。您可以通过注释掉代码来禁用第一行拉出

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");
        }
    }

}

和bean信息文件

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);
    }

}

Answer 2

解决方案包括：

• 特别为JMeter增加JVM堆，并确保您总体上遵循JMeter性能调整技巧
• 修改您的查询以仅返回行数
• 使用JSR223采样器实现您自己的代码来执行SQL查询