descargar - Rendimiento de las instrucciones de inserción de MySQL en Java: instrucciones preparadas en modo por lotes frente a una sola inserción con múltiples valores

mysql-connector-java-5.1.31-bin.jar descargar

Question

Estoy diseñando una MySQL datos MySQL que necesita manejar alrededor de 600 inserciones de fila por segundo en varias tablas InnoDB. Mi implementación actual usa declaraciones preparadas sin lote. Sin embargo, escribir en los cuellos de botella de la MySQL datos MySQL y el tamaño de mi cola aumenta con el tiempo.

La implementación está escrita en Java, no sé la versión de la mano. Utiliza el conector Java de MySQL . Necesito ver el cambio a JDBC mañana. Supongo que estos son dos paquetes de conectores diferentes.

He leído los siguientes temas sobre el tema:

• Optimizar las inserciones de MySQL para manejar una secuencia de datos
• MyISAM versus InnoDB
• Insertar datos binarios en MySQL (sin PreparedStatement)

y desde el sitio mysql:

• http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Mis preguntas son:

• ¿Alguien tiene consejos o experiencia sobre las diferencias de rendimiento al usar INSERT con declaraciones preparadas en modo por lotes frente a usar una sola INSERT con múltiples VALORES?

• ¿Cuáles son las diferencias de rendimiento entre el conector MySQL Java y JDBC ? ¿Debo usar uno u otro?

• Las tablas son para propósitos de archivo, y verán ~ 90% de escritura a ~ 10% de lectura (tal vez incluso menos). Estoy usando InnoDB. ¿Es esta la elección correcta sobre MyISAM?

Gracias de antemano por su ayuda.

Answer 1

¿Tiene algún activador en alguna de las tablas afectadas? Si no, 600 inserciones por segundo no se parecen mucho.

La funcionalidad de inserción de lotes de JDBC emitirá la misma instrucción varias veces en la misma transacción, mientras que SQL de valores múltiples comprimirá todos los valores en una sola declaración. En caso de declaración de valores múltiples, deberá construir el SQL de forma dinámica y esto podría ser una sobrecarga en términos de más código, más memoria, mecanismo de protección de inyección de SQL, etc. Primero pruebe la funcionalidad de lote normal, para su carga de trabajo, no debería ser un problema

Si no recibe los datos en lotes, considere la posibilidad de agruparlos antes de insertarlos. Usamos una Cola en un hilo separado para implementar una disposición Productor-Consumidor. En esto, retenemos inserciones hasta que haya transcurrido cierto tiempo o el tamaño de la cola haya cruzado un umbral.

En caso de que desee que se notifique al productor acerca de una inserción exitosa, se requiere un poco más de plomería.

A veces, simplemente bloqueando el hilo puede ser más sencillo y práctico.

if(System.currentTimeMills()-lastInsertTime>TIME_THRESHOLD || queue.size()>SIZE_THRESHOLD) {
    lastInsertTime=System.currentTimeMills();
    // Insert logic
    } else {
    // Do nothing OR sleep for some time OR retry after some time. 
    }

Answer 2

JDBC es simplemente un estándar de Java SE de acceso a la base de datos que ofrece las interfaces estándar por lo que no está realmente obligado a una implementación específica de JDBC. El conector MySQL Java (Connector / J) es una implementación de las interfaces JDBC solo para bases de datos MySQL. Sin experiencia, estoy involucrado en un proyecto que utiliza una gran cantidad de datos utilizando MySQL, y preferimos MyISAM para los datos que se pueden generar: permite lograr un rendimiento mucho mayor al perder transacciones, pero en general, MyISAM es más rápido, pero InnoDB es más confiable.

También me pregunté por el rendimiento de las declaraciones INSERT hace aproximadamente un año, y encontré el siguiente código de prueba antiguo en el estante de mi código (lo siento, es un poco complejo y está un poco fuera del alcance de tu pregunta). El siguiente código contiene ejemplos de 4 formas de insertar los datos de prueba:

• solo INSERT s;
• INSERT s por INSERT ;
• INSERTO a granel manual (nunca lo use, es peligroso);
• y finalmente preparado a granel INSERT ).

Utiliza TestNG como el corredor y utiliza algún legado de código personalizado como:

• El método runWithConnection() asegura que la conexión se cierra o se vuelve a conectar después de que se ejecuta la devolución de llamada (pero el siguiente código no utiliza una estrategia confiable para cerrar la declaración, incluso sin try / reducir el código);
• IUnsafeIn<T, E extends Throwable> - una interfaz de devolución de llamada personalizada para los métodos que aceptan un único parámetro pero que potencialmente arroja una excepción de tipo E, como: void handle(T argument) throws E; .

package test;

import test.IUnsafeIn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import static java.lang.String.format;
import static java.lang.String.valueOf;
import static java.lang.System.currentTimeMillis;

