Как хранить время последней активности пользователя в MySQL?

Question

[ErickSkrauch]

Вопрос прост: как хранить в MySQL (MariaDB 10) последнюю активность пользователя?

Сейчас реализация такова: имеется база users, в которой хранится основная информация о пользователях (MyISAM (Aria)) и таблица action_history, где хранится последнее действие пользователя (id = primary_key = users.id), timestamp и action (enum поле по известным операциям с аккаунтом, вроде загрузки страницы или выхода из аккаунта). Сама таблица является InnoDB (XtraDB).

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

И ещё раз повторю вопрос: как хранить время последней активности пользователя, чтобы по нему в последствии можно было выбирать пользователей?

Answer 1

[Walt Disney]

Общее для всех mysql:
Проверить какой запрос отрабатывает система если replace то плохо если update или insert on duplicate key update - то ок.
Если в action_history есть индексы кроме примари кей то их скорее всего стоит убрать т.к. вероятно запросы ( сортировки или агрегации) по этим полям будут приводить в любом случае к фулскану и индекс не будет иметь смысла, а при апдейте перестройка будет постоянно всё класть.

Для Maria: убедиться, что в Maria XtraDB не сломана построчная блокировка (всё таки опыт продакшена у марии неё вялый ). Потом вызывает опасение реализация внешнего индекса в таблицах разного типа, возможно это как то может ломать построчную блокировку. Если у вас лок всей таблицы разберитесь откуда, так не должно быть

В общем и целом, поразмышлять что INSERT DELAYED VALUES (1,2,3),(..,..,..),(N,N,N) для записи всех действий будет работать заметно веселее особенно без индексов и в одном потоке, а после некоторых шаманств с агрегацией по крону ещё не будет деградировать от распухания. Что можно про крону парсить access.log (понимать что он не рилтайм), в этом случае будет одна пачка апдейтов допустим раз в минуту и user_id будет заапдейчен только один раз, это будет пожалуй самой простой реализацией задачи упорядочивания и фильтрации потока апдейтов к mysql. Парсинг лога можно заменить на RabbitMQ, или написать своего демона который будет висеть на соке и рулить.

Но смотрите если у вас задача в стиле показать последние 10 пользователей сделавших, что то, то это решается сильно иначе.

Comments

[ErickSkrauch]

При обновлении используется UPDATE, иных индексов, кроме как первичного у таблицы нету, да и первичный не меняется (он создаётся синхронно с созданием записи пользователя), обновляется только поле timestamp и иногда action (описывал в самом вопросе).

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

Вы рекомендуете провести ряд проверок, вроде того, чтобы убедиться, не сломана ли у XtraDB постраничная блокировка. Не подскажете, как это всё проверить?

[Walt Disney]

ErickSkrauch: сначала глянуть show processlist, потом в XtraDB видимо должно быть что то похожее на SHOW ENGINE INNODB STATUS.

Я не рекомендую, а клоню в ту сторону что Maria не используют массово и если там? что то сломано то воя в интернете не будет, и искать косяки надо самому всегда.

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

Я рекомендую вам избавиться от апдейтов потоком которых вы не можете управлять. Т.к. читать вы должны всегда быстро (иначе задача не имеет смысла), то рано или поздно вы придёте к очереди апдейтов в которой будут повторяться id пользователей, т.е. очередь будет делать N апдейтов одной записи хотя очевидно имеет смысл только последняя. Т.е. соответственно у вас две задачи, получить нормальную упорядоченную очередь и дальше решить, что с ней делать варианты соответственно выше. Но если у вас только используются только N последних записей то следует не морочить голову и перейти хранение в памяти, например тот же memcache.

Answer 2

[Night]

1) Сделайте отдельную таблицу user_last_activity (user_id, timestamp), InnoDB
2) Информацию о последней активности пишите в memcache (обновляйте не чаще раза в минуту, сверяйтесь с сессией)
3) Демоном раз минуту берите данные из memcache и вставляйте в user_last_activity
4) last_activity юзера также кэшируйте на одну минуту

Answer 3

[heartdevil]

Привет.

Я в этом деле тоже не компетентен, но зачем вы сразу пишите данные в базу? У вас же есть сессия? Сначала храните данные там, а на выход из аккаунта или скажем интервал бездействия в минуту или меньше, можете сливать данные из сессии в базу.

Comments

[ErickSkrauch]

Ну да, я не пометил: актуальные данные пишутся в xcache для отображения самому пользователю, а в базу пишутся раз в N-секунд (сейчас 30). Но ситуация такова, что этого недостаточно - база всё равно забивается именно на обновлении таблицы действий.

