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(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;
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_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;
// lock must be called before mysql_query
pthread_mutex_lock(lock);
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(db);
// 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(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]
