MySQL max connections 설정

MySQL Too many connections 오류 조치

MySQL max_connections 설정

1 확인

[root@zetadb ~]# mysql -uroot -p
... (생략)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 152   |
+----------------------+-------+
1 row in set (0.00 sec)

→ max_connections 설정이 151인데 152개의 커넥션 발생

→ 초과분에 대해서는 Too many connections 오류 발생

mysql> show status like 'Aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 4     |
| Aborted_connects | 1     |
+------------------+-------+
2 rows in set (0.00 sec)

→ 클라이언트 차단 4건, 커넥션 차단 1건 발생했음

2 온라인 설정

• 즉시 적용됨

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

3 확인 2

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

4 영구 설정

• my.cnf에 max_connections 설정 추가
• mysqld 재시작시에 적용됨

[root@zetadb ~]# vi /etc/my.cnf

[mysqld]
max_connections = 500

5 확인 3

• 가능하면 MySQL 재시작하여 다시 확인

[root@zetadb ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@zetadb ~]# mysql -uroot -p
... (생략)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

6 같이 보기

• MySQL 환경변수
• my.cnf

7 참고

• http://threestory.tistory.com/6