[heartdevil]

Странно. А в среднем сколько запросов от одного пользователя раз в 30 секунд уходит на запись в базу?

[ErickSkrauch]

heartdevil: ну получается, что 1 от 1 пользователя. Но часть пользователей держат окно сайта открытым, ожидая, например, уведомлений или сообщений в чат, а это всё pool-запросы, на которых происходит обновление, так что получается немало в сумме.

Анатолий K: дело в том, что точно определить дату выхода не выйдет. Точность не так важна, но хотелось бы знать наверняка, когда последний раз пользователь был на сайте.

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

[ErickSkrauch]

Анатолий K: на счёт чата я знаю, но суть не в этом. Как, исходя из предложенных меток, узнать, когда юзер ушёл в оффлайн?

[ErickSkrauch]

Анатолий K: а теперь вопрос: откуда мы узнаем время последней активности, если запоминаем только момент входа?

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

[heartdevil]

ErickSkrauch: А у вас же есть список отслеживаемых действий? Вы с клиента раз в 10 секунд можете на сервер передавать список флажков по этим действиям, если все нули (грубо), то активности не было. Записываете время в базу. Если я правильно вас понял.

[ErickSkrauch]

heartdevil: А если юзер просто тупо закрыл браузер и ушёл по своим делам? Или у него упал интернет. Или что-то ещё. Был вариант просто-напросто слушать юзера на сокетах и там отлавливать момент отключения, но для этого требуется время на работку, а проблему хотелось бы как-то решить сейчас, хоть и не окончательно.

[heartdevil]

ErickSkrauch: А как вы расцениваете действие -- юзер тупо закрыл браузер и упал интернет?
Это же одно состояние.
Я возможно не до конца понимаю, суть проблемы, так как у меня самого в этом мало опыта, но
до того как юзер закроет браузер или у него упадет интернет, у вас уже будет отметка. Вы же постоянно будете мониторить с клиента.

[ErickSkrauch]

heartdevil: да. А куда я буду складывать инфу? В базу. А в базе что? Тормоза. А вопрос о чём? О тормозах.

Суть не в технологии сбора, суть в записи и выборке.

[heartdevil]

Так вы же не сразу в базу записываете. У вас же есть кэш. Храните тем. А потом, как сказал Анатолий, кроном можете писать с интервалами писать в базу и удалять лишнее.

[ErickSkrauch]

heartdevil: и на время записи кроном сайт будет ложиться.

Вероятно для решения этой задачи нужно использовать какое-то внешнее решение и делать ручной JOIN, чтобы вообще избежать запросов к mysql к этой таблице.

[heartdevil]

ErickSkrauch: Я не совсем понимаю, как у вас данные записываются в базу. У каждого пользователя же должен быть свой период записи. И в сумме запросов не должно быть очень много. Их должно быть много на в разные промежутки времени. А какая у вас нагрузка на сайт?

[ErickSkrauch]

heartdevil: выходит от 5 до 20-30 запросов на обновление в секунду при условии того, что каждый пользователь пишет в свой период.

[heartdevil]

ErickSkrauch: Думается, все-таки у вас где-то настройки не заданы оптимально. Не думаю, что такая нагрузка уже критична для вашей базы, хотя точно не знаю. Я бы покопался там. Внешние решения, как мне кажется, нужно более интенсивных нагрузок. Хотя, может надо поискать в интернете, может кто-то замерял MyISAM (Aria) и InnoDB (XtraDB). Непонятно какую нагрузку можно ожидать на эти версии.

Если как-то решите вопрос, отпишитесь сюда, пожалуйста.

[ErickSkrauch]

heartdevil: в том то и проблема, что используется выделение сервер с довольно мощной конфигурацией (чего стоит только i7 4 поколения), но вот именно там оно тормозит.

В общем если найду оптимальное решение, то постараюсь отписать о нём.

[heartdevil]

ErickSkrauch: Да, хотел еще узнать, а как конкретно вы записываете данные в таблицу? Не опишите поэтапно, начиная с кэша? Какие запросы используются?

[ErickSkrauch]

heartdevil: да там особой магии то и нету. При каждом запросе пишем в xcache актуальное время запроса, а так же проверяем, сколько прошло со времени записи в базу (хранится отдельно). Если больше x секунд, то дописывает и в базу. Дописывает через AR модель и метод ->save() (приложение на PhalconPHP).

[ErickSkrauch]

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