millions of tables with millions of entries, is that ok?

MySQL & MariaDB Other MySQL® Questions
1

Hello!

After reading many of blogs and opinions, I still haven’t found what I am looking for.

This should be the scenario: 1.000.000 Users in an online community. Each user with his own mailbox. Each mailbox can store up to 10.000 of messages.

What to do? One Big Table for all messages or individual tables for each user? Put all tables in one database? MyIsam? Innodb?

Thank You!
Tobias

2

Hi Tobias,

I think you should implement feature of using multiple database servers - for example, 1st server stores data for users 1-10000, 2nd server - 10001-20000, and so on.
I believe that having table which can potentially grow up to 10000000000 rows is not very good idea )
What about InnoDB/MyISAM - please see [URL=“http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html,”] http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html,[/URL] it has very detailed information about MySQL storage engines. But imho InnoDB will be better for you, as it has row-based locking, and so row updates won’t lock the whole table for writing.

3

Having one table per user will bring you into filedescriptortrouble:-)

4
[B]erkules wrote on Mon, 28 April 2008 17:18[/B]
Having one table per user will bring you into filedescriptortrouble:-)

That’s right, but keeping one table for all users will bring them into slow queries due to extremely big table. 10000 millions of rows is too huge table…

5

Hello again!

It looks like “sharding” will be the only way to cope with “millions of millions”. I have found an interesting website dedicated to these problems: [URL=“http://highscalability.com/”]http://highscalability.com/[/URL]

6
[B]tobias74 wrote on Mon, 28 April 2008 19:45[/B]
Hello again!

It looks like “sharding” will be the only way to cope with “millions of millions”. I have found an interesting website dedicated to these problems: [URL=“http://highscalability.com/”]http://highscalability.com/[/URL]

I believe our blog also has some posts about sharding - http://www.mysqlperformanceblog.com/2008/03/14/sharding-and- time-base-partitioning/ for example.