다음은 InnoDB와 관련하여 더 완벽한 답변입니다. 시간이 오래 걸리지 만 그만한 가치가 있습니다.

점을 명심 /var/lib/mysql/ibdata1InnoDB의 인프라에서 가장 붐비는 파일입니다. 일반적으로 6 가지 유형의 정보가 있습니다.

• 테이블 데이터
• 테이블 인덱스
• MVCC (Multiversioning Concurrency Control) 데이터
  • 롤백 세그먼트
  • 우주 취소
• 테이블 메타 데이터 (데이터 사전)
• 이중 쓰기 버퍼 (OS 캐싱에 의존하지 않도록 백그라운드 쓰기)
• 버퍼 삽입 (고유하지 않은 보조 인덱스에 대한 변경 관리)
• 참조 Pictorial Representation of ibdata1

InnoDB 아키텍처

많은 사람들 ibdata이 더 나은 디스크 공간 관리 및 성능을 기대하면서 여러 파일을 만들지 만 이러한 생각은 잘못되었습니다.

실행할 수 있습니까 OPTIMIZE TABLE?

불행하게도, OPTIMIZE TABLE공유 테이블 스페이스 파일에 저장된 InnoDB 테이블에 대해 실행 하면 ibdata1다음 두 가지가 수행됩니다.

• 테이블의 데이터와 인덱스를 연속적으로 만듭니다. ibdata1
• 차종은 ibdata1연속 데이터와 인덱스 페이지가 있기 때문에 성장을 추가 로ibdata1

그러나 테이블 데이터와 테이블 인덱스를 분리 ibdata1하여 독립적으로 관리 할 수 ​​있습니다.

나는 OPTIMIZE TABLE함께 실행할 수 있습니까 innodb_file_per_table?

추가이었다 가정 innodb_file_per_table에 /etc/my.cnf (my.ini). 그런 다음 OPTIMIZE TABLE모든 InnoDB 테이블에서 실행할 수 있습니까 ?

좋은 소식 : 당신이 실행하는 경우 OPTIMIZE TABLE에 innodb_file_per_table사용할 수, 이것은 생산합니다 .ibd해당 테이블에 대한 파일을. 예를 들어, mydb.mytabledatadir 이있는 테이블이 있으면 /var/lib/mysql다음을 생성합니다.

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

여기 .ibd에는 해당 테이블의 데이터 페이지 및 인덱스 페이지가 포함됩니다. 큰.

나쁜 소식 : 당신이 수행 한 모든의 데이터 페이지 및 인덱스 페이지 추출입니다 mydb.mytable생활에서 ibdata. 를 포함하여 모든 테이블의 데이터 사전 항목은 mydb.mytable여전히 데이터 사전에 남아 있습니다 ( ibdata1 의 그림 표현 참조 ). 이 시점에서 간단히 삭제할 수는 없습니다 ibdata1!!! 참고 ibdata1전혀 축소되지 않았습니다.

InnoDB 인프라 정리

ibdata1한 번만 축소하려면 다음을 수행해야합니다.

1. mysqldump모든 데이터베이스를 .sql텍스트 파일 로 덤프 (예 :로 ) ( SQLData.sql아래 사용)

2. (를 제외하고 모든 데이터베이스를 삭제 mysql하고 information_schema) 주의 : 예방 조치로서, 절대적으로 당신이 곳에서 모든 사용자의 보조금을 가지고 있는지 확인하기 위해이 스크립트를 실행하십시오 :

   mkdir /var/lib/mysql_grants
   cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
   chown -R mysql:mysql /var/lib/mysql_grants

3. MySQL과 실행에 로그인 SET GLOBAL innodb_fast_shutdown = 0;(이 완전히 남아있는 모든 트랜잭션 변경을 플래시 해 ib_logfile0와 ib_logfile1)

4. MySQL 종료

5. 다음 줄 추가 /etc/my.cnf(또는 my.iniWindows에서)를

   [mysqld]
   innodb_file_per_table
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G

   (Sidenote :에 대한 설정 이 25 % innodb_buffer_pool_size인지 확인하십시오 .innodb_log_file_sizeinnodb_buffer_pool_size

   또한 : innodb_flush_method=O_DIRECTWindows에서는 사용할 수 없습니다)

6. 삭제 ibdata*및 ib_logfile*선택적으로에서 /var/lib/mysql를 제외한 모든 폴더를 제거 할 수 있습니다 /var/lib/mysql/mysql.

7. (이것은 다시합니다 시작 MySQL은 ibdata1[기본적으로 10MB의]과 ib_logfile0및 ib_logfile11G 각).

8. 수입 SQLData.sql

이제 ibdata1각 InnoDB 테이블이 외부에 존재하기 때문에 여전히 커지지 만 테이블 메타 데이터 만 포함합니다 ibdata1. ibdata1더 이상 다른 테이블에 대한 InnoDB 데이터 및 인덱스를 포함하지 않습니다.

예를 들어, 이름이 InnoDB 테이블 인 것으로 가정하십시오 mydb.mytable. 를 보면 /var/lib/mysql/mydb테이블을 나타내는 두 개의 파일이 표시됩니다.

• mytable.frm (저장소 엔진 헤더)
• mytable.ibd (테이블 데이터 및 인덱스)

의 innodb_file_per_table옵션을 사용하면 /etc/my.cnf실행할 수 OPTIMIZE TABLE mydb.mytable있으며 파일 /var/lib/mysql/mydb/mytable.ibd이 실제로 줄어 듭니다.

필자는 MySQL DBA로 경력에 여러 번이 일을 해왔다. 사실, 처음이 작업을 수행 할 때 50GB ibdata1 파일을 500MB로 줄였습니다.

시도 해봐. 이에 대한 추가 질문이 있으면 물어보십시오. 날 믿어; 이것은 장거리뿐만 아니라 단기적으로도 효과가 있습니다.

경고

6 단계에서 mysql스키마가 삭제되어 mysql을 다시 시작할 수없는 경우 2 단계를 다시 살펴보십시오 mysql. 스키마 의 실제 사본을 작성했습니다 . 다음과 같이 복원 할 수 있습니다.

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

6 단계로 돌아가서 계속

업데이트 2013-06-04 11:13 EDT

5 단계에서 innodb_log_file_size 를 innodb_buffer_pool_size의 25 % 로 설정하는 것과 관련하여 담요 규칙은 다소 오래된 학교입니다.

다시 July 03, 2006, Percona는 적절한 innodb_log_file_size를 선택 해야하는 좋은 기사를 가지고있었습니다 . 나중에 Nov 21, 2008Percona는 한 시간 분량의 변화를 유지하면서 최대 워크로드를 기반으로 적절한 크기를 계산하는 방법 에 대한 또 다른 기사를 발표했습니다 .

이후 DBA StackExchange에 로그 크기 계산에 관한 게시물을 작성했으며이 두 Percona 기사를 참조했습니다.

• Aug 27, 2012: 48GB RAM이있는 서버에서 30GB InnoDB 테이블에 대한 적절한 조정
• Jan 17, 2013: MySQL 5.5-Innodb-innodb_log_file_size가 4GB보다 높습니까?

개인적으로, 나는 여전히 초기 설정을 위해 25 % 규칙을 따릅니다. 그런 다음 프로덕션 환경에서 시간이 지남에 따라 워크로드를보다 정확하게 결정할 수 있으므로 유지 관리주기 동안 몇 분만에 로그 크기를 조정할 수 있습니다.

---

답변