Co możemy zrobić w replikacji MySQL 5.0, aby rozwiązać problemy z przepustowością?

Rozwijam aplikację do uruchomienia na komputerze klienckim (Win), który jest skonfigurowany z instancją serwera MySQL 5.1, która będzie działać jako slave tylko do odczytu dla zdalnego urządzenia nadrzędnego. Zdalny wzorzec ma dziesiątki schematów, ale potrzebuję tylko jednego na klienta, więc podaję ustawienie replikacja-do-db w pliku my.ini, aby replikować tylko schemat, którego potrzebuje klient. Replikacja działa, ale kiedy nasi klienci docierają do regionów świata, w których dostęp do Internetu jest dostępny tylko przez sieć bezprzewodową 3G, która pobiera opłaty za wykorzystanie danych, szybko przekraczają limity swojego planu danych i napotykają na drogie problemy.

Jak rozumiem, MySQL zapisuje wszystkie transakcje dla wszystkich schematów w jednym pliku binlog, co oznacza, że ​​każdy klient musi pobrać wszystkie transakcje, które są wykonywane na każdym schemacie w systemie głównym, a następnie po pobraniu zastosować filtr bazy danych dla replikacji ustawienia do-db w pliku my.ini klienta.

Aby zminimalizować tę nieefektywność, zastosowałem ustawienie slave_compressed_protocol = 1 , które wydaje się zmniejszać przesyłane dane o 50%, ale nadal powoduje, że nasi klienci szybko przekraczają limit danych, podnosząc rachunek 3G.

Nie mogę sobie wyobrazić, że jestem jedynym, z którym mam do czynienia, więc jestem pewien, że otrzymam mnóstwo odpowiedzi na pytanie, jak to osiągnąć, ustawiając x = y. Nie mogę jednak znaleźć żadnej dokumentacji takiego ustawienia ani zalecanego podejścia.

Jak dotąd, oto moja myśl na temat możliwego rozwiązania, proszę o opinię lub alternatywne trasy:

1. Skonfiguruj urządzenie podrzędne „proxy” dla każdego schematu (w innym pudełku lub w tym samym pudełku z inną instancją / portem MySQL)
2. Skonfiguruj serwer proxy slave, aby replikował-do-db tylko jedną bazę danych, którą klienci chcą replikować.
3. Skonfiguruj instancję MySQL klienta jako slave dla odpowiedniego proxy slave.

To powinno spowodować pociągnięcie klient tylko dane binlog ich schematu. Minusem (o ile mogę stwierdzić) jest to, że dramatycznie zwiększa złożoność naszej konfiguracji, prawdopodobnie czyniąc ją bardziej kruchą.

Myśli? Czy to podejście w ogóle zadziała?

Uwaga: na serwerze RedHat działamy na serwerze MySQL 5.0, ale możemy go zaktualizować do wersji 5.5, jeśli da to rozwiązanie.

SUGESTIA # 1: Użyj wzorców dystrybucji

Distribution Master to mysql slave z włączonym log-bin, log-slave-updates i zawiera tylko tabele z silnikiem BLACKHOLE Storage Engine . Możesz zastosować replicate-do-db do Distribution Master i tworzyć dzienniki binarne w Distribution Master, które zawierają tylko schematy DB, które chcesz binlogować. W ten sposób zmniejszasz rozmiar wychodzących binlogów od Distribution Master.

Możesz skonfigurować wzorzec dystrybucji w następujący sposób:

1. mysqldump twoją bazę danych, używając opcji --no-data, aby wygenerować zrzut tylko do schematu.
2. Załaduj zrzut tylko do schematu do Distribution Master.
3. Konwertuj każdą tabelę w Distribution Master na silnik pamięci BLACKHOLE.
4. Skonfiguruj replikację do wzorca dystrybucji z wzorca z rzeczywistymi danymi.
5. Dodaj opcje replicate-do-db do /etc/my.cnf Distribution Master.

W krokach 2 i 3 można również edytować zrzut tylko do schematu i zastąpić ENGINE = MyISAM i ENGINE = InnoDB na ENGINE = BLACKHOLE, a następnie załadować ten edytowany zrzut tylko do schematu do Distribution Master.

