Mysql – Prevent heavy server query to overload Apache/Mysql

apache-2.2high-loadload balancingMySQLphp5

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.

Best Answer

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.

Related Topic