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);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
