DISQUS

DISQUS Hello! iRant is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

iRant

Jump to original thread »
Author

Load Balancing Two MySQL Servers for PHP Applications

Started by jploh · 1 year ago

My “research” on clustering and replication was just timely. I had to find a fix for an overloaded server. I could’ve just rewritten the code but I wanted to try something new first. I found out that a MySQL cluster needs to have at least three servers to get full redundancy. Replication was my only […] ... Continue reading »

12 comments

  • Andrew Blake

    2 years ago

    "I found out that a MySQL cluster needs to have at least three servers to get full redundancy."

    I'm thinking of implementing cluster over two servers - could you tell me about the issues you found that make it require three machines ?

    Thanks

    Andy
    reply
  • jploh

    2 years ago

    It's possible to have a cluster with two servers. You'll initially need three servers then you can turn off the third one once it's running (not recommended). See this article.
    reply
  • Marcus

    2 years ago

    There is a difference between MySQL Cluster and MySQL replication. What you seem to be describing is replication, in which case I don't think that the linked article will help you.

    It seems to me that your approach should work out just fine. Just don't try to get into master-master replication... it's a pain and it can only lead to tears. :)

    If you want to make it more robust, you could add a couple of features to your daemon. For example, you should pull your config information from a file. Then you could implement a simple protocol. For example, the client could send "REQ" to request a DB handle. Or the client to send "RELOAD" to cause the daemon to reload it's config file. This way you wouldn't have to stop the daemon...

    Then again, PHP probably isn't the best language to use for the daemon, since it will block on each request. If you have two requests coming in at the same time, the second one will have to wait for the first to get done. This might be an issue over time.
    reply
  • fred

    2 years ago

    Hi, i wanna know how much about performance do you get with this trick?
    reply
  • jploh

    2 years ago

    The link I gave in my last comment is for clustering. But the one used in my post is replication.

    I also extended my daemon script that's running in production. I just wanted to give a basic idea of how it works.

    For performance, refer to the image below. The one in blue was when it wasn't rotating between servers. The one in red, when rotation was in effect.

    <img src="http://img458.imageshack.us/img458/4946/jppniccmysqlqueriesweekyi4.png" alt="QPS before/after"></img>
    reply
  • sam

    1 year ago

    http://davh.dk/projects/mysql-load-balance/

    this link is not working.can you provide me any other link which has more information about algorithm.
    reply
  • jploh

    1 year ago

    You're lucky I still have the PDF file stored somewhere. Here it is:
    http://rapidshare.com/files/88019396/03-11.pdf....
    reply
  • Php Database Script

    1 year ago

    I found your site on faves.com bookmarking site.. I like it ..gave it a fave for you..ill be checking back later
    reply
  • _

    1 year ago

    reply
  • fgh

    11 months ago

    hfgh
    reply
  • Chris

    2 months ago

    For anyone trying this.. don't forget
    $stream = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);

    Put that above the socket_bind...
    reply
  • boston moving companies

    2 weeks ago

    Thanks for posting the code. It's really helpful although this was posted 2 years ago.
    reply

Add New Comment

Returning? Login