The number of connections touches several areas. Fortunately there is
very little overhead in creating MySQL connections. Connection pooling
hits a few snags in MySQL in the area of session and user variables.
Anything set inside a session will stick when the next thread picks up
a connection. These bugs are very sneaky to find as they hardly ever
crop up during testing. As far as speed goes it really depends on the
operating system. MySQL keeps one thread per connection + a few other
threads. Linux people like to say that 1.5 threads per cpu is optimal.
This is due to Linux (now old) model of N:N threading, one thread in
kernel space equals a thread in user space which is also know as light
weight processes. NGPT (I think) is slowly coming into play to help
eliminate that problem. Since databases by nature are easy on the CPU
we really don't have to worry about this much.
Since MySQL connections are so light weight and there are risks in
connection pooling I recommend not using it. One thing you do need to
watch for is making sure the thread cache is big enough so that when
your database starts accepting lots of connections you aren't
thrashing the CPU creating/destroying threads. This can be checked
with show status like 'threads_created';
The short answer is: keep max connections high enough for load, watch
the thread cache, don't use connection pooling unless you really know
On 4/15/05, Jeff Drew <jeff@stripped> wrote:
> Our Java application has several threads that use JDBC to write to several tables.
> Currently, we use one JDBC connection. Would performance improve if we used a connection
> per table? Is a single connection bad for some reason?
> Since connection pooling is a hot topic, I'm wondering if we're missing out somehow.