slow connection - pooling is a solution?

[Jacco]

Hi,

Every time, I create a connection to mySQL server, it takes about 3 seconds
before the connection is made. The server is at the other side of a VPN
connection, so this might cause the delay.

Anyway, reading the newsgroups, I learned that it is not wise to create a
connection and keep that open for the entire time the app is running. So, I
create a connection, do the query and close the connaction again. Every time
with this delay.

What can be the solution to this issue? I read some things about connection
pooling, but it's hard to find documentation about it.

any help out there? ;) TIA!

P.S. I use VB 2005 with the .NET connector. here is the code for my
connection:
Public Function Connect() As Boolean

Dim connStr As String = String.Format("server={0};port ={1};user id={2};
password={3}; database={4}; pooling=false", _

_ServerAddress, _ServerPort, _Username, _Password, _Database)

If Not Connection Is Nothing Then Connection.Close() 'close database if
needed

Try

Connection = New MySqlConnection(connStr)

Connection.Open()

If Connected() Then Return True

[Jerry Stuckle]

Jacco wrote:

> Hi,
>
> Every time, I create a connection to mySQL server, it takes about 3 seconds
> before the connection is made. The server is at the other side of a VPN
> connection, so this might cause the delay.

Quite probably correct, but there can be a lot of reasons why it takes this
long. You need to find out why - it shouldn't take 3 seconds.

> Anyway, reading the newsgroups, I learned that it is not wise to create a
> connection and keep that open for the entire time the app is running. So, I
> create a connection, do the query and close the connaction again. Every time
> with this delay.

This is a general rule, not an absolute fact. And opening/closing the
connection for each query is definitely NOT the way to go! You need to find a
happy medium there.

> What can be the solution to this issue? I read some things about connection
> pooling, but it's hard to find documentation about it.
>

It wouldn't help you unless you have multiple applications running on the client
and accessing the same mysql server.


You need to figure out why the delay in connecting/disconnection and resolve it
(if you can). You also need to plan your application a little better to create
a balance between connections and performance.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Chander Ganesan]

It's also possible that your MySQL server is performing reverse DNS
lookups - which can slow things down a bit. You might consider
disabling reverse lookups to see if that helps.


--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
[url]http://www.otg-nc.com[/url]

On-Site and Open Enrollment MySQL Training.

Jerry Stuckle wrote:

> Jacco wrote:

> > Hi,
> >
> > Every time, I create a connection to mySQL server, it takes about 3 seconds
> > before the connection is made. The server is at the other side of a VPN
> > connection, so this might cause the delay.

>
> Quite probably correct, but there can be a lot of reasons why it takes this
> long. You need to find out why - it shouldn't take 3 seconds.
>
>
>

> > Anyway, reading the newsgroups, I learned that it is not wise to create a
> > connection and keep that open for the entire time the app is running. So, I
> > create a connection, do the query and close the connaction again. Every time
> > with this delay.

>
> This is a general rule, not an absolute fact. And opening/closing the
> connection for each query is definitely NOT the way to go! You need to find a
> happy medium there.
>

> > What can be the solution to this issue? I read some things about connection
> > pooling, but it's hard to find documentation about it.
> >

>
> It wouldn't help you unless you have multiple applications running on the client
> and accessing the same mysql server.
>
>
> You need to figure out why the delay in connecting/disconnection and resolve it
> (if you can). You also need to plan your application a little better to create
> a balance between connections and performance.
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> [email]jstucklex@attglobal.net[/email]
> ==================

[Gordon Burditt]

>Every time, I create a connection to mySQL server, it takes about 3 seconds

>before the connection is made. The server is at the other side of a VPN
>connection, so this might cause the delay.
>
>Anyway, reading the newsgroups, I learned that it is not wise to create a
>connection and keep that open for the entire time the app is running.

Why? How long does your app run? If it's intended to run for years
and it isn't prepared to reconnect in case of failure, yes. If
it's going to run once per PHP page hit, by all means keep the
connection open for as many queries as that hit needs.

>So, I
>create a connection, do the query and close the connaction again. Every time
>with this delay.

Look into the possibility that reverse DNS lookup on the server
machine of the client machine's IP address might be causing a delay.

>What can be the solution to this issue? I read some things about connection
>pooling, but it's hard to find documentation about it.

Persistent connections in PHP pretty much mean that you will on
average have one connection per MAXIMUM simultaneous hits, as
distinguished from one connection per average simultaneous hits.
This can take up a lot of (MySQL) server memory.

Each Apache process establishes its own connection and keeps it
open even if that process won't be used for a long time.
I suspect persistent connections do much the same thing on other
webservers.

Gordon L. Burditt

[Jacco]

Hi Chander and Jerry,

Thank you both for your info. It makes it more clear to me now:)
About this "Reverse DNS lookup", is this a MySQL server setting or something
in the server itself? (It's running on windows 2003 server as a domain
controller).

Thanks again!



"Chander Ganesan" <chander@otg-nc.com> wrote in message
news:1150169959.401607.248830@f6g2000cwb.googlegro ups.com...

> It's also possible that your MySQL server is performing reverse DNS
> lookups - which can slow things down a bit. You might consider
> disabling reverse lookups to see if that helps.
>
>
> --
> Chander Ganesan
> The Open Technology Group
> One Copley Parkway, Suite 210
> Morrisville, NC 27560
> Phone: 877-258-8987/919-463-0999
> [url]http://www.otg-nc.com[/url]
>
> On-Site and Open Enrollment MySQL Training.
>
> Jerry Stuckle wrote:

>> Jacco wrote:

>> > Hi,
>> >
>> > Every time, I create a connection to mySQL server, it takes about 3
>> > seconds
>> > before the connection is made. The server is at the other side of a VPN
>> > connection, so this might cause the delay.

>>
>> Quite probably correct, but there can be a lot of reasons why it takes
>> this
>> long. You need to find out why - it shouldn't take 3 seconds.
>>
>>
>>

>> > Anyway, reading the newsgroups, I learned that it is not wise to create
>> > a
>> > connection and keep that open for the entire time the app is running.
>> > So, I
>> > create a connection, do the query and close the connaction again. Every
>> > time
>> > with this delay.

>>
>> This is a general rule, not an absolute fact. And opening/closing the
>> connection for each query is definitely NOT the way to go! You need to
>> find a
>> happy medium there.
>>

>> > What can be the solution to this issue? I read some things about
>> > connection
>> > pooling, but it's hard to find documentation about it.
>> >

>>
>> It wouldn't help you unless you have multiple applications running on the
>> client
>> and accessing the same mysql server.
>>
>>
>> You need to figure out why the delay in connecting/disconnection and
>> resolve it
>> (if you can). You also need to plan your application a little better to
>> create
>> a balance between connections and performance.
>>
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> [email]jstucklex@attglobal.net[/email]
>> ==================

>

[Bill Karwin]

Jacco wrote:

> Thank you both for your info. It makes it more clear to me now:)
> About this "Reverse DNS lookup", is this a MySQL server setting or something
> in the server itself? (It's running on windows 2003 server as a domain
> controller).

There are docs about the options for MySQL Server's usage of DNS here:

[url]http://dev.mysql.com/doc/refman/5.0/en/dns.html[/url]

Regards,
Bill K.