[mysql] 24.2.15. How to Make a Threaded Client

1318747102_mysql_multi_thread_ex.c.txt

1318747102_mysql_multi_thread_ex.c.txt

예제를 찾다 mysql사이트까지 가서 찾았네요. 보다 좋은 예제가 있으면 고유 하시죠^^

24.2.15.How to Make a Threaded Client

The client library is almost thread-safe. The biggest problem is that the subroutines in net.c that read from sockets are not interrupt safe. This was done with the thought that you might want to have your own alarm that can break a long read to a server. If you install interrupt handlers for the SIGPIPE interrupt, the socket handling should be thread-safe.

To avoid aborting the program when a connection terminates, MySQL blocks SIGPIPE on the first call to mysql_server_init(), mysql_init(), or mysql_connect(). If you want to use your own SIGPIPE handler, you should first call mysql_server_init() and then install your handler.

In the older binaries we distribute on our Web site (http://www.mysql.com/), the client libraries are not normally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library.

To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys, -lmystrings, and -ldbug libraries and the net_serv.o code that the server uses.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use this. See Section24.2, “MySQL C API”. In this case, you don't have to worry about the net_serv.o object file or the other MySQL libraries.

When using a threaded client and you want to use timeouts and interrupts, you can make great use of the routines in the thr_alarm.c file. If you are using routines from the mysys library, the only thing you must remember is to call my_init() first! See Section24.2.11, “C API Threaded Function Descriptions”.

All functions except mysql_real_connect() are by default thread-safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (The notes below for mysql_real_connect() actually apply to mysql_connect() as well, but because mysql_connect() is deprecated, you should be using mysql_real_connect() anyway.)

To make mysql_real_connect() thread-safe, you must recompile the client library with this command:

shell> ./configure --enable-thread-safe-client 

This creates a thread-safe client library libmysqlclient_r. (Assuming that your OS has a thread-safe gethostbyname_r() function.) This library is thread-safe per connection. You can let two threads share the same connection with the following caveats:

• Two threads can't send a query to the MySQL server at the same time on the same connection. In particular, you have to ensure that between a mysql_query() and mysql_store_result() no other thread is using the same connection.

• Many threads can access different result sets that are retrieved with mysql_store_result().

• If you use mysql_use_result, you have to ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result().

• If you want to use multiple threads on the same connection, you must have a mutex lock around your mysql_query() and mysql_store_result() call combination. Once mysql_store_result() is ready, the lock can be released and other threads may query the same connection.

• If you program with POSIX threads, you can use pthread_mutex_lock() and pthread_mutex_unlock() to establish and release a mutex lock.

You need to know the following if you have a thread that is calling MySQL functions which did not create the connection to the MySQL database:

When you call mysql_init() or mysql_connect(), MySQL creates a thread-specific variable for the thread that is used by the debug library (among other things).

If you call a MySQL function, before the thread has called mysql_init() or mysql_connect(), the thread does not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.

To get things to work smoothly you have to do the following:

1. Call my_init() at the start of your program if it calls any other MySQL function before calling mysql_real_connect().

2. Call mysql_thread_init() in the thread handler before calling any MySQL function.

3. In the thread, call mysql_thread_end() before calling pthread_exit(). This frees the memory used by MySQL thread-specific variables.

You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. In most cases this is because you haven't included the thread libraries on the link/compile line.

Previous / Next / Up / Table of Contents

User Comments

Posted by Matthias Urlichs on May 14 2004 5:22pm

[Delete] [Edit]

Sharing one connection between threads doesn't make much sense anyway. *Passing* it, yes, for instance by way of a connection pool. But *sharing*? MySQL connections are not resource intense: just open a second one.

Posted by Season Sze on March 16 2005 5:27pm

[Delete] [Edit]

In fact, I found plenty of connections of mysql client doesn't make the database faster at all. Try to use as less as u can in your program

Posted by Lefteris Tsintjelis on October 7 2005 10:48am

[Delete] [Edit]

첨부파일을 사용하세요. ^^v

--------------------------------------------------------------------------------

#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql.h>

// Just a small exmaple of multithreading, MUST link with -lpthreads -lmysqlclient_r
// Note: Optimal # of threads and connections pool is the # of CPUs BUT,
// that depends a lot on how fast you expect the answer to your queries

#define QPERTHR 500
#define THREADS 2
#define CONPOOL (THREADS)

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[CONPOOL];

void *db_pthread(void *arg);
static void db_die(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[THREADS];
db_config dbc;

strcpy(dbc.host,"");
strcpy(dbc.user,"");
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, Queries per Thread: %d, Total Queries: %d\n",\
CONPOOL, THREADS, QPERTHR, THREADS * QPERTHR);

// pre initialize connections and locks
for (i = 0; i < CONPOOL; ++i) {

dbm[i].db = db_connect(dbm[i].db, &dbc);
pthread_mutex_init(&dbm[i].lock, NULL);
}

// pthread_setconcurrency(THREADS);
// fire up the threads
for (i = 0; i < THREADS; ++i)
pthread_create(&pthread[i], NULL, db_pthread, (void *)i);

// wait for threads to finish
for (i = 0; i < THREADS; ++i)
pthread_join(pthread[i], NULL);

for (i = 0; i < CONPOOL; ++i) {
pthread_mutex_destroy(&dbm[i].lock);
db_disconnect(dbm[i].db);
}

exit(EXIT_SUCCESS);
}

void *db_pthread(void *arg) {
int i = (int) arg, j, cancelstate;

// Always a good idea to disable thread cancel state or
// unexpected crashes may occur in case of database failures
pthread_setcancelstate(PTHREAD_CANCEL_DISABLE,&cancelstate);

if ((mysql_thread_init() != 0))
db_die("mysql_thread_init failed: %s", mysql_error(dbm[i].db));

for (j = 0; j < QPERTHR; ++j)
db_query(dbm[i].db, &(dbm[i].lock), "show status");

mysql_thread_end();
pthread_setcancelstate(PTHREAD_CANCEL_ENABLE,&cancelstate);
pthread_exit((void *)0);
}

static void db_die(char *fmt, ...) {
int i;

va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
for (i = 0; i < CONPOOL; ++i) {

pthread_mutex_destroy(&dbm[i].lock);
db_disconnect(dbm[i].db);
}
exit(EXIT_FAILURE);
}

MYSQL *db_connect(MYSQL *db, db_config *dbc) {

if (!(db = mysql_init(db)))
db_die("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("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("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);
// count total rows * fields and return the value, if SELECT
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("mysql_store_result failed: %s", mysql_error(db));
}
}
return (ret);
}

--------------------------------------------------------------------------------

Posted by [name withheld] on February 24 2006 8:37pm

[Delete] [Edit]

Thank you Lefteris; the example helps a lot!

At the end of db_thread, shouldn't you call:

pthread_setcancelstate(cancelstate,0);

to restore it, instead of

pthread_setcancelstate(PTHREAD_CANCEL_ENABLE, &cancelstate);

Thanks
npj

Posted by [name withheld] on February 24 2006 8:55pm

[Delete] [Edit]

Also,

You need to initialize the db_mutex::db fields to null. It's likely the compiler would do this automatically since your dbm array is statically allocated, but this practice could lead to confusing bugs in an example.

Add your own comment.

--------------------------------------------------------------------------------------------------

이것도 개념잡는데 도움이 될것 같아서 -ㅡ; 추가 함(오래된 자료 같음 3.x.x ?)

18.4.52 How to make a thread-safe client

클라이언트는 거의 thread-safe하다. 가장 큰 문제점은 'net.c'에서 소켓을 읽는 서브루틴들이 interrupt-safe하지 못하다는 것이다. 이것은 서버로부터 읽는 작업이 오래 걸릴 때 클라이언트가 작업을 중단할 수 있는 기능을 원할 것이라는 생각에서 그렇게 작성되어졌다.

표준 클라이언트 라이브러리는 쓰레드 옵션없이 컴파일되어진다.

Thread-safe 클라이언트를 만들기 위해서는 -lmysys, -lstring, -ldbug 라이브러리와 서버가 사용하는 net_serv.o를 함께 사용한다.

Threaded client를 사용하면 'thr_alarm.c' 파일에 있는 루틴들을 활용할 수 있다. mysys 라이브러리의 루틴들을 사용할 때 기억해야할 단 한가지는 my_init() 함수를 가장 먼저 호출해야 한다는 것이다!

mysql_real_connect()를 제외한 모든 함수들은 현재 thread-safe하다. 다음의 내용에서는 thread-safe 클라이언트 라이브러리를 컴파일하는 방법과 그것을 thread-safe 방식으로 사용하는 방법에 대하여 기술한다(여기서는 mysql_real_connect() 함수에 대한 예를 설명하며, mysql_connect()의 경우에도 동일하다).

mysql_real_connect() 함수를 thread-safe하게 만들기 위해서는 다음의 명령으로 클라이언트를 재컴파일하여야 한다:

shell> CPPFLAGS=-DTHREAD_SAFE_CLIENT ./configure ...

pthread 라이브러리가 디폴트로 포함되어지지 않기 때문에 표준 클라이언트(standard client)를 링킹할 때 정의되지 않은 심볼(undefined symbols)로 인한 에러가 발생할 수 있다.

결과로 나오는 'libmysqld.a' 라이브러리는 이젠 thread-safe하다. 이것은 두개의 thread가 mysql_real_connect()에 의해 리턴된 동일한 연결 핸들(connection handle)을 동시에 질의하지 않는 한 클라이언트 코드가 thread-safe하다는 것을 의미한다; 클라이언트/서버 프로토콜은 주어진 연결에 대하여 동시에 하나의 요청만을 허용한다. 동일한 연결에 대하여 복수개의 thread를 사용하길 원하는 경우에는 mysql_query()와 mysql_store_result()의 호출 묶음(call combination)에 대하여 mutex lock을 해야 한다.

일단 mysql_store_result()가 준비되면 lock은 해제될 수 있으며 다른 thread들은 동일한 연결에 대하여 query를 할 수 있다(다시 말해서, 서로 다른 thread들이 적절한 locking 프로토콜을 사용하는 한 mysql_store_result()과 함께 생성된 서로 다른 MYSQL_RES 포인터들을 사용할 수 있다). POSIX thread로 프로그램한다면 mutex lock을 생성하고 해제하는데 pthread_mutex_lock()와 pthread_mutex_unlock() 함수를 사용할 수 있다.

mysql_store_result()가 아닌 mysql_use_result()를 사용하는 경우에는 lock이 mysql_use_result()와 mysql_fetch_row()에 대한 호출을 surround할 필요가 있다. 그러나 threaded client는 mysql_use_result()를 사용하지 않는 것이 가장 좋다.