MariaDB/MySQL 데이터베이스 압축, 조각 모음 및 최적화

이 기사에서는 데이터베이스가 있는 디스크의 공간을 절약하는 데 도움이 되는 MySQL/MariaDB의 테이블/데이터베이스 압축 및 조각 모음 방법을 살펴봅니다.

대규모 프로젝트의 데이터베이스는 시간이 지남에 따라 엄청나게 증가하며 이를 어떻게 처리할 것인지에 대한 질문이 항상 제기됩니다. 문제를 해결하는 방법에는 여러 가지가 있습니다. 오래된 정보를 삭제하거나 데이터베이스를 작게 분할하거나 서버의 디스크 크기를 늘리거나 테이블을 압축/축소하여 데이터베이스의 데이터 양을 줄일 수 있습니다.

데이터베이스 기능의 또 다른 중요한 측면은 성능을 향상시키기 위해 때때로 테이블과 데이터베이스를 조각 모음해야 한다는 것입니다.

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 테이블이 있는 생산적인 데이터베이스가 있는 경우 다음을 수행하세요.

1. 서버의 모든 데이터베이스를 백업합니다(mysql 및 performance_schema 제외). 다음 명령을 사용하여 데이터베이스 덤프를 가져올 수 있습니다. # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. 데이터베이스 백업을 생성한 후 mysql/mariadb 서버를 중지합니다.
3. my.cfg에서 설정 변경
4. ibdata1 삭제 및 ib_log 파일;
5. mysql/mariadb 데몬 시작
6. 백업에서 모든 데이터베이스 복원:# mysql -u [username] –p[password] [database_name] < [dump_file.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 리소스에는 문제가 없지만 디스크 공간 문제가 있는 경우 db 테이블에 압축을 사용합니다.

MyISAM/MariDB의 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 및 MariaDB에서 테이블 및 데이터베이스 최적화

테이블과 데이터베이스를 최적화하려면 조각 모음을 수행하는 것이 좋습니다. 데이터베이스에 조각 모음이 필요한 테이블이 있는지 확인하십시오.

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 콘솔에서 다음 명령을 실행하십시오.

# 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

여기서 innodb_test는 데이터베이스 이름입니다.

또는 서버의 모든 데이터베이스 최적화 실행:

# 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

따라서 서버의 공간을 절약하기 위해 때때로 MySQL/MariDB 테이블과 데이터베이스를 최적화하고 압축할 수 있습니다. 최적화 작업을 수행하기 전에 데이터베이스를 백업하는 것을 잊지 마십시오.