MySQL InnoDB가 테이블에서 데이터 행을 삭제한 후 디스크 공간을 해제하지 않음

MySQL InnoDB가 테이블에서 데이터 행을 삭제한 후 디스크 공간을 해제하지 않음

InnoDB 스토리지 엔진을 사용하는 MySQL 테이블이 하나 있는데, 약 200만 개의 데이터 행이 포함되어 있습니다.테이블에서 데이터 행을 삭제해도 할당된 디스크 공간이 해제되지 않았습니다., 는, ibdata1 의 파일을 실행해도 입니다.optimize table명령어를 입력합니다.

MySQL에서 디스크 공간을 회수할 수 있는 방법이 있습니까?

이 어플리케이션은 약 50개의 다른 장소에서 실행되고 있으며, 현재는 거의 모든 장소에서 디스크 용량 부족 문제가 발생하고 있습니다.

MySQL ibdata1 입니다. 네, 네, 네, 네, 네, 네, 네, 네, 네, 네, 네, 네, 네. 만약 당신이optimize table삭제된 레코드로부터 사용된 공간을 해방하기 위해 나중에 재사용합니다.

다른 방법으로는 를 사용하도록 서버를 설정하는 방법이 있는데, 이를 위해서는 백업, 데이터베이스 폐기 및 복원이 필요합니다.긍정적인 측면은 테이블의 .ibd 파일이 나중에 축소된다는 것입니다.

나도 똑같은 문제가 있었어

즉, 데이터베이스를 삭제해도 innodb는 디스크 공간을 개방하지 않습니다.내보내기, mysql 중지, 파일 수동 삭제, mysql 시작, 데이터베이스 및 사용자 생성 및 Import가 필요했습니다.200MB의 행밖에 없어서 다행이지만 250GB의 innodb 파일이 필요 없었습니다.

고의로 실패하다.

innodb_file_per_table을 사용하지 않을 경우 디스크 공간을 회수할 수 있지만 시간이 많이 걸리고 상당한 다운타임이 필요합니다.

사용법은 매우 상세하지만, 아래에 관련 부분을 붙여 넣었습니다.

또한 스키마의 복사본도 덤프에 보관하십시오.

현재 시스템 테이블스페이스에서 데이터 파일을 삭제할 수 없습니다.시스템 테이블스페이스 크기를 줄이려면 다음 절차를 수행합니다.

mysqldump를 사용하여 모든 InnoDB 테이블을 덤프합니다.

서버를 정지합니다.

ibdata 및 ib_log 파일을 포함하여 기존의 모든 테이블스페이스 파일을 삭제합니다.정보의 백업 복사본을 보관하려면 MySQL 설치에서 파일을 삭제하기 전에 모든 ib* 파일을 다른 위치에 복사하십시오.

InnoDB 테이블의 .frm 파일을 삭제합니다.

새로운 테이블스페이스를 설정합니다.

서버를 재기동합니다.

덤프 파일을 Import 합니다.

10년 후, 나도 같은 문제가 생겼다.다음과 같은 방법으로 해결했습니다.

• 남은 모든 데이터베이스를 최적화했습니다.
• 컴퓨터와 MySQL을 서비스(Windows+r --> services.msc)로 재기동했습니다.

이상입니다. :)

내가 찾은 가장 빠른 방법은:

ALTER TABLE YOURTABLE ENGINE=InnoDB

비반트 레코드를 잘라내거나 삭제한 후 이를 실행하면 테이블스페이스가 축소됩니다.

여기 Shlomi Noach가 쓴 멋진 기사가 있습니다.여기서 이것과 더 많은 정보를 찾았습니다.

Shlomi Noach의 크레딧.도움이 됐으면 좋겠다.여기에 붙일 경우를 대비해서:

InnoDB를 사용하는 경우 테이블스페이스 스토리지를 관리하는 두 가지 방법이 있습니다.

모든 것을 하나의 큰 파일에 넣습니다(옵션으로 분할).테이블당 1개의 파일이 있습니다.두 가지 옵션의 장점과 단점을 논의하여 innodb_file_per_table이 바람직하다는 것을 납득시키도록 노력하겠습니다.

단일 테이블스페이스

하나의 큰 파일에 모든 것을 포함한다는 것은 모든 스키마의 모든 테이블과 인덱스가 해당 파일에서 함께 '혼재'된다는 것을 의미합니다.

이를 통해 다음과 같은 우수한 속성을 사용할 수 있습니다. 즉, 다른 테이블과 다른 스킴 간에 빈 공간을 공유할 수 있습니다.따라서 로그 테이블에서 많은 행을 삭제하면 현재 사용되지 않은 공간은 다른 테이블의 새 행이 차지할 수 있습니다.

