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

InstantCMS 1.X

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

#1 28 марта 2018 в 00:56
Здравствуйте.
На сайте давно наблюдается проблема запросы к базе данных очень тормозят сайт, запросы связаны с блогами.
вот проблемные запросы
Как оптимизировать эти запросы? база данных около 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.
#2 28 марта 2018 в 08:51
@XGluk,
Похоже, что дело не в большом количестве записей в таблице.
Нагенерил 80000 постов. Вот выборка из них:
  1. D:\OSPanel\domains\D117.tes\components\blogs\frontend.php => getPosts()
  2. SELECT p.*,
  3. u.nickname AS author, u.login, u.is_deleted,
  4. b.allow_who AS blog_allow_who,
  5. b.seolink AS bloglink,
  6. b.title AS blog_title,
  7. b.owner AS owner
  8. FROM cms_blog_posts p
  9. INNER JOIN cms_blogs b ON b.id = p.blog_id
  10. LEFT JOIN cms_users u ON u.id = p.user_id
  11. WHERE 1=1 AND b.owner = 'user'
  12. ORDER BY p.pubdate DESC
  13. LIMIT 0, 10
  14. Запрос занял 0.36299 с.
Это дефолтный 1.10.7
А что у Вас там в запросе за таблицы джойнятся — не ясно.
Похоже, компонент изменен для вывода каких-то картинок. Смотрите индексы во всех таблицах, которые есть в запросе.
#3 28 марта 2018 в 11:48
Ris,
Все компоненты дефолтные без изменений, обновлялись с 1.10.3 по мере выхода новых версий.
поэксперементировав выяснил что проблема вот в этой строчке файла frontend.php
///////////////////////// ПОСЛЕДНИЕ ПОСТЫ //////////////////////////////////////
//устанавливаем сортировку
$inDB->orderBy('p.pubdate', 'DESC');
дело именно в запросе 'p.pubdate'
отключив эту строку торможение пропадает полностью, но блоги выдаются не последние по дате, а первые которые были созданы.
Как изменить запрос чтобы выдавались и последние блоги и не тормозило?
Может возможно как-то выводить блоги по последним id из базы _blog_posts или ещё какой-то запрос который будет выдавать последние записи не запрашивая дату?
#4 28 марта 2018 в 12:37
@XGluk,
Вот без этого $inDB->orderBy('p.pubdate', 'DESC'); — никак.
Повесьте на pubdate индекс и посмотрите на результат.
  1. ALTER TABLE cms_blog_posts ADD INDEX(`pubdate`)
#5 28 марта 2018 в 12:53


@XGluk,
Повесьте на pubdate индекс и посмотрите на результат.

  1. ALTER TABLE cms_blog_posts ADD INDEX(`pubdate`)

Ris
Подскажите куда это вставить, или это sql запрос в phpMyAdmin выполнить?
Ещё заметил такую штуку, сделал вывод последних комментариев, модулем на главную, там тоже выводятся последние комментарии и база комментариев ещё больше чем блогов, но никакого тормоза не наблюдается.
Может можно по аналогии вывода последних комментариев как-то сделать вывод последних блогов?
#6 28 марта 2018 в 13:00

sql запрос в phpMyAdmin выполнить?

@XGluk
Да
#7 28 марта 2018 в 14:08
Ris,
С блогами сработало, всё стало летать в Опере но в Хроме почему то тормозит, хостер говорит что Добавленный индекс не используется в запросе.
Но есть такая же проблема с клубами в которых много постов, особенно тормозит при заходе в Блог клуба, в котором много постов. в Клубах где мало постов Блог клуба тормаза нет. Может возможно это решить подобным способом?
#8 28 марта 2018 в 18:33
Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?
#9 28 марта 2018 в 20:06


Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?

@XGluk
скорее всего github.com/instantsoft/icms1/blob/master/core/classes/blog.class.php#L64
#10 28 марта 2018 в 20:55
ideasdigger,
Попробовал, эта функция убирает блок категорий полностью, а мне нужно отключить подсчёт постов в них, тоесть чтобы в базу данных не было запроса на подсчёт постов в категории.
Либо сделать что-бы, блок категорий кешировался, а то получается при каждом его выводе пересчитывается всё заново, а каждый запрос сильно грузит базу данных.
#11 28 марта 2018 в 21:44


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

@XGluk
я лишь указан на функцию получения) измени её на это
  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 убери визуально вывод количества
это
  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.  
на это
  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.  


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

@XGluk
более сложный вариант) но тоже реален, можно кэшировать заранее и обновлять кеш лишь при случаях, добавлении/удалении категорий, изменении добавлении/ удалении записей, или просто по таймингу раз там в 5 минут, используя функции cmsCore::isCached(), cmsCore::getCache(), cmsCore::saveCache(),
#12 28 марта 2018 в 22:14
ideasdigger,
Попробовал вставить указанный выше код, считать перестало, но стало показывать вообще все рубрики их всех клубов разом, а не только те которые относятся к конкретному клубу.
#13 29 марта 2018 в 00:12
Благодарю всех кто помог и направил на нужный путь!
В общем методом тыка решил выше описанную задачу.
запрос выглядит так
$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}'"; }

Теперь блоги клубов стали просто летать!
В идеале сделать бы кеширование этих данных и функционал бы сохранился и проблема решилась, но моих знаний для этого пока недостаточно, может кто возьмётся?
#14 29 марта 2018 в 00:17
Осталась последняя проблема когда заходишь на стену клуб где большое число постов, то эти страницы по прежнему открываются медленно.
Пока не пойму что там тормозит, может кто подскажет как на стене клуба отключать разные блоки, чтобы выяснить в чём проблема?
#15 29 марта 2018 в 14:24
Не могу найти файл и где формируется sql запрос и сам запрос который выводит информацию из блогов клуба на стену клуба. Есть подозрение что проблема именно в этом запросе.
Подскажите, кто знает где формируется этот запрос?
Вы не можете отвечать в этой теме.
Войдите или зарегистрируйтесь, чтобы писать на форуме.
Используя этот сайт, вы соглашаетесь с тем, что мы используем файлы cookie.