High CPU usage on insert to MySQL from Java

Question

I am trying to write an array to mysql database, however this seems to put the cpu to 100% usage.

public void add_data(double[] data) throws SQLException {
        PreparedStatement c = null;
        String sql = "INSERT INTO lowpassdata(data) VALUES(?)";
        c = this.conn.prepareStatement(sql);
        for(int i=0; i<data.length; i++) {
            c.setDouble(1, data[i]);
            c.executeUpdate();
        }
        c.close();
    }

Prepare and close your Statement out of the loop. Do only c.setDouble and c.executeUpdate within the loop. — Selaron, Oct 22 '18 at 11:03
This might speed up things, but nevertheless your CPU will be utilized by 100% during this loop is processed. Why do you think your CPU should be idle? — Selaron, Oct 22 '18 at 11:07
How long is data? If you change data to a scalar, what happens? When you step with the debugger, what line hangs up? — nicomp, Oct 22 '18 at 11:12
Take a look at this question [Multiple Updates in MySQL](https://stackoverflow.com/questions/3432/multiple-updates-in-mysql). You can use a [StringBuilder](https://download.java.net/java/early_access/jdk11/docs/api/java.base/java/lang/StringBuilder.html) to compose your query and execute a single query for multiple updates. — Bakudan, Oct 22 '18 at 11:14
@nicomp data is roughly 26000 long, just the process of writing to the database takes along time and high CPU usage. — ar242, Oct 22 '18 at 11:14

score 1 · Accepted Answer · answered Oct 22 '18 at 11:27

JDBC calls are blocking (synchronous), so give no opportunities for other threads to run while they are running, and you are doing a lot of them in a tight loop - so I would expect to see high CPU usage.

I think that you should try 3 things:

Investigate JDBC batch operations - see the answer to JDBC Batch Insert OutOfMemoryError for an example.
yield() between batches
For MySQL, add

rewriteBatchedStatements=true

to the connection string to greatly increase performance of your batch updates. See MySQL and JDBC with rewriteBatchedStatements=true

Your description is pretty confused: blocking operations will yield the CPU to other threads when they are blocked (waiting on data from the database server), so blocking operations themselves will not lead to high CPU usage, nor will they lead to starvation for other threads. — Mark Rotteveel, Oct 22 '18 at 16:05

High CPU usage on insert to MySQL from Java

1 Answers1