I have a dedicated Cpanel server and I want to lower the number of simultaneous connections for each user to 25.
How is it done?
Thank you
How is it done?
Thank you
Hey there! There's no way to do this unless you're using CloudLinux with the MySQL Governor tool. Just cPanel alone doesn't have these type of limits in place.
You can read more about MySQL Governor here: CloudLinux - Restricting MySQL usage resources with MySQL Governor - CloudLinux
You can read more about MySQL Governor here: CloudLinux - Restricting MySQL usage resources with MySQL Governor - CloudLinux
Would the following not work in /etc/my.cnf:
...where 500 would be the maximum connection limit to the daemon (whatever you deem appropriate for your deployment) and 25 would be the max connections per user:
REF: MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables
If you didn't need this to be ubiquitous for the entire server, you could also limit per-user:
REF: MySQL :: MySQL 5.7 Reference Manual :: 6.2.16 Setting Account Resource Limits
Code:
max_connections = 500
max_user_connections = 25REF: MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables
If you didn't need this to be ubiquitous for the entire server, you could also limit per-user:
Code:
ALTER USER 'specificUser'@'localhost' WITH MAX_USER_CONNECTIONS 25;I have something to add, in case others will need it.
can be changed in WHM > SQL Services > Edit SQL Configuration
max_user_connections = 25, I didn't have this in /etc/my.cnf and when added the load increased dramatically after some time, MySQL started to crash. Maybe the reason was that in Cloudlinux DB governor I had lower number of connections and max_user_connections overrides Cloudlinux settings, I don't know. When I removed it everything went back to normal.
Another thing, at first I thought this command(and the same above max_user_connections) limits cpanel user, but it actually limits database user inside a cpanel account. In my case, one cpanel account had 30+ MySQL users and this thing doesn't help at all.
In this case you need to limit each DB user separately. Here is the command to list all users in the proper format to limit them
("concat" should be together, the forum won't let me use it here)
If using phphmyadmin then click "options" below, full text, go. Then copy the needed user/s and execute the command. Setting connections to 1 reduced the load significantly in my case.
Code:
max_connections = 500max_user_connections = 25, I didn't have this in /etc/my.cnf and when added the load increased dramatically after some time, MySQL started to crash. Maybe the reason was that in Cloudlinux DB governor I had lower number of connections and max_user_connections overrides Cloudlinux settings, I don't know. When I removed it everything went back to normal.
Another thing, at first I thought this command(and the same above max_user_connections) limits cpanel user, but it actually limits database user inside a cpanel account. In my case, one cpanel account had 30+ MySQL users and this thing doesn't help at all.
Code:
ALTER USER 'specificUser'@'localhost' WITH MAX_USER_CONNECTIONS 25;
Code:
SELECT con cat("ALTER USER '", `user`, "'@'localhost' WITH MAX_USER_CONNECTIONS 1;") FROM `mysql`.`user`If using phphmyadmin then click "options" below, full text, go. Then copy the needed user/s and execute the command. Setting connections to 1 reduced the load significantly in my case.