Tylko w kroku 3, jeśli chcesz skryptować konwersji wszystkich tabel MyISAM i InnoDB na BLACKHOLE w Distribution Master, uruchom następującą kwerendę i wyślij ją do pliku tekstowego:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=BLACKHOLE;') BlackholeConversion FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine <> 'BLACKHOLE'" > BlackholeMaker.sql

Dodatkową zaletą skryptowania konwersji tabeli na silnik pamięci BLACKHOLE jest to, że konwertowane są również tabele silnika pamięci MEMORY . Chociaż tabela silnika pamięci masowej MEMORY nie zajmuje miejsca na dysku do przechowywania danych, zajmie pamięć. Konwersja tabel MEMORY na BLACKHOLE zapewni porządek w pamięci w Distribution Master.

Dopóki nie wyślesz żadnego DDL do Distribution Master, możesz przesłać dowolny DML (INSERT, UPDATE, DELETE), zanim zechcesz pozwolić klientom replikować tylko te informacje DB, których chcą.

Napisałem już post w innej witrynie StackExchange, która omawia korzystanie z Distribution Master .

SUGESTIA # 2: Używaj mniejszych dzienników binarnych i dzienników przekazywania

Jeśli ustawisz max_binlog_size na coś absurdalnie małego, wówczas binlogs mogą być gromadzone i wysyłane w mniejszych porcjach. Istnieje również osobna opcja ustawiania wielkości dzienników przekazywania, max_relay_log_size . Jeśli max_relay_log_size = 0, domyślnie zostanie ustawiona wartość max_binlog_size.

SUGGESTION # 3: Użyj replikacji półsynchronicznej (tylko MySQL 5.5)

Skonfiguruj swoją główną bazę danych i wiele Dystrybutorów jako MySQL 5.5. Włącz replikację półsynchroniczną, aby główna baza danych mogła szybko wysyłać binlogs do Distribution Master. Jeśli WSZYSTKIMI niewolnicy są Mistrzami dystrybucji, możesz nie potrzebować replikacji półsynchronicznej lub MySQL 5.5. Jeśli którykolwiek z urządzeń podrzędnych, inny niż Wzorce dystrybucji, ma rzeczywiste dane do celów raportowania, wysokiej dostępności, pasywnego trybu gotowości lub tworzenia kopii zapasowych, skorzystaj z MySQL 5.5 w połączeniu z replikacją półsynchroniczną.

SUGESTIA # 4: Użyj rejestrowania binarnego opartego na instrukcjach, a nie opartego na wierszach

Jeśli instrukcja SQL aktualizuje wiele wierszy w tabeli, rejestrowanie binarne na podstawie instrukcji (SBBL) przechowuje tylko instrukcję SQL. Ta sama instrukcja SQL przy użyciu rejestrowania binarnego opartego na wierszach (RBBL) zarejestruje zmianę wiersza dla każdego wiersza. To oczywiste, że przesyłanie instrukcji SQL pozwoli zaoszczędzić miejsce w dziennikach binarnych wykonujących SBBL przez RBBL.

Innym problemem jest używanie RBBL w połączeniu z replicate-do-db, gdzie nazwa tabeli ma przygotowaną bazę danych . To nie może być dobre dla niewolnika, szczególnie dla Mistrza Dystrybucji. Dlatego upewnij się, że wszystkie DML nie mają bazy danych i kropki przed nazwami tabel.

Rozmiar max_binlog_size powinien być nieistotny - dane binlog są przesyłane strumieniowo w sposób ciągły.

Uwaga dotycząca „Distribution Master” - jest to „pojedynczy punkt awarii”. Oznacza to, że jeśli umrze, wszyscy niewolnicy poza nim nie będą otrzymywać danych, a przebudowa przekaźnika zajmie pracę.

SBR vs RBR - zależy od zapytania. Każda może być lepsza lub gorsza od drugiej.

Umieść Mistrzów Dystrybucji na tej samej maszynie co prawdziwy Mistrz lub na maszynie „blisko” Mistrza. Użyj oddzielnych portów, aby oddzielić instancje.