대형 프로젝트의 데이터베이스는 시간이 지나면서 엄청난 속도로 성장합니다. 이 글에서는 MySQL/MariaDB 환경에서 테이블과 데이터베이스를 압축하고 조각 모음(디프래그먼테이션)하여 서버 디스크 공간을 절약하는 실전 방법들을 소개합니다.
데이터베이스 용량 문제 해결 전략
데이터베이스가 계속 커질 때 선택할 수 있는 대표적인 해결 방법은 다음과 같습니다.
- 오래된 정보를 삭제하여 데이터 자체의 양 줄이기
- 하나의 큰 데이터베이스를 여러 개의 작은 DB로 분할하기
- 서버 디스크 용량 확장하기
- 테이블 압축 및 축소하기
또한 간과하기 쉬운 부분이 성능 유지입니다. 테이블과 데이터베이스를 주기적으로 조각 모음하면 저장 공간뿐 아니라 쿼리 성능도 함께 개선됩니다.
InnoDB 테이블 압축 & 최적화
ibdata1과 ib_log 파일 관리
InnoDB 테이블을 사용하는 대부분의 프로젝트에서는 ibdata1, ib_log 파일이 비정상적으로 커지는 문제를 겪습니다. 대체로 잘못된 MySQL/MariaDB 설정이나 DB 설계가 원인입니다. InnoDB 테이블의 모든 정보는 ibdata1 파일에 저장되며, 이 공간은 자동으로 회수되지 않아 파일이 계속 늘어납니다.
테이블 데이터를 별도의 ibd* 파일에 분산 저장하는 것을 권장합니다. my.cnf 설정 파일에 다음 한 줄을 추가하면 됩니다.
innodb_file_per_table
또는
innodb_file_per_table=1
이미 운영 중인 서버에 InnoDB 테이블이 있다면 아래 순서로 적용하세요.
- 서버의 모든 데이터베이스(mysql, performance_schema 제외)를 백업합니다. 덤프 명령:
# mysqldump -u [사용자명] –p[비밀번호] [데이터베이스명] > [덤프파일.sql] - 백업이 완료되면 mysql/mariadb 서비스를 중지합니다.
- my.cnf의 설정을 변경합니다.
- ibdata1과 ib_log 파일을 삭제합니다.
- mysql/mariadb 데몬을 시작합니다.
- 백업본으로 모든 데이터베이스를 복원합니다:
# mysql -u [사용자명] –p[비밀번호] [데이터베이스명] < [덤프파일.sql]
이 과정을 마치면 모든 InnoDB 테이블이 개별 파일로 저장되고, ibdata1 파일은 더 이상 기하급수적으로 커지지 않습니다.
InnoDB 테이블 압축
텍스트나 BLOB 데이터가 많은 테이블이라면 압축만으로 상당한 디스크 공간을 확보할 수 있습니다. 예시로 압축 가능한 테이블을 포함한 innodb_test 데이터베이스를 사용해 보겠습니다. 작업 전에는 반드시 전체 데이터베이스를 백업하세요.
MySQL 서버에 접속합니다:
# mysql -u root -p
콘솔에서 대상 데이터베이스를 선택합니다:
mysql> USE innodb_test;
다음 쿼리로 테이블 목록과 용량을 확인할 수 있습니다:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "innodb_test" ORDER BY (data_length + index_length) DESC;
여기서 innodb_test는 실제 사용 중인 데이터베이스 이름으로 바꾸면 됩니다.
압축 효과를 볼 수 있는 테이블을 골라 진행합니다. 예를 들어 b_crm_event_relations 테이블을 압축하려면 아래 쿼리를 실행합니다:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
실행 후 테이블 크기가 26MB에서 11MB로 줄어든 것을 확인할 수 있습니다.
이처럼 테이블 압축으로 호스트의 디스크 공간을 크게 아낄 수 있습니다. 다만 압축 테이블을 사용하면 CPU 부하가 증가한다는 점을 유의해야 합니다. 디스크 공간은 부족하지만 CPU 여유가 충분하다면 테이블 압축을 적극 활용하는 것이 좋습니다.
MyISAM 테이블 압축
MyISAM 테이블은 mysql 콘솔이 아니라 서버 콘솔에서 전용 유틸리티를 사용해 압축합니다. 테이블을 압축하려면 다음 명령을 실행합니다:
# myisampack -b /var/lib/mysql/test/modx_session
/var/lib/mysql/test/modx_session은 압축할 테이블 파일 경로입니다. 예시에서는 큰 테이블이 없어 작은 테이블로 테스트했지만, 그래도 효과는 뚜렷하게 나타났습니다(25MB → 18MB).
# du -sh modx_session.MYD 25M modx_session.MYD # myisampack -b /var/lib/mysql/test/modx_session Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records) - Calculating statistics - Compressing file 29.84% Remember to run myisamchk -rq on compressed tables # du -sh modx_session.MYD 18M modx_session.MYD
명령에 사용한 -b 옵션은 압축 전 원본 테이블을 백업하고 .OLD 확장자로 표시하는 역할을 합니다.
# ls -la modx_session.OLD -rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD # du -sh modx_session.OLD 25M modx_session.OLD
테이블·데이터베이스 조각 모음(최적화)
테이블과 데이터베이스를 최적화하려면 조각 모음을 수행하는 것이 좋습니다. 먼저 어떤 테이블에 조각 모음이 필요한지 확인해 보겠습니다.
MySQL 콘솔에서 데이터베이스를 선택한 뒤 아래 쿼리를 실행합니다:
SELECT table_name, ROUND(data_length/1024/1024) AS data_length_mb, ROUND(data_free/1024/1024) AS data_free_mb FROM information_schema.tables WHERE ROUND(data_free/1024/1024) > 50 ORDER BY data_free_mb;
이 쿼리는 미사용 공간이 50MB 이상인 테이블을 모두 보여줍니다:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 402 | 64 | | b_crm_timeline_bind | 827 | 150 | | b_disk_object_path | 980 | 72 |
data_length_mb — 테이블 전체 크기
data_free_mb — 테이블 내 미사용 공간
위 테이블들이 조각 모음 대상입니다. 실제 디스크 점유 상태도 확인해 봅니다:
# ls -lh /var/lib/mysql/innodb_test/ | grep b_ -rw-r----- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD -rw-r----- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD -rw-r----- 1 mysql mysql 981M Oct 17 11:54 b_disk_object_path.MYD
mysql 콘솔에서 아래 명령으로 해당 테이블들을 최적화합니다:
mysql> OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
조각 모음이 완료되면 다음과 같은 결과를 확인할 수 있습니다:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 74 | 0 | | b_crm_timeline_bind | 115 | 0 | | b_disk_object_path | 201 | 0 |
data_free_mb가 0이 되었고, 테이블 크기도 3~4배 가까이 줄어든 것을 볼 수 있습니다.
서버 콘솔에서 mysqlcheck 유틸리티로도 조각 모음을 수행할 수 있습니다:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
innodb_test는 데이터베이스명, b_workflow_file은 테이블명입니다.
데이터베이스의 모든 테이블을 최적화하려면 서버 콘솔에서 다음 명령을 실행합니다:
# mysqlcheck -o innodb_test -u root -p
서버의 전체 데이터베이스를 한 번에 최적화할 수도 있습니다:
# mysqlcheck -o --all-databases -u root -p
최적화 전후로 데이터베이스 용량을 비교하면 전체 크기가 눈에 띄게 줄어든 것을 확인할 수 있습니다:
# du -sh 2.5G # mysqlcheck -o innodb_test -u root -p innodb_test.b_admin_notify note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_admin_notify_lang note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_adv_banner note : Table does not support optimize, doing recreate + analyze instead status : OK # du -sh 1.7G
InnoDB 테이블은 직접적인 optimize를 지원하지 않기 때문에 "recreate + analyze" 방식으로 재구성되어 처리된다는 점도 참고하세요.
이처럼 MySQL/MariaDB 테이블과 데이터베이스를 주기적으로 압축하고 최적화하면 서버 디스크 공간을 크게 절약할 수 있습니다. 어떤 최적화 작업이든 반드시 사전에 데이터베이스를 백업하는 것을 잊지 마세요.