List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 6 2009 1:06am
Subject:Re: CONNECTION_ID() and Threads
View as plain text  
> Is it recommended we disable connection pooling,
> so that all connections are instantly closed ?

Frankly, no.  Well-behaved connection pooling is a good thing.  It
will reduce the load on your system resources and speed up responses
considerably.

If the only thing at issue is that you have observed various processes
reusing the same connection, then you have no issues at all.  As long
as connections aren't going stale and you are keeping a moderate
number of connections, having a few open and idle is not a bad thing
and puts no discernible load on the system.

If there are any behviours of the connection manger that are giving
you trouble, then you will have to look at however that is
configured..  in your case, that is an ASP/.net matter and you should
consult the gurus there.

 - michael dykman

On Thu, Nov 5, 2009 at 5:42 AM, Tompkins Neil
<neil.tompkins@stripped> wrote:
> Thanks for your response.  Is it recommended we disable connection pooling,
> so that all connections are instantly closed ?
>
> On Wed, Nov 4, 2009 at 4:19 PM, Michael Dykman <mdykman@stripped> wrote:
>>
>> Again, I'm no Microsoft guru, but it appears that your server
>> configuration for ADODB is the culprit.
>>
>> If 2 simultaneous processes are getting access to the same connection
>> at the same time via this call, there is somwething very wrong with
>> this service .  Now if 2 distinct processes happening not *quite* at
>> the same time are getting the same handle, no problem.  Connection
>> pooling is doing it's job.
>>
>>  - michael dykman
>>
>>
>> On Wed, Nov 4, 2009 at 10:47 AM, Tompkins Neil
>> <neil.tompkins@stripped> wrote:
>> > Hi Michael,
>> >
>> > We are using standard classic ASP code like :
>> >
>> > Function OpenConnection()
>> >     OpenConnection = "DSN=MyDSN_SSL"
>> >   End Function
>> >
>> >   DSNTemp = OpenConnection()
>> >
>> >   Set Conn = Server.CreateObject("ADODB.Connection")
>> >   Set RecordsetTest = Server.CreateObject("ADODB.RecordSet")
>> >
>> >   response.write Now()   & " connecting... <BR>
> Connected at : "
>> >   Conn.Open DSNTemp
>> >   response.write Now()
>> >   if Conn.errors.count = 0 then
>> >        response.write " - Connection
> Successful!"
>> >    else
>> >     response.write " - ERROR: Couldn't connect to database"
>> >     end if
>> >
>> >   RecordsetTest.open "SELECT CONNECTION_ID() As MySQLConnectionID"
> ,Conn
>> > 'query database for data
>> >
>> >   if not RecordsetTest.eof then
>> >    response.write "<br>" &
>> > RecordsetTest.fields("MySQLConnectionID").value &
>> > "<br>"
>> >   end if
>> >
>> >   RecordsetTest.close
>> >   set RecordsetTest = nothing
>> >
>> >   Conn.close
>> >   set Conn = nothing
>> > But my question regarding the CONNECTION_ID is why we are connecting
>> > using
>> > the same ID from two different computers.  I'm using a MySQL ODBC 5.1
>> > Driver
>> > connection.
>> >
>> > Thanks
>> > Neil
>> > On Wed, Nov 4, 2009 at 3:41 PM, Michael Dykman <mdykman@stripped>
>> > wrote:
>> >>
>> >> I am certainly no expert in Windows, but that sounds like the work of
>> >> whatever is managing the connections on your application server.
>> >> Whatever language you are using, if you opened the connection raw,
>> >> what you are describing would be impossible.  You need to look at
> how
>> >> you are acquiring your connection and then have a closer look at
>> >> whatever component is managing that..  apparently,  its' doing
> it
>> >> wrong.
>> >>
>> >>
>> >>  - michael dykman
>> >>
>> >> On Wed, Nov 4, 2009 at 10:36 AM, Tompkins Neil
>> >> <neil.tompkins@stripped> wrote:
>> >> > Hi
>> >> >
>> >> > Thanks for your quick response.  In our test environment
> running
>> >> > MySQL
>> >> > 5.1.31 over SSL (hosted external of our network) and IIS 6 on
> Windows
>> >> > 2003
>> >> > Server, I've found when opening a test connection to the database
>> >> > from
>> >> > my PC
>> >> > and one from my colleagues PC on the same internal network, we
> both
>> >> > had
>> >> > the
>> >> > same connectionID ?  Is this right ?
>> >> >
>> >> > The reason I need to know about the CONNECTION_ID() is because we
>> >> > have a
>> >> > issue on our website whereby not all database connections are
>> >> > correctly
>> >> > closed.  As commented on in this URL
>> >> > http://bugs.mysql.com/bug.php?id=12274
>> >> >
>> >> > Can anyone offer any advice regarding this ?
>> >> >
>> >> > Regards
>> >> > Neil
>> >> >
>> >> > On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
> <mdykman@stripped>
>> >> > wrote:
>> >> >>
>> >> >> Typically, a  thread works on a single connection
> exclusively..
>> >> >> otherwise it is impossible to do anything transactional.
>  However, I
>> >> >> have seen applications, in auto-commit mode use distinct
> statements
>> >> >> concurrently from the same connection to do read-only work.
>> >> >>
>> >> >> If you are worried about the latency of creating new
> connections for
>> >> >> a
>> >> >> many-threaded design, you should be looking into connection
> pooling
>> >> >> which manages the problem of maintaining many connections and
> only
>> >> >> letting one thread have a given connection at a time.
>> >> >>
>> >> >>  - michael dykman
>> >> >>
>> >> >> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
>> >> >> <neil.tompkins@stripped> wrote:
>> >> >> > Hi,
>> >> >> >
>> >> >> > With regards the CONNECTION_ID() variable in MySQL, is
> this based
>> >> >> > on
>> >> >> > a
>> >> >> > per
>> >> >> > user basis.   Or can multiple users use the same
> connection_id ?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Neil
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >>  - michael dykman
>> >> >>  - mdykman@stripped
>> >> >>
>> >> >> "May you live every day of your life."
>> >> >>    Jonathan Swift
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >>  - michael dykman
>> >>  - mdykman@stripped
>> >>
>> >> "May you live every day of your life."
>> >>    Jonathan Swift
>> >
>> >
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdykman@stripped
>>
>> "May you live every day of your life."
>>    Jonathan Swift
>
>



-- 
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
Thread
CONNECTION_ID() and ThreadsTompkins Neil4 Nov
Re: CONNECTION_ID() and ThreadsTompkins Neil4 Nov
  • Re: CONNECTION_ID() and ThreadsMichael Dykman4 Nov
    • Re: CONNECTION_ID() and ThreadsTompkins Neil4 Nov
Re: CONNECTION_ID() and ThreadsTompkins Neil5 Nov
  • Re: CONNECTION_ID() and ThreadsMichael Dykman6 Nov