Zmienić stół bez blokowania stołu?

Percona tworzy narzędzie o nazwie pt-online-schema-change , które pozwala to zrobić.

Zasadniczo tworzy kopię tabeli i modyfikuje nową tabelę. Aby zachować synchronizację nowej tabeli z oryginalną, używa ona wyzwalaczy do aktualizacji. Umożliwia to dostęp do oryginalnej tabeli, podczas gdy nowa tabela jest przygotowywana w tle.

Jest to podobne do metody Dems sugerowanej powyżej, ale robi to w sposób zautomatyzowany.

Niektóre z ich narzędzi mają uczenie curve, czyli łączenie się z bazą danych, ale gdy już to masz, są świetnymi narzędziami do posiadania.

Ex:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends

Zobacz Narzędzie Facebook ' a do zmiany schematu online.

Http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

Nie dla słabego serca; ale to wykona zadanie.

Polecam Postgres jeśli jest taka możliwość. W przypadku postgres zasadniczo nie ma przestojów z następującymi procedurami:

• ALTER TABLE ADD COLUMN (if the column can be NULL)
• ALTER TABLE DROP COLUMN
• CREATE INDEX (musi jednocześnie używać CREATE INDEX)
• WSKAŹNIK SPADKU

Inną świetną cechą jest to, że większość instrukcji DDL jest transakcyjna, więc możesz wykonać całą migrację w ramach transakcji SQL, a jeśli coś jeśli coś pójdzie nie tak, wszystko zostanie wycofane.

Napisałem to jakiś czas temu, być może to może rzucić trochę więcej wglądu na inne zalety.

To pytanie z 2009 roku. Teraz MySQL oferuje rozwiązanie:

Online DDL

Funkcja, która poprawia wydajność, współbieżność i dostępność tabel InnoDB podczas operacji DDL (przede wszystkim ALTER TABLE). Zobacz też Szczegóły w sekcji 14.11 "InnoDB i DDL Online".

Szczegóły różnią się w zależności od rodzaju operacji. W niektórych przypadkach, tabelę można modyfikować jednocześnie, gdy tabela ALTER znajduje się w postęp. Operacja może być w stanie do wykonania bez wykonywania table copy, lub za pomocą specjalnie zoptymalizowanego typu table copy. Spacja użycie jest kontrolowane przez innodb_online_alter_log_max_size Opcja konfiguracji.

Pozwala dostosować balans między wydajnością a współbieżnością podczas operacji DDL, wybierając, czy zablokować dostęp do tabeli w całości (lock=exclusive clause), zezwolić na zapytania, ale nie DML (LOCK=SHARED clause), czy zezwolić na pełne zapytanie i dostęp DML do tabeli (lock=NONE clause). Po pominięciu klauzuli LOCK lub określeniu LOCK = DEFAULT, MySQL pozwala na jak najwięcej współbieżności w zależności od rodzaju operacji.

Wykonywanie zmian w miejscu, o ile to możliwe, zamiast tworzenia nowej kopii tabeli, pozwala uniknąć tymczasowego zwiększenia wykorzystania miejsca na dysku i narzutu We / Wy związanego z kopiowaniem tabeli i rekonstruowaniem indeksów wtórnych.

Zobacz MySQL 5.6 Reference Manual -> InnoDB i Online DDL aby uzyskać więcej informacji.

Wydaje się, że online DDL dostępne również w MariaDB

Alternatywnie możesz użyć tabeli ALTER ONLINE, aby upewnić się, że Twoja zmiana Tabela nie blokuje operacji współbieżnych (nie przyjmuje blokad). On odpowiednik LOCK = NONE.

MariaDB KB o ALTER TABLE

Skoro pytałeś o inne bazy danych, oto kilka informacji o Oracle.

Dodanie kolumny NULL do tabeli Oracle jest bardzo szybką operacją, ponieważ aktualizuje tylko słownik danych. Posiada wyłączną blokadę na stole przez bardzo krótki okres czasu. Spowoduje to jednak unieważnienie wszelkich depedant przechowywanych procedur, widoków, wyzwalaczy itp. Zostaną one skompilowane automatycznie.

Stamtąd w razie potrzeby można utworzyć indeks za pomocą klauzuli ONLINE. Ponownie, tylko bardzo krótkie blokady słownikowe danych. Czyta całą tabelę szukając rzeczy do indeksowania, ale nie blokuje nikogo podczas tego.

Jeśli chcesz dodać klucz obcy, możesz to zrobić i przekonać Oracle, że dane są poprawne. W przeciwnym razie musi odczytać całą tabelę i zweryfikować wszystkie wartości, które mogą być wolne (najpierw Utwórz swój indeks).

Jeśli chcesz umieścić domyślną lub obliczoną wartość w każdym wierszu nowej kolumny, musisz uruchomić ogromną aktualizację a może mały program użytkowy, który zapełnia nowe dane. Może to być powolne, zwłaszcza jeśli rzędy stają się dużo większe i nie mieszczą się już w swoich blokach. Podczas tego procesu można zarządzać blokowaniem. Ponieważ stara wersja Twojej aplikacji, która nadal działa, nie wie o tej kolumnie, możesz potrzebować podstępnego wyzwalacza lub określić domyślną.

