Oto pełniejsza odpowiedź dotycząca InnoDB. To trochę długotrwały proces, ale może być wart wysiłku.
Należy pamiętać, że /var/lib/mysql/ibdata1jest to najbardziej obciążony plik w infrastrukturze InnoDB. Zwykle zawiera sześć rodzajów informacji:
Architektura InnoDB
Wiele osób tworzy wiele ibdataplików w nadziei na lepsze zarządzanie miejscem na dysku i lepszą wydajność, jednak to przekonanie jest błędne.
Niestety, działanie OPTIMIZE TABLEz tabelą InnoDB przechowywaną we współużytkowanym pliku obszaru tabel ibdata1powoduje dwie rzeczy:
- Sprawia, że dane i indeksy tabeli są ciągłe wewnątrz
ibdata1
- Marki
ibdata1rosną, ponieważ sąsiednie danych i indeksów stron są dołączane doibdata1
Możesz jednak oddzielić dane tabeli i indeksy tabel od ibdata1i zarządzać nimi niezależnie.
Załóżmy, że chcesz dodać innodb_file_per_tabledo /etc/my.cnf (my.ini). Czy możesz po prostu uruchomić OPTIMIZE TABLEwszystkie tabele InnoDB?
Dobra wiadomość : Kiedy uruchomisz OPTIMIZE TABLEz innodb_file_per_tablewłączoną, utworzy to .ibdplik dla tej tabeli. Na przykład, jeśli masz tabelę z mydb.mytabledatadir o wartości /var/lib/mysql, zwróci to:
/var/lib/mysql/mydb/mytable.frm/var/lib/mysql/mydb/mytable.ibd
.ibdBędą zawierać stron danych i indeksowanie stron dla tej tabeli. Wspaniały.
Złe wieści : wszystko, co zrobiłeś, to wyodrębnienie stron danych i stron indeksu mydb.mytablez miejsca zamieszkania ibdata. Wpis słownika danych dla każdej tabeli, w tym mydb.mytable, nadal pozostaje w słowniku danych (patrz reprezentacja graficzna ibdata1 ). NIE MOŻESZ PO PROSTU SKASOWAĆ W ibdata1TYM PUNKCIE !!! Należy pamiętać, że ibdata1wcale się nie skurczył.
Oczyszczanie infrastruktury InnoDB
Aby zmniejszyć się ibdata1raz na zawsze, wykonaj następujące czynności:
Zrzuć (np. Z mysqldump) wszystkie bazy danych do .sqlpliku tekstowego ( SQLData.sqljest używane poniżej)
Usuń wszystkie bazy danych (z wyjątkiem mysqli information_schema) OSTRZEŻENIE : Zapobiegawczo uruchom ten skrypt, aby upewnić się, że masz wszystkie uprawnienia użytkowników:
mkdir /var/lib/mysql_grants
cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
chown -R mysql:mysql /var/lib/mysql_grants
Zaloguj się do mysql i uruchom SET GLOBAL innodb_fast_shutdown = 0;(to całkowicie opróżni wszystkie pozostałe zmiany transakcyjne z ib_logfile0i ib_logfile1)
Zamknij MySQL
Dodaj następujące wiersze do /etc/my.cnf(lub my.iniw systemie Windows)
[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, do czego masz zestaw innodb_buffer_pool_size, upewnij się, że innodb_log_file_sizejest to 25% innodb_buffer_pool_size.
Ponadto: innodb_flush_method=O_DIRECTnie jest dostępny w systemie Windows)
Usuń ibdata*i ib_logfile*opcjonalnie możesz usunąć wszystkie foldery z /var/lib/mysqlwyjątkiem /var/lib/mysql/mysql.
Rozpocznij MySQL (Spowoduje to odtworzenie ibdata1[10MB domyślnie] a ib_logfile0i ib_logfile1na każdy 1G).
Import SQLData.sql
Teraz ibdata1będzie nadal rosnąć, ale będzie zawierać tylko metadane tabeli, ponieważ każda tabela InnoDB będzie istnieć poza ibdata1. ibdata1nie 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 zajrzysz do /var/lib/mysql/mydbśrodka, zobaczysz dwa pliki reprezentujące tabelę:
mytable.frm (Nagłówek pamięci masowej)mytable.ibd (Dane tabeli i indeksy)
Dzięki innodb_file_per_tableopcji w /etc/my.cnfmożesz uruchomić, OPTIMIZE TABLE mydb.mytablea plik /var/lib/mysql/mydb/mytable.ibdfaktycznie się zmniejszy.
Robiłem to wiele razy w swojej karierze jako administrator MySQL. W rzeczywistości, gdy to zrobiłem po raz pierwszy, zmniejszyłem plik 50 GB ibdata1 do zaledwie 500 MB!
Spróbuj. Jeśli masz dalsze pytania, po prostu zapytaj. Zaufaj mi; będzie to działać zarówno w perspektywie krótkoterminowej, jak i długoterminowej.
PRZESTROGA
Jeśli w kroku 6, jeśli mysql nie może zostać ponownie uruchomiony z powodu mysqlupuszczenia schematu, spójrz wstecz na krok 2. Wykonałeś fizyczną kopię mysqlschematu. Możesz 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
UPDATE 2013-06-04 11:13 EDT
Jeśli chodzi o ustawienie innodb_log_file_size na 25% innodb_buffer_pool_size w kroku 5, ta ogólna reguła jest raczej stara.
Wracając July 03, 2006, Percona miała fajny artykuł, dlaczego wybrać właściwy innodb_log_file_size . Później Nov 21, 2008Percona kontynuowała kolejny artykuł o tym, jak obliczyć właściwy rozmiar w oparciu o szczytowe obciążenie pracą, przy zachowaniu godzinnych zmian .
Od tego czasu napisałem posty w DBA StackExchange na temat obliczania rozmiaru dziennika i odniesienia do tych dwóch artykułów Percona.
Osobiście nadal stosowałbym zasadę 25% przy początkowej konfiguracji. Następnie, ponieważ obciążenie pracą można dokładniej określić w czasie w produkcji, można zmienić rozmiar dzienników podczas cyklu konserwacji w zaledwie kilka minut.