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
starnetdev
  • 113

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
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
starnetdev
  • 113