[sql] MySQL 서버 쿼리문이 메모리 부족으로 실행되지 않을 때 해결 방법
MySQL과 같은 데이터베이스 서버는 다른 프로그램들보다 메모리를 더 많이 사용합니다. 그리고 쿼리문에 따라서는 아주 큰 결과 데이터를 생성하기도 하기 때문에 아주 큰 메모리 영역을 필요로 할 수도 있습니다.
그래서 MySQL 서버가 한번에 처리할 수 있는 최대 데이터의 크기에 제한을 두고 있습니다.
그리고 설정한 제한 크기보다 결과 데이터가 더 더 크면 다음과 같은 에러를 발생시키고 쿼리를 실행을 중단합니다.
[ERROR] Error reading packet from server: Got packet bigger than 'max_allowed_packet' bytes ( server_errno=2020)MySQL은 환경 설정 정보에 서버의 쿼리 결과를 저장하는 최대 크기 값을 저장합니다. 이 값을 수정해서 MySQL이 쿼리 결과를 저장하는데 사용하는 최대 메모리 공간을 늘림으로써 메모리 부족이 발생하지 않도록 할 수 있습니다.
MySQL은 이 값을 "max_allowed_packet" 값으로 선언합니다. 그리고 "max_allowed_packet"은 기본 설정된 값이 서버와 클라이언트 프로그램이 서로 다릅니다. MySQL 클라이언트는 기본 값이 16Mb여서 비교적 충분한 크기지만, MySQL 서버는 기본 값이 1Mb입니다.
이 차이로 인해 초기 설정 값으로 MySQL 서버를 사용하면 서버에 큰 결과 데이터를 생성하는 쿼리를 요청했을 때 에러가 발생하게 됩니다.
max_allowed_packet 설정 값 수정
MySQL 환경 설정 파일은 "my.cnf"이며 "/etc/", "/etc/mysql/" 경로 안에 있습니다.
- vi 텍스트 편집기를 사용해 my.cnf 파일을 엽니다.
- 다음과 같이 my.cnf 파일에 옵션을 추가하거나 수정합니다. 끝 부분의 M은 Megabyte 단위 표시입니다. 기본 값은 1M로 되어 있습니다. 다음 설정은 최대 허용 패킷 크기를 64M로 설정하는 것입니다. 설정 가능한 최대 값은 1G(1024M) 입니다.
[mysqld]
max_allowed_packet=64M
3. 수정한 my.cnf 파일을 저장합니다.
4. MySQL 서버를 다시 시작합니다.
- CentOS: service mysqld restart
- Ubuntu: service mysql restart
MySQL 서비스를 실행할 때 파라미터로 설정 값 지정
max_allowed_packet 값은 MySQL 서버를 실행할 때 실행 파라미터 옵션으로 값을 지정할 수 있습니다. 임시로 큰 데이터 쿼리를 사용할 필요가 있을 때 환경 설정 파일을 수정하는 번거로움 없이 MySQL 서버를 재 실행 하는 것만으로 설정 값을 조절할 수 있습니다.
mysqld --max_allowed_packet=64Mmax_allowed_packet 주의 사항
이 옵션을 늘리면 더 큰 쿼리나 레코드를 전송할 수 있지만, 너무 큰 값을 설정하면 서버 및 클라이언트의 리소스를 과도하게 사용할 수 있습니다. 시스템에서 사용 가능한 범위 안에서 값을 설정해야 합니다.
일반적으로 max_allowed_packet은 다음과 같은 상황일 때 크기를 늘립니다:
- 큰 쿼리 전송: 큰 데이터나 쿼리를 전송해야 하는 경우.
- BLOB 또는 TEXT 컬럼 사용: BLOB(Binary Large Object) 또는 TEXT 컬럼에 큰 데이터를 저장하려는 경우.