Question How to isolate domain resource (MySQL) by Plesk ?

[Sergey K.]

Server operating system version

Ubuntu 20.04.6 LTS

Plesk version and microupdate number

Obsidian, version 18.0.57

Hi, dear community!

I've got 30 domains (Plesk Obsidian, version 18.0.57, Web Pro version)

I have the following problem with 1 domain:
This site and domain initiate problems in SQL queries and when domain operates all MySQL bases will be down on the server (error = "Too many SQL connection").

Is there a way to isolate resources of the domain by Plesk settings ( I'm talking about MySQL capacity, PHP vars , Performance settings) such way, that only this domain will be down, but not the whole MySQL of server and all sites?

 

[Peter Debik]

You have two variables for MariaDB that you can use in the [mysqld] section of /etc/my.cnf:
max_connections: The total number of connections the database server accepts
max_user_connections: The maximum number of connections a single user can use at the same time
The latter will help you limit single user accounts so that if these exceed the number, no further connections by them will be allowed while all other connections stay intact.
Remember to reload the database server after applying the changes.

 

[Sergey K.]

Thanks for the prompt reply, Peter!

2 questions acc. to your comment:

1) How to determine convenient value for all sites - var - max_user_connections ? Before to set value I want to investigate the current loading for each site.

2) You told "The latter will help you limit single user accounts so that if these exceed the number," but my.cnf is global value for the whole server. I can't find such option for each domain locally (or I don't know where to find such option) :-(

P.S. Please correct path in reply - it was misprint. Should be: /etc/mysql/my.cnf

Thanks again for your time and answers.

 

[Peter Debik]

If you only want to impose specific limits on individual users, please see the documentation how to do it (including an example) here:
MySQL :: MySQL 8.0 Reference Manual :: 6.2.21 Setting Account Resource Limits (it's the same for MariaDB, but the MySQL 8 docs are easier in this part).

I am not aware of a simple method to determine the sweet spot for the limits.

 

[Sergey K.]

Thanks for the reply.
Does it mean, it's not possible to isolate MySQL usage with help of Plesk ? Am I right?

 

[Peter Debik]

Yes, Plesk does not have an editor for the my.cnf file (it's the same with other operating system files or configuration files of services).