Как в 14 раз снизить нагрузку на базу данных MySQL


Tickets
Если кратко, то таблица modx_tickets_views — критически увеличивает нагрузку на базу данных MySQL. Уменьшить нагрузку можно отключив (если включен) счетчик просмотра неавторизованными пользователями tickets.count_guests или обнулить (очистить)/удалить таблицу modx_tickets_views из базы данных MySQL.


Ниже отрывки из переписки с ТП Бегет (может кому пригодится)

Прошу снять ограничения с сайта
На вашем сайте sql запрос
SELECT COUNT(DISTINCT `TicketView`.`parent`, `TicketView`.`uid`, `TicketView`.`guest_key`) FROM `modx_tickets_views` AS `TicketView` WHERE `TicketView`.`parent` = 59;
Мне кажеться стоит оптимизировать, потому что конструкция
COUNT(DISTINCT ...)
явно грамоздкая так как в таблице modx_tickets_views 2378294 записей, вот EXPLAIN этого запроса
modx3@bane.beget.ru[modx3]> EXPLAIN SELECT COUNT(DISTINCT `TicketView`.`parent`, `TicketView`.`uid`, `TicketView`.`guest_key`) FROM `modx_tickets_views` AS `TicketView` WHERE `TicketView`.`parent` 
= 59;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                            |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------------+
|  1 | SIMPLE      | TicketView | NULL       | range | PRIMARY       | PRIMARY | 104     | NULL | 176437 |   100.00 | Using where; Using index for group-by (scanning) |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------------+
Также в качестве оптимизации могу предложить сконвертировать всю базу данных в InnoDB, что тоже скажеться хорошо на производительности.

Если есть возможность, прошу помочь с перечисленной ниже оптимизацией:
Уменьшить конструкцию COUNT(DISTINCT …)
Сконвертировать всю базу данных в InnoDB
Здравствуйте, базу сайта перевел в innodb, создав отдельную базу — ххх, старую не стал трогать modx3, она также доступна в разделе MySQL. К сожалению запрос
COUNT(DISTINCT ...)
не смогу переписать так как здесь выборка идет по нескольким колонкам, вот небольшой совет как переписать подобный запрос stackoverflow.com/questions/24671158 /alternative-for-count-distinct, но как уже сказал проблема в том что невозможно сортировать сразу по нескольким колонкам. Также не хочеться его переписывать или вообще вносить какие либо изменения в логигу работы modx так как мы с Вами не владельцы этого кода и не сможем быстро разобраться в случае если все сломается, а сломаться может легко. Вообще могу предложить либо зачистить все таблицу modx_tickets_views либо удалить большую часть записей тем самым запрос будет выполнять быстрее, в данный момент в этой таблицы почти 3 миллиона записей, что в принципе является бутылочным горлошком с точки зрения производительности. Узнайте для чего нужна эта таблица и если она нужна для логов или это какой-нибудь счетчик посещений и скорее всего ничего особо не дает нам, то можно смело делать TRUNCATE, предварительно сделав бэкап этой таблицы.
[modx3]> select count(*) from modx_tickets_views;
+----------+
| count(*) |
+----------+
|  2385727 |
+----------+
1 row in set (0,51 sec)
И последний момент: Redis, за счет его высокой скорости работы, удобно использовать для хранения различных счетчиков, например, количества просмотров страниц сайта, или же каких-либо временных данных.
Означает ли это, что при включенном Redis данные таблицы modx_tickets_views начнут автоматически сохраняться в памяти Redis, а не в БД
Нет, изменения в работе сайтов автоматически не произойдут.
Включение Redis, в данном случае означает, что у Вас появляется доступ к контейнеру с данной БД. Использовать его Вы можете как угодно.
Для реализации описанного Вами функционала потребует либо дополнительных настроек или установки плагина, либо вмешательство в код CMS.
09 октября 2017, 12:52    Yar   
3    802 -1

