Патч: попытка оптимизации count-запросов

[Alex Revo]

Держите в курсе.

[kastorskiy]

Держите в курсе.

Вот что у меня получилось на данный момент (писано на скорую руку, код не причёсан и наличествует харкод там, где следовало бы вынести в настройки, т.к. спешил успеть к ожидаемому наплыву посетителей):

Code Select Expand

<?php
class api_querycache{

private $time_limit;
private $time_now;
private $time_die;

private $result;

function __construct($query, $timelimit = 60){
global $CONFIG;

$this->result = 0;

$this->time_limit = $timelimit*60+rand(0,30);
$this->time_now = date('U');
$this->time_die = $this->time_now+$this->time_limit;

$query_add = mysql_real_escape_string($query);

$memcache = new Memcache;
$memcache->connect('localhost', 11211);
$memcache->get('extreme_sql_'.md5($query));

if ( ($result = $memcache->get('extreme_sql_'.md5($query))) !== false ){
$this->result = $result;
}else {
$base = mysql_query($query);
$size = mysql_num_rows($base);
if ($size == 1){
$tmp = mysql_fetch_row($base);
mysql_free_result($base);

$this->result = $tmp[0];

$memcache->set('extreme_sql_'.md5($query), $this->result, false, $this->time_limit);
}
}

return TRUE;
}

function result(){
return $this->result;
}
}

class api_delcache {

static private $MEMCACHE_SERVERS = array('localhost:11211'); // add more as an array

///////////MEMCACHE FUNCTIONS /////////////////////////////////////////////////////////////////////

private function sendMemcacheCommands($command){
$result = array();

foreach(self::$MEMCACHE_SERVERS as $server){
$strs = explode(':',$server);
$host = $strs[0];
$port = $strs[1];
$result[$server] = self::sendMemcacheCommand($host,$port,$command);
}
return $result;
}
private function sendMemcacheCommand($server,$port,$command){

$s = @fsockopen($server,$port);
if (!$s){
return false;
}

fwrite($s, $command."\r\n");

$buf='';
while ((!feof($s))) {
$buf .= fgets($s, 256);
if (strpos($buf,"END\r\n")!==false){ // stat says end
    break;
}
if (strpos($buf,"DELETED\r\n")!==false || strpos($buf,"NOT_FOUND\r\n")!==false){ // delete says these
    break;
}
if (strpos($buf,"OK\r\n")!==false){ // flush_all says ok
    break;
}
}
    fclose($s);
    return self::parseMemcacheResults($buf);
}
private function parseMemcacheResults($str){
    
$res = array();
$lines = explode("\r\n",$str);
$cnt = count($lines);
for($i=0; $i< $cnt; $i++){
    $line = $lines[$i];
$l = explode(' ',$line,3);
if (count($l)==3){
$res[$l[0]][$l[1]]=$l[2];
if ($l[0]=='VALUE'){ // next line is the value
    $res[$l[0]][$l[1]] = array();
    list ($flag,$size)=explode(' ',$l[2]);
    $res[$l[0]][$l[1]]['stat']=array('flag'=>$flag,'size'=>$size);
    $res[$l[0]][$l[1]]['value']=$lines[++$i];
}
}elseif($line=='DELETED' || $line=='NOT_FOUND' || $line=='OK'){
    return $line;
}
}
return $res;

}

private function dumpCacheSlab($server,$slabId,$limit){
    list($host,$port) = explode(':',$server);
    $resp = self::sendMemcacheCommand($host,$port,'stats cachedump '.$slabId.' '.$limit);

   return $resp;

}

private function getCacheItems(){
 $items = self::sendMemcacheCommands('stats items');
 $serverItems = array();
 $totalItems = array();
 foreach ($items as $server=>$itemlist){
    $serverItems[$server] = array();
    $totalItems[$server]=0;
    if (!isset($itemlist['STAT'])){
        continue;
    }

    $iteminfo = $itemlist['STAT'];

    foreach($iteminfo as $keyinfo=>$value){
        if (preg_match('/items\:(\d+?)\:(.+?)$/',$keyinfo,$matches)){
            $serverItems[$server][$matches[1]][$matches[2]] = $value;
            if ($matches[2]=='number'){
                $totalItems[$server] +=$value;
            }
        }
    }
 }
 return array('items'=>$serverItems,'counts'=>$totalItems);
}

//////////////////////////////////////////////////////

static function delCache() {
$cacheItems= self::getCacheItems();
$items = $cacheItems['items'];
$totals = $cacheItems['counts'];

$memcache = new Memcache;
$memcache->connect('localhost', 11211);
$memcache->get('extreme_sql_'.md5($query));

foreach($items as $server => $entries) {
        foreach($entries as $slabId => $slab) {
    $items = self::dumpCacheSlab($server,$slabId,$slab['number']);
            foreach($items['ITEM'] as $itemKey=>$itemInfo){
             if (substr($itemKey, 0, 12) == 'extreme_sql_') {
            $memcache->delete($itemKey);
                }
            }
}
}
}

}
?>

Первый класс аналогичен сабжевому, только кеш хранится не в базе, а в memcashe. Второй класс служит для очистки кеша, используется так:

Code Select Expand

api_delcache::delCache();

Поскольку в моём случае фотки добавляются редко, я закешировал все агрегатные запросы с таблицами $CONFIG['TABLE_PICTURES'] и $CONFIG['TABLE_ALBUMS']. Т.е. остальные таблицы, например $CONFIG['TABLE_COMMENTS'], пока не кеширую, т.к. обновляются они значительно чаще (но возможно придётся, т.к. $CONFIG['TABLE_COMMENTS'] тоже не маленькая, больше 10000 коментов). А при добавлении фоток очищаю кеш.

Эксперемент показал, что нагрузка таки действительно уменьшилась, и возможно многим такой оптимизации окажется достаточно. Но в моем случае этого всё-таки оказалось недостаточно, наблюдались тормоза в работе галереи. Видимо надо попробовать кешировать не только агрегатные запросы.

Пока продолжаю анализировать лог медленных запросов, чего и другим советую.