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:
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.