SitePoint Sponsor

User Tag List

Results 1 to 23 of 23

Thread: Pounding CPU

  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Pounding CPU

    Hi

    I am wondering what those of you that have queries that select many of thousands of rows do to not kill the CPU or the Webpage displaying the results?

    I have a very simple php page that has an equally simple SQL query. This query selects roughly 84000 postal_codes. Now I normally wouldn't be selecting all postal_codes, but chose this table as it had a fair number of rows and was simple to query not involving Joins:
    PHP Code:
    <?php 
    require_once('./libs/page_queries/db_handler_base.php');
    $o_Db = new DbHandlerBase();
    $sql 'SELECT postal_code, city_name, state_prov, country FROM postal_codes';
    $o_Db->setSQL($sql);
    $results $o_Db->runCursorQuery();
    ?>
    When the runCursorQuery() method is run flat-lines the cpus of my local machine (which is quite fast with 4Mb of RAM).

    The corresponding db_handler_base.php that is included has the following:
    Code PHP:
    <?php Class DbHandlerBase {    
    protected $pdo;    
    protected $stmt;    
    protected $sql;    
     
      public function __construct(){        
         $this->pdo = new DbFactory();    
      }    
      public function runQuery(){        
         if($this->sql){           
              $this->stmt = $this->pdo->db->prepare($this->sql);            
               try {
                         if ($this->stmt->execute()) {                                         
                               $result = $this->stmt->fetchAll(PDO::FETCH_ASSOC);                                        
                               return($result);               
                           }             
                     } catch (PDOException $e) {                                 
                           print ("Could not execute statement.\n");                
                           print ("errorCode: " . $sth->errorCode () . "\n");                
                           print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");           
                     }        
          } else {            
                        throw new Exception('$sql parameter needs to be set');        
          }    
      }    
      public function setSQL($sql){        
         $this->sql = $sql;    
      }    
      public function runCursorQuery() {
          try {
                    $this->stmt=$this->pdo->db->prepare($this->sql, array(PDO::ATTR_CURSOR =>PDO::CURSOR_SCROLL));                      
                    $this->stmt ->execute();
                    $result = array();
                    while ($row = $this->stmt ->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
                       $result[] = $row;
                    }
                  return $result;
                }  catch (PDOException $e) {
                   print $e->getMessage();       
                }   
       }
    }
    Class DbFactory {    
      public $db;    
      protected $db_factory;
     
      function __construct() {     
           return $this->db = new PDO("mysql:host=localhost;dbname=users;charset=UTF-8", "user", "userpassword");    
      }
    }?>

    I offloaded the responsibility of the query to the server by using the cursors.

    I know this is just a contrived test, but I know when my application gets done it would not be uncommon for a user to query and return 45000 or so records and know that I will hit this again. I know that I can raise the memory used by php on my server php.ini configuration, but feel that this might mask a more basic problem.

    Any thoughts on how I might get this so I don't tax the local users machines, especially when they will not likely be near as fast or as much RAM as my development machine!

    By the way, when I do this same search using the MySQL workbench or from the command line the query takes about 24 seconds.

    Thanks,
    Steve
    ictus==""

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    The best approach I came up with is to use limit and issue paging type results (quering the database for each LIMIT offset).

    Code PHP:
    Class DbHandlerBase {
        protected $pdo;
        protected $stmt;
        protected $sql;
        protected $session;
        public function __construct(){
            $this->pdo = new DbFactory();
            $this->session = new Session();
        }
        public function countRows($column2count, $table){
    //if the total number of rows are not stored in a session yet
            if(!$this->session->get('total_rows')){
                $this->sql = "SELECT COUNT($column2count) FROM $table";
                $this->stmt = $this->pdo->db->prepare($this->sql);
                try {    
                    if ($this->stmt->execute()) { 
                        $count = $this->stmt->fetch();
                        $this->session->set('total_rows', $count);
                    } 
                } catch (PDOException $e) {
     
                    print ("Could not execute statement.\n");
                     print ("errorCode: " . $sth->errorCode () . "\n");
                     print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
                }
            }
        }
        public function runQuery($number=0, $offset=-1){
            if($this->sql){
               if($number > 0 AND $offset > -1){
                    $total_rows = $this->session->get('total_rows');
                    if($total_rows){
    $this->session->set('offset', $offset);
                        if($offset + $number <= $total_rows){
                            $this->sql = $this->sql . " LIMIT $number, $offset";
                            $stored_offset = $this->session->get('offset');
                            $this->session-set('offset', $offset + $stored_offset);
                        } else {
                            $number = $total_rows - $offset;
                            $this->sql = $this->sql . " LIMIT $number, $offset";
                            $this->session-set('offset', 0);
                        }
                    }
     
                }
                $this->stmt = $this->pdo->db->prepare($this->sql);
     
                try {    
                    if ($this->stmt->execute()) { 
     
                        $result = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
     
                        return($result);
                    } 
                } catch (PDOException $e) {
     
                     print ("Could not execute statement.\n");
                     print ("errorCode: " . $sth->errorCode () . "\n");
                     print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
                }
            } else {
                throw new Exception('$sql parameter needs to be set');
            }
        }
        public function setSQL($sql){
            $this->sql = $sql;
       }

    This code stores a session value with the total number of rows and then on each successive call to the SELECT LIMIT the new offset value is updated in the offset session.

    Maybe not the best way to do this as this is application logic helping along a queries large return size and slowness, but it works.

    Regards,
    Steve
    ictus==""

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your query is straightforward:
    Code:
    SELECT postal_code, city_name, state_prov, country FROM postal_codes

    and has no where clause, no order by clause or anything else that will slow it down.


    84000 rows is trivial in size so the performance hit won't be from the database query itself, you should be talking fractions of a second. Anything slower than that is affected by the PHP code.



  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi,

    Ok when I remove the classes and extra code and as a test using non-class oriented PHP I can make this work but not with FetchALL(), intead using FETCH(). This code shows it working but takes 36 seconds to render it to the screen. Seems really slow still?
    Code PHP:
    ini_set('memory_limit', '-1');
    $pdo = new PDO("mysql:host=localhost;dbname=users;charset=UTF-8", "user", "psw");
    $results = runQuery($pdo);
     
    foreach($results as $row){
        echo $row['postal_code'] . ', ' . $row['city_name'] . ', ' . $row['state_prov_abbr']. ', ' . $row['country_iso_code'] . '<br />';
    }
     
    function countRows($pdo){
     
        $count_sql = 'SELECT count(postal_code) as count FROM postal_codes';
        $stmt = $pdo->prepare($count_sql);
        $stmt->execute();
        $result = $stmt->fetch();
     
        $count = $result['count'];
        $count = (int)$count;
        return $count;
    }
    function runQuery($pdo){
        $sql ='SELECT postal_code, city_name, state_prov_abbr, country_iso_code FROM postal_codes';
        $stmt = $pdo->prepare($sql);
        $count = countRows($pdo);
        try {    
            if ($stmt->execute()) { 
                $i = 1;
                $rows = array();
                while($i <= $count){
                    $rows[] = $stmt->fetch(PDO::FETCH_ASSOC);
                    $i++;
                }
                return($rows);
            }
        } catch (PDOException $e) {
            print ("Could not execute statement.\n");
            print ("errorCode: " . $sth->errorCode () . "\n");
            print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
       }
    }

    Thanks,
    Steve
    ictus==""

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you should ask a moderator to post this in the php forum. you really don't have a mysql problem. run the query directly in mysql, outside of php. you should see a hit of 1 second or less, perhaps slightly more. 36 seconds for your page to load and the problem then relates to your php code and thus it isn't a mysql forum question.

  6. #6
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Thanks guelphDad!

    I have asked the moderators to move this. It definitely seems that this is a php and not mysql based issue.

    Regards,
    Steve
    ictus==""

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi, as this post has been moved to the PHP here is the full php in one place:
    Code PHP:
    <?php 
    ini_set('memory_limit', '-1');
    Class DbHandlerBase {
        protected $pdo;
        protected $session;
        public function __construct(){
            $this->pdo = new DbFactory();
            $this->session = new Session();
        }
        public function countRows($column2count, $table , $set_session = 0){
            if(!$this->session->get('total_rows')){
                $sql = "SELECT COUNT($column2count) FROM $table";
                $stmt = $this->pdo->db->prepare($sql);
                try {    
                    if ($stmt->execute()) { 
                        $count = $stmt->fetch();
                        if($set_session != 0){
                            $this->session->set('total_rows', $count);
                        }
                    } 
                } catch (PDOException $e) {
                    print ("Could not execute statement.\n");
                     print ("errorCode: " . $sth->errorCode () . "\n");
                     print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
                }
            }
        }
        public function runPagedFetchAll($number, $offset){
            if(!$number AND !$offset){
                throw new Exception('must set number of returned rows and row offset in runPagedQuery($number=0, $offset=-1)');
                return 0;
            } 
            if(!$this->session->get('total_rows')){
                throw new Exception('Call countRow() with set $set_session = 1, Session total_rows not set');
                return 0;
            } else {
                $total_rows = $this->session->get('total_rows');
            }
            if($this->sql){
                $this->session->set('offset', $offset);
                if($total_rows){
                    if($offset + $number <= $total_rows){
                        $this->sql = $this->sql . " LIMIT $number, $offset";
                        $stored_offset = $this->session->get('offset');
                        $this->session-set('offset', $offset + $stored_offset);
                    } else {
                        $number = $total_rows - $offset;
                        $this->sql = $this->sql . " LIMIT $number, $offset";
                        $this->session-set('offset', 0);
                    }
                }
                $this->stmt = $this->pdo->db->prepare($this->sql);
                try {    
                    if ($this->stmt->execute()) { 
                        $result = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
                        return($result);
                    } 
                } catch (PDOException $e) {
                    print ("Could not execute statement.\n");
                     print ("errorCode: " . $sth->errorCode () . "\n");
                     print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
                }
            } else {
                throw new Exception('$sql parameter needs to be set');
            }
        }
        public function runFetchAll(){
            if($this->sql){
                $this->stmt = $this->pdo->db->prepare($this->sql);
                try {    
                    if ($this->stmt->execute()) { 
                        $result = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
                        return($result);
                    } 
                } catch (PDOException $e) {
                    $error = 
                    "Could not execute statement.\n errorCode: $sth->errorCode () \n" 
                    . "errorInfo: " . join (", ", $sth->errorInfo ()) . "\n";
                    throw new Exception($error);
                }
            } else {
                throw new Exception('$sql parameter needs to be set');
            }
        }
        public function runFetch(){
            if(!$this->session->get('total_rows')){
                throw new Exception('Call countRow() with set $set_session = 1, Session total_rows not set');
                return 0;
            } else {
                $total_rows = $this->session->get('total_rows');
            }
            $stmt = $this->pdo->db->prepare($this->sql);
            try {    
                if ($stmt->execute()) { 
                    $i = 1;
                    $rows = array();
                    while($i <= $total_rows){
                        $rows[] = $stmt->fetch(PDO::FETCH_ASSOC);
                        $i++;
                    }
                    return($rows);
                }
            } catch (PDOException $e) {
                print ("Could not execute statement.\n");
                print ("errorCode: " . $sth->errorCode () . "\n");
                print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
           }
        }
        public function setSQL($sql){
            $this->sql = $sql;
        }
    }
    Class DbFactory {
        public $db;
        protected $db_factory;
     
        function __construct() {
          return $this->db = new PDO("mysql:host=localhost;dbname=users;charset=UTF-8", "user", "password");
        }
    }
    ?>
    And here is the code that can burry the CPU and RAM (on my local 4 core 3.2GH processor, 4Gb RAM). The result set is 38Mb.
    Code PHP:
    require_once('./libs/common/Session.php');
    require_once('./libs/page_queries/db_handler_base.php');
    $o_Session = new Session;
    $o_Db = new DbHandlerBase();
    $sql = 'SELECT postal_code, city_name, state_prov, country FROM postal_codes';
    $o_Db->setSQL($sql);
    $results = $o_Db->runPagedFetchAll($number_returned = 10, $offset =0);
    $total_rows = $o_Session->get('total_rows');
    $i = 0;
    while( $i <= $total_rows){
        displayResults($results);
        $i++;
    }
    function displayResults($results){
        foreach($results as $value){
            echo ($value['postal_code']
             . ', ' . $value['city_name']
            . ', '  . $value['state_prov_abbr']
            . ', '  . $value['country_iso_code'] . '<br />')
            ;
            unset($row);
        }
    }

    The server this is running on is a open VZ virtual machine with Intel(R) Xeon(R) CPU X3330 @ 2.66GHz, 1 cores and 4Gigs RAM (3 used), Apache version 2.2.9, PHP 5.1.2 and MySQL 5.0.51

    Can you see why this locks up the web-page for 36 - 45 seconds on a Gigabit LAN?

    Thanks
    Steve
    ictus==""

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I might be off the mark here, but I don't see how your Factory works when a constructor cannot return anything?

    Maybe this is causing the connection object to not to be cached?

    What happens if you move the pdo driver connection straight into your dbhandlerbase class?

  9. #9
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Cups,

    Doing this:
    Code PHP:
    <?php 
    require_once('./libs/common/Session.php');
    require_once('./libs/page_queries/db_handler_base.php');
    $o_Session = new Session;
    $o_Db = new DbHandlerBase();
    $sql = 'SELECT * FROM postal_codes';
    $o_Db->setSQL($sql);
    $results = $o_Db->runFetchAll($number_returned = 10, 0);
    $total_rows = $o_Session->get('total_rows');
     
    $i = 0;
    while( $i <= $total_rows){
        displayResults($results);
        $i++;
    }
    function displayResults($results){
        foreach($results as $value){
            echo ($value['postal_code']
             . ', ' . $value['city_name']
            . ', '  . $value['state_prov_abbr']
            . ', '  . $value['country_iso_code'] . '<br />')
            ;
        }
    }
    It takes about 54 seconds. I did move the creation of the PDO object inside the dbhandlerbase class. Do you think this might be related to the $this->stmt->fetchAll(PDO::FETCH_ASSOC); ?

    Thanks.
    ictus==""

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Not sure I have fully grasped what you are doing ... what about this?

    PHP Code:
    <?php 
    require_once('./libs/common/Session.php');
    require_once(
    './libs/page_queries/db_handler_base.php');
    $o_Session = new Session;
    $o_Db = new DbHandlerBase();
    $sql 'SELECT * FROM postal_codes';
    $o_Db->setSQL($sql);
    $results $o_Db->runFetchAll($number_returned 100);
    $total_rows $o_Session->get('total_rows');
    // doesnt this block mean call displayResults 84000 times?
    //$i = 0; 
    //while( $i <= $total_rows){
    //    displayResults($results);
    //    $i++;
    //}

    function displayResults($results){
        foreach(
    $results as $value){
            echo (
    $value['postal_code']
             . 
    ', ' $value['city_name']
            . 
    ', '  $value['state_prov_abbr']
            . 
    ', '  $value['country_iso_code'] . '<br />')
            ;
        }
    }

    // get the results and display them once
    displayResults($results);
    Mmm?

  11. #11
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi,

    It actually means that I am hitting the database 8400 times. This is a test based on the side effect of the slowness by not using a LIMIT clause in the SELECT. This approach call the first Select at row 0 and grabs 10 records -> sets a session offset value (10 rows higher than row 0) -> loops grabs 10 records starting at row 9 -> sets offset session ...

    If I do it this way which returns all the data in one query it is one call:
    Code PHP:
    $time_start = microtime(true);
    $sql = 'SELECT * FROM postal_codes';
    $o_Db->setSQL($sql);
    $results = $o_Db->runFetchAll();
    foreach($results as $value){
        echo ($value['postal_code']
        . ', ' . $value['city_name']
        . ', '  . $value['state_prov_abbr']
        . ', '  . $value['country_iso_code'] . '<br />');
    }
    $time_end = microtime(true);
    $time = $time_end - $time_start;
    echo 'Seconds Taken: ' . $time;
    }
    It still takes 60.07 seconds (using a stop-watch on my phone), while when I do a query using MySQL workbench or via the command line and it takes 21 seconds. The 'echo 'SecondsTaken:' . $time value reports it being
    Seconds Taken: 9.69747114182
    This is complete bull as the stop-watch is fairly accurate?!

    I don't know if 21 seconds is too slow for a 38Mb data set (postal_codes) using MySQL workbench from the same workstation that is running the php script (In Firefox 5)? Is it reasonible that the latency would triple when queried via PHP?
    ictus==""

  12. #12
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Many variables involved when actually outputting to the screen. Try using buffering and see what happens.
    PHP Code:
    $time_start microtime(true);
    $sql 'SELECT * FROM postal_codes';
    $o_Db->setSQL($sql);

    $results $o_Db->runFetchAll();
    $time_fetch_all_done microtime(true);

    ob_start();
    foreach(
    $results as $value){
        echo (
    $value['postal_code']
        . 
    ', ' $value['city_name']
        . 
    ', '  $value['state_prov_abbr']
        . 
    ', '  $value['country_iso_code'] . '<br />');
    }
    ob_end_clean();

    $time_end microtime(true);
    $time $time_end $time_start;
    echo 
    'Seconds Query: ' $time_fetch_all_done $time_start;
    echo 
    'Seconds Taken: ' $time;


  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Cripes, sorry, so you are trying to optimise your application and are putting it under stress on purpose.

    In that case I would look seriously at :

    a) Comparing native mysql functions, mysqli and PDO performance pdo mysqli benchmarks (check the dates these were done though)

    b) using xdebug to see where more PHP bottlenecks lie (and maybe even cachegrind files).

  14. #14
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    When this starts to output buffer it asks to download 'query_load_test.php' file?
    Quote Originally Posted by ahundiak View Post
    Many variables involved when actually outputting to the screen. Try using buffering and see what happens.
    PHP Code:
    $time_start microtime(true);
    $sql 'SELECT * FROM postal_codes';
    $o_Db->setSQL($sql);

    $results $o_Db->runFetchAll();
    $time_fetch_all_done microtime(true);

    ob_start();
    foreach(
    $results as $value){
        echo (
    $value['postal_code']
        . 
    ', ' $value['city_name']
        . 
    ', '  $value['state_prov_abbr']
        . 
    ', '  $value['country_iso_code'] . '<br />');
    }
    ob_end_clean();

    $time_end microtime(true);
    $time $time_end $time_start;
    echo 
    'Seconds Query: ' $time_fetch_all_done $time_start;
    echo 
    'Seconds Taken: ' $time;

    ictus==""

  15. #15
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Cups

    Quote Originally Posted by Cups View Post
    Cripes, sorry, so you are trying to optimise your application and are putting it under stress on purpose.
    Yes I guess this is what this comes down to (...optimise your application...) although it did not start out as this; I was just curious how fast it would return a somewhat large dataset and was horrified by the results!

    Quote Originally Posted by Cups View Post
    a) Comparing native mysql functions, mysqli and PDO performance pdo mysqli benchmarks (check the dates these were done though)
    I will work through this today ands see what happens.

    Quote Originally Posted by Cups View Post
    b) using xdebug to see where more PHP bottlenecks lie (and maybe even cachegrind files).
    I will slog through xdebug and see what I get

    I did MySQL tuning using 'mysqltuner'; it recommended updating a number of settings:
    table_cache = 4096

    query_cache_limit = 100M
    query_cache_size = 16M
    innodb_buffer_pool_size = 50M
    This helped some but not fantastic.

    Thanks for everyone's help!
    Steve
    ictus==""

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Let us know how you get on though, I'm intrigued.

  17. #17
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    ps there is also mysqlnd which might appeal to you.

    http://www.php.net/manual/en/mysqlnd.overview.php

  18. #18
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ran the code in your very first post of this thread on a similar zip code table with 84,000 rows and it executed in less than 1/2 a second. So I don't think it's a php issue.....
    aaron-fisher.com - PHP articles and more

  19. #19
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by Cups View Post
    Let us know how you get on though, I'm intrigued.

    ps there is also mysqlnd which might appeal to you.

    http://www.php.net/manual/en/mysqlnd.overview.php
    Thanks CUPs...

    I will let you know how this goes and will tag it onto this post once I get this fixed

    interesting but I am currently not using PHP 5.3 (5.2.1) so I can't take advantage of the Native MySQL driver

  20. #20
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by aamonkey View Post
    I ran the code in your very first post of this thread on a similar zip code table with 84,000 rows and it executed in less than 1/2 a second. So I don't think it's a php issue.....
    Thanks for letting me know, only now it sucks even more knowing that you run the code and it happens so fast? I just don't get it, fast development machine, gigabit network, isolated switching, fast dedicated virtual machine, tuned mysql and still crap; I wonder what is the difference in your setup?

    Did you run the cursor or fetchAll query?

    Regards,
    Steve
    ictus==""

  21. #21
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Thanks for letting me know, only now it sucks even more knowing that you run the code and it happens so fast? I just don't get it, fast development machine, gigabit network, isolated switching, fast dedicated virtual machine, tuned mysql and still crap; I wonder what is the difference in your setup?

    Did you run the cursor or fetchAll query?
    I ran the cursor query as in your first example. I really suspect a configuration issue, most likely with msyql (especially since you mentioned it's still very slow from the command line).

    Is this a server you setup yourself?
    aaron-fisher.com - PHP articles and more

  22. #22
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by aamonkey View Post
    I ran the cursor query as in your first example. I really suspect a configuration issue, most likely with msyql (especially since you mentioned it's still very slow from the command line).

    Is this a server you setup yourself?
    I suspect a configuration issue as well. This is a server we installed in our server array. It is debian 5 running as a Proxmox open VZ virtual machine. It has 4 Gigs RAM and two processors assigned to it. Before my basic tuning (of mysql) it was a default install of apache, mysql, and php.

    I have only used mysql for small applications and Wordpress, so this is the first time using it for a large application. Instead I normally use Oracle or Sybase, which on the same server array does not suffer these problems. Basically not an expert on configuring MySQL/PHP for good performance .
    ictus==""

  23. #23
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wish I could help more but it's not my area of expertise at all....you might try the server management forum, and for clarity take the php out of the mix and focus on how long the query is taking from the command line. Good luck!
    aaron-fisher.com - PHP articles and more


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
  •