You didn't mention what table type you are using. I'm pretty sure you would want to use InnoDB if you are going to have lots of updates going on.
I would also try a few other general system changes before playing with you structure. Try finding out where you bottleneck is (disk, memory, cpu). It may be that you just need to load up the machine with RAM to get some nice performance.
On Thursday, June 26, 2003, at 03:33 PM, 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]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
