mysql, 1040, ‘Too many connections’

mysql, 1040, ‘Too many connections’

mysql을 이용해서 스크립트 작성중 too many connections 오류가 발생했다.

스크립트 실행 전에 processlist를 확인해 보면 아래와 같다.

ysql> show full processlist\g
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+
| Id     | User     | Host            | db       | Command | Time | State    | Info                  |
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+
| 142415 | foxxipam | localhost:42880 | foxxipam | Sleep   |  239 |          | NULL                  |
| 142416 | foxxipam | localhost:42898 | foxxipam | Sleep   |  239 |          | NULL                  |
| 144300 | foxxipam | localhost       | foxxipam | Sleep   |  750 |          | NULL                  |
| 144325 | foxxipam | localhost       | foxxipam | Query   |    0 | starting | show full processlist |
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+
4 rows in set (0.00 sec)

작성한 스크립트 실행하고, processlist를 확인해 보면,

mysql> show full processlist\g
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+
| Id     | User     | Host            | db       | Command | Time | State    | Info                  |
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+
| 142415 | foxxipam | localhost:42880 | foxxipam | Sleep   |  367 |          | NULL                  |
| 142416 | foxxipam | localhost:42898 | foxxipam | Sleep   |  367 |          | NULL                  |
| 144300 | foxxipam | localhost       | foxxipam | Sleep   |  878 |          | NULL                  |
| 144325 | foxxipam | localhost       | foxxipam | Query   |    0 | starting | show full processlist |
| 144367 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144368 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144369 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144370 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144371 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144372 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144373 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144374 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144375 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144376 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144377 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144378 | foxxipam | localhost       | foxxipam | Sleep   |   17 |          | NULL                  |
| 144379 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144380 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144381 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144382 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144383 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144384 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144385 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144386 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144387 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144388 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144389 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144390 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144391 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144392 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144393 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144394 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144395 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144396 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144397 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144398 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144399 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144400 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144401 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144402 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144403 | foxxipam | localhost       | foxxipam | Sleep   |   21 |          | NULL                  |
| 144404 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144405 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144406 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144407 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144408 | foxxipam | localhost       | foxxipam | Sleep   |   30 |          | NULL                  |
| 144409 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144410 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144411 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144412 | foxxipam | localhost       | foxxipam | Sleep   |   16 |          | NULL                  |
| 144413 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144414 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144415 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144416 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144417 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144418 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144419 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144420 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144421 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144422 | foxxipam | localhost       | foxxipam | Sleep   |   40 |          | NULL                  |
| 144423 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144424 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144425 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144426 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144427 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144428 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144429 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144430 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144431 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144432 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144433 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144434 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144435 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144436 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144437 | foxxipam | localhost       | foxxipam | Sleep   |   38 |          | NULL                  |
| 144438 | foxxipam | localhost       | foxxipam | Sleep   |   30 |          | NULL                  |
| 144439 | foxxipam | localhost       | foxxipam | Sleep   |   39 |          | NULL                  |
| 144440 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144441 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144442 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144443 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144444 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144445 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144446 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144447 | foxxipam | localhost       | foxxipam | Sleep   |   37 |          | NULL                  |
| 144448 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144449 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144450 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144451 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144452 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144453 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144454 | foxxipam | localhost       | foxxipam | Sleep   |   29 |          | NULL                  |
| 144455 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144456 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144457 | foxxipam | localhost       | foxxipam | Sleep   |   29 |          | NULL                  |
| 144458 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144459 | foxxipam | localhost       | foxxipam | Sleep   |   36 |          | NULL                  |
| 144460 | foxxipam | localhost       | foxxipam | Sleep   |   29 |          | NULL                  |
| 144461 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144462 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144463 | foxxipam | localhost       | foxxipam | Sleep   |   28 |          | NULL                  |
| 144464 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144465 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144466 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144467 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144468 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144469 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144470 | foxxipam | localhost       | foxxipam | Sleep   |   28 |          | NULL                  |
| 144471 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144472 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144473 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144474 | foxxipam | localhost       | foxxipam | Sleep   |   29 |          | NULL                  |
| 144475 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144476 | foxxipam | localhost       | foxxipam | Sleep   |   35 |          | NULL                  |
| 144477 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144478 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144479 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144480 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
| 144481 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144482 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144483 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144484 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144485 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144486 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144487 | foxxipam | localhost       | foxxipam | Sleep   |   33 |          | NULL                  |
| 144488 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144489 | foxxipam | localhost       | foxxipam | Sleep   |   34 |          | NULL                  |
| 144645 | foxxipam | localhost       | foxxipam | Sleep   |   11 |          | NULL                  |
+--------+----------+-----------------+----------+---------+------+----------+-----------------------+

꽤나 많은 프로스스 리스트가 보인다. 이것이, 설정된 최대 연결수를 초과하면 위의 1040 메시지가 나온다.

현재 max_connections 값을 확인 해 보면,

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

이 max_connections 값을 두배 이상으로 설정하기 위해 아래 명령을 실행했으나, 권한이 없다고한다.

mysql> set global max_connections = 350;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

mysql root 계정으로 다시 접속하고 명령을 내린다.

mysql> set global max_connections=350;
Query OK, 0 rows affected (0.00 sec)

이제, max_connections 값이 바뀐것을 볼 수 있다.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               350 |
+-------------------+
1 row in set (0.00 sec)

이제, 위의 오류는 발생하지 않지만, mysql 서버를 재 실행하면 위에서 설정한 값이 적용되지 않고 초기값으로 되돌아간다.
이것을 방지하기 위해서는 mysql 설정 파일에 위 내용을 등록해 주면된다.
ubuntu18.04 기준으로 /etc/mysql/mysql.conf.d/mysqld.cnf 파일에 max_connections 값을 등록해준다.

# vi /etc/mysql/mysql.conf.d/mysqld.cnf

...
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
max_connections        = 350
#table_open_cache       = 64
#thread_concurrency     = 10

...

mysql 서버를 재 실행하고, max_connections 값을 확인 해 보면 설정한 값(여기에서는 350)이 적용되어 있는 것을 볼 수 있다.

# systemctl restart mysql

답글 남기기

Your email address will not be published.

%d bloggers like this: