PMM creating too many connections

Percona Monitoring and Management (PMM) PMM 2.x
1

Hi

PMM is creating more and more threads so that the threads connected is reaching the maximum set. I am using the latest version of xtradb cluster 8.0.30 and PMM 2.34.0. This only started with the latest version of PMM. If there is no data in the database then the connections do not rise but as soon as there is data in the database the connections just keep rising until it hits the maximum, which was 250, then 1000, now I am trying 10000 just to keep the cluster running.

my.cnf

Connections

#max_connections=250
max_connections=10000
max_user_connections=20

Original grant for ‘pmm’@‘localhost’
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON . TO ‘pmm’@‘localhost’;

After reading a few threads I have revoked SUPER from ‘pmm’@‘localhost’;
REVOKE SUPER ON . FROM ‘pmm’@‘localhost’;
so that pmm now has
GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT ON . TO pmm@localhost
GRANT BACKUP_ADMIN ON . TO pmm@localhost

which is supposed to be the temporary fix but this has not solved the issue. The threads connected keeps climbing.

Do we have a date when this will be fixed?

2

Hi @MikeBerry thanks for posting to the Percona forums!

You can begin by implementing an account resource limit, for example capping the number of connections allowed for the monitoring user by including a WITH MAX_USER_CONNECTIONS 10 directive. You can modify the existing user like this:

ALTER USER pmm@'%' WITH MAX_USER_CONNECTIONS 10;

I see you are already experimenting with setting this variable at the global level. I suggest you remove it there and set it only at the per-user level.

Once this is implemented, restart PMM

sudo systemctl restart pmm-agent

Then observe whether the connections are again spiking. If so, capture the output of SHOW PROCESSLIST and share it here so we can review. You should see a maximum of 10 connections by the pmm user and therefore should not be seeing an error of too many connections.

3

Hi @Michael_Coburn thanks for the reply.

I did what you said, alter pmm user and restart pmm-agent. However pmm is still creating connections and leaving them sleeping as readonly then creating more.

showprocesslist below - If I stop the pmm-agent then threads connected decreases slowly and the readonly sleeping connections slowly disappear.

