Hi, I am trying to figure out what are the issues I need to deal with and
what are the limits that I will have to take in designing the database
schema of our application.

Roughly speaking the system is somewhat a messaging system between users. It
should handle around 300,000 users and with about 1000 messages limit in
their Inbox. I am trying to figure out how should this be handled, databases
tables speaking. Using one big table seems to me enormous and maybe
inapplicable and too slow. Such a table would grow up to 300 milion
records - can a database like MySQL deal with it? Another way that I heard
of is breaking the users inboxes into groups of 10,000 users for example.
How should this be achieved?
We are planning to use MySQL/InnoDB 4.0.12 on a Linux machine x345 Dual CPU
with 2GB memory. The machine will also run JBoss/Tomcat application server
as well.

Any help would be appreciated

Erez

"Erez" <ereze (AT) netmedia (DOT) net.il> wrote

My general suggestion to questions of this type is:
focus on correct functionality First, only later make it fast.

correct functionality includes the database design.

Trying to determine bottlenecks in a complex application before
writing the application is VERY difficult. You are better off building
a prototype system and testing it. Just remember the key rule of
prototypes: when testing's done, throw it out and write real
production software.

Making something that is blazingly fast, but doesn't return the data
requested, is just useless.

Go back and find out what the BUSINESS requirements are. make a design
that solves that problem. Only when it doesn't go fast enough do you
need to look at performance.

HTH,
ed

"Erez" <ereze (AT) netmedia (DOT) net.il> wrote:
can expect from various databases on various machines.

ed.prochak (AT) magicinterface (DOT) com (Ed prochak) wrote in message news:<4b5394b2.0402161046.b368780 (AT) posting (DOT) google.com>...

I agree with Ed. Also, if you really have a concern, try to have a
layer that abstracts the data model from the application so that if
the model changes, you will impact less of the application code.

Dave

First thank you all for replying.
I understand that going and focusing on the functionality is the first step,
and that is exactly what I do. Still, it does not leave me with a
comfortable feeling, avoiding the apparently unavoidable.
Dave, the layer of abstract I am using is J2EE/CMP and I think that in the
long run if I have to go and change the tables schema, I would have to turn
to the BMP as well, and so having more control on how and from where the
data is brought.
I am still left with my question: is a database (for instance MySQL which I
am intending to use) can deal with such enourmous tables? Later, how do I
make the change, would it be a problem for the database server dealing with
these changes? And by the way, does anyone have a clue on how to backup such
a database?
I know I have put a lot of questions, but these questions really bug me and
I hope someone has some answers. Anyway, I appreciate your help,

Thanks a lot,

Erez

"Dave" <davidr21 (AT) hotmail (DOT) com> wrote

I don't know if MySQL can handle it, maybe someone else can tell you.
But couldn't you just create 300 million fake rows in a prototype
schema and run some test queries?

If you make changes later, it shouldn't be an issue. Basically, after
creating the new schema, you should be able to migrate the existing
data with some SQL scripting. (e.g., insert into new_table from select
* from old_table where ..., etc. )

You could certainly break up the table into multiple tables. You could
use some sort of combination of naming standard and/or metadata table
to map a user's inbox to a physical table. (e.g., user 21 maps to
table inbox_tab_1thru10000 ).

In the BMP code you could do the metadata lookup to get the table name
and then construct your query from that.

Don't know much about backup of MySQL, sorry.

Dave

"Erez" <ereze (AT) netmedia (DOT) net.il> wrote

"Erez" <ereze (AT) netmedia (DOT) net.il> wrote

It's not avoiding this issue. It is a question of putting it in its
proper place in the development process. (It's actually more of a
Software Engineering question than a DB question.)

I haven't ysed mySQL yet. There are others in my company that are
using it. From the types of things they are working on, I'd expect
that it would be able to handle such tables.

Why would the server have a problem?

I'd assume such details are in the documentation.

Maybe you worry too much 8^)

Build a test DB and try out some designs.

IMHO, I'd be hesitant to map a user's mailbox to it's own table. That
means you'd be creating tables with each new user. Maintenance (backup
and restore)would seem to be harder.

If the performance of mySQL is poor on large (many rows) tables, then
it might be worthwhile to look at other DB's. but with proper indices,
I'd be really surprised if that was the root problem.

Prototyping is your friend, as long as you remember to throw out the
prototype when you start to develop the real application.

HTH.
Ed

Thank you all for the good tips - prototyping.. here I come...

Erez


"Ed prochak" <ed.prochak (AT) magicinterface (DOT) com> wrote