Reminiscence

잘 동작하던 메일 스팸서버의 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)

상태를 보니 이미 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값을 줄이는 것이 더 효과적입니다.


결론을 내리자면, 테이블의 사이즈를 변경하는 것은 ALTER TABLE 또는 CREATE TABLE 명령을 이용하여 MAX_ROWS값과 AVG_ROW_LENGTH값을 주게되면 myisam_data_pointer_size 값이 변경되면서 자연적으로 적용되게 됩니다.

만약 ALTER TABLE을 사용하는 경우라면 테이블 사이즈에 따라 또는 테이블 내 행의 개수에 따라 작업 시간을 조율 하시기 바랍니다.

저같은 경우 4G의 데이터 파일을 ALTER TABLE로 수정하는데 약 12분 가량 소요되었고, 그 시간동안에는 해당 테이블에 Lock이 걸리게 되므로, 약간의 장애 유사한 현상이 발생했었습니다. ^^*

참고 하세요~ =ㅁ=

신고

댓글 0개가 달렸습니다.