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

[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

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

[Жека]

FROM (smf_ban_groups AS bg, smf_ban_items AS bi, smf_members AS mem)

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

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

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

[digger®]

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

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

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

[Жека]

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

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

[digger®]

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

Проблема в том, что вот так вот через запятую таблицы нельзя писать.
Берется декартово произведение всех 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.

[Жека]

Следуя вашей логике,

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

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

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

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

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

[digger®]

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

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

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

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

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

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

[Жека]

Это коробочный запрос из стандартного управления банами в 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®]

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

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

[Жека]

так в двойке этот запрос переписан

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

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

они оба перебирают одинаковое количество строк в таблицах

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