1

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.

thanks!

2
  • 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 mysqltuner.pl. 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
1

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.

1
  • 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. Mar 27 '14 at 0:58
0

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.

1
  • 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
0

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.

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