Modyfikowanie kolumn bardzo dużych tabel mysql z niewielkim czasem przestoju lub jego brakiem

Od czasu do czasu muszę wprowadzać zmiany w tabelach w mysql 5.1, głównie dodając kolumny. Bardzo proste dzięki poleceniu alter table. Ale moje tabele mają teraz do 40 milionów wierszy i rosną szybko ... Więc te polecenia zmieniające tabelę trwają kilka godzin. Myślę, że za kilka miesięcy potrwają dni.

Ponieważ używam amazon RDS, nie mogę mieć serwerów podrzędnych do gry, a następnie awansować do opanowania. Więc moje pytanie brzmi: czy jest sposób na to przy minimalnym przestoju? Oczywiście nie mam nic przeciwko operacji trwającej kilka godzin, a nawet dni, jeśli użytkownicy nadal mogą korzystać z bazy danych ... Czy mogą przynajmniej czytać podczas dodawania kolumn? Co się stanie, jeśli moja aplikacja spróbuje napisać? Wstawić czy zaktualizować? Jeśli zawiedzie natychmiast, to nie jest tak źle, jeśli po prostu zawiesza się i powoduje problemy z serwerem db, to jest duży problem.

To musi być dość powszechny problem ze skalowaniem, każdy musi dodać kolumny. Co zazwyczaj robi się z produkcyjnym plikiem bazowym? Slave -> migracja master?

Aktualizacja - zapomniałem wspomnieć, że używam silnika pamięci masowej innodb

Od czasu do czasu muszę wprowadzać zmiany w tabelach w mysql 5.1, głównie dodając kolumny.

Nie rób Nie naprawdę. Po prostu nie. Powinna to być bardzo rzadka okazja, gdy będzie to kiedykolwiek konieczne.

Zakładając, że twoje dane naprawdę są znormalizowane na początek, właściwym sposobem rozwiązania problemu jest dodanie nowej tabeli z relacją 1: 1 do tabeli podstawowej (nie obowiązkowe w nowej tabeli).

Konieczność regularnego dodawania kolumn jest zwykle wskaźnikiem bazy danych, która nie jest znormalizowana - jeśli twój schemat nie jest znormalizowany, to jest problem, który musisz naprawić.

Wreszcie, jeśli twój schemat naprawdę jest naprawdę znormalizowany, a Ty naprawdę, naprawdę musisz nadal dodawać kolumny, a następnie:

1. Upewnij się, że masz kolumnę znaczników czasu w bazie danych lub że generuje dzienniki replikacji
2. Utwórz kopię (B) tabeli (A)
3. dodaj nowe kolumny do B (to nadal będzie blokować myisam)
4. wyłącz transakcje
5. zmień nazwę oryginalnej tabeli (A) na coś innego (kopia zapasowa)
6. zmień nazwę nowej tabeli (B) na nazwę oryginalnej tabeli (A)
7. odtwórz transakcje od początku operacji z dziennika replikacji lub z tabeli kopii zapasowych
8. włączyć transakcje.

Ostatnio musiałem to zrobić. Amazon zalecił użycie Percona Toolkit. Pobrałem go i mogłem uruchomić coś takiego:

./pt-online-schema-change h=databasenameHostName,D=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType"

i działa świetnie. Mówi ci, ile czasu pozostało w procesie.

W rzeczywistości tworzy nową tabelę z nową kolumną, a następnie kopiuje istniejące dane. Ponadto tworzy wyzwalacz, dzięki czemu nowe dane są również przekazywane do nowej tabeli. Następnie automatycznie zmienia nazwy tabel, upuszcza stary stół i zaczynasz pracę z nową kolumną i nie ma przestojów podczas oczekiwania na aktualizacje.

symcbean zapewnia pewne solidne rekomendacje .

Aby odpowiedzieć na twoje pytanie, najłatwiejszym i najlepszym sposobem na ograniczenie wpływu jest replikacja wielu baz danych. Podwójny master z odpowiednią procedurą przełączania awaryjnego zatrzymującą replikację na aktywnym, co pozwala na zmianę nieaktywnej bez wpływu na aktywną.

Możesz to zrobić w pojedynczej bazie danych na żywo i zminimalizować wpływ, stosując procedurę podobną do tej opisanej w tej odpowiedzi . Wprawdzie jest to podobne do tego, co opisał symcbean, ale zawiera szczegóły techniczne. Możesz również użyć pola auto_increment, a nie tylko znacznika czasu.

Ostatecznie, jeśli Twój zestaw danych staje się tak duży, musisz również rozważyć archiwizację między bazami danych OLTP i OLAP . Zestaw danych transakcji nie powinien być tak duży, jeśli odpowiednio projektujesz.

Z instrukcji: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

W większości przypadków ALTER TABLE tworzy tymczasową kopię oryginalnej tabeli. MySQL uwzględnia zmiany w kopii, a następnie usuwa oryginalną tabelę i zmienia nazwę nowej. Podczas wykonywania ALTER TABLE oryginalna tabela jest odczytywana przez inne sesje. Aktualizacje i zapisy w tabeli są blokowane do momentu, gdy nowa tabela jest gotowa, a następnie są automatycznie przekierowywane do nowej tabeli bez żadnych nieudanych aktualizacji.

Czytanie działa dobrze. Zapis zostanie zablokowany, ale zostanie wykonany później. Jeśli chcesz temu zapobiec, musisz zmodyfikować oprogramowanie.

Jestem w podobnej sytuacji, w której muszę zmienić 1 z mojej tabeli transakcji, która ma prawie 65 GB. Słyszę 2 rozwiązania

1. Skorzystaj od razu ZMIEŃ i pozwól mu działać (X liczb godzin lub dni)
2. Upewnij się, że masz kolumnę znaczników czasu w bazie danych lub że generuje dzienniki replikacji
   • Utwórz kopię (B) tabeli (A)
   • dodaj nowe kolumny do B (to nadal będzie blokować myisam)
   • wyłącz transakcje
   • zmień nazwę oryginalnej tabeli (A) na coś innego (kopia zapasowa)
   • zmień nazwę nowej tabeli (B) na nazwę oryginalnej tabeli (A)