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