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 13 of 13
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reducing sql server load

    Hi,
    Sometimes my website just stops running and only displays errors (see below).
    Is there a way i can fix the problems?
    Should it help if i use mysql_close() anywere?
    Also : how to use unset() and mysql_free_result (which,when) to reduce sql server load?

    Code:
    [24-Aug-2006 18:30:01] PHP Warning:  mysql_connect(): Too many connections in /home/mruler/public_html/game_setup.php on line 13
    [24-Aug-2006 21:51:13] PHP Warning:  mysql_connect(): Lost connection to MySQL server during query in /home/mruler/public_html/game_setup.php on line 13
    [24-Aug-2006 21:51:15] PHP Warning:  mysql_connect(): Lost connection to MySQL server during query in /home/mruler/public_html/game_setup.php on line 13
    [24-Aug-2006 21:55:01] PHP Warning:  mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/mruler/public_html/game_setup.php on line 13

  • #2
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    I take it you wrote your own script.

    It would be a good idea to only open and cloe the database connections when you need them and not leave them open all the time.

    An easyw ay to do this is useing a function, that how I do mine.

    PHP Code:
        function db($action) {

        
    // Settings
            
    $dbUsername "";        // Database Username
            
    $dbPassword "";                // Database Password
            
    $dbName "";            // Database Name
            
    $dbHost "localhost";                // Database Host

        // DONT EDIT ANYTHING BELOW HERE !!
        
            
    if ($action == "open") {
                
    // Connect to th database
                    
    mysql_connect($dbHost,$dbUsername,$dbPassword);
                    
    mysql_select_db($dbName) or die( "Unable to select database");
            } elseif (
    $action == "close") {
                
    mysql_close();
            } else {
                echo 
    "Not a valid db handler";        
            }
        } 
    I then do somthing like:

    PHP Code:
    db("open");
    $query "blah"
    db("close"); 

  • #3
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That's actually not true.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #4
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by marek_mar
    That's actually not true.
    Which part isn't true?

  • #5
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The part about connecting and disconnecting for every query.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #6
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    Why isnt it? That is what i read when I was learning. Im not going to argue the point because im not an expert but thought i would make my point known.

    I quote:
    Before I show you how to work with the database, I will show you one more command:

    mysql_close();

    This is a very important command as it closes the connection to the database server. Your script will still run if you do not include this command but too many open MySQL connections can cause problems for a web host. It is good practice to always include this line once you have issued all your commands to the database, to keep the server running well.
    http://www.freewebmasterhelp.com/tutorials/phpmysql/3

  • #7
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    opening and closing for every query is bad, the best thing to do would be just increase the max number of allowed connections - if you can (if you're on shared hosting probably not)
    adding mysql_close() at the end of any page using db connections might help a little - will reduce the amount of time a connection is left open after you're done with it.
    But if you have high traffic, its likely that you just have more visitors that your host allows. Are your queries taking a long time to run? How fast do your pages load? If you're using phpmyadmin, try examining your server variables. For example, just had a look at my shared hosting and the max user connections is 25, so if I had more than 25 people on my site at any one time - then the errors would start.
    where the problem lies depends very much on your site and your sql setup

  • #8
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    Quote Originally Posted by Anthony2oo4
    Why isnt it? That is what i read when I was learning. Im not going to argue the point because im not an expert but thought i would make my point known.

    I quote:


    http://www.freewebmasterhelp.com/tutorials/phpmysql/3
    Its important to note here that he says after you've issues all your commands - not after each one.
    Your mysql connection will expire at the end of your page - but its not instant, mysql_close is useful to a certain degree, ofcourse if your page goes on to do a lot of lengthy php processing then yes, definately close it as soon as you're done. But otherwise the difference between closing it straight after your last query and closing it at the end of the page is microseconds

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I don't see where Anthony advised opening and closing on every query. Maybe his coding example implied that, but I didn't interpret it that way.

  • #10
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by NancyJ
    For example, just had a look at my shared hosting and the max user connections is 25, so if I had more than 25 people on my site at any one time - then the errors would start.
    Some users would get the too many connections error if more than 25 users were requesting sometghing at once. It's actually not that easy to do.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #11
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Maybe he's thinking of connection pooling? But i'm just thinking of .NET Sql Server now....carry on.

  • #12
    Regular Coder
    Join Date
    Aug 2006
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My sql max was already at 150. I increased it but isn't that just a temporarly solution, till your site get's bigger and then it will appear again?
    I only get the errors at evenings (high traffic then). I'll add mysql_close to the footers, maybe it helps.
    What about unset & mysql_free_result ? Are they usefull? How?

  • #13
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    unset — Unsets one or more variables.

    example
    PHP Code:
    <?
    $song 
    = array (
        
    'name' => 'Another Second to Be',
        
    'band' => 'Accept',
        
    'album' => 'Russian Roulette',
        
    'year' => 1986
    );

    print 
    "<tt>$song</tt> before calling unset:\n";
    print_r ($song);

    // Individual array elements can be unset
    print "\n<tt>\$song</tt> after calling <tt>unset (\$song['year']):</tt>\n";
    unset (
    $song['year']);
    print_r ($song);

    print 
    "\n<tt>\$song</tt> after calling <tt>unset (\$song):</tt>\n";
    unset (
    $song);
    print_r ($song);
    ?>
    I dont see how this could help you in this matter.

    mysql_free_result — Frees memory taken up by a result handle.

    PHP Code:
    mysql_free_result ($mysql_result); 
    Again i dont see how this would help with connections.


  •  

    Posting Permissions

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