Sorry to reply to myself but after reading your post again, I think you can use replication to maintain member table in sync: it is possible to restrict replication to a some tables within a database: check 'replicate-do-table' option.
Hope this helps Joseph Bueno
Joseph Bueno wrote:
I don't have direct answers to your questions but you should consider adding an integer 'userid' to member table and using it as a foreign key in member_log table instead of username. It will make selects and joins faster, data and index sizes smaller. Also, I don't know what you mean by 'medint' but if it is a mediumint, you will be in trouble since its range is [-8388608..8388607]. You should use at least an "unsigned int" and may be an "unsigned bigint" if you suspect that you will have more than 4 billion rows.
Hope this helps Joseph Bueno
Aodhan Cullen wrote:
I've got an interesting problem for you all. I'd love to hear what you think.
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)
My read/update ratio would be something along the lines of 1:3, 3 updates for every read. So it is highly unusual, and more or less rules replication out of the picture.
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 my read/update ratio just isn't right for it. And 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?
Any ideas anyone?
regards,
Aodhan.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
