Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2   Go Down

Author Topic: Патч: попытка оптимизации count-запросов  (Read 23180 times)

0 Members and 1 Guest are viewing this topic.

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

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

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

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

Структура таблицы БД:
Code: [Select]
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]
<?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]
$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]
$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();


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

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

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

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

И для примера результат который лежит в таблице кеша (сейв дампа):
Code: [Select]
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 запроса страницы индекса.
Logged

Makc666

  • Moderator
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 1614
  • Русский (ISO-8859-1) - Russian - Русский (Windows)
    • Makc's home page


GeXu3, спасибо большое за подробное описание и Вы не против, если я перенесу тему в FAQ?
Logged

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

Естественно :)

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

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

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

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

База:
Code: [Select]
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]
<?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]
$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();


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

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

Забыл добавить в скрипте одно место. В конструкторе где
Code: [Select]
$this->time_limit = $timelimit*60;надо поставить
Code: [Select]
$this->time_limit = $timelimit*60+rand(0,30);
rand по вкусу :)

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

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)

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

Alex Revo

  • Moderator
  • Coppermine addict
  • ****
  • Offline Offline
  • Posts: 637
  • Инженер человеческих душ
    • Фото Ростова-на-Дону

Спасибо GeXu3!

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

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

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

mche

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 13
    • Mega Photo Gallery
Re: Патч: попытка оптимизации count-запросов
« Reply #8 on: August 12, 2008, 06:08:17 am »

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

Alex Revo

  • Moderator
  • Coppermine addict
  • ****
  • Offline Offline
  • Posts: 637
  • Инженер человеческих душ
    • Фото Ростова-на-Дону
Re: Патч: попытка оптимизации count-запросов
« Reply #9 on: August 12, 2008, 06:57:42 am »

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

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

mche

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 13
    • Mega Photo Gallery
Re: Патч: попытка оптимизации count-запросов
« Reply #10 on: August 12, 2008, 07:43:17 am »

Подробнее о чём?
APC, xcache это расширения Php такиеже как GD, модуль по работе с графикой
В стандартную поствку не входят, устанавливаются отдельно, поэтому такой вариант не для всех.
Кроме кеширования пхп кода, могут сохрантть значения переменных в памяти между выполнениями скрипта,
в нашем случае можно хранить результаты count-запросов не используя при этом таблицы mysql
вот пример использования 
http://xcache.lighttpd.net/wiki/XcacheApi#Cacher
« Last Edit: January 13, 2009, 07:47:24 pm by Makc666 »
Logged

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)
Re: Патч: попытка оптимизации count-запросов
« Reply #11 on: September 26, 2008, 11:40:14 pm »

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

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

LeaX

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 96
    • Lost Jack and Kate
Re: Патч: попытка оптимизации count-запросов
« Reply #12 on: January 13, 2009, 12:14:04 pm »

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

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

Alex Revo

  • Moderator
  • Coppermine addict
  • ****
  • Offline Offline
  • Posts: 637
  • Инженер человеческих душ
    • Фото Ростова-на-Дону
Re: Патч: попытка оптимизации count-запросов
« Reply #13 on: January 13, 2009, 12:27:00 pm »

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

LeaX

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 96
    • Lost Jack and Kate
Re: Патч: попытка оптимизации count-запросов
« Reply #14 on: January 13, 2009, 03:23:29 pm »

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

GeXu3

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 30
    • Homepage (my info)
Re: Патч: попытка оптимизации count-запросов
« Reply #15 on: January 20, 2009, 01:31:36 am »

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

LeaX

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 96
    • Lost Jack and Kate
Re: Патч: попытка оптимизации count-запросов
« Reply #16 on: January 20, 2009, 10:24:18 am »

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

Sandrina

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 45
Re: Патч: попытка оптимизации count-запросов
« Reply #17 on: February 10, 2010, 11:02:20 am »

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

kastorskiy

  • Coppermine newbie
  • Offline Offline
  • Posts: 5

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

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

kastorskiy

  • Coppermine newbie
  • Offline Offline
  • Posts: 5

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

Page created in 0.042 seconds with 18 queries.