Professional Web Applications Themes

slow connection - pooling is a solution? - MySQL

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 ...

  1. #1

    Default slow connection - pooling is a solution?

    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 doentation 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


    Jacco Guest

  2. #2

    Default Re: slow connection - pooling is a solution?

    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 doentation 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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: slow connection - pooling is a solution?

    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 doentation 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]jstucklexattglobal.net[/email]
    > ==================
    Chander Ganesan Guest

  4. #4

    Default Re: slow connection - pooling is a solution?

    >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 doentation 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
    Gordon Burditt Guest

  5. #5

    Default Re: slow connection - pooling is a solution?

    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" <chanderotg-nc.com> wrote in message
    news:1150169959.401607.248830f6g2000cwb.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 doentation 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]jstucklexattglobal.net[/email]
    >> ==================
    >

    Jacco Guest

  6. #6

    Default Re: slow connection - pooling is a solution?

    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.
    Bill Karwin Guest

Similar Threads

  1. Connection Pooling Error
    By CRoesener in forum Coldfusion Database Access
    Replies: 0
    Last Post: June 8th, 02:57 PM
  2. Connection Pooling
    By Trevor Hartman in forum Coldfusion Server Administration
    Replies: 5
    Last Post: May 24th, 07:18 PM
  3. CFMX 6.1 and Oracle 9i Connection Pooling
    By crentz in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 16th, 11:24 PM
  4. Oracle connection pooling
    By Lenny Shprekher in forum ASP.NET Web Services
    Replies: 0
    Last Post: December 9th, 04:22 PM
  5. Java Connection/Pooling Question
    By John Hibner in forum Informix
    Replies: 0
    Last Post: November 6th, 04:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139