Mysql – How to optimize a log process in MySQL

MySQLoptimization

In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns like user_id and some tag_id's.

The problem I have, is that these tasks in some cases can go up to 1.000.000 per year per user. So if I would store all these activities in one table. that would obviously become very big (=slow).

What is best to do here? Create a single table per user (so I have 100.000 log tables) or put all these activities in one table? And what kind of engine should I use?

One important thing to note: Although i simplified the situation a bit the following doesn't look normal, but users can also change values in these tables (like tag_id's).

Best Answer

Some simple arithmetic:

100k users * 1m events per user per year * 100 bytes per event =~ 10 TB per year

In MySQL, single table is limited in size at about 4TB. I would think it should be bigger than that, but this is what official doc says.

In other words, you would be effectively forced to split or partition your huge table into set of smaller ones.

Alternatively, you can use NoSQL databases like Hadoop or MongoDB. But, it can be painful if you are used to do everything in SQL.

Related Solutions

Mysql – Storing arrays of integers in database (for efficient select)

RDBMs were built to handle these kinds of relations and operations. Plus, storing the users and items separately will help with detailed reporting (eg: number of items added today).

With optimized indexes, reading the many items per user is just one lookup away. The items table can be indexed to store the related items (per user) consecutively, thereby giving a array like reading scenario. Just make sure to use page padding to allow for the future updates/inserts/deletes.

RDBMs are pretty fast and can handle hundred-thousand operations per second, so this may not be an issue. When your items are on a different table, updates done per item will perform better than if the items were stored in a text column (as an array) in the same rows as the user. This will give you faster updates, lesser memory required for the operation, quicker locks and releases (and you may not have to lock your respective user), smaller read/writes, etc...

Mysql query – find out 1000th row from a table for each user

To get all the (1000th) rows, using the primary key of the table:

SELECT du.user_id
     , ua.*                               -- any other column you need
FROM 
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua 
      ON ua.pk =                          -- the PRIMARY KEY of the table
         ( SELECT ai.pk 
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at 
               LIMIT 1 OFFSET 999
         ) ;

or using the (user_id, created_at) combination:

SELECT du.user_id
     , ua.* 
FROM 
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua 
      ON  ua.user_id = du.user_id
      AND ua.created_at =
         ( SELECT ai.created_at
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at 
               LIMIT 1 OFFSET 999
         ) ;

I guess the number of rows in the table per user is high, so an index on (user_id, created_at, pk) would be good for efficiency.

Best Answer

Related Solutions

Mysql – Storing arrays of integers in database (for efficient select)

Mysql query – find out 1000th row from a table for each user

Related Question