1

I have some questions about understanding high server load and implement some type of load balancing in a common scenario with solutions like Apache and MySQL and PHP 5.

I am benninger in the matter and would like your opinion since Ive been reading a lot and Im totally lost in possible products or techniques to implement an easy and if possible a free solution:
Need Suggestions / Opinions about Load Balancing + some technical questions!
https://blogs.oracle.com/oswald/entry/easy_http_load_balancing_with

Imagine a simple example with only 1 server and two users connected at the same time, User A and User B.

  • User A makes a query and asks for a lot of data, a query which will probably overload the MySQL server for some minutes. ie:

    SELECT * FROM 'table_with_10GB_of_data';

  • User B asks another small fast query but the MySQL server is still busy with the first query.

The above example is just a non real scenario with a bad database table schema, and a horrible query to serve as the purpose of asking what will happen to MySQL and Apache in those situations.

I would like to know how they could handle or solve it, and how would it be possible to balance its load so that the MySQL and Apache server never gets hanged up with a bad or slow query from a single user, and at the same time they could still process other querys from the rest of the users.

Thank you so much and sorry if my question is wrong in some concepts. Please feel free to add any tip, usefull book or your own experience with servers and applications.

Community
  • 1
xtrm
  • 113
  • 1
  • 6
  • Added more links with usefull info: http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-tutorial http://webmasters.stackexchange.com/questions/22092/symfony-project-on-multiple-servers http://serverfault.com/questions/261235/load-balancing-nginx-varnish-and-http-sessions http://aws.amazon.com/es/elasticloadbalancing – xtrm Feb 28 '13 at 17:14
  • Yyou could look at query caching and performance tuning. Look at https://github.com/rackerhacker/MySQLTuner-perl It's a great MySql tuner script, run it once a month for example after your server first goes live and tune it as required as not all servers have the same users, works flows, DB schemas etc, this will help with your specific scenario a bit. Also, if you're coding an app your self looking into caching with memcached or similar, this will greatly improve performance (not a solution to dumb users or devs though!). Select an appropriate DB engine, in the case of MySQL, InnoDB or MyISAM – jwbensley Mar 01 '13 at 14:55
  • Also, you can set up MySQL replicas, Master/Master, Master/Slave etc, and then put those behind a load balancer like HAProxy, the same is true for Apache hosts, behind Pound Proxy or HAProxy, if you want to scale up the number of servers you have. Read about HAProxy, and MySQL replication. Read about SANs for distributed storage also. – jwbensley Mar 01 '13 at 14:57

2 Answers2

2

Short answer: if you want to prevent overloading the server with dumb queries, just prevent dumb queries.

Long answer:

Load balancing, as you mention, is more efficient in environements with a lot of independent queries. A large, heavy query, will always overload a server. But you can limit the incidence of a user by seting quotas. Do a search about that for Apache, or come back here for more info.

But the best solution is still to analyse the queries and decide for each query if:

  1. it should be run without any further action
  2. it should be skipped with an error message
  3. it should be sent to another server ; that's a kind of predictive balancing

Now, so that we can help you more in details, you can share with us more details, like DB structure, query types, and so on. We could find some algorithm to switch between the 3 cases.

Ninj
  • 136
  • 3
  • Thank you so much for the info Ninj, both you and alex88 where really helpfull at the symfony freenode irc channel. As you said, in the case of dumb querys, we must detect and block them. In the case of a needed query, like a big report of 10GB of data, as you both also explained the options could be to set a limit with quotas for each user and to implement a load balancing which could improve performance. Searched for some quota Apache modules, never tested them but seems nice. – xtrm Mar 01 '13 at 12:22
0

As you said Ninj, quota Apache modules could be a nice option to limit the bandwidth for each user.
It will be really usefull if they had these features:

  • Set a maximum and a minimum bandwidth to prevent server overload.
  • Automatic detection of server load and automatic limit for each user based on server load. Probably this feature is more complicated, and I dont know if there is any Apache mod which has this functionality.

Found these Apache modules:

  • Apache mod bw
  • Apache mod throttling
  • Apache mod quota

Alternative to mod_throttle

Of course first should be need to prevent overloading the MySQL with a simple query. An option would be to set also quotas, to implement load balancing and to detect and ban bad or overload querys as you explained.

Community
  • 1
xtrm
  • 113
  • 1
  • 6