23

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)
Isak Savo
  • 34,957
  • 11
  • 60
  • 92

4 Answers4

29

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:

  • Edit: it seems this bullet only applies to versions < 5.5; see this page for your appropriate version: 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.

chazomaticus
  • 15,476
  • 4
  • 30
  • 31
  • 1
    This was just the kind of answer I needed. I didn't realize I'd have to call mysql_init in each thread -- I just did it once in main(). Thanks – Isak Savo Sep 22 '09 at 06:19
  • 2
    @chazomaticus, how many threads would you typically use and how many connections would you have open? Does this scale to large numbers of threads/connections? A connection pool is really useful if you have lots of threads (100's - 1000's) but don't want the overhead of opening 1000 connections (which you may not have permission to anyway as the default for max_connections is usually set at 100). If you have low numbers of threads then your approach would work. +1 from me for showing code examples. – Glen Sep 22 '09 at 09:06
  • 1
    Since Isak is trying to stress the DB, as many threads as possible. I've run ~1000 without issue (if all the threads do is issue queries, most of their time is spent idle in `poll()`, so it's not as CPU-intensive as you might think, though it can eat a chunk of memory). You're right about max_connections capping you out at 100 by default, so for maximum stress, up that as desired. – chazomaticus Sep 22 '09 at 17:18
  • I'm on mysql 5.6.11 and I don't see `libmysqlclient_r`. Can't find any multithreading info either on the doc – gerrytan May 22 '14 at 23:00
  • Looks like as of 5.5, they stopped having the split between thread safe/unsafe versions of the library. (Good riddance.) See e.g. http://dev.mysql.com/doc/refman/5.6/en/c-api-threaded-clients.html for docs about multithreading on 5.6. – chazomaticus May 23 '14 at 05:26
6

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.)

Community
  • 1
  • 1
Glen
  • 21,816
  • 3
  • 61
  • 76
  • good answer.. thanks. But I still have problem with even opening more than one connection and the sample you linked to is missing that piece of code.. I gave you a +1 but I don't wanna accept it (yet) since it doesn't solve my problem with opening multiple connections – Isak Savo Sep 21 '09 at 17:40
1

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.

Elemental
  • 7,365
  • 2
  • 28
  • 33
  • This is what I suspected.. Just need to figure out how to make multiple connections.. can't get that to work right now – Isak Savo Sep 21 '09 at 17:43
  • Never had a problem with this but I don't usually use these libraries. Seems to me you should be able to allocate separate MYSQL structures and init and connect each one. – Elemental Sep 22 '09 at 13:08
-1

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.

Tiemen
  • 509
  • 2
  • 6
  • I'm using the libmysqlclient_r library so I assume it has all the thread safety things compiled in. Just to be sure, I'll try to do all connection stuff before spawning threads and see if that helps – Isak Savo Sep 21 '09 at 17:43