Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: [1] 2   Go Down

Author Topic: Патч: попытка оптимизации count-запросов  (Read 25222 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.073 seconds with 18 queries.