Упрощение sql запросов к блогу. v1.10.7 1.X

Сильная нагрузка на базу данных

 
Посетитель
no avatar
Сообщений: 17
Здравствуйте.
На сайте давно наблюдается проблема запросы к базе данных очень тормозят сайт, запросы связаны с блогами.
вот проблемные запросы
Как оптимизировать эти запросы? база данных около 2гб, пользователей онлайн всего до 10-15 бывает.

| 139693 | photoclu_photo | localhost | photoclu_photo | Query | 68 | Sending data | SELECT p.*,
u.nickname as author, u.login, u.is_deleted,
b.allow_who as blog_allow_who,
b.seolink as bloglink,
b.title as blog_title,
b.owner as owner , b.user_id as bloglink, up.imageurl, img.fileurl
FROM pc_blog_posts p
INNER JOIN pc_blogs b ON b.id = p.blog_id
LEFT JOIN pc_users u ON u.id = p.user_id
LEFT JOIN pc_user_profiles up ON up.user_id = u.id
LEFT JOIN pc_upload_images img ON img.target_id = p.id AND img.target = 'blog_post' AND img.component = 'blogs'

WHERE p.published = 1 AND b.owner = 'club'
AND (b.allow_who = 'all')
AND (p.allow_who = 'all')

GROUP BY p.id
ORDER BY p.rating DESC
LIMIT 0, 10 |

Данный запрос выполняется порядка минуты. Если сделать EXPLAIN данного запроса, то видно, что сервер анализирует 186 тысяч записей в базе данных при выполнении, при этом использует сортировку в файлах (это очень медленно), думаю, этот запрос и является основной причиной высокой нагрузки.

+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | p | ref | PRIMARY,seolink,user_id,title,blog_id,cat_id,allow_who | allow_who | 63 | const,const | 186629 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.blog_id | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.user_id | 1 | NULL |
| 1 | SIMPLE | up | ref | user_id | user_id | 4 | photoclu_photo.u.id | 1 | NULL |
| 1 | SIMPLE | img | ref | target_id | target_id | 4 | photoclu_photo.p.id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+

Проблемный запрос находится в файле core/classes/blog.class.php, смотрите функцию getPosts

На странице блогов также тормозит следующий запрос:

| 533 | photoclu_photo | localhost | photoclu_photo | Query | 6 | Sending data | SELECT 1
FROM pc_blog_posts p
INNER JOIN pc_blogs b ON b.id = p.blog_id

WHERE p.published = 1 AND b.owner = 'user'
AND (b.allow_who = 'all')
AND (p.allow_who = 'all') |

Если сделать анализ, то здесь также производится выборка 180 тысяч записей, что очень медленно:

+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+
| 1 | SIMPLE | p | ref | blog_id,allow_who | allow_who | 63 | const,const | 186318 | Using index condition |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.blog_id | 1 | Using where |
+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+

Данный запрос находится в том же файле core/classes/blog.class.php, функция getPostsCount.
Посетитель
small user social cms
МедальАвторитет форумаКубок зрительских симпатийПочетный донор проекта
Сообщений: 2657
@XGluk,
Похоже, что дело не в большом количестве записей в таблице.
Нагенерил 80000 постов. Вот выборка из них:
Спойлер
Это дефолтный 1.10.7
А что у Вас там в запросе за таблицы джойнятся - не ясно.
Похоже, компонент изменен для вывода каких-то картинок. Смотрите индексы во всех таблицах, которые есть в запросе.
Реклама
cms
Посетитель
no avatar
Сообщений: 17
Ris,
Все компоненты дефолтные без изменений, обновлялись с 1.10.3 по мере выхода новых версий.
поэксперементировав выяснил что проблема вот в этой строчке файла frontend.php
///////////////////////// ПОСЛЕДНИЕ ПОСТЫ //////////////////////////////////////
//устанавливаем сортировку
$inDB->orderBy('p.pubdate', 'DESC');
дело именно в запросе 'p.pubdate'
отключив эту строку торможение пропадает полностью, но блоги выдаются не последние по дате, а первые которые были созданы.
Как изменить запрос чтобы выдавались и последние блоги и не тормозило?
Может возможно как-то выводить блоги по последним id из базы _blog_posts или ещё какой-то запрос который будет выдавать последние записи не запрашивая дату?
Редактировалось: 3 раз (Последний: 28 марта 2018 в 12:14)
Посетитель
small user social cms
МедальАвторитет форумаКубок зрительских симпатийПочетный донор проекта
Сообщений: 2657
@XGluk,
Вот без этого $inDB->orderBy('p.pubdate', 'DESC'); - никак.
Повесьте на pubdate индекс и посмотрите на результат.
Код SQL:
  1. ALTER TABLE cms_blog_posts ADD INDEX(`pubdate`)
