List:General Discussion« Previous MessageNext Message »
From:Lefteris Tsintjelis Date:September 27 2005 4:27pm
Subject:To multi thread or NOT to multi thread?
View as plain text  
Hi,

     What makes me wonder is that the same test, with the code
stripped down, to my surprise, is significantly faster that the
multi threaded one, no matter how many times I run the tests. I am
including the code for both tests I run.
     Since I couldn't find a good example of mutex locking the
following one is something that worked for me. However, I am not
sure if its as optimized as it should be, so I would appreciate an
expert's opinion about this. Is this a good example of mutex
locking? Are there any other better ways for this? Is this an OS
or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box.

Timings:

Thread Safe ON
         0.14 real         0.01 user         0.10 sys
Thread Safe OFF
         0.08 real         0.00 user         0.06 sys

Thnx,

Lefteris Tsinjelis

/*************** MULTI THREADED EXAMPLE CODE ***************/
/**************** -lmysqlclient_r -lpthread ****************/
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql.h>

#define MAX 100

typedef struct db_donfig {
     char host[16];
     char user[16];
     char pass[16];
     char name[16];
     unsigned int port;
     char *socket;
} db_config;

typedef struct db_mutex {
     MYSQL *db;
     pthread_mutex_t lock;
} db_mutex;

db_mutex dbm;

void        *db_pthread(void *arg);
static void     db_die(MYSQL *db, char *fmt, ...);
MYSQL        *db_connect(MYSQL *db, db_config *dbc);
void         db_disconnect(MYSQL *db);
long         db_query(MYSQL *db, const char *query);

int main(int argc, char **argv) {
     int i;
     pthread_t pthread[MAX];
     db_config dbc;

     strcpy(dbc.host,"localhost");
     strcpy(dbc.user,"root");
     strcpy(dbc.pass,"");
     strcpy(dbc.name,"");
     dbc.port = 3306;
     dbc.socket = NULL;

     dbm.db = db_connect(dbm.db, &dbc);
     pthread_mutex_init(&dbm.lock, pthread_mutexattr_default);

     if (!mysql_thread_safe())
         fprintf(stderr, "Thread Safe OFF\n");
     else
         fprintf(stderr, "Thread Safe ON\n");

     pthread_setconcurrency(4);
     // fire up the threads
     for (i = 0; i < MAX; ++i)
         pthread_create(&pthread[i], NULL, db_pthread, NULL);
     // wait for threads to finish
     for (i = 0; i < MAX; ++i)
         pthread_join(pthread[i], 0);

     pthread_mutex_destroy(&dbm.lock);
     db_disconnect(dbm.db);

     exit(EXIT_SUCCESS);
}

void *db_pthread(void *arg) {
     db_query(dbm.db, "show status");
     pthread_exit((void *)0);
}

static void db_die(MYSQL *db, char *fmt, ...) {
     va_list ap;
     va_start(ap, fmt);
     vfprintf(stderr, fmt, ap);
     va_end(ap);
     (void)putc('\n', stderr);
     db_disconnect(db);
     exit(EXIT_FAILURE);
}

MYSQL *db_connect(MYSQL *db, db_config *dbc) {
     if ( !(db = mysql_init(db)) )
         db_die(db, "mysql_init failed: %s", mysql_error(db));
     else {
         if ( !mysql_real_connect(db, dbc->host, dbc->user, dbc->pass,
dbc->name, dbc->port, dbc->socket, 0) )
             db_die(db, "mysql_real_connect failed: %s", mysql_error(db));
     }
     return (db);
}

void db_disconnect(MYSQL *db) {
     if (db)
         mysql_close(db);
}

long db_query(MYSQL *db, const char *query) {
     long ret;

     pthread_mutex_lock(&dbm.lock);
     ret = mysql_query(db, query);
     // if query failed, exit with db error
     if (ret != 0) {
         pthread_mutex_unlock(&dbm.lock);
         db_die(db, "mysql_query failed: %s", mysql_error(db));
     }
     // if query succeeded
     else {
         MYSQL_RES *res;

         res = mysql_store_result(db);
         pthread_mutex_unlock(&dbm.lock);
         // if there are rows
         if (res) {
             MYSQL_ROW row, end_row;
             unsigned int num_fields;

             num_fields = mysql_num_fields(res);
             while ( (row = mysql_fetch_row(res)) )
                 for (end_row = row + num_fields; row < end_row; ++row)
                     ++ret;
             mysql_free_result(res);
         }
         // if there are no rows, should there be any ?
         else {
             // if query was not a SELECT, return with affected rows
             if(mysql_field_count(db) == 0)
                 ret = mysql_affected_rows(db);
             // there should be data, exit with db error
             else
                 db_die(db, "mysql_store_result failed: %s", mysql_error(db));
         }
     }
     return (ret);
}
/************** NO MULTI THREADED EXAMPLE CODE **************/
/********************** -lmysqlclient ***********************/
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql.h>

