How to improve insert in RDS of AWS?

Question

I have a MySQL db.t2.small in AWS, the server isn't in my country, but I get 70ms. Now I need to insert over 7000 records in a table, but it takes a lot, over 6min. I tried many configurations:

-query_cache_size=16777216
-query_cache_type=1
-net_write_timeout=300
-max_allowed_packet=536870912
-innodb_lock_wait_timeout=3600
-innodb_flush_log_at_trx_commit=2 (Also I tried setting it to 0).

I tried sending the requests 1 by 1 and also using bulk, but the result is the same, almost 20 insert per sec.

What am I missing?

My Java bulk code:

try {
        conn = bd.getConectionAWS();
        PreparedStatement stmt = null;
        if (conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
        try {
            String query = "INSERT INTO CONTRATO(Codigo,Nombre,IdEstado,DesEstado,FechaInicio,IndActivo,Activo,Descripcion,Cliente,Responsable) VALUES(?,?,?,?,?,?,?,?,?,?) \n"
                    + " ON DUPLICATE KEY \n"
                    + " UPDATE Nombre=?, IdEstado=?, idEstado=? ,DesEstado=? ,FechaInicio=?, IndActivo=?,Descripcion=?,Cliente=?,Responsable=?";
            stmt = conn.prepareStatement(query);
            for (Contrato contrato : listaDatos) {
                stmt.setString(1, contrato.getCodigo());
                stmt.setString(2, contrato.getNombre());
                stmt.setInt(3, contrato.getIdEstado());
                stmt.setString(4, contrato.getDesEstado());
                stmt.setTimestamp(5, contrato.getFechaInicio());
                stmt.setString(6, contrato.getIndActivo());
                stmt.setString(7, contrato.getActivo());
                stmt.setString(8, contrato.getDescripcion());
                stmt.setString(9, contrato.getCliente());
                stmt.setString(10, contrato.getResponsable());   
                stmt.setString(11, contrato.getNombre());
                stmt.setInt(12, contrato.getIdEstado());
                stmt.setString(13, contrato.getDesEstado());
                stmt.setTimestamp(14, contrato.getFechaInicio());
                stmt.setString(15, contrato.getIndActivo());
                stmt.setString(16, contrato.getActivo());
                stmt.setString(17, contrato.getDescripcion());
                stmt.setString(18, contrato.getCliente());
                stmt.setString(19, contrato.getResponsable());
                //          stmt.executeUpdate();
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
        }

I took a few things from https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

My "1 by 1" is the same, just replaced addBatch for executeUpdate();.

A SELECT 1; query takes 0.059sec.

Answer 1

There are two ways to efficiently (read: quickly) load 7000 rows.

• LOAD DATA INFILE -- After you have built a 7000-line CSV file.

• "Batch" INSERT -- like INSERT INTO t (a,b) VALUES (1,2),(5,6),(9,2), ...; -- Be cautious about the number of rows. 100 to 1000 is a good range of what to do at a time.

max_allowed_packet=536870912 -- NO, not in a tiny 2GB VM; change to 16M. Other likely settings to check:

key_buffer_size = 10M
innodb_buffer_pool_size = 200M

I assume your tables are InnoDB??

Answer 2

If you are running in RDS (not your own built EC2 instance), you can get great performance from the AWS Data Pipeline tool -- loads directly from S3. It's purpose-built for clean & fast loading.