잘 동작하던 메일 스팸서버의 DB가 갑자기 데이터가 쌓이지 않기 시작 했다..
원인은 알 수 없었고.. 이리 저리 뒤져보았더니 MySQL의 경우 Linux 시스템에 따라 MyISAM 엔진이 저장할 수 있는 최대 데테이블 데이터 사이즈가 지정되어 있다고 한다.
| Operating System | File-size Limit |
| Win32 w/ FAT/FAT32 | 2GB/4GB |
| Win32 w/ NTFS | 2TB (possibly larger) |
| Linux 2.2-Intel 32-bit | 2GB (LFS: 4GB) |
| Linux 2.4+ | (using ext3 file system) 4TB |
| Solaris 9/10 | 16TB |
| MacOS X w/ HFS+ | 2TB |
| NetWare w/NSS file system | 8TB |
- 관련 문서 : http://dev.mysql.com/doc/refman/5.0/en/full-table.html
우선 서버에서 해당 테이블의 상태를 확인하기 위해 아래와 같이 명령어를 실행..
mysql> show table status like 'mail_content' \G
*************************** 1. row ***************************
Name: mail_content
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 548249
Avg_row_length: 7833
Data_length: 4294967280
Max_data_length: 4294967295
Index_length: 40935424
Data_free: 0
Auto_increment: NULL
Create_time: 2008-04-24 11:31:01
Update_time: 2010-07-27 11:53:13
Check_time: 2010-05-12 09:50:03
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
*************************** 1. row ***************************
Name: mail_content
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 548249
Avg_row_length: 7833
Data_length: 4294967280
Max_data_length: 4294967295
Index_length: 40935424
Data_free: 0
Auto_increment: NULL
Create_time: 2008-04-24 11:31:01
Update_time: 2010-07-27 11:53:13
Check_time: 2010-05-12 09:50:03
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
상태를 보니 이미 MAX_data_Length값인 4G까지 모두 사용중이네요..
20G 까지 테이블 사이즈를 늘리기 위해 다음 명령어를 실행...하였습니다.
alter table mail_content MAX_ROWS=1000000 AVG_ROW_LENGTH=21474;
위의 내용을 보면 최대 행(Row)는 1,000,000개 까지 저장 가능하도록 하였고, 각 행의 평균 길이는 21474 Byte로 지정 하였습니다.
이 명령어는 다음 내용을 참조하여 생성 하였습니다.
MySQL의 MyISAM 스토리지 엔진의 데이터 테이블의 전체 크기는 [SHOW variables]명령을 통해 확인 할 수 있는 myisam_data_pointer_size 라는 값에 의하여 결정 된다고 합니다.
이 myisam_data_pointer_size는 최소 2부터 최대 7까지 값을 가지고 있으며, 이 값을 참조하여 다음 공식에 의해 테이블 사이즈의 최대 크기를 지정 할 수 있습니다.
[ 2 ^ ( myisam_data_pointer_size * 8 ) - 1 byte ]
위의 공식을 따라 기본값인 4를 대입하면 4Gbyte까지 생성 할 수 있음을 알 수 있습니다.
문제는 이 값을 임의로 수정하는 것이 아닌 Alter Table 또는 Create Table 명령을 사용할 때 MAX_ROWS 와 AVG_ROW_LENGTH 값을 변경 하면 myisam_data_pointer_size 값이 자동으로 변경 된다는 것 입니다.
그럼 제가 원하는 20G 사이즈의 테이블을 생성하기 위해서는 어떻게 해야 할까요?
다음의 3가지 방식의 명령 모두 20G 사이즈의 테이블로 수정하는 명령입니다.
명령 1 : ALTER TABLE mail_content MAX_ROWS=1 AVG_ROW_LENGTH=21474836480
명령 2 : ALTER TABLE mail_content MAX_ROWS=20971520 AVG_ROW_LENGTH=1024
명령 3 : ALTER TABLE mail_content MAX_ROWS=1000000 AVG_ROW_LENGTH=21474
위의 3가지 방식의 명령 모두 20G 사이즈의 테이블을 생성하는 명령어 입니다.
자신이 원하는 방식을 이용하면 되겠지만, 테이블의 내용에 따라서 이 명령어를 다르게 적용하여 주는 것이 더 좋습니다.
예를 들어 테이블의 데이터 타입이 BLOB 또는 TEXT 일 경우에는 MAX_ROWS를 일정 수치이하로 줄이고 AVG_ROW_LENGTH 값을 늘려주는 것이 좋습니다.
다른 예로 테이블의 데이터 타입이 Integer 또는 Boolean과 같은 데이터 사이즈가 작은 경우라면 MAX_ROWS값을 늘리고 AVG_ROW_LENGTH값을 줄이는 것이 더 효과적입니다.
이 myisam_data_pointer_size는 최소 2부터 최대 7까지 값을 가지고 있으며, 이 값을 참조하여 다음 공식에 의해 테이블 사이즈의 최대 크기를 지정 할 수 있습니다.
[ 2 ^ ( myisam_data_pointer_size * 8 ) - 1 byte ]
위의 공식을 따라 기본값인 4를 대입하면 4Gbyte까지 생성 할 수 있음을 알 수 있습니다.
문제는 이 값을 임의로 수정하는 것이 아닌 Alter Table 또는 Create Table 명령을 사용할 때 MAX_ROWS 와 AVG_ROW_LENGTH 값을 변경 하면 myisam_data_pointer_size 값이 자동으로 변경 된다는 것 입니다.
그럼 제가 원하는 20G 사이즈의 테이블을 생성하기 위해서는 어떻게 해야 할까요?
다음의 3가지 방식의 명령 모두 20G 사이즈의 테이블로 수정하는 명령입니다.
명령 1 : ALTER TABLE mail_content MAX_ROWS=1 AVG_ROW_LENGTH=21474836480
명령 2 : ALTER TABLE mail_content MAX_ROWS=20971520 AVG_ROW_LENGTH=1024
명령 3 : ALTER TABLE mail_content MAX_ROWS=1000000 AVG_ROW_LENGTH=21474
위의 3가지 방식의 명령 모두 20G 사이즈의 테이블을 생성하는 명령어 입니다.
자신이 원하는 방식을 이용하면 되겠지만, 테이블의 내용에 따라서 이 명령어를 다르게 적용하여 주는 것이 더 좋습니다.
예를 들어 테이블의 데이터 타입이 BLOB 또는 TEXT 일 경우에는 MAX_ROWS를 일정 수치이하로 줄이고 AVG_ROW_LENGTH 값을 늘려주는 것이 좋습니다.
다른 예로 테이블의 데이터 타입이 Integer 또는 Boolean과 같은 데이터 사이즈가 작은 경우라면 MAX_ROWS값을 늘리고 AVG_ROW_LENGTH값을 줄이는 것이 더 효과적입니다.
결론을 내리자면, 테이블의 사이즈를 변경하는 것은 ALTER TABLE 또는 CREATE TABLE 명령을 이용하여 MAX_ROWS값과 AVG_ROW_LENGTH값을 주게되면 myisam_data_pointer_size 값이 변경되면서 자연적으로 적용되게 됩니다.
만약 ALTER TABLE을 사용하는 경우라면 테이블 사이즈에 따라 또는 테이블 내 행의 개수에 따라 작업 시간을 조율 하시기 바랍니다.
저같은 경우 4G의 데이터 파일을 ALTER TABLE로 수정하는데 약 12분 가량 소요되었고, 그 시간동안에는 해당 테이블에 Lock이 걸리게 되므로, 약간의 장애 유사한 현상이 발생했었습니다. ^^*
참고 하세요~ =ㅁ=
'Linux > MySQL' 카테고리의 다른 글
| MySQL Check Table & Repair Table 자동 스크립트 (0) | 2010/10/11 |
|---|---|
| MySQL 테이블 사이즈 늘리기 (0) | 2010/07/30 |
| Perl-DBD-MySQL 설치 후 libmysqlclient.so.xx를 찾지 못해 발생하는 에러.. (0) | 2009/03/23 |
| MySQL Repliacation 복구 (0) | 2008/12/26 |
| MySQL load data infile 문. (0) | 2006/12/14 |
| [Error]Client does not support authentication protocol requested by server (0) | 2006/12/05 |
