I am interested in any thoughts that people may have for creating a scalable mysql
infrastructure.
I have a web application which runs on several front end web servers which hit one
backend mysql
server. Presently I can continue to grow by adding front end webservers -- the mysql
server is not
close to maxed out. Looking toward the future I will have to make a decision about
how to grow the
mysql serving capability and have several ideas on how to do it.
Now some questions for the group:
1. I can guess that my select to insert/update ratio is probably on the order of 4:1
but is there a
simple tool to use to determine the actual ratio.
2. When I want to scale up the mysql server what are the pros/cons of each of the
following:
a. Create an NFS server on the backend and load balance several mysql servers
all accessing
the same database files via NFS (is this even possible/desirable?)
b. Make one big server the primary insert/update server and replicate the
data out to many
read-only slaves (at what ratio of read to writes in conjunction with number of slaves
does this
start to limit scalability).
c. Buy one big monster server every year and hope to stay ahead of my needs
(and have the
previous years machine as a backup)
3. With a replication strategy how does one make sure that the current information is
displayed to
a user? For example, a frequent action in a web application is to update information
in a user's
profile. Typically the way this is done is for the user to enter the information into
a web form
submit the form and then the user gets a page with the current data displayed in a
read-only format
(so the user knows the update was successful). How do most people handle this to make
sure that the
current data is displayed? Do you just perform the select from the write server for
this one case?
Or is replication fast enough that performing the select from one of the slaves is ok?
4. Replication (choice b) seems to be the preferred way to go, based on what I have
seen on the
list; is there a reason why NFS is not an option? Also is the choice determined by
the type of
database (InnoDB vs. MyIsam).
I am interested in any comments/experience people may have on this issue. I have many
thoughts of
my own regarding ease of maintenance, backup, reliability, ease of expansion, cost,
performance,
etc. However I have not had time or hardware to test the different possibilities and
would greatly
appreciate hearing what others have to say.
Thank you for your comments,
Steven Balthazor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
