Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 '12 at 5:26
add comment

migrated from stackoverflow.com Nov 23 '12 at 9:45

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
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." –  ypercube Nov 23 '12 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 '12 at 9:57
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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