Stamtąd, można zrobić switcharoo na serwerach aplikacji do nowej wersji kodu i będzie działać. Zostawić Twój podstępny spust.

Alternatywnie, możesz użyć DBMS_REDEFINITION, która jest czarną skrzynką zaprojektowaną do tego typu rzeczy.

To wszystko jest tak kłopotliwe, aby przetestować, itp, że po prostu mamy wczesny niedzielny poranek przerwy, gdy wydajemy główną wersję.

Jeśli nie możesz sobie pozwolić na przestoje bazy danych podczas aktualizacji aplikacji, powinieneś rozważyć utrzymanie klastra z dwoma węzłami dla wysokiej dostępności. Dzięki prostej konfiguracji replikacji możesz dokonywać niemal w pełni online zmian strukturalnych, takich jak ta, którą sugerujesz:

• Poczekaj, aż wszystkie zmiany będą replikowane na pasywnym slave
• Zmień pasywny slave na aktywny master
• Wykonaj zmiany strukturalne na Starym master
• Powtórz zmiany z powrotem nowy mistrz do starego mistrza
• czy master swapping ponownie i wdrożenie nowej aplikacji jednocześnie

Nie zawsze jest to łatwe, ale działa, zwykle z 0 przestojami! Drugi węzeł nie musi być tylko pasywny, może być używany do testowania, robienia statystyk lub jako węzeł zapasowy. Jeśli nie masz infrastruktury replikacja może być skonfigurowana w ramach jednego komputera (z dwoma instancjami MySQL).

Nie. Jeśli używasz tabel MyISAM, według mojego najlepszego zrozumienia robią tylko blokady tabel - nie ma blokad rekordów, po prostu starają się zachować wszystko hyperfast poprzez prostotę. (Inne tabele MySQL działają inaczej.) W każdym przypadku można skopiować tabelę do innej tabeli, zmienić ją, a następnie przełączyć je, aktualizując różnice.

Jest to tak ogromna zmiana, że wątpię, aby jakikolwiek DBMS ją obsługiwał. Za korzyść uważa się możliwość zrobienia tego z danymi w tabeli w miejsce.

Rozwiązanie tymczasowe...

Innym rozwiązaniem może być dodanie innej tabeli z kluczem głównym oryginalnej tabeli wraz z nową kolumną.

Wprowadź klucz główny do nowej tabeli i wprowadź wartości dla nowej kolumny w nowej tabeli, zmodyfikuj zapytanie, aby dołączyć do tej tabeli dla operacji wyboru, a także musisz wstawić, aktualizować oddzielnie dla tej wartości kolumny.

Gdy będziesz w stanie uzyskać przestój, możesz zmienić oryginalną tabelę, zmodyfikować zapytania DML i upuść nową tabelę utworzoną wcześniej

W przeciwnym razie możesz wybrać metodę klastrowania, replikację, narzędzie pt-online-schema z percona

Za pomocą wtyczki Innodb można zmieniać polecenia tabeli, które tylko dodają lub upuszczają indeksy wtórne, "szybko", tzn. bez przebudowy tabeli.

Ogólnie rzecz biorąc, w MySQL każda zmiana tabeli polega na przebudowaniu całej tabeli, co może zająć bardzo dużo czasu(tzn. jeśli tabela zawiera użyteczną ilość danych).

Naprawdę musisz zaprojektować swoją aplikację tak, aby instrukcje ALTER TABLE nie musiały być wykonywane regularnie; na pewno nie chcesz żadnych zmian Tabela wykonywana podczas normalnego działania aplikacji, chyba że jesteś gotowy czekać lub zmieniasz małe tabele.

Polecam jedno z dwóch podejść:

1. Projektuj tabele baz danych z myślą o potencjalnych zmianach. Pracowałem na przykład z systemami zarządzania treścią, które regularnie zmieniają pola danych w treści. Zamiast budować fizyczną strukturę bazy danych, aby dopasować się do początkowych wymagań pola CMS, znacznie lepiej jest zbudować elastyczną strukturę. W tym przypadku, używając pola tekstowego blob (na przykład varchar(max)) do przechowywania elastycznych danych XML. To sprawia, że zmiany strukturalne bardzo rzadsze. Zmiany strukturalne mogą być kosztowne, więc również tutaj można odnieść korzyść.

2. Miej czas konserwacji systemu. Albo system wyłącza się podczas zmian (co miesiąc, itp.), a zmiany są zaplanowane w najmniej intensywnej porze dnia (na przykład 3-5 rano). Zmiany są wystawiane przed wdrożeniem produkcji, więc będziesz mieć dobre oszacowanie czasu przestoju.

2a. mają zbędne serwery, tak, że gdy system ma przestoje, cała witryna nie ulega awarii. Pozwoli to na" rolowanie " aktualizacji w sposób rozłożony, bez zdejmowania całej witryny.

Opcje 2 i 2a mogą nie być wykonalne; dotyczą one zazwyczaj tylko większych obiektów/operacji. Są to jednak ważne opcje i osobiście korzystałem ze wszystkich przedstawionych tutaj opcji.

