Recent Topics

[2.x] MySQL error Out of memory

Started by on Mar 29, 2009 – Contents updated: Mar 29, 2009

Mar 29, 2009 11:27    

My b2evolution Version: 2.x

We (lahaine.org, b2evo 2.4.6) were getting this error, specially when we used the archives filter in items, in order to modify an old post.

The error appeared always in ItemList2::count_total_rows(), and investigating we found two things: first looking at ulimit -a there was no problem with the mysql cache. Then, this article: http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html, which says "Many functions, such as CURRENT_DATE, NOW, RAND and others, negate the use of the cache."

We looked at _itemquery.class.php to see what happens when using archive filter, and found that if( !empty($m) ), meaning that a date was selected, the code make use of the function EXTRACT (year, month, etc.) in the query. We modified the code going back to the old good

PHP

if( strlen($m) > )
                {
                    $day substr($m62);
                    $date_min $year '-' $month '-' $day $hourmin;
                    $date_max $year '-' $month '-' $day $hourmax;
                }
 
                $this->WHERE_and$this->dbprefix.'datestart <= \''$date_max.'\'' );
 
                $this->WHERE_and$this->dbprefix.'datestart >= \''$date_min.'\'' );

etc., and it worked. The error is gone. So we arrive to the conclusion (even if we couldn't find any source in mysql.com to confirm it) that EXTRACT also negates the use of the mysql cache, and it wouldn't be used. But in b2evo 3.1 still is, maybe the developers can take a look at it...

Apr 04, 2009 21:33

Hi Fede. Could you identify the file and lines you started with and ended with? I looked at /inc/items/model/_itemquery.class.php and found one "!empty($m)" only once, but in an elseif - not a good old fashioned if. In other words I found

Code

elseif( !empty($m) )
{  // We want to restrict on an interval:
  $this->WHERE_and( 'EXTRACT(YEAR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,0,4)) );
  if( strlen($m) > 5 )
    $this->WHERE_and( 'EXTRACT(MONTH FROM '.$this->dbprefix.'datestart)='.intval(substr($m,4,2)) );
  if( strlen($m) > 7 )
    $this->WHERE_and( 'EXTRACT(DAY FROM '.$this->dbprefix.'datestart)='.intval(substr($m,6,2)) );
  if( strlen($m) > 9 )
    $this->WHERE_and( 'EXTRACT(HOUR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,8,2)) );
  if( strlen($m) > 11 )
    $this->WHERE_and( 'EXTRACT(MINUTE FROM '.$this->dbprefix.'datestart)='.intval(substr($m,10,2)) );
  if( strlen($m) > 13 )
    $this->WHERE_and( 'EXTRACT(SECOND FROM '.$this->dbprefix.'datestart)='.intval(substr($m,12,2)) );
 
  $start_is_set = true;
  $stop_is_set = true;
}

Is that the block you replaced with the snippet you provided?

BTW I know nothing about speeding up or slowing down servers other than "it happens". But I figure if you've got a method to make the server work less and do the same stuff then heck yeah I wants it!

Apr 05, 2009 19:38

Hi, EdB. You are right. This is where the function EXTRACT is used, so the entire block will be now:

PHP

elseif( !empty($m) )
            {    // We want to restrict on an interval:
 
// if $m, don't use EXTRACT, because probably negates the use of the mysql cache
// (see http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html)
                $hourmin ' 00:00:00';
                $hourmax ' 23:59:59';
 
                $year substr($m04);
                $date_min $year '-01-01' $hourmin;
                $date_max $year '-12-31' $hourmax;
 
                if( strlen($m) > )
                {
                    $month substr($m42);
                    $date_min $year '-' $month '-01' $hourmin;
                    $date_max $year '-' $month '-31' $hourmax;
                }
 
                if( strlen($m) > )
                {
                    $day substr($m62);
                    $date_min $year '-' $month '-' $day $hourmin;
                    $date_max $year '-' $month '-' $day $hourmax;
                }
 
                $this->WHERE_and$this->dbprefix.'datestart <= \''$date_max.'\'' );
 
                $this->WHERE_and$this->dbprefix.'datestart >= \''$date_min.'\'' );
//  end
 
/*                 $this->WHERE_and( 'EXTRACT(YEAR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,0,4)) );
                if( strlen($m) > 5 )
                    $this->WHERE_and( 'EXTRACT(MONTH FROM '.$this->dbprefix.'datestart)='.intval(substr($m,4,2)) );
                if( strlen($m) > 7 )
                    $this->WHERE_and( 'EXTRACT(DAY FROM '.$this->dbprefix.'datestart)='.intval(substr($m,6,2)) );
                if( strlen($m) > 9 )
                    $this->WHERE_and( 'EXTRACT(HOUR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,8,2)) );
                if( strlen($m) > 11 )
                    $this->WHERE_and( 'EXTRACT(MINUTE FROM '.$this->dbprefix.'datestart)='.intval(substr($m,10,2)) );
                if( strlen($m) > 13 )
                    $this->WHERE_and( 'EXTRACT(SECOND FROM '.$this->dbprefix.'datestart)='.intval(substr($m,12,2)) ); */
 
                $start_is_set true;
                $stop_is_set true;
            }
 
        }

Our solution is not very elegant, but effective. So, if it's true that EXTRACT negates the use of the mysql cache (and it seems so, as now the error is gone), when you don't use this function the mysql server is working less, because it uses the cache. It's important for us, as we have about 40.000 posts. It's all we know about speeding up or slowing down servers.

Apr 05, 2009 19:53

Thanks :D

Appreciate the info cuz I'd have never EVER guessed my way to something like this.


Form is loading...

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