Jaki jest najlepszy sposób na zmniejszenie rozmiaru ibdata w mysql?

Mam niektóre serwery produkcyjne, których ibdatapliki powiększają się z dnia na dzień.

Zużył już 290 GB miejsca.

Tabele na serwerach to w większości InnoDB i są duże żądania odczytu i zapisu.

Rozmiar pliku dziennika również rośnie. Tabele zawierają ogromną ilość danych.

Jak mogę kontrolować rosnący rozmiar obu?

Nie używam innodb_file_per_table.

Należy pamiętać, że najbardziej obciążonym plikiem w infrastrukturze InnoDB jest / var / lib / mysql / ibdata1

Ten plik zwykle zawiera wiele klas informacji (gdy innodb_file_per_table ma wartość 0)

• Dane tabeli
• Indeksy tabel
• Dane MVCC (Multiversioning Concurrency Control)
  • Wycofywanie segmentów
  • Cofnij przestrzeń tabel
• Metadane tabeli
• Zobacz reprezentację obrazkową

Wiele osób tworzy wiele plików ibdata, mając nadzieję na lepsze zarządzanie i wydajność miejsca na dysku. To nie pomaga.

Niestety, OPTYMALIZUJ TABELĘ w stosunku do tabeli InnoDB przechowywanej w ibdata1 robi dwie rzeczy:

• Sprawia, że ​​dane tabeli i indeksy są ciągłe w ibdata1
• Sprawia, że ​​ibdata1 rośnie, ponieważ ciągłe dane są dołączane do ibdata1

Możesz segregować dane tabel i indeksy tabel od ibdata1 i zarządzać nimi niezależnie za pomocą innodb_file_per_table . Aby raz na zawsze zmniejszyć ibdata1, musisz wykonać następujące czynności

Krok 01) MySQL Zrzuć wszystkie bazy danych do pliku tekstowego SQL (nazwij go SQLData.sql) ( więcej szczegółów tutaj )

Etap 02) Usuń wszystkie bazy danych (z wyjątkiem mysql, performance_schemai information_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

Sidenote: Niezależnie od tego, jaki zestaw masz dla innodb_buffer_pool_size, upewnij się, że innodb_log_file_size to 25% wielkości innodb_buffer_pool_size.

Krok 05) Usuń ibdata1, ib_logfile0 i ib_logfile1

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, ib_logfile0 i ib_logfile1 przy 1G każdy

Krok 07) Załaduj ponownie SQLData.sql do mysql

ibdata1 wzrośnie, ale będzie zawierał tylko metadane tabeli

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 (Strona główna danych tabeli i indeksów tabel dla 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żesz uruchomić, OPTIMIZE TABLE mydb.mytablea plik /var/lib/mysql/mydb/mytable.ibdfaktycznie 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ę. !!!

Jeśli chcesz zobaczyć, ile rzeczywistych danych jest przechowywanych w MyISAM i InnoDB, uruchom następujące zapytanie:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;