How to store user's last activity time in MySQL?

Question

PHP

The question is simple: how to store the last user activity in MySQL (MariaDB 10)?
Now the implementation is as follows: there is a users database , which stores basic information about users (MyISAM (Aria)) and an action_history table , which stores the last user action (id = primary_key = users.id), timestamp and action (enum field for known operations with account, such as loading a page or signing out). The table itself is InnoDB (XtraDB).
Previously, when a request to update information left with each request to DI for the current user, this caused unreal brakes due to the constant updating of this table and further waiting for it to be unlocked. Right now these requests only happen in notification list pools, etc., i.e. in requests invisible to the user, but nevertheless this design is still the bottleneck of the project.
And once again I repeat the question: how to store the time of the last activity of the user, so that later you can select users from it?

Answer 1

Common to all mysql:
Check which query the system is processing if replace is bad if update or insert on duplicate key update is ok.
If there are indexes in action_history other than Primary Key, then they should most likely be removed. probably queries (sorting or aggregation) on these fields will lead to a fullscan in any case and the index will not make sense, and with an update, the restructuring will constantly put everything.
For Maria: make sure that row locking is not broken in Maria XtraDB (after all, Maria's production experience is sluggish). Then the implementation of an external index in tables of different types raises concerns, perhaps this can somehow break the row-by-row lock. If you have a lock of the entire table, figure out where, it should not be like this
In general, thinking that INSERT DELAYED VALUES (1,2,3),(..,..,..),(N,N,N) to record all actions will work noticeably more fun, especially without indexes and in one flow, and after some shamanism with crown aggregation, it will not yet degrade from swelling. That it is possible to parse access.log about the cron (to understand that it is not realtime), in this case there will be one pack of updates, let's say once a minute and the user_id will be updated only once, this will be perhaps the simplest implementation of the task of ordering and filtering the update stream to mysql. Log parsing can be replaced with RabbitMQ, or you can write your own daemon that will hang on the juice and drive.
But look, if you have a task in style to show the last 10 users who have done something, then this is solved in a very different way.

Answer 2

1) Make a separate table user_last_activity (user_id, timestamp), InnoDB
2) Write information about the last activity in memcache (update no more than once a minute, check with the session)
3) Use the daemon to take data from memcache once a minute and insert it into user_last_activity
4) last_activity also cache the user for one minute

Answer 3

Hello.
I am also not competent in this matter, but why do you immediately write data to the database? Do you have a session? First, store data there, and when you log out of your account or, say, an inactivity interval of a minute or less, you can merge data from the session into the database.