Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    UK, London
    Posts
    762

    Optimizing PHP + MySQL

    Which on of these methods will reduce/prevent getting these errors "Too many connections to the mysql server"

    Method 1 - index.php
    PHP Code:
    <?php

    // Connect To MySQL Server
    mysql_connect($host$user$pass);
    mysql_select_db($db);

    // Start Header
    include("Header.php");

    // Contents Page
    $page $_GET["Page"];

    switch(
    $page)
    {
       case 
    "Page A":
       include(
    "page_a.php");  // This Page Runs 3 Querys
       
    break;

       case 
    "Page B":
       include(
    "page_b.php"); // This Page Runs 1 Querys
       
    break;

       case 
    "Page C":
       include(
    "page_c.php"); // This Page Runs 1 Querys
       
    break;

       default:
       include(
    "home.php"); // This Page Runs 5 Querys
       
    break;
    }

    // Start Footer
    include("Footer.php");

    // Close MySQL Connection
    mysql_close();

    ?>
    Method 2 - index.php
    PHP Code:
    <?php

    // Start Header
    include("Header.php");

    // Contents Page
    $page $_GET["Page"];

    switch(
    $page)
    {
       case 
    "Page A":
       include(
    "page_a.php");  // This Page Runs 3 Querys
       
    break;

       case 
    "Page B":
       include(
    "page_b.php"); // This Page Runs 1 Querys
       
    break;

       case 
    "Page C":
       include(
    "page_c.php"); // This Page Runs 1 Querys
       
    break;

       default:
       include(
    "home.php"); // This Page Runs 5 Querys
       
    break;
    }

    // Start Footer
    include("Footer.php");

    ?>
    On Method 2, mysql_connect(); will be used before and mysql_close() after every query on each pages.

    So for e.g. If user was on www.domain.com/index.php 5 quries would have been used, so connection to the mysql server via mysql_connect() would have happened 5 times.

    So, which one of this method would you say is best/optimised.

  2. #2
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    665
    The error you are obtaining is not caused by a particular script or the amount of queries issued by a script, but by the sum of all connections that are being issued to the server. One connection can call 349304930 queries and not raise this issue.

    Under your [mysqld] block in /etc/my.cnf, look at:

    [mysqld]
    max_connections= ???
    and adjust as required.. If you have limited it on a per user basis, you might want to raise that too.

    If your MySQL server is not the bottleneck, you might also be hitting the PHP-set limit (php.ini). Revise your:

    mysql.max_links
    mysql.max_persistent

    settings as required.
    circlical - hosting software development
    forums * blog

  3. #3
    Join Date
    Jan 2005
    Location
    UK, London
    Posts
    762
    if this statement "One connection can call 349304930 queries" is true, then it looks like im better of starting the mysql connection before the header and close it after the footer, whilst all the quries are done within the middle.

    This way, im using one connection to perform many queries.

    Anyhow, im not using my own dedicated server, im hosted by a web hosting company. it looks like they limit the max connection you can make to server.

  4. #4
    Join Date
    Nov 2005
    Location
    USA
    Posts
    874
    I would go with the first method.
    GS RichCopy 360 Enterprise - Voted #1 for data migration and replication in terms of performance and features. Replicate data across between servers in the same network, WAS, or even across the internet

  5. #5
    Join Date
    Sep 2005
    Location
    Southern California
    Posts
    179
    Could also consider using mysql_pconnect, as that can sometimes decrease connection count.

    Also, both methods would be equally as effective. Only one page will be loaded inside the switch statement, so only one connection will be established. Does not matter which way it is done.

    From a programming standpoint, the first method is better as there is no duplicated code.

  6. #6
    Join Date
    Nov 2006
    Location
    Richmond
    Posts
    18
    Thanks for the tips
    http://www.roguecomputer.com - bringing personalized service to customized computers

  7. #7
    Join Date
    Jan 2005
    Location
    UK, London
    Posts
    762
    Quote Originally Posted by localhost127
    Could also consider using mysql_pconnect, as that can sometimes decrease connection count.

    Also, both methods would be equally as effective. Only one page will be loaded inside the switch statement, so only one connection will be established. Does not matter which way it is done.

    From a programming standpoint, the first method is better as there is no duplicated code.
    The thing with my second method is that, i would connect to mysql server, run the query, close the connection for every query. So, if i had 5 query on the home page, so i would connect to mysql server 5 times to execute 5 queries.

    Now i realise that, i can run many queryies with just one connection per page, so im going to start using method one (which i havent done before).

  8. #8
    Join Date
    Sep 2005
    Location
    Southern California
    Posts
    179
    Quote Originally Posted by latheesan
    The thing with my second method is that, i would connect to mysql server, run the query, close the connection for every query. So, if i had 5 query on the home page, so i would connect to mysql server 5 times to execute 5 queries.

    Now i realise that, i can run many queryies with just one connection per page, so im going to start using method one (which i havent done before).

    Ah, i see. I saw the mysql_close at the end of your first solution and assumed that each included file would connect, run 5 queries, and then finish. If you were connecting once for each query then the tips mentioned in this thread will definitely improve the correctness and efficiency of your code.

  9. #9
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    665
    Inside of a single thread of execution, method one or method two have no functional difference. They are equivalent in function. They would only be different, if you were issuing the fourth parameter to mysql_connect which forces a new connection. PHP otherwise will rehash any existing connection with matching parameters. PHP is smart! Also, mysql_close is implicit with the end of your script's execution.

    It was suggested above, and is a good suggestion, to use mysql_pconnect if you are permitted to do so (if your script gets heavy traffic, and will otherwise generate lots of connections). The connection overhead to establishing a connection with mysql_connect to a local socket is MICROSCOPICALLY small and EXTREMELY fast however - so there's no real speed advantage to using pconnect, it might just circumvent the limit you are experiencing.
    circlical - hosting software development
    forums * blog

  10. #10
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    if this statement "One connection can call 349304930 queries" is true, then it looks like im better of starting the mysql connection before the header and close it after the footer, whilst all the quries are done within the middle.

    This way, im using one connection to perform many queries.
    PHP will automatically reuse a connection if it is open again with the same parameters to mysql_open(), and it does automatically close a connection at the end of the page; however it is good practice to execute mysql_close().

    So unless you are explicitly closing and opening connections, your connection count will not change.

    The guaranteed way to make this work was already mentioned by Saeven, which is to increase the link limit in MySQL.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

Posting Permissions

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