Recent Topics

Scalability Issue

Started by on Aug 14, 2005 – Contents updated: Aug 14, 2005

Aug 14, 2005 21:46    

We recently noticed that when we got to about 15,000 posts in our blog that it was not possible to open the admin->edit section on the largest blog. Some investigation led to the conclusion that the procedure function calc_max() located in _class_itemlist.php was excuting such a large query that our mysql system was balking at handling the request.
The query being executed was :

Code

$this->request = "SELECT  ID, post_author, post_issue_date,   post_mod_date, post_status, post_locale, post_content, post_title, post_urltitle, post_url, post_category, post_autobr, post_flags, post_wordcount, post_comments, post_renderers, post_karma
      FROM ($tableposts INNER JOIN $tablepostcats ON ID = postcat_post_ID)
      INNER JOIN $tablecategories ON postcat_cat_ID = cat_ID ";

What the procedure calc_max was doing was to count the num_rows to see how many entries there were in this particular blog.

Since all that was being determined was the count, we recommend the follwoing changes in _class_itemlist.php:

At about line 486:

Code

$this->request = "SELECT  ID, post_author, post_issue_date,   post_mod_date, post_status, post_locale, post_content, post_title, post_urltitle, post_url, post_category, post_autobr, post_flags, post_wordcount, post_comments, post_renderers, post_karma
    FROM ($tableposts INNER JOIN $tablepostcats ON ID = postcat_post_ID)            INNER JOIN $tablecategories ON postcat_cat_ID = cat_ID ";            
$this->request_alt = "SELECT  count(*) as total_num_posts
    FROM ($tableposts INNER JOIN $tablepostcats ON ID = postcat_post_ID)
    INNER JOIN $tablecategories ON postcat_cat_ID = cat_ID ";
  if( $blog == 1 )
    { // Special case: we aggregate all cats from all blogs
    $this->request .= 'WHERE 1 ';
    $this->request_alt  .= 'WHERE 1 ';
    }
    else
    {
    $this->request .= 'WHERE cat_blog_ID = '. $blog;
    $this->request_alt  .= 'WHERE cat_blog_ID = '. $blog;
    }
    $this->request .= $where. " ORDER BY post_$orderby $limits  ";
    $this->request_alt  .= $where;

{to form a new request that asks for the count(*) rather than all the data.

and additional changes to calc_max as follows}

Code

function calc_max()
  {
    global $DB;
//print"IIIIIIIIIIIIIIIIIIII<BR>";
    if( $this->preview )
      return 1;  // 1 row in preview mode
// This is a ridiculous bit of code. It selects every post in the blog in great detail, just to calculate the number.
// A count request would have been a lot simpler
//For now I have just set this to return 100, more thqan enough for my purposes  JWN 8/11/05
    $nxt_request = $this->request_alt;
  //echo "IIIIIIII".$nxt_request."IIIIIIIII<BR>";    
//    if( $pos = strpos(strtoupper($this->request), 'LIMIT'))
//    { // Remove the limit form the request
//      $nxt_request = substr($this->request, 0, $pos);
//    }
//    echo "IIIIIIII".$nxt_request."IIIIIIIII<BR>";
 
    $DB->query( $nxt_request );
    $return_array_alt = $DB->get_col( $query = NULL, $x=0, $title = '' );
    //print_r($return_array_alt);
    //$this->total_num_posts = $DB->num_rows;
    $this->total_num_posts = $return_array_alt[0];
//print "total_num_posts = $total_num_posts:$DB->total_num_posts:$this->total_num_posts";
    $this->max_paged = intval( ($this->total_num_posts-1) / max($this->posts_per_page, $this->result_num_rows)) +1;
    if( $this->max_paged < 1 )
      $this->max_paged =1;
  }

Hope you can use this for new version.

Aug 15, 2005 05:27

Please clarify for me if you mean both of these changes together. I'm not on the dev team and am therefore not asking wrt the core code - I'm just curious. Seems to me that your hack makes your server's life easier when the person in the back office is on blog#1, so I wonder why limit it to triggering only on blog#1. My thought is that if it's good on blog 1 in a big blog then it's probably good on any blog number in any blog.

The changes to the calc_max function are where I'm most curious. Seems to me you are saying "do both of these or your back office will die" but I figured I'd ask.

Aug 15, 2005 06:45

Yes, they all go together. Up near the top, I redefined the $request to $request_alt, and then use this in calc_max instead of the original query. It does work with all the blogs: see the line:

$this->request_alt .= 'WHERE cat_blog_ID = '. $blog;

Since calc_max doesn't use any of the data from the original query, jsut selecting count(*) makes the whole thing go much faster when you have a lot of posts in a blog. On my system, it stopped working at about 15,000 posts; but, obviously, that depends on the size of the sql server.


Form is loading...

Photo gallery software – This forum is powered by b2evolution CMS, a complete engine for your website.