How to access MySQL from multiple threads concurrently

Question

We're doing a small benchmark of MySQL where we want to see how it performs for our data.

Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries.

The MySQL documentation (5.0) isn't really clear about multi threaded clients. I should point out that I do link against the thread safe library (libmysqlclient_r.so)

I'm using prepared statements and do both read (SELECT) and write (UPDATE, INSERT, DELETE).

• Should I open one connection per thread? And if so: how do I even do this.. it seems mysql_real_connect() returns the original DB handle which I got when I called mysql_init())
• If not: how do I make sure results and methods such as mysql_affected_rows returns the correct value instead of colliding with other thread's calls (mutex/locks could work, but it feels wrong)

Answer 1

You could create a connection pool. Each thread that needs a connection could request a free one from the pool. If there's no connection available then you either block, or grow the pool by adding a new connection to it.

There's an article here describing the pro's and cons of a connection pool (though it is java based)

Edit: Here's a SO question / answer about connection pools in C

Edit2: Here's a link to a sample Connection Pool for MySQL written in C++. (you should probably ignore the goto statements when you implement your own.)

Answer 2

MySQL Threaded Clients in C

It states that mysql_real_connect() is not thread safe by default. The client library needs to be compiled for threaded access.

Answer 3

Seems clear to me from the mySQL Docs that any specific MYSQL structure can be used in a thread without difficulty - using the same MYSQL structure in different threads simultaneously is clearly going to give you extremely unpredictable results as state is stored within the MYSQL connection.

Thus either create a connection per thread or used a pool of connections as suggested above and protect access to that pool (i.e. reserving or releasing a connection) using some kind of Mutex.

Answer 4

As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some caveats that I've come across:

• Like you say you're already doing, link against libmysqlclient_r.
• Call mysql_library_init() (once, from main()). Read the docs about use in multithreaded environments to see why it's necessary.
• Make a new MYSQL structure using mysql_init() in each thread. This has the side effect of calling mysql_thread_init() for you. mysql_real_connect() as usual inside each thread, with its thread-specific MYSQL struct.
• If you're creating/destroying lots of threads, you'll want to use mysql_thread_end() at the end of each thread (and mysql_library_end() at the end of main()). It's good practice anyway.

Basically, don't share MYSQL structs or anything created specific to that struct (i.e. MYSQL_STMTs) and it'll work as you expect.

This seems like less work than making a connection pool to me.