Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts

    max_user_connections

    Hi All,

    I have a problem with exceeding the max_user_connections (100 allowed) on my hosting package. Is their any software or facility where I can monitor these connections so I can work out how to optimise my application?

    Regards

    Gary

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    Since you are using ASP (per your other post), it's really hard to see how this is possible.

    I'd say you have some design issues in your application.

    You should be able to run maybe 5000 users on only 20 connections, in a typical environment.

    Are you storing connections in session variables? Are you opening more than one connection per ASP page?? Both of those are huge no-no's.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    Oops! I think I am guilty of both errors. I'll go through the files and check. Thanks for the information re 5000 users on only 20 connections. I wasn't sure how users vs connections typically related.

    Regards

    Gary

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    I *did* say "typical" environment, of course. e.g., eCommerce. Blogging. That kind of stuff.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts

    max_user_connections problem

    Hi OP,

    I have checked my connection strings. I don't store these as session variables.

    In the main page that is causing the max_user_connections problem, I have the following database activity:

    1. At the top of the page I have a cache_control script to prevent local PC's caching the results and a check_user script to validate the user access.

    Then I:

    2. Open a connection to the database

    3. Run a large sql query (containing 7 inner joins) to gather all the data needed to display the "In Progress" list of tasks for the customer and mechanics.

    4. Run a couple of smaller sql queries (containing 1 inner join) to display additional data and close the recordset immediately after display

    5. Close the first large sql query recordset

    6. Run a large sql query (containing 7 inner joins) to gather all the data needed to display the "Completed" list of tasks for the customer and mechanics.

    7. Run a couple of smaller sql queries (containing 1 inner join) to display additional data and close the recordset immediately after display

    8. Close the second large sql query recordset

    9. Close the connection to the database

    I haven't indexed my database tables yet. I'm still working out which fields to index.

    How bad is my above design?

    Regards

    Gary

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    Doesn't look all that bad from here.

    Certainly adding indexes will help *a lot*. But I can't see how you are exceeding 100 connections using that code.

    The thing is, IIS wouldn't normally allow more than 20 or so simultaneous users (depends on the size of the server, of course), so that's another reason I'm surprised.

    Just how many people are using this system, anyway???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    Normally, about 100 users permenantly logged in with about 1 new/updated record every minute (not sure how this translates into database connections).

    Regards

    Gary

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    Yeah, you have a bug somewhere.

    Assuming these are all *browser*-based connections, I doubt that you really need more than a handful of available connections. Wouldn't be surprised if you could get away with a single connection, though that might be stretching it.

    Understand, with ASP (and JSP and PHP and....) users this is normal operation:
    -- Browser makes HTTP Request of server
    -- Assuming that the Request implies the need for some DB-based operation, the ASP page makes a connection to the DB
    -- The ASP page makes one or more queries against the DB and uses them to prepare the HTML (or XML or even text) that is sent back to the client
    -- The ASP page uses HTTP Response to send the data to the client
    -- The HTTP Request/Response cycle is done. The client and server *forget entirely about each other*
    -- The ASP engine "tears down" the thread/page that was used to do the Response
    -- In the process, the Connection is closed and reset (even if your ASP code forgot to do so!!!)

    Exceptions/more details:

    -- If you are using a connection POOL, then the connection isn't really closed; instead it's just returned to the pool for use by the next ASP page. Even if your code does "conn.close", the close just returns the connection to the pool.
    -- I say the client and server forget all about each other. From the HTTP perspective, this is literally true. The connection across the internet is closed down, gone, lost forever.
    -- So how do ASP (and JSP and PHP and...) clients/servers "remember" each other? Through the use of "Sessions." Sessions are really just a "Dictionary" of session objects, keyed by the sessionid. In the case of ASP, the sessionid is always saved on the client in a heavily encrypted Cookie. When the client comes back to make another HTTP Request of the server, it sends the Cookie to the server, the server decrypts the sessionid fro the cookie and looks up the session information for that user is in the Session dictionary.

    ANYWAY...

    The point is, if you have constructed your ASP pages correctly, then each page needs a connection object (whether from a connection pool or dynamically created) only so long as the HTTP Request/Response is "alive". For most commercial applications, that period of time is measured in milliseconds. Possibly a couple of hundred milliseconds, but seldom much more than that. And if you only have 100 users, it is *HIGHLY* unlikely that you will have more than, say, 10 users who just happen to have HTTP Request/Response cycles "open" at the same time.

    So... If you are exceeding 100 connections, it has to be because of a bug in your use of the connections. *Something* is holding open a connection when it doesn't really need/want to.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Gary Williams (02-04-2010)

  • #9
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    Thanks, I follow most of that (except the 'connection POOL' bit) and know that, even with many apparently simultanuous uses, the chances of many uses' recordset requests all occuping the exact same few milliseconds is quite unlikely. However, I think that is what I have achieved and my problem is self inflicted.

    So that all my users can see the latest list of tasks, whoever has entered the most recent one, I have included a page refresh on the main page every 90 seconds. This saves each user from having to hit the browser refresh button. Concequently, every users browser is making this request at exactly the same time, yes?

    Is the above correct or am I talking rubbish?

    The main page that the users are viewing has one open connection string at the top of the page and one break connection string at the bottom of the page. Within the page, there a number of recordset openings and closings to create the search dropdowns automatically for various database tables and several recordset openings and closings to create the actual list of tasks. The recordset sql queries are quite long containing upto 7 mutiple joins each.

    What I need to achieve is that every users screen always displays the uptodate list of tasks, so I need a 'server push' yes? Do you know of a technique that will monitor all database tables for add/update operations and then push the new data out?

    You've previously mentioned that I should look to make more use of AJAX. I am working on converting this problem page to AJAX and I have created some very simple database example which I'll post in case these may help others.

    You should make your previous post a 'sticky'. I think a lot of people would benefit from reading that.

    Regards

    Gary


  •  

    Posting Permissions

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