The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Advice on running MySQL processes

Discussion in 'General Discussion' started by ukndoit, Sep 10, 2007.

  1. ukndoit

    ukndoit Member

    Joined:
    Feb 19, 2002
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    We have a website that on prelaunch alone brought in almost 3,000 members, we anticipate official launch will bring in over 10k per month. This is a high demand product, so it is going very good. The problem is this, with the little less than 3k members, our MySQL processes where already using a lot of resources with everything we do, so we got a new dedicated server and now I am trying to figure out the best way to do this.

    Should we just have all the MySQL and cron jobs ran on the second server, and therefore on the primary server just connect to MySQL remotely(on the other server).
    or should we make it a cluster that the primary just makes everything happen on the secondary and then have the cron jobs only run on the second server?

    I appreciate any advice you can give me on this.

    Richard
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    When running MySQL remotely, you must consider any lag that may be encountered by transmitting data back and forth between servers. Granted, if it's all in the same data center, this lag will likely be minimal but still worthy of noting.

    I know some people get random ideas of having their web server on one part of the globe, and have their database server on the other side of the world. That is likely to not be a good setup for most situations.

    You might be in a situation where remote MySQL may be a good idea, especially if the query processing is what is occupying many resources. If the time it takes to process data locally is comparable to (or worse than) processing the data on a remote server, then you may want to give this serious consideration. May want to do some trial runs with some queries locally vs. externally and log the time it takes for queries to execute to ensure running MySQL on a remote server will not result in a substantial performance decrease for the website.

    Another thing worthy of consideration, and something I used to do at a previous occupation, is see if there are any ways to optimize your MySQL queries. Additionally, see if you can employ caching mechanisms to help alleviate some of the MySQL load. However, that's more of a coding task than a server administration task and may not be entirely feasible if the website isn't something that was custom built.
     
  3. ukndoit

    ukndoit Member

    Joined:
    Feb 19, 2002
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    I figured it out. I used WHM to setup MySQL to run not on localhost but on the IP, then I changed all my connect settings to the IP address and on the remote server granted access to the primary servers IP, and granted permission for the user. It does work.
    the updating of the MySQL will be done remotely, which that does not use all the resources up, it is the cron jobs that do all the functions of the Matrix that take up all the resources. This is a referral marketing company, with a compensation plan that is awesome on it, with a 2 x 2 forced matrix, that expands to a 3 x 3 and then a 4 x 4. The hard part is that when it expands 4x4 on the fly, a person could have 88,000 +/- a few members in their organization of referrals, and so if the person at the top refers someone it would have to query all the way to the bottom to find the first person with an opening. That ONE person, having to run upto 88,000 queries is what kills the resources. So the small amount of latency caused by the primary server running small processes on the SQL server is not that bad, compared to the amount of resources eaten up on the server by the PHP running queries.

    what do you think?

    Thanks.
    Richard



     
  4. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    Also consider, if you have both boxes in the same datacenter, see if you can get yourself a switch and setup your remote sql via a private IP instead of public IP. Much easier to manage and saves a bit on bandwidth.

    Outside of replication across several servers, it sounds like you are setting things up properly. Eventually you will need to add more and more servers so database replication would be your next step.
     
  5. ukndoit

    ukndoit Member

    Joined:
    Feb 19, 2002
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Ok, thank you. But what is database replication?
    Where can I learn about that?

    Thank you much!
    Richard

     
  6. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
  7. kev1nk

    kev1nk Member

    Joined:
    Sep 11, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Re: mysql

    There is something very important what we just do not know. You are telling us that you are almost out of available resources. Which service is taking your resources?
    Another important questions. Is your MySQL configured properly?
    The hardware configuration. Is it good enough for your requirements?
    Replication is OK but if you have many visitors then a remote server will not be enough
    and you should check the mysql cluster option.
    I think that the cluster is the solution in your case.

    Best Regards
    Kevin
     
Loading...

Share This Page