Opened 16 months ago
Last modified 16 months ago
#116 new defect
Limit the number of MySQL connections from Crin4 to Crin1
| Reported by: | chris | Owned by: | chris |
|---|---|---|---|
| Priority: | major | Milestone: | Maintenance |
| Component: | crin1 | Version: | |
| Keywords: | Cc: | peter | |
| Estimated Number of Hours: | 0 | Add Hours to Ticket: | 0 |
| Billable?: | yes | Total Hours: | 0.75 |
Change History (4)
comment:1 follow-up: ↓ 2 Changed 16 months ago by peter
comment:2 in reply to: ↑ 1 ; follow-up: ↓ 3 Changed 16 months ago by chris
- Add Hours to Ticket changed from 0 to 0.5
- Total Hours 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";
comment:3 in reply to: ↑ 2 Changed 16 months ago by chris
- Add Hours to Ticket changed from 0 to 0.25
- Total Hours 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";
comment:4 Changed 16 months ago by peter
Thanks Chris. We will be doing some careful testing soon.
