MySQL강좌16편: MYSQL의 “too many connections” 에러 해결하는 방법

MySQL강좌16편: MYSQL의 “too many connections” 에러 해결하는 방법

 

정상적으로 잘 운용되고있던 MYSQL데이터베이스가 어느날 갑자기 "Warning...too many connections...."라는 에러 메시지와 함께 종료되어 버리는 경우가 있다. 이에 대한 근본적인 원인은 MYSQL의 환경변수에 설정되어있는 “max_connections”의 값을 초과하는 쿼리가 발생하였기 때문이다. 

 

이에 대한 원인을 설명하기 위해서는 php에서 사용하는 MYSQL연결함수인 mysql_connect()와 mysql_pconnect의 차이점 그리고, mysql_close()함수에 대한 정확한 이해가 필요하다.

 

간단히 설명하자면, mysql_connect()함수를 이용하여 MYSQL에 연결하였다면 해당스크립트가 종료됨과 동시에 mysql_close()함수를 호출하지 않았다하더라도 자동으로 연결이 종료된다. 하지만 mysql_pconnect()함수는 해당스크립트가 종료된 후 mysql_close()함수가 호출되어도 연결이 끊어지지 않은 채로 계속 연결을 유지하고 있다.

 

따라서 얼핏보기에는 "too many connections"라는 에러메시지는 mysql_pconnect()라는 함수의 사용때문에 발생하는 것 같지만 (물론 그런 이유도 있을 수 있지만) 근본적인 원인은 이와 다를 수 있다.  즉 MYSQL의 메뉴얼을 보면 mysql_connect()함수를 사용하면 해당스크립트의 종료와 함께 연결이 종료된다고 되어있지만 앞에서 설명한 바와같이 ./mysqladmin -u root -p processlist"라는 명령어를 통해서 살펴보면 해당 프로세스가 종료되지 않고 그대로 남아있다는 것을 알 수 있다.

 

다음 예를 보도록하자.

 

[root@sulinux bin]#/usr/local/mysql/bin/mysqladmin -u root -p processlist Enter password: ******** +----+----------+-----------+------------+---------+------+-------+------------------+ | Id | User     | Host      | db         | Command | Time | State | Info             | +----+----------+-----------+------------+---------+------+-------+------------------+ | 4  | picasso  | localhost | picasso_db | Sleep   | 33   |       |                  | | 5  | sspark   | localhost | sspark_db  | Sleep   | 20   |       |                  | | 8  | tempuser | localhost | temp_db2   | Sleep   | 4    |       |                  | | 9  | root     | localhost |            | Query   | 0    |       | show processlist | +----+----------+-----------+------------+---------+------+-------+------------------+ [root@sulinux bin]#

 

결론적으로 "mysqladmin -u -p variables"의 결과로서 볼 수 있었던 MYSQL의 환경변수값들 가운데 "wait_timeout"의 값만큼 서버에 그대로 연결을 유지한 채로 남아있는 것이다.  따라서 이것이 "too many connections"에러가 발생하는 궁극적인 원인이 되는 것이다.

 

이를 해결하려면 MYSQL이 빠른 응답을 할 수 있도록 서버의 하드웨어사양(CPU, 메모리등)을 높이는 방법도 있겠지만 급하게 조치해야하는 경우에는 MYSQL의 필요한 변수의 값을 보다 높게 설정하는 방법이 현명할 것이다.

 

앞의 예에서 살펴보았듯이 “./mysqladmin -u  root -p variables"를 실행하면 MYSQL의 전체 환경변수의 값을 확인 할 수 있을 것이다. 이들 MYSQL 환경변수 가운데 다음 3가지의 값을 현재보다 높게 설정함으로서 “too many connections”에러를 응급조치할 수 있다.

 

max_connections             : MYSQL에 connect할 수 있는 최대 개수
table_open_cache            : MYSQL의 테이블캐쉬크기
wait_timeout                : MYSQL에 connect하여 쿼리를 지속할 수 있는 시간

 

즉 다음과 같이 MYSQL의 환경변수 값을 확인하기 바란다.

 

MYSQL의 max_connections변수값 확인

 

[root@sulinux bin]#/usr/local/mysql/bin/mysqladmin -u root -p variables | grep max_connections Enter password: ******** | max_connections                 | 151                                    | [root@sulinux bin]#

 

MYSQL의 table_open_cache변수값 확인

 

[root@sulinux bin]#/usr/local/mysql/bin/mysqladmin -u root -p variables | grep table_open_cache Enter password: ******** | table_open_cache                | 64                                     | [root@sulinux bin]#

 

MYSQL의 wait_timeout변수값 확인

 

[root@sulinux bin]#/usr/local/mysql/bin/mysqladmin -u root -p variables | grep wait_timeout Enter password: ******** | table_lock_wait_timeout         | 50                                     | | wait_timeout                    | 28800                                  | [root@sulinux bin]#

 

위의 3가지 MYSQL환경변수값을 확인하였다면 보다 적절한 값을 설정하여 다음과 같이 MYSQL을 시작하면 된다.

 

[root@sulinux bin]#/usr/local/mysql/bin/mysqld_safe -O max_connections=1000 -O table_open_cache=512 -O wait_timeout=57600 & [1] 3144 [root@sulinux bin]#090429 00:06:07 mysqld_safe Logging to '/usr/local/mysql/data/sulinux.net.err'. 090429 00:06:07 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data   [root@sulinux bin]#

 

위의 예는 max_connections값과 table_cache값, 그리고 wait_timeout값을 앞의 경우보다 더 높게 설정하여 MYSQL을 시작한 것이다. 주의 할 것은 위의 변수들 가운데 wait_timeout값을 높이는 것만이 능사가 아니라는 점이다. 즉 경우에 따라서는 이 변수값을 낮추는 것이 효율적인 경우도 있다.

 

이와같이 실행시킨 후에 다시 "./mysqladmin -u root -p variables"로 새로 설정된 변수값들이  변경되어 있음을 직접 확인해보기 바란다.

 

그리고 이와 같은 방법으로 “too many connections”에러가 해결되었다면 재부팅시에 MYSQL을 시작하면서 매번 자동 적용을 위하여 /etc/rc.d/rc.local파일에 아래와 같이 MYSQL을 시작할 수 있도록 설정해두는 것도 좋은 방법이 된다.

 

/usr/local/mysql/bin/mysqld_safe -O max_connections=1000 -O table_open_cache=512 -O wait_timeout=57600 &