Комментарии (24)

  1. Василий Наумкин 09 октября 2017, 12:57 # +2
    О сколько нам открытий чудных!

    Наверное, не просто так эта настройка выключена по умолчанию.
    1. Василий Наумкин 09 октября 2017, 13:48 # +3
      Ну и, наверное, реклама modhost.pro: количество записей в tickets_views у modx.pro составляет 13 254 408 штук.

      При этом, наш сайт находится на самом слабом сервере хостинга — h1, и проблем никаких нет.
    2. Алексей Федоров 09 октября 2017, 18:33 # 0
      Может я слишком разбалован, но привык считать «готовым решением» проблемы такой подход, который поможет сохранить полезный функционал и убрать сложности. То есть — отключите, удалите — это не решение, на мой взгляд. Вот если бы была некая альтернатива, чтобы и счетчик сохранить, и нагрузку уменьшить — это уже решение.
      1. Yar 09 октября 2017, 20:34 # 0
        Более подходящего раздела на сайте я не нашел
      2. Василий Столейков 09 октября 2017, 20:54 # +1
        Я тоже думал над этой проблемой…
        У меня на одном из сайтов таблица tickets_views имеет 12,166,216 строк и весит1.9 ГиБ!
        И функционал очень нужный (нельзя отключить подсчёт гостей) и база разбухает всё больше…

        Моё видение решения (ещё не пробовал):
        1. Предложить изменить функционал сайта и подсчитывать не все просмотры страниц, а просмотры за последние 3 месяца например. А остальные подчищать кроном время от времени. Но это ведёт за собой серьёзное изменение функционала, и заказчик врядли на это пойдёт.
        2. Проходится регулярно кроном по всем страницам сайта изаписывать их просмотры в тв или расширенное поле таблицы контента. Но это хоть и ускорит работу сайта, но не решит проблему разбухания базы...
        1. Yar 09 октября 2017, 21:00 # +1
          У меня теперь не количество просмотров, а количество просматривающих страницу в данный момент
          1. Василий Столейков 09 октября 2017, 21:31 # 0
            Интересная идея!
            И за какой период ты считаешь просмотры? 10, 20, 30 минут?
            1. Yar 09 октября 2017, 21:36 # -1
              Честно — никой, тупо изменил формулировку)
              Набрал по 10-150 просмотров и отключил подсчет неавторизов. Теперь это количество просматривающих страницу. (Инфо-сайт со средним качеством контента)
          2. Максим Кузнецов 09 октября 2017, 21:49 # +4
            Проходится регулярно кроном по всем страницам сайта изаписывать их просмотры в тв или расширенное поле таблицы контента. Но это хоть и ускорит работу сайта, но не решит проблему разбухания базы...

            Как вариант — по крону пробегаться по tickets_views, считая count на данный момент и, перенеся полученный результат в отдельное поле, очищать таблицу. При последующих срабатываниях — приплюсовывать результат.
            1. Василий Столейков 09 октября 2017, 21:51 # 0
              О! Классная идея! Надо будет обязательно реализовать…
              1. Сергей Шлоков 10 октября 2017, 12:20 # +3
                Но перед тем, как это сделать, нужно учесть, что в Tickets просмотры на самом деле это визиты. Т.е. один и тот же пользователь считается только один раз, сколько F5 не жми. А предложенный тобой вариант будет учитывать каждый просмотр. Например, у меня в siteStatistics считается и то и другое, кому что нужно.
                1. Алексей Федоров 10 октября 2017, 12:45 # +1
                  Сергей, компонент решает завяленную топикстартером проблему?
                  1. Василий Столейков 10 октября 2017, 13:07 # 0
                    Поддерживаю вопрос
                    1. Сергей Шлоков 10 октября 2017, 14:32 # 0
                      Чудес не бывает. Если нужна статистика, то нужны и данные. Tickets и siteStatistics в этом плане работают одинаково. По другому никак.
                  2. Василий Столейков 10 октября 2017, 13:12 # 0
                    К сожалению на tickts_views у меня слишком много логики построено на сайте, например используется ViewsOnline и другие подсчёты основанные на посещениях…
                    Да и как говорил Сергей, при таком подходе будут считаться просмотры, а не только посетители, мешанина будет.
                    1. Максим Кузнецов 10 октября 2017, 13:59 # 0
                      *пожал плечами* В данной задаче нельзя выиграть во всем сразу — весь вопрос в том, что для вас в приоритете:

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

                      — если критично уменьшить размер БД, то можно попробовать создавать для каждого тикета лишь по 1 записи, в которой будут перечислены id всех пользователей, которые просмотрели тикет. Но в таком случае отпадут гостевые просмотры.

                      — если нет возможности менять связанные дополнения, то остается или отключить просмотры для гостей, или повышать характеристики сервера
                2. Сергей Шлоков 10 октября 2017, 12:22 # +5
                  Жду статью про снижение нагрузки в 100500 раз при замене ресурсов на статические страницы.
                  1. Yar 10 октября 2017, 13:05 # +1
                    Я б такую лайкнул ;)
                  2. Пётр Молчанов 11 октября 2017, 11:29 # 0
                    А как-то вообще можно отключить данный подсчет? Если нет, то было бы не плохо добавить такую опцию. Я, например, не использую эти просмотры никак.
                    1. Василий Столейков 12 октября 2017, 08:05 # 0
                      Думаю можно и в плагине вручную закомментировать, но если в настройках не отмечен подсчёт гостями, то и данные у тебя практически не будут собираться (если сайт без авторизации).
                      Я бы лучше кроном (либо плагином) время от времени просто очищал таблицу просмотров, ну или просто не обращал бы на неё внимания, не думаю что это так критично без просмотра гостями.
                      1. Пётр Молчанов 12 октября 2017, 10:55 # 0
                        щас посмотрел — более 200 тыс записей в таблице. очистил)
                      2. Василий Наумкин 12 октября 2017, 09:04 # 0
                        Нет, нельзя.

                        Это необходимо для вывода количества непрочитанных комментов в списке тикетов авторизованному пользователю.
                        1. Пётр Молчанов 12 октября 2017, 10:55 # 0
                          буду знать) каменты у нас че-то не охотно пишут… дай бог штук 10 за год
                      3. Волков Николай 14 октября 2017, 18:33 # 0
                        Увидел картинку с нагрузками и сразу вспомнил случай из жизни :-) Как-то раз я немного ошибся и в результате…
                        Вы должны авторизоваться, чтобы оставлять комментарии.