Hi guys!
What are you using for connection pooling, for read/write queries
separation in your applications? MySQL Proxy? SQL Relay? Some other
stuff?

On 2/21/2011 5:31 PM, vadim wrote:
First question is - why do you think you need connection pooling?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> wrote

Its useful because making a connection can be slow ... now I know that
"slow" is relative, because its actually pretty fast, but by reusing an
existing connection instead of opening a new one can yield a noticable
performance improvement (assuming its a busy system with lots of connections
being opened and closed etc, etc, ymmv).

To the OP, how are you connecting to MySQL? because you might find
connection pooling is already there. For example if you are using the .net
mysql connector then it already supports connection pooling (although it
might need turning on), whereas if you are using the odbc connector then it
doesn't. Which in part comes back to Jerry's question of why do you think
you NEED connection pooling.
--
Brian Cryer
http://www.cryer.co.uk/brian

"Brian Cryer" <not.here (AT) localhost (DOT) invalid> wrote:
I underlined the important word.

But of course connection pooling comes at a price. It ties resources on
both ends (server and client). If done wrong, it has the potential to
do real damage, from never released locks to several-hour-lasting
transactions up to information leaking (through user variables and/or
temporary tables).

And even if done right: before the pool can hand out a connection it
has to test it (wait_timeout any one?) which requires a roundtrip to
the server. This alone already reduces the advantage over volatile
connections by a fair amount.

My personal conclusion: I've never seen a real world application where
connection pooling made a significant difference in performance. In
fact I've seen only one such scenario: it was a benchmark.


XL

On 2/22/2011 4:27 AM, Brian Cryer wrote:
I understand why people THINK they need it. But I have yet to SEE an
actual need with the exception of a VERY FEW instances - and none of
them were using MySQL.

Sure, if you're making 1K connections/second, connection pooling will
help. But in most cases it needlessly ties up system resources, and
I've seen some cases where it actually slows down the system.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Tue, 22 Feb 2011 09:27:45 -0000, Brian Cryer wrote:

PHP also DTRT with regard to connections: call for a connection with
the same parameters as has previously been opened and you get handed
back the identifier for the already-open connection. (That's occaionally
bitten someone switching from PHP to building something that uses the
C API, but the answer is often "pay attention or check whether you've
database connection resource" than muck about with someone else's
connection pooling mechanism.)

--
It seems that we were audited recently, and the auditors found a certain
'f' word in the comments of a configuration file, and deemed that this
is a 'security risk'.
-- Paul Fenwick

It also seems that a previous connection user can do a lot to
mess up a connection for the next user. Either maliciously, or
because the client started doing something and never managed
to finish it.

- Set the connection charset to something wierd.
- Set the transaction isolation level to something wierd.
- Set the SQL mode to something wierd.
- Set up a short connection timeout so the connection has timed out
before the next user gets it.
- Start a transaction and don't finish it.
- Leave behind a bunch of unreleased locks.
- Set the date format defaults to something wierd.
In fact, just about any SET SESSION <variable> = <value> is a potential
way of messing up the connection. SET GLOBAL is worse, but the
user probably doesn't have privileges to do that.

You also need to send down a "ROLLBACK" command.

How many dozen system variables do you have to set back to standard
values before giving the user the connection? Either that, or the
user needs to set them before starting any useful work.

gordonb.orc4y (AT) burditt (DOT) org (Gordon Burditt) wrote:

....


There is

http://dev.mysql.com/doc/refman/5.1/...ange-user.html
see also:
http://forge.mysql.com/wiki/MySQL_In..._CHANGE_U SER

(but this function is not exported by all APIs)

A correct connection pool implementation would call this function on
a connection before putting it back into the pool in order to cleanup
the session context. Also a correct implementation would call

http://dev.mysql.com/doc/refman/5.1/en/mysql-ping.html
aka
http://forge.mysql.com/wiki/MySQL_In...tocol#COM_PING

immediately before handing a connection out of the pool, to make sure
the connection is still valid and not run into wait_timeout.


XL