Home / server / Questions / 124158
In Process
Eduardo
Asked: 2010-03-19 23:11:40 +0800 CST

Throttle or limit resources used by a user in a database?

  • 772

I would like to know how databases (like MySQL) are able to manage per user resources (like Oracle Database Resource Manager does) to be able to have users that may only get resources only when the database is mostly idle.
https://stackoverflow.com/questions/2418581/is-there-a-way-to-throttle-or-limit-resources-used-by-a-user-in-oracle

database-administration resource-management

3 Answers

  1. Depends.

    Oracle has a Resource manager. SQL Server has one too (got one in 2008). Others (mySQL) dont.

    how they work? Like every scheduler. Basically they measure performance counters and if one user / database goes over the allocated limit, his queries get slowed down (lower piority, normally) compared to the rest.

    Note that "database is mostly idle" is REALLY a complicated question for databases. what you talk of? IO? CPU? Various queries have various bottlenecks.

    • 1

  2. MySQL, as far as I know, does not have a way to artificially limit resource usage by a user.

    Usually you don't limit database access / queries directly, if you want to offer different service levels to customers you do that at another level (eg. you limit the number of processes answering web requests, this will indirectly limit resource usage on the whole backend infrastructure).

    Users who do overuse a shared system to the point of creating problems to other users are usually detected by other means (eg. the system monitoring will alert you of an unresponsive system) and killed by hand. But that's not regular "scheduling" of normal operations, it's problem detection and response.

    • 1

  3. You can limit the amount of resources per user by alter commands

    ALTER USER 'dbreports'@'aihello.com' WITH MAX_QUERIES_PER_HOUR 100;
ALTER USER 'dbreports'@'aihello.com' WITH MAX_UPDATES_PER_HOUR 10;
ALTER USER 'dbreports'@'aihello.com' WITH MAX_CONNECTIONS_PER_HOUR 5;
ALTER USER 'dbreports'@'aihello.com' WITH MAX_USER_CONNECTIONS 2;

    More info here: https://dev.mysql.com/doc/refman/8.0/en/user-resources.html

    • 0