mysql에서 ibdata의 크기를 줄이는 가장 좋은 방법은 무엇입니까? 290GB의 공간을 소비했습니다. 서버의 테이블은

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;


답변