MySQL ODBC Connector - To multi thread or NOT to multi thread?

Author

To multi thread or NOT to multi thread?

Lefteris Tsintjelis

2005-09-27, 1:23 pm

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_de
fault);

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

pthread_setconcurren
cy(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_destro
y(&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(d
b);
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(re
s);
}
// 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(d
b);
// 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(re
s);
}
// 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/mysql? unsub...sie.nctu.edu.tw

John McCaskey

2005-09-27, 1:23 pm

------ =_Part_2622_13225861
.1127839932162
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

I think I can shed a bit of light on the topic. There are several reasons
why your multithreaded code is not a good example and would be slower.

1) locking/unlocking mutexes of course does add *some* overhead
2) you have a single database connection and are passing it around between
threads thus serializing the actual queries, as such the queries are not
multithreaded at all and your code is kind of a silly use of threading --
this combined with #1 above naturally does make your threaded code slower
3) "show status" may not be a good example of threaded performance server
side -- A better test would be a variety of different insert queries or
such, or changes to different tables. Depending on your table type some
locking may occur on inserts that can serialize them if you are inserting
the same data or data on the same data page in the database, more disparate
queries however will actually execute in parallel and should see a speed
increase.

John

On 9/27/05, Lefteris Tsintjelis <lefty@ene.asda.gr> wrote:
>
> 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 <http://dbc.name>,"");
> dbc.port =3D 3306;
> dbc.socket =3D NULL;
>
> dbm.db =3D db_connect(dbm.db, &dbc);
> pthread_mutex_init(&dbm.lock, pthread_mutexattr_de
fault);
>
> if (!mysql_thread_safe(
))
> fprintf(stderr, "Thread Safe OFF\n");
> else
> fprintf(stderr, "Thread Safe ON\n");
>
> pthread_setconcurren
cy(4);
> // fire up the threads
> for (i =3D 0; i < MAX; ++i)
> pthread_create(&pthread[i], NULL, db_pthread, NULL);
> // wait for threads to finish
> for (i =3D 0; i < MAX; ++i)
> pthread_join(pthread
[i], 0);
>
> pthread_mutex_destro
y(&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 =3D 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 =3D mysql_query(db, query);
> // if query failed, exit with db error
> if (ret !=3D 0) {
> pthread_mutex_unlock
(&dbm.lock);
> db_die(db, "mysql_query failed: %s", mysql_error(db));
> }
> // if query succeeded
> else {
> MYSQL_RES *res;
>
> res =3D mysql_store_result(d
b);
> pthread_mutex_unlock
(&dbm.lock);
> // if there are rows
> if (res) {
> MYSQL_ROW row, end_row;
> unsigned int num_fields;
>
> num_fields =3D mysql_num_fields(res
);
> while ( (row =3D mysql_fetch_row(res)
) )
> for (end_row =3D row + num_fields; row < end_row; ++row)
> ++ret;
> mysql_free_result(re
s);
> }
> // 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) =3D=3D 0)
> ret =3D 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 <http://dbc.name>,"");
> dbc.port =3D 3306;
> dbc.socket =3D NULL;
>
> dbm.db =3D db_connect(dbm.db, &dbc);
>
> if (!mysql_thread_safe(
))
> fprintf(stderr, "Thread Safe OFF\n");
> else
> fprintf(stderr, "Thread Safe ON\n");
>
> for (i =3D 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 =3D 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 =3D mysql_query(db, query);
> // if query failed, exit with db error
> if (ret !=3D 0) {
> db_die(db, "mysql_query failed: %s", mysql_error(db));
> }
> // if query succeeded
> else {
> MYSQL_RES *res;
>
> res =3D mysql_store_result(d
b);
> // if there are rows
> if (res) {
> MYSQL_ROW row, end_row;
> unsigned int num_fields;
>
> num_fields =3D mysql_num_fields(res
);
> while ( (row =3D mysql_fetch_row(res)
) )
> for (end_row =3D row + num_fields; row < end_row; ++row)
> ++ret;
> mysql_free_result(re
s);
> }
> // 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) =3D=3D 0)
> ret =3D 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/mysql? unsub...m
ail.com
>
>

------ =_Part_2622_13225861
.1127839932162--

Lefteris Tsintjelis

2005-09-27, 1:23 pm

John McCaskey wrote:
> Hi,
>
> I think I can shed a bit of light on the topic. There are several reasons
> why your multithreaded code is not a good example and would be slower.
>
> 1) locking/unlocking mutexes of course does add *some* overhead

*lots* would probably be a better choice here! :)

> 2) you have a single database connection and are passing it around between
> threads thus serializing the actual queries, as such the queries are not
> multithreaded at all and your code is kind of a silly use of threading --
> this combined with #1 above naturally does make your threaded code slower

But this is what I had in mind though. I wanted to be that way instead of
opening multi threaded connections but, from the looks of it, I guess you
are right and its not really worth the trouble. I have read somewhere that
opening a few connections can be slower but I guess that was probably wrong.

> 3) "show status" may not be a good example of threaded performance server
> side -- A better test would be a variety of different insert queries or
> such, or changes to different tables. Depending on your table type some
> locking may occur on inserts that can serialize them if you are inserting
> the same data or data on the same data page in the database, more disparate
> queries however will actually execute in parallel and should see a speed
> increase.

