0

I am writing a multi-threaded application in C++ using Boost threads (pthread). The application spawns 100 threads and each thread does the following task (I am writing a code snippet that will be running in each thread):

try {

    driver = get_driver_instance();
    con = driver->connect(SettingsClass.HostName, \
                  SettingsClass.UserName,SettingsClass.Password);

    // SettingsClass is a global static class whose members 
    // (HostName, UserName, Password, etc) are initialized once
    // before *any* thread is created.

    con->setSchema("MyDatabase");

    driver->threadInit();

    string dbQuery = "select A, B, C from XYZTable where D=?";
    prepStmt = con->prepareStatement(dbQuery);
    prepStmt->setInt(1, 1);
    rSet = prepStmt->executeQuery();

    /* Do Something With rSet, the result set */

    delete rSet;
    delete prepStmt;
    if (con != NULL && !con->isClosed()) {
        con -> close();
    driver->threadEnd();
    delete con;
    } 
    catch (SQLException &e) 
    {
        /* Log Exception */                            
    }

On running the process (the app, as earlier mentioned, i.e. with 100 such threads), I attach gdb midway and observe that more than 40% of the threads have hanged in the read() call. All the backtraces have mysql library functions (vio_read(), etc) and none are from my code as my code does not perform any I/O.

Could anyone point out why is this issue arising. Should I check my code / network or MySQL server configuration? Have I used the C++ connector library properly?

Undo
  • 25,519
  • 37
  • 106
  • 129
Cik
  • 381
  • 5
  • 16
  • 100 threads? That could mean more or less 100mb of memory!!! – Mark Garcia Jan 04 '13 at 07:19
  • first: you are not spawning 100 threads because you think you get data out of the database faster, are you? second: what mysql engine are the tables set to, inno or MyISAM? – Najzero Jan 04 '13 at 07:25
  • I am using InnoDB @Najzero – Cik Jan 04 '13 at 07:53
  • @Mark: Both my servers have 16 gigs of memory each! – Cik Jan 04 '13 at 07:54
  • 3
    @Cik Still, more threads doesn't necessarily mean higher performance. – Mark Garcia Jan 04 '13 at 07:56
  • @Mark, That be true, but I am trying to figure out the bottleneck. Is it in the network, the Database Server or my code ? – Cik Jan 04 '13 at 08:08
  • @Cik you are most likely having issues with locks on that table/rows. Try http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql (the answer with 33 upvotes) and see if the hangs vanish but nonethe less, its not good practice to spawn so many threads to blister the database. Rather fetch them in one thread to a temporary location and let your threads spawn after for computation work (if necc) – Najzero Jan 04 '13 at 08:46
  • @Najzero what about insert queries? – Cik Jan 04 '13 at 09:09
  • innoDB should not lock the complete table during insert but only the rows. In the example above you do not insert but read from your database. If you follow the linked example, the last one given, you will have to start the no-lock option before selecting from your table and end it with `COMMIT;` . The INSERT commands should not bypass the locking mechanism. – Najzero Jan 04 '13 at 09:11

0 Answers0