List:General Discussion« Previous MessageNext Message »
From:Lefteris Tsintjelis Date:September 28 2005 9:49am
Subject:Re: To multi thread or NOT to multi thread?
View as plain text  
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);
}

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