I have tried with other queries, some random ones as well, and the results
where very similar.

Thnx,

Lefteris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

John McCaskey

2005-09-27, 1:23 pm

------ =_Part_2726_26889006
.1127841113896
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi again,

On 9/27/05, Lefteris Tsintjelis <lefty@ene.asda.gr> wrote:
>
> John McCaskey wrote:
> reasons
>
> *lots* would probably be a better choice here! :)

Well, it certainly depends on how much lock contention there is. In a well
designed multi-threaded app you want to minimize the lock contention so tha=
t
it will be minimal. In your example lock contention is very heavy, so in
that case *lots* may be a better word!

> 2) you have a single database connection and are passing it around betwee=
n
t[color=darkred]
> --
> slower
>
> But this is what I had in mind though. I wanted to be that way instead of
> opening multi threaded connections but, from the looks of it, I guess you
> are right and its not really worth the trouble. I have read somewhere tha=
t
> opening a few connections can be slower but I guess that was probably
> wrong.

Ok, I can understand why you would have it in mind, but it's going to be a
bad idea. Opening multiple connections will of course add some overhead, bu=
t
its a different kind. What you have to ask yourself is whether the queries
you are running in seperate threads are capable of being run in parallel
server side. If so then opening multiple threads will be a performance win.
If the queries you are running will get serialized on the server anyway the=
n
the extra overhead of the additional connections will slow you down. The
current implementation you have however will always be slower than doing it
without threading as thats esentially what happens with your lock contentio=
n
anyway.

> 3) "show status" may not be a good example of threaded performance server
> inserting
> disparate
d[color=darkred]
>
> I have tried with other queries, some random ones as well, and the result=
s
> where very similar.

Yep, your example would have the same results with any query due to the
above mentioned serialization and lock contention you have. However, if you
fixed that and opened one connection per thread (or a pool of say 5
connections that would be shared by 5-n threads) then the type of query and
how it executes server side is going to have a definate impact.

Good luck!

Thnx,

>
> Lefteris
>
>

------ =_Part_2726_26889006
.1127841113896--

Pooly

2005-09-27, 8:24 pm

2005/9/27, Lefteris Tsintjelis <lefty@ene.asda.gr>:
> 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 :)

--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Lefteris Tsintjelis

2005-09-27, 8:24 pm

Pooly wrote:
> 2005/9/27, Lefteris Tsintjelis <lefty@ene.asda.gr>:
>
>
>
> 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/mysql? unsub...sie.nctu.edu.tw

John McCaskey

2005-09-27, 8:24 pm

------ =_Part_4325_20576298
.1127856744897
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello again,

I modified your threading code to use a thread pool. Here are my results:

pooled-threading.c:
johnm@devweb01:~$ gcc -lmysqlclient_r -lpthread pooled-threading.c
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.068s
user 0m0.041s
sys 0m0.097s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.088s
user 0m0.036s
sys 0m0.098s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.107s
user 0m0.036s
sys 0m0.100s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.068s
user 0m0.043s
sys 0m0.102s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.067s
user 0m0.044s
sys 0m0.088s
johnm@devweb01:~$

poor-threading.c (your original threading):
johnm@devweb01:~$ gcc -lmysqlclient_r -lpthread poor-threading.c
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.118s
user 0m0.026s
sys 0m0.069s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.110s
user 0m0.018s
sys 0m0.049s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.110s
user 0m0.029s
sys 0m0.050s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m0.109s
user 0m0.029s
sys 0m0.054s
johnm@devweb01:~$

