CODEWITHSUNDEEP
c++mysqldatabasemultithreading
Andy
Andy

Reputation: 309

Insert Data into Single MySQL Table Concurrently by Multi-thread in C++

I am doing an experiment that concurrently insert data into a MySQL table by multi-thread.

Here is partial code in C++.

    bool query_thread(const char* cmd, MYSQL* con) {
      if( !query( cmd, con ) ) {
        return 0;
      }
      return 1;
    }
    int main() {
          ........
      if(mysql_query(m_con, "CREATE TABLE tb1 (model INT(32), handle INT(32))") != 0) {
        return 0;
      }
        
      thread thread1(query_thread, "INSERT INTO tb1 VALUES (1,1)", m_con);
      thread thread2(query_thread, "INSERT INTO tb1 VALUES (2,2)", m_con);
      thread thread3(query_thread, "INSERT INTO tb1 VALUES (3,3)", m_con);
      thread1.join();
      thread2.join();
      thread3.join();
    }

But the MySQL error message is issued.

error cmd: INSERT INTO tb1 VALUES (1,1)

Lost connection to MySQL server during query

Segmentation fault

My questions are as following.

  1. Is it because the MySQL cannot accept concurrently insertion? Or bad use of multi-thread.
  2. By multi-thread insertion as above, does it help to speed up the program? I understand the best way are multiple insert per query and LOAD DATA INFILE. But I just want to know if this way can help.

Upvotes: 2

Views: 2644

Answers (1)

Dima Tisnek
Dima Tisnek

Reputation: 11781

Each thread must have:

  • own database connection
  • own transaction
  • own cursor

This, however will not make your inserts much faster. In short, the innodb log (journal) is essentially serial which limits server total insert rate. Read mysql performance blog (percona / mariadb) for details. Certainly there are parameters to tweak and there seem to have been advances with recently.

Upvotes: 5

Related Questions