CRIN Trac: Ticket #116: Limit the number of MySQL connections from Crin4 to Crin1

peter — Wed, 17 May 2017 12:35:08 GMT

I think we should just start with connections for now.
What if we took connections down to 50?
UPDATE mysql.user SET max_connections = 50 WHERE user='myuser' AND
host='10.1.2.30'; FLUSH PRIVILEGES;
On Wed, 17 May 2017 at 12:44 CRIN Trac <trac@trac.crin.org> wrote:
> #116: Limit the number of MySQL connections from Crin4 to Crin1
> ------------------------------+-----------------------------------------
>            Reporter:  chris   |                      Owner:  chris
>                Type:  defect  |                     Status:  new
>            Priority:  major   |                  Milestone:  Maintenance
>           Component:  crin1   |                    Version:
>            Keywords:          |  Estimated Number of Hours:  0
> Add Hours to Ticket:  0       |                  Billable?:  1
>         Total Hours:  0       |
> ------------------------------+-----------------------------------------
>  Both the dev, [[Crin4]] and live [[Crin2]], servers use the MySQL database
>  on [[Crin1]] and we want to limit the resources available to [[Crin4]] so
>  it reduced the impact on the live site.
>
>  Some documentation on this:
>
>  * https://dba.stackexchange.com/a/15427
>  * https://dev.mysql.com/doc/refman/5.7/en/user-resources.html
>
> --
> Ticket URL: <https://trac.crin.org/trac/ticket/116>
> CRIN Trac <https://trac.crin.org/trac>
> Trac project for CRIN website and servers.
>

hours changed; totalhours set

chris — Thu, 18 May 2017 11:27:07 GMT

hours changed from 0 to 0.5
totalhours set to 0.5

Replying to peter:

I think we should just start with connections for now. What if we took connections down to 50?

OK, looking at what we have to start with:

mysql> select Host,User,ssl_type,max_questions,max_updates,max_connections,max_user_connections from user;
+-----------+------------------+----------+---------------+-------------+-----------------+----------------------+
| Host      | User             | ssl_type | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------------------+----------+---------------+-------------+-----------------+----------------------+
| localhost | root             |          |             0 |           0 |               0 |                    0 |
| crin1     | root             |          |             0 |           0 |               0 |                    0 |
| 127.0.0.1 | root             |          |             0 |           0 |               0 |                    0 |
| ::1       | root             |          |             0 |           0 |               0 |                    0 |
| localhost | debian-sys-maint |          |             0 |           0 |               0 |                    0 |
| localhost | piwik            |          |             0 |           0 |               0 |                    0 |
| localhost | owncloud         |          |             0 |           0 |               0 |                    0 |
| localhost | mediawiki        |          |             0 |           0 |               0 |                    0 |
| localhost | phpmyadmin       |          |             0 |           0 |               0 |                    0 |
| crin2     | example          | ANY      |             0 |           0 |               0 |                    0 |
| crin2     | drupal           | ANY      |             0 |           0 |               0 |                    0 |
| crin2     | root             | ANY      |             0 |           0 |               0 |                    0 |
| crin4     | stage            | ANY      |             0 |           0 |               0 |                    0 |
| crin4     | dev              | ANY      |             0 |           0 |               0 |                    0 |
| crin4     | newprod          | ANY      |             0 |           0 |               0 |                    0 |
| crin2     | newprod          | ANY      |             0 |           0 |               0 |                    0 |
| localhost | newprod          |          |             0 |           0 |               0 |                    0 |
| localhost | stage            |          |             0 |           0 |               0 |                    0 |
| localhost | dev              |          |             0 |           0 |               0 |                    0 |
+-----------+------------------+----------+---------------+-------------+-----------------+----------------------+
19 rows in set (0.00 sec)

It looks to me that we have some lines above that we don't need, users "example" doesn't need to connect from the live server, Crin2 to the database server, so:

mysql> delete from user where Host="crin2" and User="example";
Query OK, 1 row affected (0.00 sec)

In terms of the max connections, looking at /etc/mysq;/my.cnf we haven't set a limit for this, looking at the Munin graphs there is a default limit of 151 or 152.

I think a max of 50 connections per hour might not make any difference -- connections are probably per php-fpm process and one process might be running a while?

The value for each per-hour limit should be an integer representing a count per hour. For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections by the account.

How about setting the max number of connections to 2 and limiting the select and updates to 1,000 per hour?:

UPDATE user SET max_user_connections=2,max_questions=1000,max_updates=1000 WHERE host="crin4";

hours, totalhours changed

chris — Fri, 19 May 2017 12:54:13 GMT

hours changed from 0 to 0.25
totalhours changed from 0.5 to 0.75

I have done the following:

mysql> UPDATE user SET max_user_connections=2,max_questions=1000,max_updates=1000 WHERE host="crin4";
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> select Host,User,Grant_priv,ssl_type,max_questions,max_updates,max_connections,max_user_connections from user;
+-----------+------------------+------------+----------+---------------+-------------+-----------------+----------------------+
| Host      | User             | Grant_priv | ssl_type | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------------------+------------+----------+---------------+-------------+-----------------+----------------------+
| localhost | root             | Y          |          |             0 |           0 |               0 |                    0 |
| crin1     | root             | Y          |          |             0 |           0 |               0 |                    0 |
| 127.0.0.1 | root             | Y          |          |             0 |           0 |               0 |                    0 |
| ::1       | root             | Y          |          |             0 |           0 |               0 |                    0 |
| localhost | debian-sys-maint | Y          |          |             0 |           0 |               0 |                    0 |
| localhost | piwik            | N          |          |             0 |           0 |               0 |                    0 |
| localhost | owncloud         | N          |          |             0 |           0 |               0 |                    0 |
| localhost | mediawiki        | N          |          |             0 |           0 |               0 |                    0 |
| localhost | phpmyadmin       | N          |          |             0 |           0 |               0 |                    0 |
| crin2     | drupal           | N          | ANY      |             0 |           0 |               0 |                    0 |
| crin2     | root             | N          | ANY      |             0 |           0 |               0 |                    0 |
| crin4     | stage            | N          | ANY      |          1000 |        1000 |               0 |                    2 |
| crin4     | dev              | N          | ANY      |          1000 |        1000 |               0 |                    2 |
| crin4     | newprod          | N          | ANY      |          1000 |        1000 |               0 |                    2 |
| crin2     | newprod          | N          | ANY      |             0 |           0 |               0 |                    0 |
| localhost | newprod          | N          |          |             0 |           0 |               0 |                    0 |
| localhost | stage            | N          |          |             0 |           0 |               0 |                    0 |
| localhost | dev              | N          |          |             0 |           0 |               0 |                    0 |
+-----------+------------------+------------+----------+---------------+-------------+-----------------+----------------------+
18 rows in set (0.00 sec)

Please let me know if this needs tweaking -- I have loaded the front page of the dev site and it seems fine but I haven't tried load testing it,

Replying to chris:

How about setting the max number of connections to 2 and limiting the select and updates to 1,000 per hour?:
UPDATE user SET max_user_connections=2,max_questions=1000,max_updates=1000 WHERE host="crin4";

peter — Tue, 23 May 2017 11:29:04 GMT

Thanks Chris. We will be doing some careful testing soon.