I've simplified the database design of my web application to its root problem, so it'll be very easy to see my difficulty, and if you're feeling generous possibly offer a solution.
I have two tables.
member table (circa 1,000,000 rows - 100 meg - easily fit in a single table)
username varchar(30) pk
password varchar(30)
settings varchar(30)
member_log table (circa 3,000,000,000 rows - a few 100 gigs - a few 100 very simple updates and selects a second, with some quite long selects every minute or so - when the update is being done it needs to select the settings for the user from the member table before it does the update to the member_log table)
logid medint pk
fk_username varchar(30) fk
description varchar(200)
Now ehm … what's the most efficient way of doing this?
What I would like to do is:
Is have a copy of the member table on every server, then break up the member_log based on the username, and spread it across multiple servers.
database server a
full member table
1/4 member_log table
database server b
full member table
1/4 member_log table
database server c
full member table
1/4 member_log table
database server d
full member table
1/4 member_log table
In the future, if the servers start to slow down then I'll just add
database server e
full member table
member_log table
Well that's what I'd like to do, but I don't know how to do this.
My main problem is keeping the full member table in sync.
I can't use replication, because I only want to keep one table in sync, not the whole database.
So i don't know what to do. How do I do this, and do this efficently?
What database are you using? It might help get an answer. I wonder if a little more information might help as well.
I am wondering about the need for the full member table at all but I dont really understand what you are trying to do.
thx for replying, it will probably be using MySQL, as it is a very simple database but speed and efficiency is the most crucial factor.
You were right though, I didn't need the FULL member table on each server. So I think i may have come up with something else that may work.
I'll just break my member table up into two tables.
member_main table
username varchar(30) pk
password varchar(30)
log_server varchar(30)*
* log_server will know which server to look at when doing reports
member_settings table
username varchar(30) pk
settings varchar(30)
member_log table
logid medint pk
fk_username varchar(30) fk
description varchar(200)
then I could distribute it across like
database server a
full member_main table
database server b
1/4 member_settings table
1/4 member_log table
database server c
1/4 member_settings table
1/4 member_log table
database server d
1/4 member_settings table
1/4 member_log table
database server e
1/4 member_settings table
1/4 member_log table
this is very much the 'design' stage and anything goes.
Perhaps MySQL really wouldn't be the best choice for this type of project. It'd be very hard to maintain a database like that.
