Resource Usage with MySQL Between Two Servers

I currently the lead programmer for the website thecbl dot net and am looking for ways to cut down on resource usage on our main server.

We currently pull data from the gaming website's player profile, store it in our database and update it when the profile is called if it is older than 24 hours. This cache is called from our own personal adaptation of the profile, and within the staff areas as well as dynamic signatures.

I was thinking about delegating the task of pulling the data and storing it onto one of our other servers, and having the main server use remote MySQL to pull the data. This is only worth doing if it will actually save load on the main server (from downloading the data and updating/selecting from the database).

So my question is - if I were to split this (decently large-sized) table from the main server and use remote MySQL to get the data, would I actually be saving on resources?

Thanks Ozzu Community! ;D

If you are having a problem with load on the server, then using MySQL from another server could lower the load some. As far as how much change you will notice it really depends on how much of your load to begin with was from MySQL. With Ozzu, I would say a descent amount of the load does come from MySQL, and if Ozzu ever got busy enough it may very well be worth it to do the same thing and have a dedicated MySQL server, as well as a dedicated Apache server to serve the website.

Another thing you may want to consider instead of doing this, is to see if you can optimize the way your code is, and your SQL queries. At one time Ozzu was creating a enormous load on the server which much less traffic than it is now. Instead of throwing new hardware into the picture to resolve things, I went through every SQL statement and found the ones that were resource hogs. Rewriting and optimizing the queries can really make a difference. Another thing that helped a great deal was to properly use the right indexes on the MySQL tables so that lookups were much quicker. You can also utilize the MySQL slow query log to find queries that take too long which could cause MySQL to use more resources than it should. Eventually this could lead to running out of memory and having to swap to the hard drive which is extremely slow and causes really large loads. This was something Ozzu at one point had problems with in the past. After going through everything and making sure things were optimized, Ozzu has been able to grow without having to have multiple servers to run the website (yet anyway). I should note that Ozzu does offload all of its DNS to two dedicated DNS servers which does decrease the load slightly.

So overall if I were you I would look for ways to optimize first, and if you feel there is nothing else that can be done then consider breaking resources up between numerous servers. Hope this helps some.

Thanks Bigwebmaster, I have already optimized queries and indexes and the problems have continued - even used slow query log, but various queries from phpBB and the CBL itself showed up when traffic was high because the server was unable to handle the requests. We are, unfortunately, on a small budget (being a non-profit community site after all), and therefore on a budget host, so the code was written around that knowledge. I'll take another look over on your advice, and if problems still persist, then I will probably make the move of splitting the tables.

Thanks again! ;D

Another thing you may want to do is check that your MySQL server is setup properly and optimized. A good tool for checking this is MySQLTuner and MySQLReport. These tools can help you find problems that you may or may not have realized you had and can seriously help you to optimize your system to handle higher traffic loads. They have helped me a great deal in the past.

MySQLTuner is really helpful and easier to understand than MySQLReport.
Nice recommendations.