January 11th, 2014, 05:07 PM
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?
January 11th, 2014, 07:01 PM
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.
January 12th, 2014, 02:27 AM
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