БД MySQL, имеется таблица statistics, куда собирается статистика.
Таблица состоит из 15 полей, имеет индекс по двум полям.
Когда пользователь заходит на сайт или совершает на сайте какое-нибудь действие, идет запись в эту таблицу.
В день в таблицу в среднем пишется 30-80 тысяч записей.
Подскажите, пожалуйста, правильно ли я понимаю следующее:
1. При каждой записи в таблицу индексы таблицы пересчитываются
2. Если так, то чем больше будет эта таблица, тем дольше будет пересчитываться индекс
Если вышеописанные утверждения верны, то подойдет ли следующая схема: Создаю таблицу tmp_statistics, которая полностью идентична таблице statistics, но не имеет индексов. Пишу статистику только в нее, а раз в какое-то время (например раз в 30 минут по крону) запускается скрипт, который берет данные из таблицы tmp_statistics и вставляет их в таблицу statistics разом большой пачкой, а таблицу tmp_statistics очищает.
Например, если за 30 минут в tmp_statistics собралось 10000 записей, то индекс пересчитается один раз, а не 10000 раз.
Имеет ли смысл такая оптимизация?
PS. Я знаю, что для статистики MySQL не очень подходит. Мы планируем переходить на другие хранилища, но пока нужно оптимизировать MySQL
Александр, В таблице хранятся данные и отдельно хранятся индексы.
Я имею ввиду вот это:
Вставка в таблицу с индексами медленнее, чем без. Если нужно вставлять большими кусками по милиону записей и больше, можно выключить индексы, вставить данные, а потом включить.
ALTER TABLE DISABLE KEYS
INSERT INTO xxx VALUES (1,2,3)
ALTER TABLE ENABLE KEYS
В 99% это не нужно, достаточно вставлять данные большими кусками c помощью
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); что уже даст очень приличный прирост скорости
sim3x, писать начали в мускуле, потому что нужно было начать собирать данные, БД у проекта - мускул и так было проще. Со временем стали проявляться минусы мускула на больших объемах данных. Данные используются для красивых графиков, которые смотрят пользователи. На другие хранилище переходить нужно, чтобы быстрее все это работало. Как вставка, так и чтение. Под другими хранилищами я подрозумеваю, например, ClickHouse
BD_ l3ftoverZ!, Можно, но меня интересует в первую очередь вариант, который я описал. То есть запись в таблицу без индексов, что, судя по всему, должно быть быстрее, чем запись в таблицу с индексами.
1. Индекс это сбалансированное дерево. Если не ошибаюсь, то скорость обнолвения дерева зависит от его высоты. Высота дерева индекса обычно небольшая. Поэтому обновления индексов проходят очень быстро "в онлайне" и при OLTP-нагрузках это ок.
2. Да, но рост времени логарифмический. Я бы рекомендовал посмотреть в сторону table partitioning. Там и индексы будут "локальными" и будут расти только в пределах одной партиции. И управляемость таблицей будет выше.
PS. Не могу гарантировать что мое мнение полностью соответсвует реальному положению дел. Все покажет только тестирование.
WebDev,
Красивые графики с онлайн обновлением требуют очень много ресурсов
И они плохо масштабируются влоб
Потому пишите в текстовые логи и агрегируйте за промежутки времени
sim3x, Графики без онлайн обновлений.
Так что вы можете сказать по моему вопросу? Имеет ли смысл писать в таблицу без индексов и переносить в таблицу с индексами большими пачками?
sim3x,
Таблица statistics выглядит примерно так: id, user_referrer, user_ip, user_action, page_id, created_at
Каждое действие пользователя записывается в таком виде, что вы предлагаете суммировать?
WebDev,
1. Не делайте вставку на каждую запись. Пачкой по несколько тысяч.
2. Не делайте никаких запросов кроме вставки в таблицу статистики
3. Не выводите отчеты реалтайм. По крону каждые Х часов, агрегируйте в другую таблицу их и выводите.
4. Для сбора аналитики отлично подходит нода и кликхаус. Мы к примеру перешли на такой стек пол года как с объемом несколько миллионов вставок в день. Все отлично, вставка моментальная, ответ 1.5 мс, вставка пачкой каждые 10 сек.
WebDev, дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац). В противном случае, ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.
1. Да
2. При 80к вставок - это одна вставка в секунду - я бы не заморачивался с временным хранилищем, даже при большом объёме уже имеющихся данных. Если составной индекс не включает текстовое поле - точно не заморачивался, тем более, если вы планируете переходить на другие хранилища) Ну и вообще, если вы решили оптимизировать, у вас реально проседает вставка? Сколько по времени идёт?
Средний