mysql> show processlist;
±----±----------------±------------------------------------±-----±--------±-----±-------------------------------------±-----------------------------------------------------------------------------------------------------±--------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
±----±----------------±------------------------------------±-----±--------±-----±-------------------------------------±-----------------------------------------------------------------------------------------------------±--------±----------±--------------+
| 1 | system user | | NULL | Sleep | 952 | innobase_commit_low (-1) | NULL | 952499 | 0 | 0 |
| 2 | system user | | NULL | Sleep | 952 | wsrep aborter idle | NULL | 952499 | 0 | 0 |
| 7 | event_scheduler | localhost | NULL | Daemon | 949 | Waiting on empty queue | NULL | 949226 | 0 | 0 |
| 10 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949224 | 0 | 0 |
| 11 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949224 | 0 | 0 |
| 12 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949224 | 0 | 0 |
| 13 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949223 | 0 | 0 |
| 14 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949223 | 0 | 0 |
| 15 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949223 | 0 | 0 |
| 16 | system user | | NULL | Sleep | 949 | wsrep: applier idle | NULL | 949223 | 0 | 0 |
| 46 | mberry | 192.168.164.131:20915 | NULL | Sleep | 0 | | NULL | 215 | 56 | 143 |
| 47 | mberry | 192.168.164.131:20916 | NULL | Sleep | 2 | | NULL | 2184 | 614 | 614 |
| 56 | mberry | 192.168.164.131:20917 | NULL | Sleep | 515 | | NULL | 514771 | 0 | 0 |
| 57 | mberry | 192.168.164.131:20918 | NULL | Sleep | 515 | | NULL | 514771 | 0 | 0 |
| 126 | mberry | DBA-Win10-Prod1.crowngp.local:35432 | NULL | Sleep | 94 | | NULL | 94285 | 0 | 0 |
| 127 | mberry | DBA-Win10-Prod1.crowngp.local:35433 | NULL | Sleep | 94 | | NULL | 94284 | 0 | 0 |
| 136 | mberry | 192.168.164.131:20926 | NULL | Sleep | 51 | | NULL | 51284 | 0 | 0 |
| 137 | mberry | 192.168.164.131:20927 | NULL | Sleep | 51 | | NULL | 51283 | 0 | 0 |
| 279 | pmm | localhost:38738 | NULL | Query | 151 | Opening tables | SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE,
ifnull(ENGINE, ‘NONE’) as ENGIN | 150877 | 69 | 0 |
| 282 | pmm | localhost:38770 | NULL | Query | 151 | Opening tables | SELECT t.table_schema, t.table_name, column_name, auto_increment,
pow(2, case data_type
wh | 150877 | 520 | 0 |
| 308 | pmm | localhost:38996 | NULL | Query | 91 | Opening tables | SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE,
ifnull(ENGINE, ‘NONE’) as ENGIN | 90881 | 60 | 0 |
| 311 | pmm | localhost:39012 | NULL | Query | 91 | Opening tables | SELECT t.table_schema, t.table_name, column_name, auto_increment,
pow(2, case data_type
wh | 90899 | 472 | 0 |
| 313 | readonly | localhost:39062 | NULL | Connect | 91 | Waiting in connection_control plugin | NULL | 90879 | 0 | 0 |
| 314 | readonly | localhost:39066 | NULL | Connect | 91 | Waiting in connection_control plugin | NULL | 90810 | 0 | 0 |
| 315 | readonly | localhost:39070 | NULL | Connect | 91 | Waiting in connection_control plugin | NULL | 90729 | 0 | 0 |
| 316 | readonly | localhost:39074 | NULL | Connect | 91 | Waiting in connection_control plugin | NULL | 90610 | 0 | 0 |
| 317 | readonly | localhost:39078 | NULL | Connect | 91 | Waiting in connection_control plugin | NULL | 90609 | 0 | 0 |
| 318 | readonly | localhost:39082 | NULL | Connect | 90 | Waiting in connection_control plugin | NULL | 90435 | 0 | 0 |
| 319 | readonly | localhost:39086 | NULL | Connect | 90 | Waiting in connection_control plugin | NULL | 90336 | 0 | 0 |
| 320 | readonly | localhost:39090 | NULL | Connect | 90 | Waiting in connection_control plugin | NULL | 90194 | 0 | 0 |
| 327 | readonly | localhost:39170 | NULL | Connect | 61 | Waiting in connection_control plugin | NULL | 60836 | 0 | 0 |
| 328 | readonly | localhost:39174 | NULL | Connect | 61 | Waiting in connection_control plugin | NULL | 60751 | 0 | 0 |
| 329 | readonly | localhost:39178 | NULL | Connect | 61 | Waiting in connection_control plugin | NULL | 60671 | 0 | 0 |
| 330 | readonly | localhost:39186 | NULL | Connect | 60 | Waiting in connection_control plugin | NULL | 60535 | 0 | 0 |
| 331 | readonly | localhost:39190 | NULL | Connect | 60 | Waiting in connection_control plugin | NULL | 60374 | 0 | 0 |
| 332 | readonly | localhost:39194 | NULL | Connect | 60 | Waiting in connection_control plugin | NULL | 60285 | 0 | 0 |
| 337 | pmm | localhost:39242 | NULL | Query | 31 | Opening tables | SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE,
ifnull(ENGINE, ‘NONE’) as ENGIN | 30883 | 8 | 0 |
| 340 | pmm | localhost:39256 | NULL | Sleep | 1 | | NULL | 890 | 0 | 0 |
| 341 | pmm | localhost:39258 | NULL | Query | 31 | Opening tables | SELECT t.table_schema, t.table_name, column_name, auto_increment,
pow(2, case data_type
wh | 30892 | 464 | 0 |
| 342 | readonly | localhost:39306 | NULL | Connect | 31 | Waiting in connection_control plugin | NULL | 30882 | 0 | 0 |
| 343 | readonly | localhost:39310 | NULL | Connect | 31 | Waiting in connection_control plugin | NULL | 30793 | 0 | 0 |
| 344 | readonly | localhost:39314 | NULL | Connect | 31 | Waiting in connection_control plugin | NULL | 30706 | 0 | 0 |
| 345 | readonly | localhost:39318 | NULL | Connect | 31 | Waiting in connection_control plugin | NULL | 30620 | 0 | 0 |
| 346 | readonly | localhost:39322 | NULL | Connect | 30 | Waiting in connection_control plugin | NULL | 30595 | 0 | 0 |
| 347 | readonly | localhost:39326 | NULL | Connect | 30 | Waiting in connection_control plugin | NULL | 30464 | 0 | 0 |
| 348 | readonly | localhost:39330 | NULL | Connect | 30 | Waiting in connection_control plugin | NULL | 30321 | 0 | 0 |
| 349 | readonly | localhost:39334 | NULL | Connect | 30 | Waiting in connection_control plugin | NULL | 30253 | 0 | 0 |
| 356 | mberry | localhost:39412 | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
| 357 | readonly | localhost:39432 | NULL | Connect | 1 | Waiting in connection_control plugin | NULL | 823 | 0 | 0 |
| 358 | readonly | localhost:39436 | NULL | Connect | 1 | Waiting in connection_control plugin | NULL | 736 | 0 | 0 |
| 359 | readonly | localhost:39440 | NULL | Connect | 1 | Waiting in connection_control plugin | NULL | 670 | 0 | 0 |
| 360 | readonly | localhost:39444 | NULL | Connect | 0 | Waiting in connection_control plugin | NULL | 522 | 0 | 0 |
| 361 | readonly | localhost:39448 | NULL | Connect | 0 | Waiting in connection_control plugin | NULL | 406 | 0 | 0 |
| 362 | readonly | localhost:39452 | NULL | Connect | 0 | Waiting in connection_control plugin | NULL | 258 | 0 | 0 |
±----±----------------±------------------------------------±-----±--------±-----±-------------------------------------±-----------------------------------------------------------------------------------------------------±--------±----------±--------------+
54 rows in set (0.00 sec)

4

Hi,

I think the problem here is that connection is not working properly. Seems to be related to this bug report:
https://bugs.mysql.com/bug.php?id=89155

More information here:
https://dev.mysql.com/doc/refman/8.0/en/connection-control.html

I recommend checking with the connection control plugin disabled. If the number of connections disappears, then probably we’re experiencing a problem with the credentials used by pmm to connect.

I would verify if the agent can connect from localhost using the readonly user and the password configured in pmm.

Pep