Increase the Max SQL connections per user

[psytanium]

Hello,

A user hosted on my VPS experience the Max SQL connection error, especially when they advertise on social media, and they lead some increased audience on the website.

I tried to run
mysql -u {username} -p {password} from terminal, I'm asked to enter a password, I enter the database password but i receive this error:

ERROR 1045 (28000): Access denied for user 'woodandgasleb'@'localhost' (using password: YES)

Also I tried to use the new cPanel feature SQL Services > Edit SQL Configuration

I increased the "Max Connections" from 500 to 600. Is this enough to prevent the randomly appearing error when we open the website ?

 

[cPanelWilliam]

Hello there! It's difficult to know whether 600 will be a high enough value for the max_connections since this would depend on the amount of traffic the site receives and the demands of the site/database. It is best to make small changes to the max_connections and monitor the site/MySQL error log to determine if further changes are needed, as making a very large increase to the value could cause the server to become unstable.

 

[psytanium]

Hello there! It's difficult to know whether 600 will be a high enough value for the max_connections since this would depend on the amount of traffic the site receives and the demands of the site/database. It is best to make small changes to the max_connections and monitor the site/MySQL error log to determine if further changes are needed, as making a very large increase to the value could cause the server to become unstable.

1. How can I increase the max sql connection per user ?
2. How can I know the max sql connection needed per user ?
3. Adjusting the "Max Connections" in Services > Edit SQL Configuration, is the right thing to fix the problem ?

Thanks

 

[cPRex]

As far as I know, there isn't a user-level option for the MySQL connections. That variable is set globally and would apply to all user accounts. In order to get that functionality, you'd need a tool like CloudLinux's MySQL Governor:

CloudLinux - Restricting MySQL usage resources with MySQL Governor - CloudLinux

www.cloudlinux.com

The only way to know how many connections you need it to see if the user hits the limits. It may be possible for a professional database administrator to look at the code of the site and determine how many MySQL connections that would make, and then you could extrapolate a guess based on that number, but it is still ultimately a guess. You just need to see how the server behaves once the real traffic starts to happen.

Yes, Max Connections is the best way to resolve the current issues you're seeing.

 

[psytanium]

Does this article help with max sql connection "per user" ?

https://support.cpanel.net/hc/en-us/articles/360052603654-How-to-Modify-the-max-connections-Value-in-MySQL

 

[cPRex]

No, there is no "per user" option for this setting in a default MySQL configuration.