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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Nov 23 '12 at 9:45

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

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

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

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


share|improve this answer

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.

share|improve this answer

Your Answer


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.