Jeśli ktoś nadal to czyta lub zdarza się tu przyjść, jest to duża korzyść z używania systemu bazodanowego NoSQL, takiego jak mongodb. Miałem ten sam problem dotyczący zmiany tabeli, aby dodać kolumny dla dodatkowych funkcji lub indeksy na dużej tabeli z milionami wierszy i wysokimi wpisami. Skończyłoby się to zablokowaniem na bardzo długi czas, więc zrobienie tego w żywej bazie danych frustrowałoby naszych użytkowników. Na małych stołach można ujść na sucho.

Nienawidzę tego, że musimy "Zaprojektuj nasze stoły, aby ich nie zmieniać". Po prostu nie sądzę, że to działa w dzisiejszym świecie stron internetowych. Nie możesz przewidzieć, w jaki sposób ludzie będą korzystać z twojego oprogramowania, dlatego szybko zmieniasz rzeczy na podstawie opinii użytkowników. Dzięki mongodb możesz dodawać "kolumny" do woli bez przestojów. Tak naprawdę nawet ich nie dodajesz, po prostu wstawiasz dane z nowymi kolumnami i robi to automatycznie.

Warto sprawdzić: www.mongodb.com

Ogólnie rzecz biorąc, odpowiedź będzie brzmiała "Nie". Zmieniasz strukturę tabeli, która potencjalnie będzie wymagała wielu aktualizacji " i zdecydowanie się z tym Zgadzam. Jeśli spodziewasz się, że będziesz robił to często, zaproponuję alternatywę dla" atrapowych " kolumn-użyj VIEW S zamiast tabel dla SELECTING danych. IIRC, zmiana definicji widoku jest stosunkowo lekka, a indirection poprzez widok jest wykonywana podczas kompilacji planu zapytań. Wydatek jest taki, że trzeba by dodać kolumnę do nowej tabeli i zrobić widok JOIN w kolumnie.

Oczywiście działa to tylko wtedy, gdy możesz użyć kluczy obcych do wykonywania kaskadowych usuwania i tak dalej. Innym bonusem jest to, że możesz utworzyć nową tabelę zawierającą kombinację danych i skierować na nią Widok bez zakłócania użytkowania klienta.

Różnica między Postgres i MySQL w tym względzie polega na tym, że w Postgres nie tworzy on ponownie tabeli, ale modyfikuje słownik danych, który jest podobny do Oracle. Dlatego operacja jest szybka, podczas gdy nadal wymaga przydzielenia wyłącznej blokady tabeli DDL na bardzo krótki czas, jak wspomniano powyżej przez innych.

W MySQL operacja skopiuje dane do nowej tabeli podczas blokowania transakcji, co było głównym problemem dla baz danych MySQL przed wersją 5.6.

Dobra wiadomość jest taka, że że od wydania MySQL 5.6 ograniczenie zostało w większości zniesione i teraz możesz cieszyć się prawdziwą mocą MYSQL DB.

Jak wspomniał SeanDowney, pt-online-schema-change jest jednym z najlepszych narzędzi do zrobienia tego, co opisałeś w pytaniu tutaj. Ostatnio zrobiłem wiele zmian w schemacie NA ŻYWO DB i poszło całkiem dobrze. Więcej na ten temat możecie przeczytać na moim blogu TUTAJ: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.

Zdecydowanie powinieneś spróbować pt-online-schema-change. Używam tego narzędzia do migracji na AWS RDS z wieloma niewolnikami i działało bardzo dobrze dla mnie. Napisałem rozbudowany post na blogu o tym, jak to zrobić, co może być dla ciebie pomocne.

Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/

Atrapy kolumn są dobrym pomysłem, jeśli można przewidzieć ich typ (i uczynić je nullable). Sprawdź, jak Twój silnik magazynowy radzi sobie z nullami.

MyISAM zablokuje wszystko, jeśli nawet wspomnisz nazwę tabeli w drodze, przez telefon, na lotnisku. Po prostu to robi...

To powiedziawszy, blokady nie są tak wielką sprawą; o ile nie próbujesz dodać domyślnej wartości dla nowej kolumny do każdego wiersza, ale pozwól jej siedzieć jako null, a twój silnik pamięci masowej jest wystarczająco inteligentny, aby nie iść pisząc to, powinieneś być w porządku z blokadą, która jest utrzymywana tylko na tyle długo, aby zaktualizować metadane. Jeśli spróbujesz napisać nową wartość, to jesteś skończony.

TokuDB może dodawać / upuszczać kolumny i dodawać indeksy "hot", tabela jest w pełni dostępna przez cały proces. Jest on dostępny za pośrednictwem www.tokutek.com

Niezupełnie.

W końcu zmieniasz podstawową strukturę tabeli, a to trochę informacji, które są dość ważne dla systemu bazowego. Przenosisz również (prawdopodobnie) dużą część danych na dysku.

Jeśli planujesz robić to dużo, lepiej po prostu wypełniać tabelę "atrapą" kolumn, które są dostępne do wykorzystania w przyszłości.