Wydajność replikacji MySQL


15

Mam poważny problem z wydajnością replikacji MySQL 5.5 między dwoma komputerami, głównie tabelami myISAM z replikacją opartą na instrukcjach. Dzienniki binarne i katalog danych mysql znajdują się na tym samym dysku Fusion ioDrive.

Problem był ostatnio dużym problemem, kiedy musieliśmy wstrzymać replikację na około. 3 godziny. Ponowne nadrobienie zaległości zajęło około 10 godzin.

10 godzin do nadrobienia

Jak mogę zwiększyć wydajność replikacji? Maszyna B jest w zasadzie bezczynna (mało, We / Wy, 2 zmaksymalizowane rdzenie z 16, dużo wolnej pamięci RAM), ponieważ tylko 1 wątek mySQL zapisywał dane. Oto kilka pomysłów, które miałem:

  • Przejdź do replikacji opartej na wierszach. W testach przyniosło to jedynie wzrost wydajności o 10-20%
  • Uaktualnij do mySQL 5.6 z replikacją wielowątkową. Możemy łatwo podzielić nasze dane na osobne bazy danych, a testy porównawcze wydają się wskazywać, że to pomogłoby, ale kod nie wydaje się gotowy do produkcji.
  • Niektóre zmienne konfiguracyjne, które pomogą przyspieszyć replikację

Podstawowy problem polega na tym, że jeśli nadrobienie zaległości zajmuje 10 godzin po wstrzymaniu na 3 godziny, oznacza to, że replikacja zapisuje 13 godzin danych w ciągu 10 godzin lub jest w stanie zapisać przy 130% prędkości przychodzących danych. przynajmniej podwójne zapisywanie na komputerze głównym w najbliższej przyszłości, więc desperacko potrzebuję sposobu na poprawę wydajności replikacji.

Maszyna A:

  • Mistrz
  • 24 GB pamięci RAM
  • 1,2 TB Fusion ioDrive2
  • 2x E5620
  • Połączenie gigabitowe

my.cnf:

[mysqld]
server-id=71
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

log-bin=/data_fio/mysqlbinlog/mysql-bin.log
binlog-format=STATEMENT
replicate-ignore-db=mysql

log-slave-updates = true

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

Maszyna B:

  • Niewolnik
  • 36 GB pamięci RAM
  • 1,2 TB Fusion ioDrive2
  • 2x E5620
  • Połączenie gigabitowe

my.cnf:

[mysqld]
server-id=72
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

plugin-load=archive=ha_archive.so;blackhole=ha_blackhole.so

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

Maszyna B jest w zasadzie bezczynna . Takie jest moje doświadczenie z replikacją na MySQL 5.1. Replikacja jest jednowątkowa, a jeden procesor zostanie zmaksymalizowany, podczas gdy inne pozostaną bezczynne.
Stefan Lasiewski

robisz kopie zapasowe niewolnika?
Mike

@ Stefan-Lasiewski Żeby było jasne, to MySQL 5.5, ale tak. Jest jednowątkowy i bardzo frustrujący
Nick

@Mike Tak, a także duże zapytania, które trwają wiele minut w ciągu dnia. Replikacja zwalnia do około 100 sekund, a następnie zajmuje trochę czasu, aby nadrobić zaległości. Usługa, która uruchamia te zapytania, uruchomi jedno zapytanie, zaczeka na jego nadrobienie, a następnie uruchom inne, zaczeka itd. Jeśli jesteśmy w stanie przyspieszyć replikację, możemy zwiększyć częstotliwość uruchamiania tych zapytań
Nick

1
@ stefan-lasiewski Tak - Jeśli nic nie zatrzyma replikacji, to oczywiście nie zostanie opóźniona. Podstawowym problemem jest to, że szybkość replikacji stanowi wąskie gardło w zwiększaniu liczby zapisów w systemie głównym. Jeśli nadrobienie 1 sekundy zajmuje 3.3s, oznacza to, że replikacja zapisuje dane 4.3s w 3.3s, lub jest w stanie replikować się tylko z 130% szybkości przychodzących danych. Szukam przynajmniej podwójnego zapisu obciążenie na tym serwerze.
Nick

