Welcome to WebmasterWorld Guest from 3.228.24.192

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

distributed database architecture for a large database

     
5:18 pm on Jun 26, 2003 (gmt 0)

New User

10+ Year Member

joined:June 18, 2003
posts:39
votes: 0


I've got an interesting problem for you all. I'd love to hear what you think. (this was the most relevant forum I could find! please move it if it's not suited to this forum).

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?

7:12 am on June 27, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


Welcome to WebmasterWorld nahdoic,

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.

9:48 am on June 27, 2003 (gmt 0)

New User

10+ Year Member

joined:June 18, 2003
posts:39
votes: 0


hi,

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

10:04 am on June 27, 2003 (gmt 0)

New User

10+ Year Member

joined:June 18, 2003
posts:39
votes: 0


if you've any more thoughts or ideas please send them on too :)

this is very much the 'design' stage and anything goes.

11:01 pm on June 27, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:June 9, 2002
posts:41
votes: 0


What OS are you going to use?
May I remind you that there is a pretty small (2GB or 4GB) filesize limit in some OS (say, linux), you may need to split a single "1/4 member_log table" into several (say, 36 [0-9a-z] or 62 [0-9 a-z A-Z]) smaller tables under those OS.
10:23 pm on June 29, 2003 (gmt 0)

New User

10+ Year Member

joined:June 18, 2003
posts:39
votes: 0


Yeah, I was aware of that. I was hoping to run it on redhat 7.2, which means it will be limited to 2GB.

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.