#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0

    MySQL - 2 servers - Cluster? Cron? Help


    I have a call center that helps raise money for non-profits.

    I have one server that is used by customers and the call center and it's under a heavy load.

    A majority of the load is caused by a job that runs on the server every 20 minutes. This job basically pulls out 100,000 donor records to call. This database is 9gigs.

    When it's queried, the server slows down a lot. The query is pretty complex.

    I want to move this portion of the database away from the main server. This way, when it's queried, I can have this second server's resources 100% dedicated to this task alone, and the task will not interfere with the other processes on the primary server.

    The question is how to do this.

    First, I thought of just having an independent mysql server that has this DB on it only. But this causes some minor issues because we'd have to copy out put across the network.

    Not a big deal but maybe there is a better way.

    So I am wondering if a cluster would help with this so long as this DB's querying can all be processed on the other server. It would be nice to have one server address, one set of creds, and one url, etc.

    I've never done a cluster before so I am unsure.

    Are there any other options?
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,067
    Rep Power
    9398
    First, are you sure that the query has been optimized as far as it can go? 100k records from a 9GB database shouldn't be a huge strain, but I say that without knowing exactly where the data is or how it's being queried.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0

    problem found


    the problem is my ORDER BY RAND().

    I am trying to find a better way. Using ORDER BY RAND increases query time 80 fold.




    Originally Posted by requinix
    First, are you sure that the query has been optimized as far as it can go? 100k records from a 9GB database shouldn't be a huge strain, but I say that without knowing exactly where the data is or how it's being queried.

IMN logo majestic logo threadwatch logo seochat tools logo