Odpowiedzi:


4

Wow, masz strasznie mocny sprzęt do rozwiązania tego problemu. Nie ma o wiele więcej do zaoferowania pod względem sprzętowym, z wyjątkiem aktualizacji do być może procesorów Sandy / Ivy Bridge w celu uzyskania o 20-50% lepszej wydajności z wyszukiwań Btree itp.

Pamiętaj, że moją mocną stroną jest Innodb, więc idę

  1. Zignoruj ​​to, co robisz, i zachowuj się, jakby to nie miało znaczenia.
  2. Załóżmy, że ten problem jest wystarczającym impulsem, aby zmusić Cię do aktualizacji. Tak, to aktualizacja.

Innodb może pomóc w pełni wykorzystać całą tę pamięć, przechowując te często używane wiersze w puli buforów. Możesz dostroić go tak, aby był tak duży, jak chcesz (powiedzmy 80% pamięci), a świeże odczyty / zapisy pozostaną w pamięci, dopóki nie będzie trzeba zepchnąć ich na dysk, aby zrobić więcej miejsca na najnowsze dane. W pamięci jest o rząd wielkości szybszy niż twoje FusionIO.

Istnieje wiele innych funkcji Innodb, takich jak hasze adaptacyjne, mechanizmy automatycznego blokowania itp., Które mogą być dobrodziejstwem dla twojego środowiska. Ty jednak znasz swoje dane lepiej niż ja.

W świecie innodb dobrym rozwiązaniem krótkoterminowym jest optymalizacja twojego slave'a - czy naprawdę potrzebujesz każdego indeksu swojego slave'a, który masz na swoim masterie? Indeksy są kulą i łańcuchem przy wstawianiu / aktualizowaniu / usuwaniu, NAWET z kartami Fusion IO. IOPS to nie wszystko. Procesory mostkowe Sandy / Ivy mają znacznie lepszą przepustowość pamięci i wydajność obliczeniową - mogą znacznie zmienić Westmeres, które masz teraz. (Wykres 20-50% ogółem). Usuń wszystkie indeksy, których nie potrzebujesz na slave!

Po drugie, i prawie na pewno dotyczy tylko innodb, jest to, że mk-prefetch może wiedzieć, które aktualizacje i zanim slave je zapisze. Pozwala to mk-prefetch na uruchomienie najpierw zapytania do odczytu, tym samym zmuszając dane do zapisania w pamięci do czasu, gdy pojedyncza replika uruchomi zapytanie do zapisu. Oznacza to, że dane są w pamięci, a nie w fusionIO, co oznacza szybki wzrost wydajności o rząd wielkości. To ogromna różnica, więcej niż można się spodziewać. Wiele firm używa tego jako stałego rozwiązania. Dowiedz się więcej, sprawdzając Percona Toolkit .

Po trzecie, a co najważniejsze, po przejściu na Innodb, zdecydowanie sprawdź Tokutek. Ci faceci mają niesamowicie niesamowite rzeczy, które przewyższają wydajność Innodb w zakresie zapisu / aktualizacji / usuwania. Mówią o poprawie szybkości replikacji jako jednej z kluczowych korzyści, a na podstawie ich benchmarków można zobaczyć, dlaczego szalone IOPS Fusions nadal nie pomogą ci w przypadku Btrees . (Uwaga: nie są przeze mnie niezależnie weryfikowane). Używają drop-in zamiennego indeksu btree, który, choć o wiele bardziej złożony, poprawia wiele algorytmicznych ograniczeń prędkości indeksów btree.

