Le 25/06/2013 00:07, Marian Marinov a écrit :
On 06/24/2013 11:21 PM, Peter Laursen wrote:
Why not take a more daring approach - implement 'priorities' in
processlist?
"SET PRIORITY = high|medium|low [or 1..9] FOR processlist.ID=xx;"
I realize that this is not a trivial task, and obviously it requires
so much change in server architecture that it is
not a solution for Marian here-and-now.
Priorities actually require a new logic for handling the queries. This
is something that I don't think is feasible in SQL servers as a whole.
The problem is that the queries are generally short lived, so
implementing a priority based scheduling will only slow down the server.
It will be nice for long lived queries, but then the next problem is,
what would be the priority criteria and what would be the actions? We
will be going again back to the problem of pausing a query or stacking
many queries because of one that took a lot of resources.
@Marian BTW (as you probably know the answer): what do hosting
companies offering 2.95 $-per-month 'web hotel' solutions
do where hundreds or thousands of users/customers share a MySQL
server and one user repeatedly executes idiotic queres
from home-grown applications resulting in starving of the server and
thus effectively causing denial-of-service for
other users/customers? I was always surprised that they could manage
it with MySQL, actually!
Most hosting companies do not monitor mysql processes and in fact
don't care much until one user abuses the system. After a few strikes
the user is usually persuaded either to upgrade or go to another host.
In the case of a shared hosting environnement, this priority idea might
not be effective in all cases.
For example, if you would use a crontab script that reads the
processlist to SET PRIORITY on queries, if the user has a forum or a
guestbook that allows the posting of messages or the registration by
bots, you can end up with many small insert/select queries that would
take less than a sec but that would use resources.
Another problem is that lowering priority wont be good if the server is
already slow and having trouble executing the already priorized queries.
Usually on shared hosting server, you limit the number of concurrent
connections per user depending on the hosting plan and kill the queries
running longer than X sec (you can use different settings depending on
the query type and the user), you can also use a crontab script that
would read the processlist periodically to count how much long queries
are ran by each users then send an automated mail to the abusing
customer saying that if it still happens on the next few days its
database will be read-locked or that he needs to take a better hosting plan.
Its not always easy to identify the origin of the problem if the admin
is not watching the processlist at the moment of the problem and not
always due ton long running queries but you can set a crontab that wil
save the processlist on a file if the number of running queries (or the
slow queries) gets abnormal (i think that one of the Percona Toolkit
tools can do that) and set your monitoring to send an alert in these cases.
Having on its backoffice an "overquota/abnormal ressource usage/hacked
db" database disabler button is also quite handy when you encounter
these issues often.
Another useful practice could be to monitor if a DB has grown more than
x% between two DB size calculation, if the growth seems abnormal, the
admins are notified and got to check (it can send a mail/put in the
back-office a recap of the biggest tables of the DB so you can directly
see when its the usual WP/Prestashop or phpBB tables that can be
problematic).
ps: you can also check the tables called %wordlist and %wordmatch, the
ones contains much datas are holding "old-style" search indexes and
datas for forums and CMS and can grow very fast and using this kind of
search rather than fulltext could use more resources, the use of these
can be usually disabled really simply either by modifying a search type
setting from the forum/CMS backoffice either by modifying it on the
config file or the table config ; after that, these tables can be
truncated safely.
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
