Domyślnie MySQL InnoDB przechowuje wszystkie tabele wszystkich baz danych w jednym pliku globalnym. Możesz to zmienić, ustawiając innodb_file_per_table w konfiguracji, która następnie tworzy jeden plik danych dla każdej tabeli.

Zastanawiam się, dlaczego innodb_file_per_tabledomyślnie nie jest włączone. Czy korzystanie z niego ma swoje wady?

Mam pełną odpowiedź na to pytanie.

Po wstawieniu innodb_file_per_table , a nowe tabele InnoDB można zmniejszyć za pomocą ALTER TABLE <innodb-table-name> ENGINE=InnoDB';To zmniejszy nowe .ibdpliki GWARANTOWANE.

Jeśli uruchomisz ALTER TABLE <innodb-table-name> ENGINE=InnoDB';tabelę InnoDB utworzoną przed użyciem tabeli_pliku_inodb_per_table, spowoduje to wyrwanie danych i indeksów dla tej tabeli z pliku ibdata1 i zapisanie jej w .ibdpliku, co spowoduje pozostawienie stałego gołębia w całości w ibdata1, którego nigdy nie można ponownie użyć .

ibdata1Plik normalnie mieści cztery rodzaje informacji

• Dane tabeli
• Indeksy tabel
• MVCC (Multiversioning współbieżności Control) Data
  • Wycofywanie segmentów
  • Cofnij spację
• Tabela metadanych (słownik danych)
• Podwójny bufor zapisu (zapisywanie w tle, aby uniknąć polegania na buforowaniu systemu operacyjnego)
• Wstaw bufor (zarządzanie zmianami w nieunikalnych indeksach wtórnych)
• Zobacz Pictorial Representation of ibdata1

Oto gwarantowany sposób zmniejszenia pliku ibdata1 prawie na zawsze ...

KROK 01) MySQL Zrzuć wszystkie bazy danych do pliku tekstowego SQL (nazwij go SQLData.sql)

KROK 02) Usuń wszystkie bazy danych (oprócz schematów mysql, information_schema i performance_schema)

KROK 03) Zamknij mysql

KROK 04) 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
innodb_data_file_path=ibdata1:10M:autoextend

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.

• KROK 05) Usuń ibdata1, ib_logfile0 i ib_logfile1 ( przed usunięciem zobacz aktualizację poniżej! )

W tym momencie powinien istnieć tylko schemat mysql w / var / lib / mysql

• KROK 06) Uruchom ponownie mysql

Spowoduje to ponowne utworzenie ibdata1 przy 10 MB (nie konfiguruj opcji), ib_logfile0 i ib_logfile1 przy 1G każdy

• KROK 07) Ponownie załaduj SQLData.sql do mysql

ibdata1 wzrośnie, ale będzie zawierać tylko metadane tabeli i dane przerywane MVCC.

Każda tabela InnoDB będzie istnieć poza ibdata1

Załóżmy, że masz tabelę InnoDB o nazwie mydb.mytable. Jeśli wejdziesz do /var/lib/mysql/mydb, zobaczysz dwa pliki reprezentujące tabelę

• mytable.frm (Nagłówek silnika pamięci masowej)
• mytable.ibd(Strona główna danych tabeli i indeksów tabeli dla mydb.mytable)

ibdata1 nigdy nie będzie już zawierał danych i indeksów InnoDB.

Z opcją innodb_file_per_table w /etc/my.cnf, możesz uruchomić OPTIMIZE TABLE mydb.mytableLUB, ALTER TABLE mydb.mytable ENGINE=InnoDB;a plik /var/lib/mysql/mydb/mytable.ibdfaktycznie się zmniejszy.

Robiłem to wiele razy w mojej karierze jako MySQL DBA bez żadnego problemu. W rzeczywistości, kiedy pierwszy raz to zrobiłem, zwinąłem 50 GB pliku ibdata1 do 50 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 2013-07-02 15:08 EDT

Jest jedno zastrzeżenie, które mam w tym względzie, które zaktualizowałem w innych moich postach, ale przegapiłem to: aktualizuję swoją odpowiedź nieco bardziej za pomocą innodb_fast_shutdown, ponieważ kiedyś restartowałem mysql i zatrzymywałem mysql, aby to zrobić. Teraz ten jeden krok jest niezbędny, ponieważ każda niezatwierdzona transakcja może zawierać inne ruchome części w dziennikach transakcji InnoDB i poza nimi ( patrz Infrastruktura InnoDB ).

Należy pamiętać, że ustawienie innodb_fast_shutdown na 2 również wyczyści dzienniki, ale więcej ruchomych części nadal istnieje i zostanie wybranych podczas odzyskiwania po awarii podczas uruchamiania mysqld. Ustawienie 0 jest najlepsze.

Zobacz błąd .

Czy korzystanie z niego ma swoje wady?

• więcej otwartych plików
• otwórz / otwórz ponownie
• Plik .ibd nie zmniejsza się (patrz 1 , 2 )

Zawsze używam innodb_file_per_table w dużych bazach danych.

innodb_file_per_table jest domyślnie włączony w MariaDB.

Powodem, dla którego zdecydowałem się nie używać innodb_file_per_table, jest to, że każda tabela jest umieszczana we własnym pliku, co oznacza, że ​​każda tabela ma swój własny, osobny narzut (sygnatury plików itp.), Co powoduje całkowity, ogólny rozmiar MySQLkatalogu większy niż w przypadku korzystania ze wspólnego obszaru tabel. Ponadto istnieje więcej marnowanego miejsca z powodu luźności klastra, gdy wiele małych plików jest zamiast jednego dużego pliku.

To prawda, że ​​dodatkowy narzut nie jest ogromną sumą w wielkim schemacie rzeczy, szczególnie jeśli używasz dużego dysku lub masz gigantyczną bazę danych, ale dla mnie (i prawdopodobnie wielu „użytkowników domowych”) wszystko to się sumuje i wciąż było za dużo dla małego dysku z dużymi klastrami, w którym trzymałem swój sklep MySQL.

Na przykład mój magazyn baz danych z moimi bazami danych WordPress i kilkoma innymi małymi bazami danych (phpBB, dev, niektóre testy AMP itp.), Konwersja na tabelę zmieniła go z 32 MB na 50 MB, a to nawet nie obejmuje tego, ibdata1które wciąż wymaga się co najmniej 10 MB , o łącznie co najmniej 60 MB.

Jak powiedziałem, może to nie stanowić większego problemu dla niektórych osób, szczególnie przedsiębiorstw, ale jeśli jesteś użytkownikiem domowym, który hostuje tylko Twoją witrynę, blog itp., Może to rzeczywiście mieć wpływ na wybór dostawca hosta, ponieważ wiele hostów ogranicza wielkość bazy danych oprócz całkowitego wykorzystania dysku.

Aby dodać trochę więcej informacji

Od mysql 5.6.6 jest on domyślnie włączony

z innodb_file_per_table = 1, tablica upuszczania może być wolniejsza patrz tutaj