Проблема с форумом: нагрузка на MySQL

Автор yup, 17 октября 2013, 05:50:24

« предыдущая - следующая »

0 Пользователей и 1 гость просматривают эту тему.

yup

Пожалуйста, помогите разобраться. Форум (yuptalk.ru) грузится через раз.

На данный момент понятно, что проблема с нагрузкой MySQL.

Письмо от хостера следующее:
Уведомляем Вас, что работой базы данных MySQL u16619_3 создается повышенная нагрузка на сервер баз данных. Примеры запросов находятся в прикрепленном файле.

Оптимизируйте, пожалуйста, Ваши скрипты, SQL-запросы и дайте нам знать о результатах.  Например, Вы можете использовать метод EXPLAIN для выяснения "тяжелых" запросов и создание индексов (CREATE INDEX), тем самым снизив нагрузку на базу данных:
* http://masterhost.ru/support/faq/technical/mysql-optimization/

Хостер прислал лог:
SELECT mem.ID_MEMBER, mem.is_activated + 10 AS new_value      FROM (smf_ban_groups AS bg, smf_ban_items AS bi, smf_members AS mem)      WHERE bg.ID_BAN_GROUP = bi.ID_BAN_GROUP         AND bg.cannot_access = 1         AND (bg.expire_time IS NULL OR bg.expire_time > 1381659249)         AND (mem.ID_MEMBER = bi.ID_MEMBER OR mem.emailAddress LIKE bi.email_address)         AND mem.is_activated < 10;


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

Версия: 1.1.9

Подскажите, пожалуйста, как можно попробовать починить. Сейчас хостимся на мастерхосте.

Жека

Цитата: yup от 17 октября 2013, 05:50:24FROM (smf_ban_groups AS bg, smf_ban_items AS bi, smf_members AS mem)

Запрос сами писали?

Проблема в том, что вот так вот через запятую таблицы нельзя писать.
Берется декартово произведение всех 3-х таблиц.

Например, если в каждой таблице по 100 записей, то MySQL должен будет обработать 100*100*100 = 1 млн. записей.
Отсюда и тормоза.

digger®

Цитата: Жека от 17 октября 2013, 09:14:03
Проблема в том, что вот так вот через запятую таблицы нельзя писать.
Берется декартово произведение всех 3-х таблиц.

Например, если в каждой таблице по 100 записей, то MySQL должен будет обработать 100*100*100 = 1 млн. записей.
Отсюда и тормоза.

:facepalm:
Зачем писать про то, в чем не разбираешься?

Жека

Цитата: digger® от 17 октября 2013, 13:15:22Зачем писать про то, в чем не разбираешься?

Слушаю вашу версию ;)

digger®

Цитата: Жека от 17 октября 2013, 15:41:03
Слушаю вашу версию ;)


ЦитироватьПроблема в том, что вот так вот через запятую таблицы нельзя писать.
Берется декартово произведение всех 3-х таблиц.
Например, если в каждой таблице по 100 записей, то MySQL должен будет обработать 100*100*100 = 1 млн. записей.
Отсюда и тормоза.

Следуя вашей логике,
Код: (sql)
SELECT any FROM table

нельзя писать, потому что MySQL должен будет обработать все записи из таблицы, а вдруг их там миллионы.

Код: (sql)
SELECT any FROM table1 JOIN table2

нельзя писать, потому что MySQL должен будет обработать умножение всех записей таблиц, а вдруг их там миллионы.

Да и вообще нельзя писать никакие SQL запросы, потому что MySQL должен будет обработать записи из таблиц, а вдруг их там миллионы.
Однако, прогрессивное человечество давно изобрело условия для выборок и индексы для записей. Поэтому, при критике запросов, желательно смотреть не до первого селекта, а подальше.

У ТС в запросе обычные джойны в плоском виде, с условиями и индексами и никакого особого криминала там нет, чтобы безапеляционно заявлять, что "вот так вот через запятую таблицы нельзя писать".

yup,
Для начала, обновите движок с версии шестилетней давности до, хотя бы, актуальной SMF1.

Жека

Цитата: digger® от 17 октября 2013, 16:03:11Следуя вашей логике,

Ничего подобного.
Я акцентировал внимание на перечисление таблиц через запятую в разделе FROM

Цитата: digger® от 17 октября 2013, 16:03:11У ТС в запросе обычные джойны в плоском виде, с условиями и индексами и никакого особого криминала там нет

Да ну.
Мне просто интересно сколько записей вернет запрос на базе ТС такого вида:

SELECT SUM(1) FROM smf_ban_groups AS bg, smf_ban_items AS bi, smf_members AS mem

Чем больше исходная выборка, тем хуже, и никакие WHERE тут не помогут, пока не перепишет запрос.

digger®

Цитата: Жека от 17 октября 2013, 17:13:57
Ничего подобного.
Я акцентировал внимание на перечисление таблиц через запятую в разделе FROM

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

Цитировать
Мне просто интересно сколько записей вернет запрос на базе ТС такого вида:
SELECT SUM(1) FROM smf_ban_groups AS bg, smf_ban_items AS bi, smf_members AS mem

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

Цитировать
Чем больше исходная выборка, тем хуже, и никакие WHERE тут не помогут, пока не перепишет запрос.

Это коробочный запрос из стандартного управления банами в SMF. Зачем ТС должен его переписывать?

Жека

Цитата: digger® от 17 октября 2013, 17:44:42Это коробочный запрос из стандартного управления банами в SMF. Зачем ТС должен его переписывать?

Ну, собственно, ТС не ответил на мой первый же вопрос ему, поэтому могу только догадываться.

Раз коробочный, то только оптимизация таблиц и индексов поможет.

yup

Всем большое спасибо за мнения!

Отвечаю на вопросы. Никаких изменений в движке вручную не делалось, почему бан пользователя занимает много времени и, видимо, ресурсов не понятно.

Обновить движок не удается. Похоже, что проблема в том, что каким-то образом отдельные файлы / база данных оказались разных версий.

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

Спасибо!

kak2z

а если не через запятую, как тогда этот запрос более эффективно сделать?

Код

SELECT * FROM  smf_topics, smf_messages, smf_members, smf_boards
WHERE smf_topics.id_board NOT IN ({array_int:id_boards})
AND smf_topics.approved = 1
AND smf_topics.id_board=smf_boards.id_board
AND smf_topics.id_first_msg=smf_messages.id_msg
AND smf_topics.id_member_started=smf_members.id_member
ORDER BY  smf_topics.id_topic DESC
LIMIT {int:start}, {int:num_news}
Если нужно что то исправить, обновить, переставить, настроить, сделать форум заново - пишите в ЛС)

digger®

Цитата: kak2z от 18 октября 2013, 20:46:41а если не через запятую, как тогда этот запрос более эффективно сделать?

Этот запрос итак достаточно эффективно решает задачу для которой он написан.
Можно через join объединить таблицы, так в двойке этот запрос переписан, но они оба перебирают одинаковое количество строк в таблицах.

Жека

Цитата: digger® от 18 октября 2013, 21:47:31так в двойке этот запрос переписан

Не удивительно, ибо "Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, поскольку его не рекомендует стандарт SQL ANSI"

Лучше всегда писать явные join-ы, так и читабельнее, и не забудешь никогда в where сделать нужные нужные соединения.

Цитата: digger® от 18 октября 2013, 21:47:31они оба перебирают одинаковое количество строк в таблицах

Согласен при условии правильной секции WHERE, чтобы оптимизатор разобрал, что это всё-таки INNER JOIN, а не CROSS.