Przyspieszenie konwersji MyISAM do InnoDB

Mam serwer mysql 5.1 z bazą danych około 450 tabel, zajmującą 4 GB. Zdecydowana większość tych tabel (wszystkie oprócz 2) to MyIsam. W większości przypadków było to w porządku (nie wymaga transakcji), ale aplikacja zyskuje ruch i niektóre tabele zostały naruszone z powodu blokowania tabel podczas aktualizacji. To jest powód, dla którego 2 tabele są teraz InnoDB.

Konwersja w mniejszych tabelach (100 000 wierszy) wcale nie trwa długo, co powoduje minimalne przestoje. Jednak kilka moich tabel śledzenia zbliża się do 50 milionów wierszy. Czy istnieje sposób na przyspieszenie ALTER TABLE...ENGINE InnoDBna dużych stołach? A jeśli nie, to czy istnieją inne metody konwersji minimalizujące przestoje w tych tabelach o dużym obciążeniu zapisu?

mysql-5 storage-engine

— Derek Downey
źródło

Należy pamiętać: wiele pytań w jednym poście zniechęca ludzi, którzy mogą odpowiedzieć na jedno z pytań, od opublikowania odpowiedzi.

— BenV

I VtC, ponieważ odpowiedź na to pytanie jest dość skomplikowana. Powinieneś otworzyć jako kilka pytań indywidualnie.

— jcolebrand

chętnie skorzystam z porady, aby zamienić je w jedno pytanie, ale czy zaleca się usunięcie tego pytania i otwarcie nowego? przeróbka polegałaby głównie na usunięciu 2 pierwszych pocisków i zmianie pierwszego (zaktualizowałem także tytuł, aby odzwierciedlić, które silniki pamięci)

— Derek Downey

Albo będzie dobrze. Zwykle łatwiej jest napisać dwa inne pytania i usunąć jedno. Jednak równie łatwo możesz pozostawić to pytanie w celu „odniesienia”, a pozostałe dwa powrócić do niego, tak jak pytanie „to jest mój ogólny cel”.

— jcolebrand

przeredaguj ten w jeden punkt, a następnie opublikuj kolejne pytania.

— Brian Ballsun-Stanton

Odpowiedzi:

Zacznę od stwierdzenia, że nienawidzę ZMIENIĆ. To zło, IMHO.

Powiedz, to jest twój obecny schemat tabeli -

CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;

Oto ścieżka, którą polecam -

Utwórz nowy obiekt tabeli, który zastąpi stary:

CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8

Wstaw wszystkie wiersze ze starej tabeli według nazwy do nowej tabeli:

INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;

Test dymu swojej migracji:

SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;

Zamień nazwy tabel, aby zachować kopię zapasową na wypadek, gdybyś musiał cofnąć.

RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;

Przejdź do testów regresji.

Takie podejście staje się coraz bardziej preferowane w przypadku tabel z wieloma indeksami i milionami wierszy.

Myśli?

— randomx
źródło

Uzgodnione ... chociaż jeśli jest to bardzo transakcyjne, może być konieczne usunięcie bazy danych w tym czasie. (ale najprawdopodobniej tabela zmian da ci dłuższy okres przestoju)

— Joe

Tak, pomyślałem, że to będzie wymagać przestoju dla bardziej aktywnych tabel. Będę musiał przeprowadzić kilka testów, ale dlaczego miałoby to ALTER TABLEtrwać dłużej niż INSERT INTO...SELECTw przypadku 50 milionów wierszy?

— Derek Downey,

Nie będzie. Zasadniczo MySQL działa wewnętrznie dokładnie tak, jak sugerował ten plakat. Tworzy kopię definicji i pobiera ładunki do kopii.

— Morgan Tocker

Podoba mi się ta metoda, ponieważ pomija część „kopiuj do tmp”, co może zająć trochę czasu w przypadku dużych tabel.

— Haluk

Dodam teraz, że w MySQL 5.7 zmiany są znacznie szybsze i łatwiejsze.

— randomx

1) Ochrona przed stratą jest funkcją paranoi. Zawsze wykonuj kopię zapasową. Jeśli naprawdę jesteś paranoikiem, wykonaj kopię zapasową, a następnie przywróć z kopii zapasowej.

2) Ta strona podręcznika MySQL zawiera instrukcje dotyczące konwersji typów tabel.

Najszybszym sposobem na zmianę tabeli na InnoDB jest wykonanie wstawek bezpośrednio do tabeli InnoDB. To znaczy użyj ALTER TABLE ... ENGINE = INNODB lub utwórz pustą tabelę InnoDB o identycznych definicjach i wstaw wiersze INSERT INTO ... SELECT * FROM ....

3) PostgreSQL wykonuje wyszukiwanie pełnotekstowe , wydaje się, że silnik Sphinx robi to dla MySQL

— Brian Ballsun-Stanton
źródło

na pewno przyjrzę się sfinksowi, ponieważ dopiero niedawno o nim słyszałem.

— Derek Downey

X razy łatwiej jest zoptymalizować cały serwer (konfigurację pamięci, pamięci podręczne, indeksy), gdy używany jest tylko jeden silnik. Mieszanie myisam z innodb na dużych bazach danych zawsze utknie w pewnym momencie wymuszonym przez kompromis, aby oba silniki działały dobrze (ale nie doskonale :)

Polecam zainteresować się niektórymi dedykowanymi wyszukiwarkami pełnotekstowymi, takimi jak sfinks , lucene ( solr ) i pozbyć się go z warstwy bazy danych.

— sbczk
źródło