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).

  • archive your log table to a separate table on a monthly basis.. if you want to analyze them, move them to hadoop..
    – emaillenin
    Nov 23, 2012 at 5:26

3 Answers 3


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.

  • I think that these are old limitations. With the EXT4 and XFS file systems, it's now 16TB with EXT4 and 8 EB with XFS. As your linked page says: "For the most up-to-date information, be sure to check the documentation specific to your operating system." Nov 23, 2012 at 9:51
  • Ok, so it looks like I have to dive into Hadoop if I want to have this seriously fixed. Alternatively monthly seperate tables could be a solution although complicated, but same goes for Hadoop I figure. InnoDB seems to be able to carry more data, am I right? So that might be better then MyISAM?
    – Glooh
    Nov 23, 2012 at 9:57

You can use a partition table, you can split the table using a hash on user_id, then you can create as many partitions you want(you can add partitions later on if you want).

see: http://dev.mysql.com/doc/refman/5.6/en/partitioning.html


What about using audit and logging it to an external server? This might be a viable solution. You don't mention what version or if you are MySQL or MariaDB. I assume MySQL McAfee has one if you are MySQL and not an Enterprise customer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.