이와 같은 우수한 속성은 그다지 좋지 않은 속성으로도 해석됩니다. 즉, 데이터는 테이블스페이스 전체에 걸쳐 크게 분할될 수 있습니다.

InnoDB 테이블스페이스의 성가신 특징은 절대 줄어들지 않는다는 것입니다.따라서 로그 테이블에서 이러한 행을 삭제한 후에도 테이블 영역 파일(일반적으로 ibdata1)은 여전히 동일한 스토리지를 유지합니다.파일 시스템에 저장 공간을 해제하지 않습니다.

특정 테이블이 어떻게 패치되지 않은 채로 방치되어 디스크 용량이 90%에 달하고 SMS 알림이 울리기 시작하는지를 여러 번 보았습니다.

이 경우에는 할 일이 거의 없다.음, 한 명은 언제든지 열을 제거할 수 있어요.물론, 이 공간은 InnoDB에 의해 재사용될 것입니다.그러나 디스크 공간의 약 80~90%를 차지하는 파일은 성능 저하를 초래합니다.디스크 바늘이 먼 거리를 움직여야 한다는 뜻입니다.전체 Disk 성능이 매우 저하됩니다.

이 문제를 해결하는 가장 좋은 방법은 (행 삭제 후) 새로운 슬레이브를 설정하고 해당 슬레이브로 데이터를 덤프하는 것입니다.

InnoDB 핫백업

재미있는 것은 ibbackup 유틸리티가 테이블스페이스 파일을 그대로 복사한다는 것입니다.120GB(그 중 30GB만 사용)인 경우에도 120GB의 백업 및 restore가 가능합니다.

mysqldump, mk-sqdump

mysqldump는 원래 사용하던 기계만 있다면 최적의 선택입니다.InnoDB만 사용한다고 가정할 경우 단일 트랜잭션으로 덤프하면 됩니다.또는 mk-parallel-dump를 사용하여 작업 속도를 높일 수 있습니다(덤프 방법 및 접근성 요구에 따라 잠금에 유의하십시오).

innodb_file_per_table

이 파라미터를 설정하면 테이블별로 .ibd 파일이 생성됩니다.결과는 다음과 같습니다.

테이블스페이스는 다른 테이블 간에 공유되지 않으며 다른 스킴 간에 공유되지 않습니다.각 파일은 자체 테이블스페이스로 간주됩니다.다시 말하지만 테이블스페이스는 크기가 줄어들지 않습니다.테이블 영역당 공간을 다시 확보할 수 있습니다.잠깐, 마지막 두개는 서로 상충하는 것 같지 않아?설명합시다.

이 로그 테이블의 예에서는 많은 행을 삭제합니다(최대 90GB의 데이터가 삭제됩니다)..ibd 파일은 축소되지 않습니다.하지만 우리는 할 수 있다:

ALTER 테이블 로그 엔진=InnoDB

새로운 임시 파일이 생성되어 테이블이 재구축됩니다.기존 데이터만 새 테이블에 추가됩니다.완료되면 원래 테이블이 삭제되고 새 테이블 이름이 원래 테이블로 변경됩니다.

물론 이 작업에는 오랜 시간이 걸리고 테이블이 완전히 잠깁니다. 즉, 쓰기 및 읽기가 허용되지 않습니다.그러나 여전히 디스크 공간을 확보할 수 있습니다.

새로운 InnoDB 플러그인을 사용하면 TRUNCATE TABLE 로그 문을 실행할 때 디스크 공간도 다시 확보됩니다.

단편화는 단일 테이블스페이스만큼 나쁘지 않습니다.데이터는 작은 파일의 범위 내에서 제한됩니다.

감시

innodb_file_per_table의 다른 장점은 파일 시스템 수준에서 테이블 크기를 모니터링할 수 있다는 것입니다.MySQL에 액세스하거나 SHOW TABLE STATUS를 사용하거나 INFORMATION_SCHEMA를 쿼리할 필요가 없습니다. MySQL 데이터 디렉토리(및 서브 디렉토리)에서 상위 10개의 파일을 검색하여 크기를 감시할 수 있습니다.어떤 테이블이 가장 빨리 자라는지 알 수 있습니다.

지원하다

마지막으로 .ibd 파일을 복사하여 단일 InnoDB 테이블을 백업할 수 없습니다.하지만 이 방향으로 작업이 이루어지길 바랍니다.

