How can I improve MySQL Insert Performance using MySQL C++ Connector?

user_677302 0
Viewed 44

I am inserting a lot of records to a MySQL database and I'm trying to achieve decent INSERT performance. I am using MySQL 8.0 with the MySQL Connector C++ 8.0.

In order to determine the fastest way to insert my data, I built a small test program that simply inserts 10000 records into a table. In case it helps, this is the table structure:

CREATE TABLE IF NOT EXISTS Parent (
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
xxuint1 INTEGER UNSIGNED,
xxuint2 INTEGER UNSIGNED,
xxuint3 INTEGER UNSIGNED,
xxuint4 INTEGER UNSIGNED)

I created a structure with values and made an array (tblParent[10000]) of 10,000 of them filled with random numbers. Population of this array was completed before the insertions so I could measure insertion performance only. The function below is my basic Insertion function:

void InsertData(sql::Connection* con)
{
    sql::PreparedStatement* pstmt = NULL;

    try {
        std::string sql = "INSERT INTO Parent("
            "xxuint1, xxuint2, xxuint3, xxuint4"
            ") VALUES (?,?,?,?);";

        pstmt = con->prepareStatement(sql);
        for (size_t i = 0; i < NUM_PARENTS; ++i) {
            pstmt->setUInt(1, tblParent[i].uint1);
            pstmt->setUInt(2, tblParent[i].uint2);
            pstmt->setUInt(3, tblParent[i].uint3);
            pstmt->setUInt(4, tblParent[i].uint4);
            pstmt->execute();
        }
    } catch(sql::SQLException &e) {
        std::cout << "SQLException: " << e.what() << std::endl;
    }

    delete pstmt;
}

In general, when inserting many records, you can achieve much better performance by using multiple values lists:

INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?), ... number_of_records

as opposed to inserting records one at a time. for each number of records:

INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?)

I was assuming that the above code would use the multiple values lists method behind the scenes, but based on my performance measurements I don't believe it is. Here's what I got:
InsertData code with 10,000 records:
~300 records / sec.
Surrounding InsertData with "START TRANSACTION" and "COMMIT":
~8000 records / sec

If I rewrite Insert Data so that I am inserting the data from the array directly into the sql as strings, e.g.

std::string sql = "INSERT INTO Parent("
            "xxuint1, xxuint2, xxint3, xxbigint4"
            ") VALUES (";
for (size_t i = 0; i < NUM_PARENTS; ++i) {
    sql += to_string(tblParent[i].uint1) + ", ";
    sql += to_string(tblParent[i].uint2) + ", ";
    sql += to_string(tblParent[i].uint3) + ", ";
    sql += to_string(tblParent[i].uint4) + "); ";
}

I get similar performance as above.

Performance improved when I explicitly began using the multiple values lists. I adjusted my sql to include " VALUES (?, ?, ?), (?, ?, ?), ..." and this improved performance to ~14,000 records / sec. But the best times came from converting my data to strings, and inserting that data directly into the sql, using the multiple values lists. I got up to ~40,000 records / sec doing this.

However, though the speed is fine, I don't think converting my data to text and inserting this into the sql is an ideal method. How can I optimize my insert speed and still use the pstmt->setUint() method?

mysql c++
2 Answers

I did a presentation some years ago comparing the overhead of different insert methods: https://www.slideshare.net/billkarwin/load-data-fast

Like you, I found that inserting multiple rows in one statement with multiple tuples in the VALUES clause is better. Your first code example does not do that by itself, you have to write the INSERT statement with multiple tuples, as in your second code example.

Avoiding a full transaction per row (i.e. autocommit) helps a lot. That's what you did when you start a transaction before the loop. There's a practical limit for the number of bytes you can insert per transaction before you break your binary logging, so if you have a very large number of inserts, try to do them in batches of, say, no more than 10k rows. I'd probably choose 1000 rows per batch to be safe. That's at least 1/1000th the overhead from transaction.

If you can reduce the number of indexes and insert triggers on the table, that will help. The cost to insert a row is approximately proportional to the number of indexes it needs to update (with some variation because of the change buffer, etc.). A trigger increases the overhead because it likely runs other DML, like inserting to log tables, which means more index writes.

Some MySQL Server tuning options can help reduce overhead a little bit, but at the cost of less data durability.

All these optimizations are dwarfed by switching to LOAD DATA INFILE, which is designed for bulk data loading. You can get something like an order of magnitude improvement this way. But there's still overhead per row due to index writes, and there's still a practical limit on the size of a transaction.

Bill_Karwin_208600

Suggestions to consider for your my.cnf or my.ini [mysqld] section

innodb_change_buffer_max_size=50  # from 25 (percent) set aside in buffer pool
innodb_change_buffering=none  # from all - most likely -
innodb_write_io_threads=64  # for max capacity

to improve rate of inserts per second.

Reference previous answers. dba.stackexchange.com Question 5666 see Rolando's 9/12/2011 details dba.stackexchange.com Question 196715 look down for Rolando's change buffering and cautions related to many aspects of these variables.

user_44206490
Related