Rozwiązałem to w StackOverflow w październiku 2010 roku .
Pamiętaj o najbardziej zajętym pliku w infrastrukturze InnoDB: / var / lib / mysql / ibdata1
Ten plik zwykle zawiera cztery rodzaje informacji
- Dane tabeli
- Indeksy tabel
- Dane MVCC (Multiversioning Concurrency Control)
- Metadane tabeli (lista identyfikatorów obszaru tabel)
Uruchamianie OPTIMIZE TABLEz tabelą InnoDB przechowywaną w ibdata1 ma dwie rzeczy:
- Sprawia, że dane tabeli i indeksy są ciągłe w ibdata1, dzięki czemu dostęp jest szybszy
- Sprawia, że ibdata1 rośnie, ponieważ ciągłe dane i strony indeksowe są dołączane do ibdata1
Podczas gdy możesz segregować dane tabel i indeksy tabel od ibdata1 i zarządzać nimi niezależnie za pomocą innodb_file_per_table , duża rozbieżność całego miejsca na dysku w ibdata1 po prostu nie zniknie i nie będzie można go odzyskać. Musisz zrobić więcej.
Aby raz na zawsze zmniejszyć ibdata1 , wykonaj następujące czynności:
1) MySQL Zrzuć wszystkie bazy danych do pliku tekstowego SQL (nazwij go /root/SQLData.sql)
2) Usuń wszystkie bazy danych (oprócz schematu mysql)
3) Zamknij mysql
4) Dodaj następujące wiersze do /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Uwaga: Niezależnie od tego, jaki zestaw masz dla innodb_buffer_pool_size, upewnij się, że innodb_log_file_size to 25% innodb_buffer_pool_size.
5) Usuń ibdata1, ib_logfile0 i ib_logfile1
W tym momencie powinien istnieć tylko schemat mysql w katalogu / var / lib / mysql
6) Uruchom ponownie mysql
Spowoduje to ponowne utworzenie ibdata1 przy 10 lub 18 MB (w zależności od wersji MySQL), ib_logfile0 i ib_logfile1 przy 1G każdy
7) Ponownie załaduj /root/SQLData.sql do mysql
ibdata1 wzrośnie, ale będzie zawierał tylko metadane tabeli. W rzeczywistości będzie rosła bardzo powoli z biegiem lat. Jedynym sposobem szybkiego wzrostu ibdata1 jest posiadanie jednego lub więcej z poniższych:
- Wiele DDL (
CREATE TABLE, DROP TABLE, ALTER TABLE)
- Dużo transakcji
- Wiele zmian do zatwierdzenia na transakcję
Każda tabela InnoDB będzie istniała poza ibdata1
Załóżmy, że masz tabelę InnoDB o nazwie mydb.mytable. Jeśli przejdziesz do / var / lib / mysql / mydb, zobaczysz dwa pliki reprezentujące tabelę
- mytable.frm (nagłówek silnika pamięci masowej)
- mytable.ibd (Home of Table Data and Table Indexs for mydb.mytable)
ibdata1 nigdy nie będzie już zawierał danych i indeksów InnoDB.
Dzięki opcji innodb_file_per_table w /etc/my.cnf można uruchomić, OPTIMIZE TABLE mydb.mytable;a plik /var/lib/mysql/mydb/mytable.ibd faktycznie się zmniejszy.
Robiłem to wiele razy w mojej karierze jako MySQL DBA
W rzeczywistości, kiedy pierwszy raz to zrobiłem, zwinąłem 50 GB pliku ibdata1 do 500 MB.
Spróbuj. Jeśli masz dodatkowe pytania, napisz do mnie. Zaufaj mi. Będzie to działać w krótkim okresie i na dłuższą metę !!!
AKTUALIZACJA 2012-04-19 09:23 EDT
Po uruchomieniu powyższych kroków, w jaki sposób można ustalić, które tabele należy poddać defragmentacji? Można się dowiedzieć, ale będziesz miał skrypt.
Oto przykład: Załóżmy, że masz tabelę mydb.mytable. Po włączeniu innodb_file_per_table masz plik /var/lib/mysql/mydb/mytable.ibd
Będziesz musiał pobrać dwie liczby
FILESIZE FROM OS: Możesz sprawdzić rozmiar pliku w systemie operacyjnym w ten sposób
ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'
FILESIZE FROM INFORMATION_SCHEMA: Możesz sprawdzić rozmiar pliku z Information_schema.tables w następujący sposób:
SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
Wystarczy odjąć wartość INFORMACJE_SCHEMA od wartości OS i podzielić różnicę przez wartość INFORMACJA_SCHEMA.
Następnie zdecydujesz, jaki procent uważa za konieczne defragmentację tej tabeli. Oczywiście defragmentujesz go za pomocą jednego z następujących poleceń:
OPTIMIZE TABLE mydb.mytable;
lub
ALTER TABLE mydb.mytable ENGINE=InnoDB;