Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a server which relies heavily on a MYSQL database. Recently, there have been load issues and max mysql connection issues.

My idea is to have a primary server with the php code and files and a database, and a secondary server with only the database duplicated (so that both databases are the same). The user will access the primary server, and within the PHP code, half the connections will be made to the database on the primary server, and half to the database on the secondary server.

My question is is this the best way to reduce the server load? Or should the files be duplicated as well so that the server side processing of the php code is also split between both servers.


share|improve this question

closed as too broad by symcbean, Tim Brigham, mdpc, Katherine Villyard, Ward Mar 28 '14 at 14:09

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

Have you turned on the slow query log? In my experience, the first time people run into the problem the cause is slow queries, mostly caused by a lack of indexes. Sometimes it's caused by locking instead, either MyISAM read/write table locks interleaving or PHP session file locks with a database connection being held open. The slow query log can help identify the MyISAM problem and can rule it out too. Also, use Do you have MySQL's query_cache turned on? – Ladadadada Mar 26 '14 at 15:35
Voting to close this as too braod - this is a horrendously complex question and getting part of it wrong will result in loss of or corrupt data and/or no worse rather than better performance. – symcbean Mar 26 '14 at 16:33

Your first step to improve issues would be to separate the MySQL server and the web processing portion. You sound like you need to move to a 2-tier system. Once the two layers are separated, you can look into further load balancing the MySQL level if that is truly where you are having a load issue.

Before going that far though, I would look into a MySQL caching module like memcached.

share|improve this answer
The term "MySQL caching module like memcached" is wrong. Memcached is a generic caching module, and has no direct relationship with MySQL. Also, it is not that simple to take into use. Depends of course on the software used. – Tero Kilkanen Mar 27 '14 at 0:58

I'd say this is not the best way. If you have to (or more) servers, I think the best you can do is put the app (php) and the DB on different servers. Also, have you considered using varnish to your static content?

In terms of performance, load balancing is not always the final solution, but you could try refactoring you mysql queries.

share|improve this answer
Using Varnish for static content is pointless. Its use is to cache expensive generated content that doesn't depend on cookies so it doesn't have to be generated for every request. Using it to cache static content is just duplicating the filesystem cache, which is wasteful as well as adding latency for the extra hop for every request. – Ladadadada Mar 26 '14 at 15:38

There's no specific "right" answer that cover all scenarios for load balancing (to reduce load) a LAMP-based web app.

That said, there are some tips or comments that can be applied:

  • First off, I suggest to profile your server and applications to understand and pinpoint the bottleneck, making sure in your case that it's in fact the MySQL database.
  • Once/if you are sure that it's indeed mysql, try the different optimization techniques, several of those mentioned here like: incrementing (size) variables in mysql config file if you have enough RAM, adding caching with memcached for ex, reviewing slow queries and adding indexes as needed, adding front caching with Varnish for ex, using SSD disk etc.
  • If you decide moving to a using several servers, you have several valid options. You can have a) an architecture where you receive requests on your web server and redirect the db queries (you can do this right in the php code) to two or more database servers in the back like you suggested (you'll have to make sure they are in sync, replicated) or you can have b) an architecture where the requests are handle at the front by a load balancer (with nginx for example) and they are redirected to two or more servers that contain all the stack (php code plus database), also you'd need to replicate the databases and possibly files (if users upload files for example). Depending on the particulars of your situation one option could be "better" than the other, and there are also more complex possibilities.
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.