How to throttle or prioritize a query in MySql

问题:

Is there anyway to prioritize or throttle a query at all in MySQL?

I'm a DBA on a server that sees a lot of unoptimized queries come into the server and they just destroy the CPU. I'm looking at throttling certain users that hit on the database in poor fashion.

Clarification:

I realize that MySQL has facilities built in to limit number of queries, connections, etc. But those aren't really the issue, it's that once in a blue moon a user will send an unoptimized query, and I'd need to time it out or something similar.

回答1:

http://dev.mysql.com/doc/refman/4.1/en/user-resources.html

Starting from MySQL 4.0.2, you can limit access to the following server resources for individual accounts:

The number of queries that an account can issue per hour

The number of updates that an account can issue per hour

The number of times an account can connect to the server per hour

Additional Info -

MySQL does not have a query timeout value, or any other built-in way to throttle an individual query. However, it is pretty trivial to write a script that will kill long-running queries. You could even capture the user and query so that you can beat the offending user/programmer later.

Here is an example of one using PERL.

回答2:

I know that phpMyAdmin has an area for Max # of Queries per hour, Max # of Updates per hour, Max # of Connections per hour, and Max # of User Connections per user. There are probably other ways to set these values other than phpMyAdmin