Estoy ejecutando lotes de lotes, que contienen declaraciones preparadas insert

public static void main(String... args) throws Exception {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    BufferedReader csv = new BufferedReader(new InputStreamReader(Main.class.getClassLoader().getResourceAsStream("records.csv")));
    String line;
    createConnectionAndPreparedStatement();
    while ((line = csv.readLine()) != null) {
        tupleNum++;
        count++;
        List<String> row = new ArrayList<String>(Arrays.asList(line.split(";")));

        tupleCache.add(row);
        addBatch(row, ps);
        if (count > BATCH_SIZE) {
            count = 0;
            executeBatch(ps);
            tupleCache.clear();
        }
    }
}

protected static void createConnectionAndPreparedStatement() throws SQLException {
    System.out.println("Opening new connection!");
    con = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
    con.setAutoCommit(true);
    con.setAutoCommit(false);
    ps = con.prepareStatement(insertQuery);

    count = 0;
}


private static void executeBatch(PreparedStatement ps) throws SQLException, IOException, InterruptedException {
    try {
        ps.executeBatch();
    } catch (BatchUpdateException bue) {
        if (bue.getMessage() != null && bue.getMessage().contains("Exceeded the memory limit")) {
            // silently close the old connection to free resources
            try {
                con.close();
            } catch (Exception ex) {}
            createConnectionAndPreparedStatement();
            for (List<String> t : tupleCache) {
                addBatch(t, ps);
            }
            // let's retry once
            ps.executeBatch();
        }
    }
    System.out.println("Batch succeeded! -->" + tupleNum );
    con.commit();
    ps.clearWarnings();
    ps.clearBatch();
    ps.clearParameters();
}

private static void addBatch(List<String> tuple, PreparedStatement ps) throws SQLException {
    int sqlPos = 1;
    int size = tuple.size();
    for (int i = 0; i < size; i++) {
        String field = tuple.get(i);
        //log.error(String.format("Setting value at pos [%s] to value [%s]", i, field));
        if (field != null) {
            ps.setString(sqlPos, field);
            sqlPos++;
        } else {
            ps.setNull(sqlPos, java.sql.Types.VARCHAR);
            sqlPos++;
        }
    }
    ps.addBatch();
}

Entonces, en una aplicación independiente, todo está bien y no se producen excepciones después de las inserciones de lotes de 700k. Pero cuando ejecuto el mismo código en el cerdo personalizado StoreFunc después de aproximadamente 6-7k inserciones por lotes, obtengo la siguiente excepción:

java.sql.BatchUpdateException: 112007;Exceeded the memory limit of 20 MB per session for prepared statements. Reduce the number or size of the prepared statements.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1824)

Y solo reiniciar la conexión ayuda. ¿Alguien puede ayudarme con ideas de por qué está sucediendo y cómo solucionarlo?

1
Alexey 25 may. 2017 a las 22:17

2 respuestas

La mejor respuesta

Según su descripción y la información de error, según mi experiencia, creo que el problema fue causado por la configuración sobre la memoria en el lado del servidor de SQL Azure, como los límites de memoria para las conexiones dentro del grupo de recursos del servidor.

Traté de seguir la pista para buscar la explicación específica sobre los límites de memoria de conexión, pero fallé, además del contenido a continuación de aquí.

Memoria de conexión

SQL Server reserva tres búferes de paquetes para cada conexión realizada desde un cliente. Cada búfer se dimensiona de acuerdo con el tamaño de paquete de red predeterminado especificado por el procedimiento almacenado sp_configure. Si el tamaño de paquete de red predeterminado es inferior a 8 KB, la memoria para estos paquetes proviene del grupo de búferes de SQL Server. Si tiene 8 KB o más, la memoria se asigna desde la región MemToLeave de SQL Server.

Y continué buscando packet size & MemToLeave y míralos.

Según la información anterior, supongo que "Excedió el límite de memoria de 20 MB por sesión para las declaraciones preparadas" significa toda la memoria utilizada de conexiones paralelas sobre el grupo de búfer de memoria máxima de la instancia de SQL Azure.

Entonces, sugerí dos soluciones que puedes probar.

  1. Se recomienda reducir el valor de la variable BATCH_SIZE para que la memoria del servidor cueste menos que el tamaño máximo de la agrupación de almacenamiento intermedio de memoria.
  2. Intente escalar su instancia de SQL Azure.

Espero que ayude.


Aquí hay dos nuevas sugerencias.

  1. Realmente no estoy seguro de que el controlador MS jdbc sea compatible con el escenario actual usando Apache Pig para hacer esto como un trabajo ETL en paralelo. Intente utilizar el controlador jdbc jtds en lugar del controlador MS.
  2. Creo que una mejor manera es usar herramientas más profesionales para hacer esto, como sqoop o kettle.
2
Peter Pan 26 may. 2017 a las 18:46

Me encuentro con el mismo problema cuando intenté escribir un marco de datos de pandas en el almacén de datos de Azure SQL. Especifiqué el tamaño de fragmento, asigné al usuario de carga con la clase de recurso más grande. Sin embargo, el problema aún ocurre.

De acuerdo con la documentación, la instrucción INSERT VALUE por defecto solo usa clase de recurso smallrc.

La única solución que se me ocurre es ampliar el DWU, pero no es una solución óptima ya que el costo será muy alto.

0
hui chen 18 jun. 2019 a las 12:51