Oto pełniejsza odpowiedź odnośnie InnoDB. Jest to trochę długi proces, ale może być warty wysiłku.

Należy pamiętać, że /var/lib/mysql/ibdata1 jest najbardziej ruchliwym plikiem w infrastrukturze InnoDB. Zwykle zawiera sześć rodzajów informacji:

• Dane Tabeli
• Indeksy Tabel
• MVCC (Multiversioning Concurrency Control) dane
  • Rollback Segments
  • Cofnij Spację
• Metadane Tabeli (Dane Słownik)
• podwójny Bufor zapisu (zapis w tle, aby zapobiec poleganiu na buforowaniu systemu operacyjnego)
• Insert Buffer (zarządzanie zmianami w niestandardowych indeksach wtórnych)
• Zobacz Pictorial Representation of ibdata1

Architektura InnoDB

Wiele osób tworzy wiele plików ibdata w nadziei na lepsze zarządzanie przestrzenią dyskową i wydajność, jednak to przekonanie jest błędne.

Czy mogę biec OPTIMIZE TABLE ?

Niestety, bieganie OPTIMIZE TABLE w porównaniu z tabelą InnoDB przechowywaną w udostępnionym pliku table-space ibdata1 robi dwie rzeczy:

• sprawia, że dane tabeli i indeksy przylegają do siebie ibdata1
• sprawia, że ibdata1 rośnie, ponieważ sąsiadujące strony danych i indeksów są dołączane do ibdata1

Można jednak segregować dane tabel i indeksy tabel z ibdata1 i zarządzać nimi niezależnie.

Czy mogę biec OPTIMIZE TABLE z innodb_file_per_table ?

Przypuśćmy, że dodasz innodb_file_per_table do /etc/my.cnf (my.ini). Możesz po prostu biegać.OPTIMIZE TABLE na wszystkich stołach InnoDB?

Good News : When you run OPTIMIZE TABLE z innodb_file_per_table włączone, spowoduje to utworzenie pliku .ibd dla tej tabeli. Na przykład, jeśli masz tabelę mydb.mytable z datadirem /var/lib/mysql, wygeneruje ona po:

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

.ibd będzie zawierać strony z danymi i strony indeksujące dla tej tabeli. Świetnie.

Zła wiadomość : wszystko, co zrobiłeś, to wyodrębnienie stron danych i stron indeksu mydb.mytable z mieszkania w ibdata. Zapis słownika danych dla każdej tabeli, w tym mydb.mytable, nadal pozostaje w słowniku danych(zobacz obrazkowa Reprezentacja ibdata1 ). NIE MOŻNA PO PROSTU USUNĄĆ ibdata1 W TYM Punkt !!! należy pamiętać, że ibdata1 w ogóle się nie skurczył.

InnoDB Cleanup

Aby zmniejszyć ibdata1 raz na zawsze musisz wykonać następujące czynności:

1. Zrzut (np. z mysqldump) wszystkich baz danych do pliku tekstowego .sql (SQLData.sql jest używany poniżej)

2. Upuść wszystkie bazy danych (z wyjątkiem mysql i information_schema) Uwaga: na wszelki wypadek uruchom ten skrypt, aby upewnić się, że masz wszystkie granty użytkowników w miejsce:

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

3. Zaloguj się do mysql i uruchom SET GLOBAL innodb_fast_shutdown = 0; (spowoduje to całkowite usunięcie wszystkich pozostałych zmian transakcyjnych z ib_logfile0 i ib_logfile1)

4. Shutdown MySQL

5. Dodaj następujące linie do /etc/my.cnf (lub my.ini w systemie Windows)

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

   (Uwaga boczna: niezależnie od zestawu dla innodb_buffer_pool_size, upewnij się, że innodb_log_file_size jest 25% z innodb_buffer_pool_size.

   Również: innodb_flush_method=O_DIRECT nie jest dostępny w systemie Windows)

6. Delete ibdata* and ib_logfile*, Opcjonalnie, you może usunąć wszystkie foldery w /var/lib/mysql, z wyjątkiem /var/lib/mysql/mysql.

7. Uruchom MySQL (spowoduje to odtworzenie ibdata1 [domyślnie 10MB] oraz ib_logfile0 i ib_logfile1 po 1G).

8. Import SQLData.sql

Teraz ibdata1 będzie nadal rosnąć, ale będzie zawierać tylko metadane tabeli, ponieważ każda tabela InnoDB będzie istnieć poza ibdata1. ibdata1 nie będzie już zawierać danych InnoDB i indeksów dla innych tabel.

Na przykład, załóżmy, że masz tabelę InnoDB o nazwie mydb.mytable. Jeśli spojrzysz w /var/lib/mysql/mydb zobaczysz dwa pliki reprezentujące tabelę:

• mytable.frm (Nagłówek Silnika Pamięci)
• mytable.ibd (Dane tabeli i indeksy)

Z opcją innodb_file_per_table w /etc/my.cnf, możesz uruchomić OPTIMIZE TABLE mydb.mytable, A Plik /var/lib/mysql/mydb/mytable.ibd zmniejszy się.

Robiłem to wiele razy w mojej karierze jako MySQL DBA. W rzeczywistości, gdy pierwszy raz to zrobiłem, skurczyłem 50GB ibdata1 plik do 500MB!

Zastrzeżenie

W kroku 6, jeśli mysql nie może ponownie uruchomić się z powodu mysql schematu begin, spójrz wstecz na Krok 2. Zrobiłeś fizyczną kopię schematu mysql. Można go przywrócić w następujący sposób:

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

Wróć do kroku 6 i kontynuuj

AKTUALIZACJA 2013-06-04 11: 13

W odniesieniu do Ustawienia innodb_log_file_size na 25% innodb_buffer_pool_size w kroku 5 to raczej stara szkoła.

Z powrotem na July 03, 2006, Percona miał ładny artykuł Dlaczego wybrać odpowiedni innodb_log_file_size . Później, w Nov 21, 2008, Percona kontynuowała kolejny artykuł na temat jak obliczyć odpowiedni rozmiar w oparciu o szczytowe obciążenie pracą z zachowaniem godzinnych zmian.

Od tego czasu pisałem posty w dba StackExchange o obliczaniu rozmiaru dziennika i gdzie odwołałem się do tych dwa artykuły Percona.

• Aug 27, 2012 : poprawne dostrajanie tabel InnoDB 30GB na serwerze z 48GB RAM
• Jan 17, 2013 : MySQL 5.5-Innodb-innodb_log_file_size ponad 4GB razem?

Osobiście nadal wybrałbym regułę 25% dla wstępnej konfiguracji. Następnie, ponieważ obciążenie pracą może być dokładniejsze określone w czasie produkcji, możesz zmienić rozmiar dzienników podczas cyklu konserwacji w zaledwie minut.