| Thread Previous • Date Previous • Date Next • Thread Next |
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 isnot 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.
| Thread Previous • Date Previous • Date Next • Thread Next |