Właśnie zastanawiam się nad przyjęciem Tokutka. Jeśli zwolnią tyle prędkości zapisu, to mogę dodać więcej indeksów. Ponieważ kompresują dane i indeksy w tak wspaniałych proporcjach (podają 25x), nie płacisz nawet (wydajności, konserwacji) ceny za zwiększone dane. Płacisz jednak ($) za ich silnik, 2500 $ rocznie za wstępnie skompresowane GB, IIRC. Mają zniżki, jeśli masz replikowane dane, ale możesz nawet zainstalować Tokutek na swoim niewolniku i utrzymać swojego mistrza w niezmienionej postaci. Sprawdź szczegóły techniczne w wykładzie MIT Algoritms Open Courseware . Alternatywnie, mają mnóstwo technicznych artykułów na swoim blogu i regularne oficjalne dokumenty dla tych, którzy nie mają 1:20, aby obejrzeć wideo. Wierzę, że ten film podaje również formułę Big-O określającą szybkość odczytywania. Ja mamzakładać, że odczyty są wolniejsze (zawsze istnieje kompromis!), ale formuła jest zbyt skomplikowana, abym mógł ocenić, ile. Twierdzą, że jest mniej więcej taki sam, ale wolałbym zrozumieć matematykę (mało prawdopodobne!). Być może znajdziesz się w lepszej sytuacji, aby to odkryć niż ja.

Ps Nie jestem związany z Tokutkiem, nigdy nie prowadziłem ich produktu i nawet nie wiedzą, że na nie patrzę.

Aktualizacja :

Widzę, że masz kilka innych pytań na tej stronie i pomyślałem, że włączyłem:

Po pierwsze, pobieranie wstępne slave prawie na pewno nie będzie działać dla myisam, chyba że masz wyjątkowe środowisko. Wynika to głównie z tego, że pobieranie wstępne blokuje te tabele, do których zamierzasz pisać, lub w wątku podrzędnym blokuje się tabelę, której potrzebuje demon pobierania wstępnego. Jeśli Twoje tabele są wyjątkowo dobrze zrównoważone pod kątem replikacji, a różne tabele są zapisywane w trybie rundy, może to działać - ale pamiętaj, że jest to bardzo teoretyczne. Książka „High Performance Mysql” zawiera więcej informacji w sekcji „Problemy z replikacją”.

Po drugie, prawdopodobnie twój niewolnik ma obciążenie 1,0-1,5, może być wyższy, jeśli masz uruchomione inne procy lub zapytania, ale poziom podstawowy wynosi 1,0. Oznacza to, że prawdopodobnie jesteś związany z procesorem, co jest prawdopodobne w przypadku FusionIO na pokładzie. Jak wspomniałem wcześniej, Sandy / Ivy Bridge da nieco więcej mocy, ale prawdopodobnie nie wystarczy, aby przejść przez trudniejsze czasy przy minimalnym opóźnieniu. Jeśli obciążenie tego urządzenia podrzędnego jest w większości tylko do zapisu (tj. Niewiele odczytów), twój procesor prawie na pewno spędza czas na obliczaniu pozycji dla wstawiania / usuwania btree. To powinno wzmocnić mój punkt powyżej dotyczący usuwania niekrytycznych indeksów - zawsze możesz je ponownie dodać później. Wyłączenie hyperthreadingu nie będzie działać, więcej procesora nie jest twoim wrogiem. Gdy przekroczysz 32 GB pamięci RAM, powiedzmy 64 GB, musisz się martwić dystrybucją pamięci RAM, ale nawet wtedy objawy są różne.

Na koniec, i co najważniejsze (nie pomijaj tej części;)), zakładam, że korzystasz teraz z RBR (replikacja oparta na wierszach), ponieważ wspomniałeś o trywialnym wzroście wydajności podczas przełączania go również. Jednak może istnieć sposób na uzyskanie jeszcze większej wydajności. Błąd MySQL 53375 może się pojawić, jeśli tabele są replikowane bez klucza podstawowego. Urządzenie podrzędne nie jest w zasadzie wystarczająco inteligentne, aby używać niczego poza kluczem podstawowym, więc brak jednego zmusza wątek replikacji do wykonania pełnego skanowania tabeli dla każdej aktualizacji. Naprawą jest po prostu dodanie łagodnego, zastępczego automatycznego klucza inkrementującego. Zrobiłbym to tylko, gdyby stół był duży (powiedzmy kilka dziesiątek tysięcy lub więcej wierszy). Oczywiście wiąże się to z kosztem posiadania innego indeksu na stole, co podnosi cenę płaconą za procesor. Zauważ, że jest bardzo mało teoretycznych argumentów przeciwko temu, ponieważ InnoDB dodaje jeden za kulisami, jeśli nie. Fantom nie jest jednak użyteczną obroną przeciwko 53375. Wolfram również może rozwiązać ten problem, ale musisz mieć pewność, że używając Wolfram masz proste kodowanie. Ostatnim razem, gdy grałem z nim, zginąłby okropnie, gdy każdy ciąg inny niż UTF8 wymagał replikacji. To czas, kiedy się poddałem.