import core.SqlBaseTest;
import org.testng.annotations.AfterSuite;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public final class InsertVsBatchInsertTest extends SqlBaseTest {

    private static final int ITERATION_COUNT = 3000;

    private static final String CREATE_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS ttt1 (c1 INTEGER, c2 FLOAT, c3 VARCHAR(5)) ENGINE = InnoDB";
    private static final String DROP_TABLE_QUERY = "DROP TABLE ttt1";
    private static final String CLEAR_TABLE_QUERY = "DELETE FROM ttt1";

    private static void withinTimer(String name, Runnable runnable) {
        final long start = currentTimeMillis();
        runnable.run();
        logStdOutF("%20s: %d ms", name, currentTimeMillis() - start);
    }

    @BeforeSuite
    public void createTable() {
        runWithConnection(new IUnsafeIn<Connection, SQLException>() {
            @Override
            public void handle(Connection connection) throws SQLException {
                final PreparedStatement statement = connection.prepareStatement(CREATE_TABLE_QUERY);
                statement.execute();
                statement.close();
            }
        });
    }

    @AfterSuite
    public void dropTable() {
        runWithConnection(new IUnsafeIn<Connection, SQLException>() {
            @Override
            public void handle(Connection connection) throws SQLException {
                final PreparedStatement statement = connection.prepareStatement(DROP_TABLE_QUERY);
                statement.execute();
                statement.close();
            }
        });
    }

    @BeforeTest
    public void clearTestTable() {
        runWithConnection(new IUnsafeIn<Connection, SQLException>() {
            @Override
            public void handle(Connection connection) throws SQLException {
                final PreparedStatement statement = connection.prepareStatement(CLEAR_TABLE_QUERY);
                statement.execute();
                statement.close();
            }
        });
    }

    @Test
    public void run1SingleInserts() {
        withinTimer("Single inserts", new Runnable() {
            @Override
            public void run() {
                runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                    @Override
                    public void handle(Connection connection) throws SQLException {
                        for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                            final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
                            statement.setInt(1, i);
                            statement.setFloat(2, i);
                            statement.setString(3, valueOf(i));
                            statement.execute();
                            statement.close();
                        }
                    }
                });
            }
        });
    }

    @Test
    public void run2BatchInsert() {
        withinTimer("Batch insert", new Runnable() {
            @Override
            public void run() {
                runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                    @Override
                    public void handle(Connection connection) throws SQLException {
                        final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
                        for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                            statement.setInt(1, i);
                            statement.setFloat(2, i);
                            statement.setString(3, valueOf(i));
                            statement.addBatch();
                        }
                        statement.executeBatch();
                        statement.close();
                    }
                });
            }
        });
    }

    @Test
    public void run3DirtyBulkInsert() {
        withinTimer("Dirty bulk insert", new Runnable() {
            @Override
            public void run() {
                runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                    @Override
                    public void handle(Connection connection) throws SQLException {
                        final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
                        for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                            if ( i != 0 ) {
                                builder.append(",");
                            }
                            builder.append(format("(%s, %s, ''%s'')", i, i, i));
                        }
                        final String query = builder.toString();
                        final PreparedStatement statement = connection.prepareStatement(query);
                        statement.execute();
                        statement.close();
                    }
                });
            }
        });
    }

    @Test
    public void run4SafeBulkInsert() {
        withinTimer("Safe bulk insert", new Runnable() {
            @Override
            public void run() {
                runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                    private String getInsertPlaceholders(int placeholderCount) {
                        final StringBuilder builder = new StringBuilder("(");
                        for ( int i = 0; i < placeholderCount; i++ ) {
                            if ( i != 0 ) {
                                builder.append(",");
                            }
                            builder.append("?");
                        }
                        return builder.append(")").toString();
                    }

                    @SuppressWarnings("AssignmentToForLoopParameter")
                    @Override
                    public void handle(Connection connection) throws SQLException {
                        final int columnCount = 3;
                        final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
                        final String placeholders = getInsertPlaceholders(columnCount);
                        for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                            if ( i != 0 ) {
                                builder.append(",");
                            }
                            builder.append(placeholders);
                        }
                        final int maxParameterIndex = ITERATION_COUNT * columnCount;
                        final String query = builder.toString();
                        final PreparedStatement statement = connection.prepareStatement(query);
                        int valueIndex = 0;
                        for ( int parameterIndex = 1; parameterIndex <= maxParameterIndex; valueIndex++ ) {
                            statement.setObject(parameterIndex++, valueIndex);
                            statement.setObject(parameterIndex++, valueIndex);
                            statement.setObject(parameterIndex++, valueIndex);
                        }
                        statement.execute();
                        statement.close();
                    }
                });
            }
        });
    }

}

Eche un vistazo a los métodos anotados con la anotación @Test: realmente ejecutan las INSERT . También, por favor, eche un vistazo a la constante CREATE_TABLE_QUERY : en el código fuente usa InnoDB produciendo los siguientes resultados en mi máquina con MySQL 5.5 instalado (MySQL Connector / J 5.1.12):

InnoDB
Single inserts: 74148 ms
Batch insert: 84370 ms
Dirty bulk insert: 178 ms
Safe bulk insert: 118 ms

Si cambia CREATE_TABLE_QUERY InnoDB a MyISAM, verá un aumento significativo en el rendimiento:

MyISAM
Single inserts: 604 ms
Batch insert: 447 ms
Dirty bulk insert: 63 ms
Safe bulk insert: 26 ms

Espero que esto ayude.

UPD:

Por cuarta vez, debe personalizar correctamente max_allowed_packet en mysql.ini (la sección [mysqld] ) para que sea lo suficientemente grande como para admitir paquetes realmente grandes.

Answer 3

Sé que este hilo es bastante antiguo, pero pensé que mencionaría que si agrega "rewriteBatchedStatements = true" a la url jdbc al usar mysql, puede generar enormes ganancias de rendimiento al usar sentencias batched.