no-threading.c (your original as well):
johnm@devweb01:~$ gcc -lmysqlclient no-threading.c
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m0.096s
user 0m0.023s
sys 0m0.032s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m0.095s
user 0m0.012s
sys 0m0.038s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m0.095s
user 0m0.019s
sys 0m0.028s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m0.094s
user 0m0.015s
sys 0m0.034s
johnm@devweb01:~$

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 tha=
t
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:
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m3.380s
user 0m2.487s
sys 0m5.761s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m3.373s
user 0m2.602s
sys 0m5.720s
johnm@devweb01:~$ time ./a.out
Thread Safe ON

real 0m3.406s
user 0m2.503s
sys 0m5.670s
johnm@devweb01:~$ 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):
johnm@devweb01:~$ gcc -lmysqlclient no-threading2.c
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m9.085s
user 0m1.404s
sys 0m3.377s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m8.961s
user 0m1.436s
sys 0m3.313s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m8.937s
user 0m1.461s
sys 0m3.253s
johnm@devweb01:~$ time ./a.out
Thread Safe OFF

real 0m8.977s
user 0m1.419s
sys 0m3.291s
johnm@devweb01:~$

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 =3D 3306;
dbc.socket =3D NULL;

for(i=3D0; i<CONNECTIONS; ++i) {
dbm[i].db =3D 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_setconcurren
cy(4);
// fire up the threads
for (i =3D 0; i < MAX; ++i)
pthread_create(&pthread[i], NULL, db_pthread, (void *)(i%CONNECTIONS));
// wait for threads to finish
for (i =3D 0; i < MAX; ++i)
pthread_join(pthread
[i], 0);

for(i=3D0; i<CONNECTIONS; ++i) {
pthread_mutex_destro
y(&dbm[i].lock);
db_disconnect(dbm[i].db);
}
exit(EXIT_SUCCESS);
}