오늘 (당초 질문이 있은 지 11년 후) 이 문제에 직면하여 테이블을 삭제하고 다시 작성함으로써 문제를 해결할 수 있었습니다.DB 재설치, 덤프 및 restore, 스토리지 변경, 테이블 맵 변경 등이 필요하지 않았습니다.

InnoDB를 사용하고 있지만 innodb_file_per_table이 아니기 때문에 테이블에서 90K 행을 삭제해도 DB 사이즈가 변경되지 않습니다.그래서 테이블을 떨어뜨리고 다시 만들었습니다.

내 경우 테이블이 0열로 청소되어 있기 때문에 테이블을 떨어뜨리기 쉬웠지만, 구조를 유지하기 위해 실행했다.

create table mynewtable as select * from myoldtable where 1=2;

이어서

drop table myoldtable;

이로 인해 DB 크기가 5G에서 400MB로 감소했습니다.

공간 재확보 문제를 해결하는 다른 방법은 테이블 내에 여러 개의 파티션을 만들고 범위 기반, 값 기반 파티션을 만들고 파티션을 드롭/트래닝하여 공간을 재확보하는 것입니다. 그러면 특정 파티션에 저장된 데이터 전체가 사용하는 공간이 해방됩니다.

테이블 분할을 도입할 때 테이블 스키마에 몇 가지 변경이 필요합니다.예를 들어, [Unique Keys], [Indexs to include partition column]등입니다.

몇 년 전 mysql5.7 버전에서도 같은 문제가 발생하여 ibdata1이 150 Gb를 차지했기 때문에 undo 테이블스페이스를 추가했습니다.

backup Mysqldump를 합니다.
service mysql 。
dir 데이터를 합니다.
my " my.cnf " my.cnf "에합니다.

 #undo tablespace
  innodb_undo_directory =  /var/lib/mysql/
  innodb_rollback_segments = 128 
  innodb_undo_tablespaces = 3
  innodb_undo_logs = 128  
  innodb_max_undo_log_size=1G
  innodb_undo_log_truncate = ON

service mysql 。
backup mysqldump " " "

문제가 해결되었습니다!!

OPTIMIZE를 사용해도 문제가 해결되지 않으면 다음을 시도해 보십시오.

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;

또는

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

저도 같은 문제가 있어요.는 든든 from i i from from from from에서 모든 데이터를 했다.logging_event그 . ★★★★★★★★★★★★★★★★★★,logging_event.ipd 15GB데이터를 저장합니다.
그래서 다음 단계로 해결했습니다.

1. 기존 테이블에서 새 테이블을 만듭니다.
   CREATE TABLE logging_event_new like logging_event;

2. 실제 테이블을 드롭합니다.
   DROP TABLE logging_event;

3. [ to ]를 선택합니다.
   RENAME TABLE logging_event_new to logging_event;

이치노
이제 저는 더 것을 가지고 있습니다.15GB사용 가능한 공간입니다.

MySQL Inodb 엔진의 테이블에서 데이터를 삭제한 후 디스크 공간을 회수하는 방법은 여러 가지가 있습니다.

처음부터 innodb_file_per_table을 사용하지 않는 경우 모든 데이터를 덤프하고 모든 파일을 삭제하며 데이터베이스를 다시 만들고 데이터를 다시 가져오는 것이 유일한 방법입니다(위의 FlipMcF 답변 확인).

innodb_file_per_table을 사용하는 경우,

1. 모든 데이터를 삭제할 수 있는 경우 truncate 명령은 데이터를 삭제하고 디스크 공간을 회수합니다.
2. Alter table 명령은 디스크 공간을 회수할 수 있도록 테이블을 삭제하고 다시 만듭니다.따라서 데이터 삭제 후 하드디스크를 해제하기 위해 아무것도 변경하지 않는 alter 테이블을 실행합니다(즉, 테이블 TBL_A에 charset uf8이 있습니다.데이터 삭제 후 ALTER TABLE TBL_A charset utf8 -> 이 명령어는 테이블에서 아무것도 변경하지 않지만 mysql이 테이블을 다시 만들고 디스크 공간을 확보합니다.
3. TBL_A와 같이 TBL_B를 만듭니다.TBL_A에서 유지할 선택 데이터를 TBL_B에 삽입합니다.TBL_A를 드롭하고 TBL_B의 이름을 TBL_A로 변경합니다.이 방법은 TBL_A와 삭제해야 할 데이터가 큰 경우 매우 효과적입니다(MySQL innodb의 delete 명령어는 성능이 매우 저하됨).

언급URL : https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table