#define MAX 100

typedef struct db_donfig {
     char host[16];
     char user[16];
     char pass[16];
     char name[16];
     unsigned int port;
     char *socket;
} db_config;

typedef struct db_mutex {
     MYSQL *db;
     pthread_mutex_t lock;
} db_mutex;

db_mutex dbm;

static void     db_die(MYSQL *db, char *fmt, ...);
MYSQL        *db_connect(MYSQL *db, db_config *dbc);
void         db_disconnect(MYSQL *db);
long         db_query(MYSQL *db, const char *query);

int main(int argc, char **argv) {
     int i;
     db_config dbc;

     strcpy(dbc.host,"localhost");
     strcpy(dbc.user,"root");
     strcpy(dbc.pass,"");
     strcpy(dbc.name,"");
     dbc.port = 3306;
     dbc.socket = NULL;

     dbm.db = db_connect(dbm.db, &dbc);

     if (!mysql_thread_safe())
         fprintf(stderr, "Thread Safe OFF\n");
     else
         fprintf(stderr, "Thread Safe ON\n");

     for (i = 0; i < MAX; ++i)
         db_query(dbm.db, "show status");

     db_disconnect(dbm.db);

     exit(EXIT_SUCCESS);
}

static void db_die(MYSQL *db, char *fmt, ...) {
     va_list ap;
     va_start(ap, fmt);
     vfprintf(stderr, fmt, ap);
     va_end(ap);
     (void)putc('\n', stderr);
     db_disconnect(db);
     exit(EXIT_FAILURE);
}

MYSQL *db_connect(MYSQL *db, db_config *dbc) {
     if ( !(db = mysql_init(db)) )
         db_die(db, "mysql_init failed: %s", mysql_error(db));
     else {
         if ( !mysql_real_connect(db, dbc->host, dbc->user, dbc->pass,
dbc->name, dbc->port, dbc->socket, 0) )
             db_die(db, "mysql_real_connect failed: %s", mysql_error(db));
     }
     return (db);
}

void db_disconnect(MYSQL *db) {
     if (db)
         mysql_close(db);
}

long db_query(MYSQL *db, const char *query) {
     long ret;

     ret = mysql_query(db, query);
     // if query failed, exit with db error
     if (ret != 0) {
         db_die(db, "mysql_query failed: %s", mysql_error(db));
     }
     // if query succeeded
     else {
         MYSQL_RES *res;

         res = mysql_store_result(db);
         // if there are rows
         if (res) {
             MYSQL_ROW row, end_row;
             unsigned int num_fields;

             num_fields = mysql_num_fields(res);
             while ( (row = mysql_fetch_row(res)) )
                 for (end_row = row + num_fields; row < end_row; ++row)
                     ++ret;
             mysql_free_result(res);
         }
         // if there are no rows, should there be any ?
         else {
             // if query was not a SELECT, return with affected rows
             if(mysql_field_count(db) == 0)
                 ret = mysql_affected_rows(db);
             // there should be data, exit with db error
             else
                 db_die(db, "mysql_store_result failed: %s", mysql_error(db));
         }
     }
     return (ret);
}


Thread
To multi thread or NOT to multi thread?Lefteris Tsintjelis27 Sep
  • Re: To multi thread or NOT to multi thread?John McCaskey27 Sep
    • Re: To multi thread or NOT to multi thread?Lefteris Tsintjelis27 Sep
      • Re: To multi thread or NOT to multi thread?John McCaskey27 Sep
  • Re: To multi thread or NOT to multi thread?Pooly27 Sep
    • Re: To multi thread or NOT to multi thread?Lefteris Tsintjelis27 Sep
      • Re: To multi thread or NOT to multi thread?John McCaskey27 Sep
        • Re: To multi thread or NOT to multi thread?Lefteris Tsintjelis28 Sep
          • Re: To multi thread or NOT to multi thread?John McCaskey28 Sep
            • Re: To multi thread or NOT to multi thread?Lefteris Tsintjelis28 Sep