Посетитель
no avatar
Сообщений: 17
Ris:

@XGluk,
Повесьте на pubdate индекс и посмотрите на результат.
Код SQL:
  1. ALTER TABLE cms_blog_posts ADD INDEX(`pubdate`)
Подскажите куда это вставить, или это sql запрос в phpMyAdmin выполнить?
Ещё заметил такую штуку, сделал вывод последних комментариев, модулем на главную, там тоже выводятся последние комментарии и база комментариев ещё больше чем блогов, но никакого тормоза не наблюдается.
Может можно по аналогии вывода последних комментариев как-то сделать вывод последних блогов?
Посетитель
small user social cms
МедальАвторитет форумаКубок зрительских симпатийПочетный донор проекта
Сообщений: 2657
@XGluk:
sql запрос в phpMyAdmin выполнить?
Да
Посетитель
no avatar
Сообщений: 17
Ris,
С блогами сработало, всё стало летать в Опере но в Хроме почему то тормозит, хостер говорит что Добавленный индекс не используется в запросе.
Но есть такая же проблема с клубами в которых много постов, особенно тормозит при заходе в Блог клуба,в котором много постов. в Клубах где мало постов Блог клуба тормаза нет. Может возможно это решить подобным способом?
Редактировалось: 2 раз (Последний: 28 марта 2018 в 14:36)
Посетитель
no avatar
Сообщений: 17
Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?
Посетитель
small user social cms
Почетный донор проектаМедаль
Сообщений: 316
@XGluk:

Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?
скорее всего https://github.com/instantsoft/icms1/blob/master/core/classes/blog.class.php#L64
Решаю проблемы на ваших проектах)
Посетитель
no avatar
Сообщений: 17
ideasdigger,
Попробовал, эта функция убирает блок категорий полностью, а мне нужно отключить подсчёт постов в них, тоесть чтобы в базу данных не было запроса на подсчёт постов в категории.
Либо сделать что-бы, блок категорий кешировался, а то получается при каждом его выводе пересчитывается всё заново, а каждый запрос сильно грузит базу данных.
Редактировалось: 2 раз (Последний: 28 марта 2018 в 21:04)
Посетитель
small user social cms
Почетный донор проектаМедаль
Сообщений: 316
@XGluk:

ideasdigger,
Попробовал, эта функция убирает блок категорий полностью, а мне нужно отключить подсчёт постов в них, тоесть чтобы в базу данных не было запроса на подсчёт постов в категории.
я лишь указан на функцию получения) измени её на это
Код PHP:
  1.  
  2. public function getBlogCats($blog_id){
  3. $sql = "SELECT cat.*
  4. FROM cms_blog_cats cat";
  5. $result = $this->inDB->query($sql);
  6. if(!$this->inDB->num_rows($result)){ return false; }
  7. $cats = array();
  8. while($cat = $this->inDB->fetch_assoc($result)){
  9. $cats[] = $cat;
  10. }
  11. return cmsCore::callEvent('GET_BLOG_CATS', $cats);
  12. }
  13.  
