Advice / best practice - thread connection pools / mutexes

[Karl Pielorz]

Hi,

We have an application that's threaded using pthreads at the moment under 
FreeBSD 6.4. At the moment every thread has it's own (in this case) MySQL 
connection.

We're looking at changing this due to the volume of connections this 
creates  (it's not uncommon to have 100+ threads running).

We have another application that's threaded - but only uses a single MySQL 
connection (protected by pthread_mutex).

What we'd ideally like is a 'pool' of say 4-8 connections - protected by 
mutex, where a thread needing a connection can grab the 'next available 
free one'.

I'm looking at any advice / pointers about how to implement this under 
FreeBSD.

Current thoughts have been:

 - Have the code 'try' for a lock on each mutex in turn, if it fails on one 
- it moves to the next. This sounds pretty poor - apart from the overhead 
of all the trying, there's no guarantee that as you move to mutex #4, mutex 
#3 won't free up - and you'll "miss it"

 - Use a modulo of the thread ID, which gives us a value of say between 0-3 
- and just have the thread try for that particular mutex, and block until 
it becomes free. Providing the thread ID's are sequential (which ours are) 
it should distribute all the requests around the connection pool.


If three threads try to lock the same mutex - two will block. Is there any 
guarantee which order the two blocking ones will get it when the first one 
releases it? - Is it FIFO?

What happens if a whole bunch of threads (40?) all try to lock the same 
mutex - is there a limit on how many threads can be blocked waiting on a 
mutex?

So if anyone's got any advice, pointers - or links to any material/books 
that would help with this, I'd be very grateful...


Thanks,

-Karl