I want to limit MySQL user resources. On a dual 1 GHz, 4 GB RAM web server I have so far set the following:

set-variable=max_connections=100
set-variable=max_connect_errors=512
set-variable=wait_timeout=30
set-variable=thread_cache_size=80

I want to set a further limit on a *per user basis* if possible e.g. number of queries/updates per hour. What are my options?

BTW what's the difference between max_user_connections and max_connections? And more importantly, how will limiting the max_user_connections affect my users?

Thank you.

When you say users I am not sure if you mean visiitors, or the ones that have an acount and use mysql.

If your have some kind of frontend to that database you could set visitor limitations there.

If you want to limit people with accounts, then you could limit thir bandwidth (results of those queries have to go somewhere) and you could limit their database size by limiting their disk quota.

This is interesting topic, and I hope someone has something smarter to say.

Originally posted by sasha
When you say users I am not sure if you mean visiitors, or the ones that have an acount and use mysql.

If your have some kind of frontend to that database you could set visitor limitations there.

If you want to limit people with accounts, then you could limit thir bandwidth (results of those queries have to go somewhere) and you could limit their database size by limiting their disk quota.

This is interesting topic, and I hope someone has something smarter to say.

The 'users' are my clients not visitors. The 'resources' are my memory and CPU cycles. Limiting bandwidth is not an issue as I have tons of it and limiting the db size is pointless if they have hundreds of concurrent connections. Any other suggestions?

I am by no means expert but what you could do is to start multiple servers (they would need to run on different ports) and then pass specific set of options for each one. Ruining more servers would be more memory intensive, but it would let you control max connections per server (per user assuming that each user gets one) so it would save you resources here.

You could schedule cronjob based on load average and run mysqladmin processlist to show you runing processes and then kill them if too many mysql threads are runing for any user.

Have you read this?

http://www.mysql.com/doc/en/User_resources.html

max_user_connections seems to be a global option, meaning you can't limit individual users, just users as a whole (john and joe both get 100 connections max -- john can't get 50 and joe can't get 75).

Mysql 4.0.2+ seems to address that issue. I've never used it so I don't know.