void *db_pthread(void *arg) {
int i,j;
i =3D (int)arg;
mysql_thread_init();

for(j=3D0; 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 =3D 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(l
ock);
ret =3D mysql_query(db, query);
// if query failed, exit with db error
if (ret !=3D 0) {
db_die(db, "mysql_query failed: %s", mysql_error(db));
pthread_mutex_unlock
(lock);
}
// if query succeeded
else {
MYSQL_RES *res;

res =3D mysql_store_result(d
b);
// if there are rows
if (res) {
MYSQL_ROW row, end_row;
unsigned int num_fields;

num_fields =3D mysql_num_fields(res
);
while ( (row =3D mysql_fetch_row(res)
) )
for (end_row =3D row + num_fields; row < end_row; ++row)
++ret;
mysql_free_result(re
s);

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) =3D=3D 0)
ret =3D 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 <lefty@ene.asda.gr> wrote:
>
> Pooly wrote:
>
> 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/mysql? unsub...m
ail.com
>
>

------ =_Part_4325_20576298
.1127856744897--

Lefteris Tsintjelis

2005-09-28, 7:23 am

John McCaskey wrote:
> Hello again,
>
> I modified your threading code to use a thread pool. Here are my results:

Hello,

I modified the thread pool a bit to get rid of that lock ASAP. It is safe
to get rid of that lock right after mysql_store_results.

http://dev.mysql.com/doc/mysql/en/threaded-clients.html

The conclusion I draw from all this is that if you have a very fast
connection to your db its best NOT to use threading at all if your queries
are simple and you expect the results fast.

Here is another interesting thing though, if multi threading is used, I
got the best results by using 2 connections, I am also using dual CPUs
(i%CONNECTIONS). I guess everyone can draw its own conclusions depending
on his needs but for me, and since I only need to do *few, simple, local*
queries, looks like I can "live" without the multi thread over head for
now. The more complex are the queries, slower the connections and higher
the response time is, (the slower the answer you get in general) then
multi threading could be a winner.

-- Clearly the winner ---

Thread Safe OFF
DB Connections: 1, Total Queries: 10000
8.20 real 1.21 user 4.88 sys
Thread Safe OFF
DB Connections: 1, Total Queries: 10000
8.19 real 1.12 user 4.98 sys

pooled-threading2.c (moved that unlock right after mysql_store_results)
:

Thread Safe ON
DB Connections: 1, Threads: 100, Total Queries: 10000
13.20 real 2.91 user 8.07 sys
Thread Safe ON
DB Connections: 1, Threads: 100, Total Queries: 10000
13.11 real 2.69 user 8.12 sys

Thread Safe ON
DB Connections: 2, Threads: 100, Total Queries: 10000
12.70 real 2.70 user 8.08 sys
Thread Safe ON
DB Connections: 2, Threads: 100, Total Queries: 10000
12.74 real 2.89 user 7.89 sys

Thread Safe ON
DB Connections: 3, Threads: 100, Total Queries: 10000
12.81 real 2.87 user 8.01 sys
Thread Safe ON
DB Connections: 3, Threads: 100, Total Queries: 10000
12.90 real 2.91 user 8.04 sys

Thread Safe ON
DB Connections: 4, Threads: 100, Total Queries: 10000
12.88 real 2.89 user 8.03 sys
Thread Safe ON
DB Connections: 4, Threads: 100, Total Queries: 10000
12.89 real 2.95 user 8.00 sys

Thread Safe ON
DB Connections: 5, Threads: 100, Total Queries: 10000
13.05 real 2.68 user 8.34 sys
Thread Safe ON
DB Connections: 5, Threads: 100, Total Queries: 10000
12.92 real 2.84 user 8.11 sys

Thread Safe ON
DB Connections: 10, Threads: 100, Total Queries: 10000
13.05 real 3.00 user 8.00 sys
Thread Safe ON
DB Connections: 10, Threads: 100, Total Queries: 10000
12.98 real 2.79 user 8.18 sys

Thread Safe ON
DB Connections: 15, Threads: 100, Total Queries: 10000
13.08 real 2.72 user 8.30 sys
Thread Safe ON
DB Connections: 15, Threads: 100, Total Queries: 10000
13.08 real 2.71 user 8.31 sys

Thread Safe ON
DB Connections: 20, Threads: 100, Total Queries: 10000
13.18 real 2.94 user 8.16 sys
Thread Safe ON
DB Connections: 20, Threads: 100, Total Queries: 10000
13.17 real 2.95 user 8.12 sys
------------------------------------------------------------------
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql.h>

#define MAX 100
#define CONNECTIONS 2

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(vo
id *arg);
static void db_die(MYSQL *db, char *fmt, ...);
MYSQL *db_connect(M
YSQL *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;

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

if (!mysql_thread_safe(
))
fprintf(stderr, "Thread Safe OFF\n");
else
fprintf(stderr, "Thread Safe ON\n");
fprintf(stdout, "DB Connections: %d, Threads: %d, Total Queries: %d\n", CONNECTIONS, MAX, MAX * MAX);

// pre initialize connections and locks
for (i = 0; i < CONNECTIONS; ++i) {
dbm[i].db = db_connect(dbm[i].db, &dbc);
pthread_mutex_init(&dbm[i].lock, NULL);
}

// pthread_setconcurren
cy(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_destro
y(&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;

// lock must be called before mysql_query
pthread_mutex_lock(l
ock);
ret = mysql_query(db, query);
// if query failed, exit with db error
if (ret != 0) {
// Get rid of the lock first
pthread_mutex_unlock
(lock);
db_die(db, "mysql_query failed: %s", mysql_error(db));
}
// if query succeeded
else {
MYSQL_RES *res;

res = mysql_store_result(d
b);
// Get rid of the lock ASAP, only safe after mysql_store_result
pthread_mutex_unlock
(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(re
s);
}
// 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/mysql? unsub...sie.nctu.edu.tw

John McCaskey

2005-09-28, 1:23 pm

------=_Part_3581_3786155.1127925209248
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

On 9/28/05, Lefteris Tsintjelis <lefty@ene.asda.gr> wrote:
>
> John McCaskey wrote:
> results:
>
> Hello,
>
> I modified the thread pool a bit to get rid of that lock ASAP. It is safe
> to get rid of that lock right after mysql_store_results.

I wasnt quite sure about the safety of the error output that occured later
on, but it is true for sure in the case of no errors occuring releasing
sooner is safe. Good optimization.

http://dev.mysql.com/doc/mysql/en/threaded-clients.html
>
> The conclusion I draw from all this is that if you have a very fast
> connection to your db its best NOT to use threading at all if your querie=
s
> are simple and you expect the results fast.

I dont think that is neccesarily true, it will also depend on what type of
queries. But for your situation it does appear true.

Here is another interesting thing though, if multi threading is used, I
> got the best results by using 2 connections, I am also using dual CPUs
> (i%CONNECTIONS). I guess everyone can draw its own conclusions depending
> on his needs but for me, and since I only need to do *few, simple, local*
> queries, looks like I can "live" without the multi thread over head for
> now. The more complex are the queries, slower the connections and higher
> the response time is, (the slower the answer you get in general) then
> multi threading could be a winner.
>
> -- Clearly the winner ---

<snip>

Given your connections are local and you have 2 cpus Id be interested to se=
e
4 connections, 4 threads, 2500 queries per thread. Opening many more thread=
s
than you have cpus is always going to be a bad idea unless there is some
sort of blocking io operation (like waiting for the network, or a disk read=
)
or something occuring in each thread so that alot of the time spent is just
idle. However, with just 2-4 threads and connections you should be able to
execute the queries in parallell as you actually have the hardware to do so=
,
while avoiding alot of the thread overhead that you saw with 100. You will
likely still go with single threaded for your situation, but it would be
very interesting to me if we could see the scenario I just outlined posted
to compare to your last posted results.

John

------=_Part_3581_3786155.1127925209248--

Lefteris Tsintjelis

2005-09-28, 8:24 pm

John McCaskey wrote:
>
> I wasnt quite sure about the safety of the error output that occured later
> on, but it is true for sure in the case of no errors occuring releasing
> sooner is safe. Good optimization.

I have no problems here using it like that, or any errors or core dumps or
connectivity problems, but then again I run those tests local.

>
> Given your connections are local and you have 2 cpus Id be interested to see
> 4 connections, 4 threads, 2500 queries per thread. Opening many more threads
> than you have cpus is always going to be a bad idea unless there is some
> sort of blocking io operation (like waiting for the network, or a disk read)
> or something occuring in each thread so that alot of the time spent is just
> idle. However, with just 2-4 threads and connections you should be able to
> execute the queries in parallell as you actually have the hardware to do so,
> while avoiding alot of the thread overhead that you saw with 100. You will
> likely still go with single threaded for your situation, but it would be
> very interesting to me if we could see the scenario I just outlined posted
> to compare to your last posted results.

You must have read my mind! I modified it a bit more to allow just that and
already performed those tests this morning and you are very right. It appears
that adding one more thread per CPU doesn't add significantly much over head
so I would probably go with a total of 2 threads per CPU just to be on the
safe side for the cases you have described. The optimal and very logical,
under normal conditions, is to have one thread per CPU and it does make a lot
of sense.
--------------------- Local database -----------------
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 5000, Total Queries: 10000
12.74 real 2.48 user 8.28 sys
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 5000, Total Queries: 10000
12.61 real 2.74 user 7.90 sys

Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 2500, Total Queries: 10000
12.83 real 2.63 user 8.23 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 2500, Total Queries: 10000
12.80 real 2.84 user 8.02 sys

Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 1250, Total Queries: 10000
13.06 real 2.79 user 8.31 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 1250, Total Queries: 10000
12.97 real 2.76 user 8.18 sys

Now, watch those multi threaded queries really take off when the same tests
are performed on a remote database with average ping time of 11ms and since
its a remote one, I performed 1000 queries tests but the diffrence is very
obvious! Clearly, multi threading is the winner here.

---------------- Remote database with average ping of 11 ms -----------------
Thread Safe OFF
DB Connections: 1, Total Queries: 1000
32.70 real 0.07 user 0.54 sys
Thread Safe OFF
DB Connections: 1, Total Queries: 1000
33.23 real 0.11 user 0.51 sys

Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 500, Total Queries: 1000
19.47 real 0.26 user 0.88 sys
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 500, Total Queries: 1000
20.52 real 0.28 user 0.86 sys

Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 250, Total Queries: 1000
13.48 real 0.24 user 0.89 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 250, Total Queries: 1000
13.05 real 0.25 user 0.90 sys

Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 125, Total Queries: 1000
10.39 real 0.28 user 0.85 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 125, Total Queries: 1000
10.06 real 0.25 user 0.89 sys

Lefteris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links