Hello again,
I modified your threading code to use a thread pool. Here are my results:
pooled-threading.c:
[EMAIL PROTECTED]:~$ gcc -lmysqlclient_r -lpthread pooled-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.068s
user 0m0.041s
sys 0m0.097s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.088s
user 0m0.036s
sys 0m0.098s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.107s
user 0m0.036s
sys 0m0.100s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.068s
user 0m0.043s
sys 0m0.102s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.067s
user 0m0.044s
sys 0m0.088s
[EMAIL PROTECTED]:~$
poor-threading.c (your original threading):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient_r -lpthread poor-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.118s
user 0m0.026s
sys 0m0.069s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.110s
user 0m0.018s
sys 0m0.049s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.110s
user 0m0.029s
sys 0m0.050s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.109s
user 0m0.029s
sys 0m0.054s
[EMAIL PROTECTED]:~$
no-threading.c (your original as well):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient no-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.096s
user 0m0.023s
sys 0m0.032s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.095s
user 0m0.012s
sys 0m0.038s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.095s
user 0m0.019s
sys 0m0.028s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.094s
user 0m0.015s
sys 0m0.034s
[EMAIL PROTECTED]:~$
I ran these on linux 2.6, my db server is not localhost but another server
on the same network. As you can see the pooled threading (I had 25
connections in the pool) was the fastest as far as real-time. Part of this
might be because it utilizes network bandwith better. It did however use
more actual cpu time than the single threaded implementation, but usually
what you really care about is real-time anyway. Apart from that I'd say that
testing with 100 thread each doing one query is silly as you incur the
thread creation/initialization overhead once per query. A better test is to
have 100 threads do 100 queries in a row or something, vs a single thread
doing 10000 queries. Here are my results for doing that with the same
implementations (I dropped your threading implementation as I think we've
established its not the winner):
pooled-threading2.c:
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.380s
user 0m2.487s
sys 0m5.761s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.373s
user 0m2.602s
sys 0m5.720s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.406s
user 0m2.503s
sys 0m5.670s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.403s
user 0m2.472s
sys 0m5.698s
no-threading2.c (yours modified to do 100*100 instead of just 100):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient no-threading2.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m9.085s
user 0m1.404s
sys 0m3.377s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.961s
user 0m1.436s
sys 0m3.313s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.937s
user 0m1.461s
sys 0m3.253s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.977s
user 0m1.419s
sys 0m3.291s
[EMAIL PROTECTED]:~$
As you can see the same differences get more exaggerated here, there is CPU
overhead to do threading, but the real-time does decrease by using it.
Here is the code for pooled-threading2.c:
/*************** MULTI THREADED EXAMPLE CODE ***************/
/**************** -lmysqlclient_r -lpthread ****************/
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql/mysql.h>
#define MAX 100
#define CONNECTIONS 25
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[CONNECTIONS];
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, pthread_mutex_t *lock, const char *query);
int main(int argc, char **argv) {
int i;
pthread_t pthread[MAX];
db_config dbc;
my_init();
strcpy(dbc.host,"devdb01");
strcpy(dbc.user,"scopeuser");
strcpy(dbc.pass,"gosonicsalpha");
strcpy(dbc.name <http://dbc.name>,"");
dbc.port = 3306;
dbc.socket = NULL;
for(i=0; i<CONNECTIONS; ++i) {
dbm[i].db = db_connect(dbm[i].db, &dbc);
pthread_mutex_init(&dbm[i].lock, NULL);
}
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, (void *)(i%CONNECTIONS));
// wait for threads to finish
for (i = 0; i < MAX; ++i)
pthread_join(pthread[i], 0);
for(i=0; i<CONNECTIONS; ++i) {
pthread_mutex_destroy(&dbm[i].lock);
db_disconnect(dbm[i].db);
}
exit(EXIT_SUCCESS);
}
void *db_pthread(void *arg) {
int i,j;
i = (int)arg;
mysql_thread_init();
for(j=0; j<MAX; ++j)
db_query(dbm[i].db, &(dbm[i].lock), "show status");
mysql_thread_end();
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, pthread_mutex_t *lock, const char *query) {
long ret;
pthread_mutex_lock(lock);
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));
pthread_mutex_unlock(lock);
}
// 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);
pthread_mutex_unlock(lock);
}
// 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));
pthread_mutex_unlock(lock);
}
}
return (ret);
}
On 9/27/05, Lefteris Tsintjelis <[EMAIL PROTECTED]> wrote:
>
> Pooly wrote:
> > 2005/9/27, Lefteris Tsintjelis <[EMAIL PROTECTED]>:
> >
> >>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.
> >
> >
> > You ran several queries with multiple thread, fine, but they are all
> > serialised over one connection, so you get all the overhead of locking
> > and thread-creation, for no advantage... So that's the result
> > expected.
> > (So, yes forthe troll, it's an OS issue, threads creation are somewhat
> > slow on FreeBSD :)
>
> My intention was to avoid the overhead of multiple network
> connections and I didn't expect it to have that much difference. It
> is probably an OS issue a bit here as well, I have to agree with
> that. I will test and see what happens with a few network connections
> but I have a bad feeling about this one also. I don't think it will
> get much better and not even close to a non multi thread
> implementation, but further tests will show. I am just curious if
> anyone could run the same tests in some other OS and maybe compare
> some notes. I looked around but the few things I found are doubtful.
> There are no good performance tests between threads and no threads
> with random access reads and writes, or maybe even better MyISAM and
> InnoDB as well as threads/no threads. This should also be interesting
> due to the locking differences of those two databases among other
> things.
>
> Lefteris
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