Dziękuję bardzo za poświęcony czas! Naprawdę doceniam informacje, które tu podałeś. Przejście do InnoDB było czymś, co rozważaliśmy od dłuższego czasu, głównie ze względu na zalety blokowania na poziomie wiersza. To daje mi trochę do myślenia. Dzięki jeszcze raz.
Nick

Wow, to jest naprawdę genialna analiza mysql :)
Kevin

4

nie jest to odpowiedź, ale można rozważyć zastosowanie replikatora wolframu i jego komercyjnych produktów w celu uzyskania większej elastyczności. czy wąskim gardłem jest wykorzystanie 100% procesora na pojedynczym rdzeniu?


Dzięki! To ciekawe rozwiązanie, choć trochę waham się przed podłączeniem oprogramowania innych firm do MySQL. W dokumentacji napisano: „Nie trzeba aktualizować, aby czekać na przyszłe wersje MySQL lub migrować do niesprawdzonych alternatyw”, więc wydaje się, że jest podobny do tego, co MySQL 5.6 będzie obsługiwał. Czy masz jakieś doświadczenie z Tungsten Replicator?
Nick

nie, po prostu wiedz, że współpracuje z nimi renomowany współpracownik ekosystemu mysql [ datacharmer.blogspot.com ]. co powiesz na wąskie gardło - czy jesteś pewien, że to obciążenie jednego rdzenia jest czynnikiem ograniczającym?
pQd

Dzięki za informację. RE: czynnik ograniczający, nie, wcale nie jestem pewien. Nie sądzę, że to I / O, ponieważ iostat informuje, że Fusion ioDrive wykonuje <10 MB / s zapisów. Jestem prawie pewien, że urządzenie jest w stanie znacznie więcej. Z drugiej strony zawsze jest 1, a sporadycznie 1 dodatkowy rdzeń, który jest ustalony na 100%, podczas gdy inne są bezczynne. Co z wyłączeniem hiperwątkowości?
Nick

@Nick - przepraszam, nie mogę poradzić na temat hiperwątkowości. ale spróbuj ... także - spróbuj zainstalować munin lub kaktusy z szablonami mysql i spójrz bardziej szczegółowo, co się dzieje.
pQd

Sprawdź ten post w Ciągłych ludziach : scale-out-blog.blogspot.ca/2011/10/... Cytat: „Ogólnie rzecz biorąc, możemy śmiało powiedzieć, że natywna replikacja jednowątkowa jest prawdopodobnie niemożliwa do wykonania w ramach operacji wejścia / wyjścia sprawa bez przechodzenia do jakiejś kombinacji dysków SSD i / lub pobierania wstępnego slave. ”
HTTP500

2

Więc jeśli wykonujesz kopie zapasowe na slave .. i używasz tabel myiasm ... blokujesz tabele, aby wykonać kopie zapasowe, aby zapobiec uszkodzeniu. Tak więc replikacja nie może działać, dopóki tworzenie kopii zapasowej nie zostanie zakończone.


Absolutnie. Regularnie blokujemy tabele dla kopii zapasowych lub długich zapytań, ale problem leży w szybkości replikacji po wznowieniu wątku We / Wy. Szacuję, że replikuje się tylko przy 130% prędkości przychodzących danych, co ogranicza, o ile bardziej możemy skalować tę konfigurację, chyba że poprawimy szybkość replikacji. Czy to ma sens?
Nick
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.