ibdata
파일 크기가 매일 증가 하는 일부 프로덕션 서버가 있습니다.
이미 290GB의 공간을 소비했습니다.
서버의 테이블은 대부분 InnoDB이며 높은 읽기 및 쓰기 요청이 있습니다.
로그 파일 크기도 증가합니다. 테이블에는 엄청난 양의 데이터가 있습니다.
두 크기의 증가하는 크기를 어떻게 제어 할 수 있습니까?
사용하고 있지 않습니다 innodb_file_per_table
.
답변
InnoDB 인프라에서 가장 바쁜 파일은 / var / lib / mysql / ibdata1입니다.
이 파일은 일반적으로 많은 클래스의 정보를 저장합니다 ( innodb_file_per_table 이 0 인 경우).
- 테이블 데이터
- 테이블 인덱스
- MVCC (Multiversioning Concurrency Control) 데이터
- 롤백 세그먼트
- 테이블 스페이스 실행 취소
- 테이블 메타 데이터
- 그림 표현 참조
많은 사람들이 디스크 공간 관리 및 성능 향상을 위해 여러 ibdata 파일을 만듭니다. 도움이되지 않습니다.
불행히도 ibdata1에 저장된 InnoDB 테이블에 대한 OPTIMIZE TABLE은 두 가지 작업을 수행합니다.
- ibdata1 내에서 테이블의 데이터와 인덱스를 연속적으로 만듭니다.
- 인접한 데이터가 ibdata1에 추가되므로 ibdata1이 커집니다.
ibdata1에서 테이블 데이터와 테이블 인덱스를 분리하고 innodb_file_per_table을 사용하여 독립적으로 관리 할 수 있습니다 . ibdata1을 한 번만 축소하려면 다음을 수행해야합니다.
1 단계) MySQL 모든 데이터베이스를 SQL 텍스트 파일로 덤프 (SQLData.sql이라고 함) ( 자세한 내용은 여기 참조 )
단계 02) 를 제외하고 (모든 데이터베이스를 드롭 mysql
, performance_schema
그리고 information_schema
)
단계 03) MySQL 종료
단계 04) /etc/my.cnf에 다음 줄을 추가하십시오
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
참고 : innodb_buffer_pool_size에 대한 설정이 무엇이든, innodb_log_file_size가 innodb_buffer_pool_size의 25 %인지 확인하십시오.
단계 05) ibdata1, ib_logfile0 및 ib_logfile1 삭제
이 시점에서 / var / lib / mysql에는 mysql 스키마 만 있어야합니다.
단계 06) mysql 재시작
이렇게하면 10MB에서 ibdata1, 1G에서 ib_logfile0 및 ib_logfile1이 각각 다시 생성됩니다.
07 단계) SQLData.sql을 mysql로 다시로드
ibdata1은 커지지 만 테이블 메타 데이터 만 포함
각 InnoDB 테이블은 ibdata1 외부에 존재합니다.
mydb.mytable이라는 InnoDB 테이블이 있다고 가정하십시오. / var / lib / mysql / mydb에 들어가면 테이블을 나타내는 두 개의 파일이 나타납니다.
- mytable.frm (스토리지 엔진 헤더)
- mytable.ibd (mydb.mytable의 테이블 데이터 및 테이블 인덱스 홈)
ibdata1은 더 이상 InnoDB 데이터 및 인덱스를 포함하지 않습니다.
/etc/my.cnf의 innodb_file_per_table 옵션을 사용하면 실행할 수 OPTIMIZE TABLE mydb.mytable
있으며 파일 /var/lib/mysql/mydb/mytable.ibd
이 실제로 줄어 듭니다.
저는 MySQL DBA로 경력에 여러 번이 일을했습니다
사실, 처음이 작업을 수행 할 때 50GB ibdata1 파일을 500MB로 축소했습니다.
시도 해봐. 이에 대한 추가 질문이 있으면 저에게 이메일을 보내주십시오. 날 믿어. 이것은 단기적으로 그리고 장기적으로 작동합니다. !!!
MyISAM 및 InnoDB에 얼마나 많은 실제 데이터가 저장되어 있는지 보려면 다음 쿼리를 실행하십시오.
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;