Hi i am new to MySQL and currently using version 5.5, currently i have one instance for MySQL, with 50 databases hosted on that instance. We have identified that per database there were fix amount of users connection per database is getting used like in my case its 100 users are active on that database. we would like to know is there any specific setting under which this behavior is getting observed?
I haven't done this in MySQL, but if you read here (MySQL 5.6), you can set limits per user account - but not AFAICS, per schema (substitue 5.5 or 5.7 in the link for other releases which seem identical).
This would be very logical and a very worthwhile feature and, while not knowing the internals of the coding of the MySQL engine in C/C++ in great detail, strikes me as being a very useful feature request. If you'd care to open one and report back here, I'll file a "me-too"!
The relevant bits of the documentation are here:
To address such concerns, MySQL permits limits for individual accounts on use of these server resources:
The number of queries an account can issue per hour
The number of updates an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server by an account
Note the recurrence of the word
account and not
Any statement that a client can issue counts against the query limit, unless its results are served from the query cache. Only statements that modify databases or tables count against the update limit.
This actually makes sense, not a characteristic I've come to expect from MySQL documentation! :-)
And finally (and possibly most relevantly!):
Resource counting occurs per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.
So, resources are dished out on a
per account basis and no other.
A potential workaround is to specify a single user account per schema and then restrict that user to x number of queries, y number of updates and z number of simultaneous connections. Not very satisfactory I know, but that's the state of play at the minute!
(p.s. +1 for a good question!)
You may set a cron every minute to trigger below command and pipe it to a logfile and rotate it every day.
select current_timestamp(); select count(*) sumOfUsers , user,db from information_schema.processlist group by user,db;
After a day you may grep for values more than 90 in sumOfUsers column to see which line and then you may get timestamp above it. Thus you may measure time range and do manual monitoring around that time to see how often it takes the connections almost 100. Also you may add Host in query to see if any particular application host is doing this pile up of connections.