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

[GeXu3]

Суть проблемы:
запросы на "SELECT count(*)" создают просто неимоверную нагрузку на mysql когда количество фотографий в галерее превышает пару десятков тысяч Время выполнения скрипта ооочень некрасиво возростает (видать не рассчитывал никто на такие нагрузки). Когдато раньше я писал про такую вот проблему - там решение было через кеш кроном. Сильно вдумываццо не стал и решил написать свой велосипед. Сейчас тестирую - вроде работает.

Решение в теории:
Чтобы не ганять по 100 раз count по огромным таблицам нужно делать простое кеширование гдето в базе результатов запроса, чтоб потом обращаццо к ним напрямую через select where не пересчитывая каждый раз таблицы. Желательно для этого не использовать никаких кронов и прочей ерунды.

Решение на практике:
Решение пишу от конкретной таблицы неособо заморачиваясь префиксацией. Думаю и так будет понятно.

Структура таблицы БД:

Code Select Expand

CREATE TABLE `sunph_gx_countcache` (
  `query` text NOT NULL,
  `date` decimal(10,0) NOT NULL,
  `value` bigint(20) NOT NULL,
  PRIMARY KEY  (`query`(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Что здесь к чему:
- query - здесь мы будем хранить текст запроса. Именно по тексту запроса будет происходить проверка есть у нас кеш или нет. Зачем? Для универсальности решения.
- date - временная метка в формате date('U'). По ней будем определять устарел ли кеш и надо ли перекешировать.
- value - собственно само кешируемое значение.

Класс-обработчик:

Code Select Expand

<?php
class api_querycache{

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

private $result;

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

$this->result = 0;

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

$query_add = mysql_real_escape_string($query, $CONFIG['LINK_ID']);


$sql_query = "SELECT
sunph_gx_countcache.value,
sunph_gx_countcache.`date`
FROM
sunph_gx_countcache
WHERE
sunph_gx_countcache.query =  '$query_add' AND
sunph_gx_countcache.`date` >  '$this->time_now'";

$base = cpg_db_query($sql_query);

$size = mysql_num_rows($base);

if ($size == 1){
$tmp = mysql_fetch_object($base);
mysql_free_result($base);

$this->result = $tmp->value;
}else {
mysql_free_result($base);

$base = cpg_db_query($query);
$size = mysql_num_rows($base);
if ($size == 1){
$tmp = mysql_fetch_row($base);
mysql_free_result($base);

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

$sql_query = "delete from sunph_gx_countcache
where
sunph_gx_countcache.query = '$query_add'";
$base = cpg_db_query($sql_query);
mysql_free_result($base);

$sql_query = "insert  into sunph_gx_countcache
   (`query`, `date`, `value`)
values ('$query_add', '$this->time_die', '$this->result')";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
}
}

return TRUE;
}

function result(){
return $this->result;
}
}
?>
Его надо приинклудить хоть даже вверху init.inc.php (у меня для своих классов специальная папка и файл-инициализатор который прикручен вверху init.inc.php)

Что здесь к чему:
- $query - запрос по которому нужно провести кеширование или взять данные из кеша. запрос на каурт, сум и т.д. тоесть возвращающий 1x1 таблицу.
- $timelimit - лимит по времени между кешами. в минутах.

Как это работает:
Сначала в таблице каша ищеццо был ли результат запроса ($query) прокеширован ранее и не истёк ли срок его жизни (`date`>date('U')).
Если вернулась строка размерностью 1 (кеш есть) то запоминаем значение и радуемся.
Если вернулся 0 (или не единица - мало ли какой там глюк произошел) - необходимо перекешировать. Для этого выполняеццо запрос ($query) и если размерность результата 1 происходит кешированье. Сначала удаляюццо все записи где запрос равен кешируемому запросу, после чего в базу заносиццо новый кеш запроса.

Примерение на практике:
Прокешируем, например, один из запросов в index.php.

Старый вариант:

Code Select Expand

$result = cpg_db_query("SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a WHERE p.aid = a.aid AND approved='YES' AND category = {$subcat['cid']}" . $album_filter);
$nbEnr = mysql_fetch_array($result);
mysql_free_result($result);
$pic_count = $nbEnr[0];

Новый вариант:

Code Select Expand

$sql_query = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a WHERE p.aid = a.aid AND approved='YES' AND category = {$subcat['cid']}" . $album_filter;
$gx_tmp = new api_querycache($sql_query);
$pic_count = $gx_tmp->result();


В общем вот так вот

Жду комментариев, может критики, а может вообще тумаков надаёте и выгоните.

З.Ы.: на безопасность подобный марахай увы не тестил.

[GeXu3]

И для примера результат который лежит в таблице кеша (сейв дампа):

Code Select Expand

INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category >= 10000', '1209564972', '58139');
INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category = 2', '1209564972', '301');
INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category = 12', '1209564972', '25');
INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category = 7', '1209564972', '342');
INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category = 6', '1209564972', '143');
INSERT INTO `sunph_gx_countcache` VALUES ('SELECT count(*) FROM sunph_pictures as p, sunph_albums as a WHERE p.aid = a.aid AND approved=\'YES\' AND category = 3', '1209564972', '340');


По сути прокешированы 2 запроса страницы индекса.

[Makc666]

GeXu3, ñïàñèáî áîëüøîå çà ïîäðîáíîå îïèñàíèå è Âû íå ïðîòèâ, åñëè ÿ ïåðåíåñó òåìó â FAQ?

[GeXu3]

Естественно

Только момент - надо быть внимательным где эту штуку можно применить, а где нет (чёб глюков небыло). Если не тот запрос прокешировать то могут вылезти ошибки ровно пока не пройдёт перепись прокешированого запроса.

Щас подумываю еще над двумя функциями: принудительного перекеширования и полной очистки кеша. Но новые версии выдам когда выловлю все глюки текущей.

[GeXu3]

Немного наворотил идею. В процессе возникла необходимость принудительной очистки кеша и перекешированья. Решил добавить в таблицу еще один столбец-метку: page. Тоесть страницу на которой был выполнен запрос. Зачем? Чтобы можно было тереть кеш по нескольким критериям, и если мне надо вытереть кеш только для страницы альбомов то я сделаю это только для неё. В общем обновлённая версия (чувствую что не последняя):

База:

Code Select Expand

CREATE TABLE `sunph_gx_countcache` (
  `query` text NOT NULL,
  `date` decimal(10,0) NOT NULL,
  `value` bigint(20) NOT NULL,
  `page` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`query`(300),`page`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Класс

Code Select Expand

<?php
class api_querycache{

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

private $query_orig;
private $query_escaped;

private $page;

private $result;

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

$this->result = 0;

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

$this->query_orig = $query;
$this->query_escaped = mysql_real_escape_string($this->query_orig, $CONFIG['LINK_ID']);

$this->page = mysql_real_escape_string($_SERVER['PHP_SELF'], $CONFIG['LINK_ID']);

if ($query!=''){
return TRUE;
}else {
return FALSE;
}
}

function exec(){
if ($this->query_orig!=''){
$base = cpg_db_query($this->query_orig);
$size = mysql_num_rows($base);
if ($size == 1){
$tmp = mysql_fetch_row($base);
mysql_free_result($base);

$this->result = $tmp[0];
}
return TRUE;
}else {
return FALSE;
}
}

function cache(){
if ($this->query_orig!=''){
$sql_query = "SELECT sunph_gx_countcache.value, sunph_gx_countcache.`date` FROM sunph_gx_countcache WHERE sunph_gx_countcache.query =  '$this->query_escaped' AND sunph_gx_countcache.`date` >  '$this->time_now' AND sunph_gx_countcache.`page` = '$this->page'";
$base = cpg_db_query($sql_query);
$size = mysql_num_rows($base);

if ($size == 1){
$tmp = mysql_fetch_object($base);
mysql_free_result($base);
$this->result = $tmp->value;
}else {
mysql_free_result($base);
$this->recache();
}
return TRUE;
}else {
return FALSE;
}
}

function recache(){
if ($this->exec()){
$this->insertcache();
}
}

function insertcache(){
if ($this->query_orig!=''){
$this->delete();

$sql_query = "insert  into sunph_gx_countcache (`query`, `date`, `value`, `page`) values ('$this->query_escaped', '$this->time_die', '$this->result', '$this->page')";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
return TRUE;
}else {
return FALSE;
}
}

function delete(){
if ($this->query_orig!=''){
$sql_query = "delete from sunph_gx_countcache where sunph_gx_countcache.query = '$this->query_escaped' and sunph_gx_countcache.`page` = '$this->page'";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
return TRUE;
}else {
return FALSE;
}

}

function delete_query($query = ''){
global $CONFIG;

if ($query!=''){
$query = $this->query_escaped;
}else {
$query = mysql_real_escape_string($query, $CONFIG['LINK_ID']);
}

$sql_query = "delete from sunph_gx_countcache where sunph_gx_countcache.query = '$query'";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
}

function delete_page($page = ''){
global $CONFIG;

if ($page != ''){
$page = $this->page;
}else {
$page = mysql_real_escape_string($page, $CONFIG['LINK_ID']);
}

$sql_query = "delete from sunph_gx_countcache where sunph_gx_countcache.`page` = '$page'";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
}

function delete_all(){
$sql_query = "delete from sunph_gx_countcache";
$base = cpg_db_query($sql_query);
mysql_free_result($base);
}

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

Новые переменные:
query_orig - оригинальный запрос как он есть
query_escaped - заслешеный для добавления в базу
page - страница на которой происходило выполнение


Новые функции:
exec - простое выполнение запроса без кеширования
cache - кешированье
recache - принудительное перекеширование независимо от того истекло время жизни или нет
insertcache - добавить кеш текущего запроса в базу
delete - удалить кеш текущего запроса на текущей странице в базу (ключи запрос и страница)
delete_query - удалить кеш текущего запроса (ключ - запрос. вытирает кеш таких же запросов сделаных на всех страницах. По умолчанию $this->query_escaped но можно указать свою)
delete_page - удалить кеш всех запросов текущей старницы (ключ - имя стрницы. По умолчанию берёццо $this->page, но можно указать свою)
delete_all - вытирает вообще всю таблицу кеша
result - результат выполнения запроса (циферка из запроса или из кеша)

Вызов запроса на кеширование:
Слегка изменился потому как процедура кеширования вынесена из конструктора. Теперь её надо вызывать вручную.

Code Select Expand

$sql_query = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a WHERE p.aid = a.aid AND approved='YES' AND category = {$subcat['cid']}" . $album_filter;
$gx_tmp = new api_querycache($sql_query);
$gx_tmp->cache();
$pic_count = $gx_tmp->result();


В общем вот такие вот изменения. Мне вот чего интересно - как создатели копермайна решат подобную проблему.

[GeXu3]

Забыл добавить в скрипте одно место. В конструкторе где

Code Select Expand

$this->time_limit = $timelimit*60;
надо поставить

Code Select Expand

$this->time_limit = $timelimit*60+rand(0,30);

rand по вкусу

Прибавляет случайное время смерти кеша. Если на странице выполняеццо одновременно 10-20 запросов то это позволит рассинхронизировать время их следующего перекеширования. Проще говоря при первой загрузке кешируюццо все запросы, а далее - группами по 1-2-3 в зависимости от рандома (например Вася прокеширует запросы 3, 5, 7, а через 5 секунд коля прокеширует запросы 1, 4 а лёша - 2 и 7). Серверу станет немного легче.

[GeXu3]

Так, на нагрузках повылазили бока. Всё оттого что insert идёт через cpg_db_query... при высокой нагрузке оч большая вероятность того, что пока один чел заинсертит то перед ним еще трое инсерт кинут. В итоге вылетает ошибка базы данных... исправил пустив insert напрямую через $base = @mysql_query($sql_query, $CONFIG['LINK_ID']); ... кривовато, зато вроде пока держит при среднем 10 человек на сайте (сайт - http://sunphoto.org.ua ... в нём уже внедрена эта система. Кешируюццо почти все запросы на count и sum)...

[Alex Revo]

Спасибо GeXu3!

Решение неплохое и достойно дальнейшего развития.
У меня в галерее число фото дошло до 14 000 и тоже столкнулся с проблемой чрезмерной нагрузки на БД.
Патч проанализировал и установил, пока вижу что кешируются только самые тажелые запросы. Это уже хорошо. На счет безопасности думаю волноваться не о чем, все сделано грамотно.

Если у тебя есть новые варианты или какие-то дополнения, пиши, я подключусь к дальнейшему развитию темы.

Топик перенес в ФАК и прикрепил. Если не сложно отредактируй посты, оставив в первом сообщении текущую версию хака, чтобы посетители долго не искали что к чему.
+1

[mche]

Я бы вместо `query` text NOT NULL, хранил бы в базе md5 от query
немного ьыстрое поиск и индексация.
А вобще для подобных кэшей лучше использовать не базы mysql,
а кеширование на APC, xcache которые хранят кэшируемые значения в памяти вэбсервера, соответсвенно скорочть выше чем при работе с базой.

[Alex Revo]

А вобще для подобных кэшей лучше использовать не базы mysql,
а кеширование на APC, xcache которые хранят кэшируемые значения в памяти вэбсервера, соответсвенно скорочть выше чем при работе с базой.

Расскажите подробнее.

[mche]

Подробнее о чём?
APC, xcache это расширения Php такиеже как GD, модуль по работе с графикой
В стандартную поствку не входят, устанавливаются отдельно, поэтому такой вариант не для всех.
Кроме кеширования пхп кода, могут сохрантть значения переменных в памяти между выполнениями скрипта,
в нашем случае можно хранить результаты count-запросов не используя при этом таблицы mysql
вот пример использования 
http://xcache.lighttpd.net/wiki/XcacheApi#Cacher

[GeXu3]

Давно не писал. Вижу народу понравилось. Думаю скоро буду писать универсальную систему для кеширования результата, чёб кешировать не только цифры, а и остальные тяжелые результаты (пока мысль - сейвить serialized массив). Щас не пишу - идея должна полностью вызреть. Расчитываю встроить кеш напрямую в класс работы с БД...

Я бы вместо `query` text NOT NULL, хранил бы в базе md5 от query

хммм... хорошая мысль - объёмы БД точно сократяццо, а знач и скорость обработки... но реализовать лучче флагом, тоесть когда флаг=1 то гнать md5, а когда нет - чистый запрос. Почему так? очень просто - когда ты дебажишь систему кеширования, тоесть не знаешь работает она как надо или гдето чегото глючит.

[LeaX]

У меня в галерее 10700 файлов и пошли большие нагрузки на процессор и сервер.
Скажите, а вообще в галерее нет своего кэша что ли? Что нужно отдельно ставить?

И кто-нибудь может написать четкую инструкцию этого патча? А то тут вносились какие-то дополнения и сейчас трудно разобраться (мне вот лично) как лучше сделать.

[Alex Revo]

Вопрос очень резонный, у меня с ходу наладить этот патч не получилось, переехал на другой сервер и забросил это дело.
Если у вас на сервере apache 2.0, то посмотрите рецепты отсюда, должно помочь.

[LeaX]

Alex Revo а у вы у кого хоститесь? Неужели для coppermine с картинками больше 10 тыс. нужно отдельный сервер? У меня назгрузка за 7 дней с одной только галереи 600, при лимите в 500.
Хотелось бы понять это нормально или сбой?

[GeXu3]

LeaX, отпишись в аську как получилось допатчить, че потом хостер сказал, чего с нагрузкой. Ато аська грохнулась, адрес сайта успешно забыл, а  интересно же.

[LeaX]

я не допатчила...решила пока на VPS переехать, пока переезжала вылезла проблема с кодировками... 
В общем пока галерею закрыла... вечером сегодня постараюсь на эту тему подумать.
Адрес сайта в профиле - переход по ссылкам с сайта в галерею очень просто найти. 

[Sandrina]

объясните пожалуйста, куда именно надо прописывать код... я поняла только про часть где надо вставить init.inc.php, а другой код, который для БД?

[kastorskiy]

Я бы вместо `query` text NOT NULL, хранил бы в базе md5 от query
немного ьыстрое поиск и индексация.

А crc32 не подойдёт?

[/quote]А вобще для подобных кэшей лучше использовать не базы mysql,
а кеширование на APC, xcache которые хранят кэшируемые значения в памяти вэбсервера, соответсвенно скорочть выше чем при работе с базой.[/quote]
А я собираюсь заюзать memcache. Но для принудительной очистки кеша видимо вё-таки придётся так-же хранить и в базе данные для кеширования (но обращения к этой таблице буддет минимизированы).

[kastorskiy]

Наткнулся на memcache.php – веб-интерфейс для сервера memcached. Он умееет получать список ключей мемкеша. Надо будет поковырять его код, и тогда по идее можно будет избавится от дополнительных запросов к БД.