hi,
i've a general question regarding MySQL replication.
we have an application that is currently setup to run on 4 servers. Server 1 hosts the MySQL Database and serves up pages via Apache that connect to the Database. The other 3 servers run an application that also connects to the Database on server 1.
The load on the databnase can vary but on rare occasions it gets very high. running a TOP command in linux on Server 1 reveals the MySQL CPU usgae can exceed 100%. This at times results in MySQL "Lost Connection" errors. So obviously this setup needs some improvement!!
I'm looking at reducing the load through the various scripts running but i also want to have the Database setup in the most optimal way.
So in general would replication help in this scenario?
If so, Would it be best to have a single Database replicated from the main database on server 1 or would it be best to replicate the main database out to a salve version on each of the other servers.
When replicating how much of a load does it add to each server?
And how often can the replication take place?
all comments welcome!!
thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
hi,
thanks for your reply.
the 3 servers running the application are CPU intensive. server 1 has the lowest CPU usage of the three. (based on idle time).
on replication. would i see much of an increased performance?
and how quick is the replication process? is it instantaneous or is it delayed by a few seconds/minutes?
any delay is critical!
thanks for your reply
Hi,
There will be a delay, but I don't know how much it will be.
The steps are like this as far as I know:
1. The query is done on master
2. The query is saved in the binary log
3. The query is propagated to the slaves
The delay depends on:
1. The master CPU load
2. The bandwidth between master and slaves
You will see an increase in performance as queries will
be performed locally, not via the network to the master.
Cheers,
za-k/
Business Accounts
Answer for Membership
by: adrpoPosted on 2008-03-11 at 22:45:54ID: 21103355
Hi,
Have you tried not using replication, but moving the DB
to another of those servers, not the apache one.
It might make it faster if you have a dedicated db server.
If you still want to use replication, read more
about load balancing and replication here:
http://dev.mysql.com/doc/r
You could replicate on each server (a slave on each one)
with the database 1 as master. You will only be able to
WRITE on MASTER so if you have a lot of selects on slave
that's ok, but updates always need to go via the MASTER.
If you have a lot of writes on the slaves then this setup
might not be good for you.
Cheers,
za-k/