ну а так же в файле /templates/_default_/components/com_blog_view.tpl убери визуально вывод количества
это
Код PHP:
  1.  
  2. {foreach key=tid item=cat from=$blogcats}
  3. <div class="blog_cat">
  4. <table cellspacing="0" cellpadding="2">
  5. <tr>
  6. <td width="16"><img src="/templates/{template}/images/icons/folder.png" border="0" /></td>
  7. {if $cat_id!=$cat.id}
  8. <td><a href="{$blog.blog_link}/cat-{$cat.id}">{$cat.title}</a> <span style="color:#666666">({$cat.post_count})</span></td>
  9. {else}
  10. <td>{$cat.title} <span style="color:#666666">({$cat.post_count})</span></td>
  11. {$cur_cat=$cat}
  12. {/if}
  13. </tr>
  14. </table>
  15. </div>
  16. {/foreach}
  17.  
на это
Код PHP:
  1.  
  2. {foreach key=tid item=cat from=$blogcats}
  3. <div class="blog_cat">
  4. <table cellspacing="0" cellpadding="2">
  5. <tr>
  6. <td width="16"><img src="/templates/{template}/images/icons/folder.png" border="0" /></td>
  7. {if $cat_id!=$cat.id}
  8. <td><a href="{$blog.blog_link}/cat-{$cat.id}">{$cat.title}</a></td>
  9. {else}
  10. <td>{$cat.title} </td>
  11. {$cur_cat=$cat}
  12. {/if}
  13. </tr>
  14. </table>
  15. </div>
  16. {/foreach}
  17.  
@XGluk:

ideasdigger,
Либо сделать что-бы, блок категорий кешировался, а то получается при каждом его выводе пересчитывается всё заново, а каждый запрос сильно грузит базу данных.
более сложный вариант) но тоже реален, можно кэшировать заранее и обновлять кеш лишь при случаях, добавлении/удалении категорий, изменении добавлении/ удалении записей, или просто по таймингу раз там в 5 минут, используя функции cmsCore::isCached(), cmsCore::getCache(), cmsCore::saveCache(),
Редактировалось: 2 раз (Последний: 28 марта 2018 в 21:46)
Решаю проблемы на ваших проектах)
Посетитель
no avatar
Сообщений: 17
ideasdigger,
Попробовал вставить указанный выше код, считать перестало, но стало показывать вообще все рубрики их всех клубов разом, а не только те которые относятся к конкретному клубу.
Посетитель
no avatar
Сообщений: 17
Благодарю всех кто помог и направил на нужный путь!
В общем методом тыка решил выше описанную задачу.
запрос выглядит так
$sql = "SELECT cat.*
FROM cms_blog_cats cat

WHERE cat.blog_id = '$blog_id'
GROUP BY cat.id";

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

* Получает количество постов по условиям
* @param bool $show_all
* @return int
*/
public function getPostsCount($show_all = false){

$pub_where = ($show_all ? '1=1' : 'p.published = 1');

if(isset($this->owner)) { $pub_where .= " AND b.owner = '{$this->owner}'"; }

Теперь блоги клубов стали просто летать!
В идеале сделать бы кеширование этих данных и функционал бы сохранился и проблема решилась, но моих знаний для этого пока недостаточно, может кто возьмётся?
Посетитель
no avatar
Сообщений: 17
Осталась последняя проблема когда заходишь на стену клуб где большое число постов, то эти страницы по прежнему открываются медленно.
Пока не пойму что там тормозит, может кто подскажет как на стене клуба отключать разные блоки, чтобы выяснить в чём проблема?
Посетитель
no avatar
Сообщений: 17
Не могу найти файл и где формируется sql запрос и сам запрос который выводит информацию из блогов клуба на стену клуба. Есть подозрение что проблема именно в этом запросе.
Подскажите, кто знает где формируется этот запрос?
В начало страницы
Предыдущая темаСледующая тема Перейти на форум:
Быстрый ответ
Чтобы писать на форуме, зарегистрируйтесь или авторизуйтесь.