Migracja 110 mln wierszy z SQL Server do Elasticsearch

Opublikowane przez admin w dniu

Cześć. Dziś postaram się opisać, jak zmigrowałem dosyć sporą ilość danych (110 mln wierszy) z SQL Servera do Elasticsearcha. Zacznijmy od konfiguracji ES. Użyłem do tego Kibany.

Tworzymy index:

Dla zwiększenia wydajności dodawania nowych dokumentów warto ustawić dwie zmienne:

refresh_interval odpowiada za aktualizacje informacji o naszym indexie, natomiast number_of_replicas odpowiada za repliki naszych primary shards. Na czas migracji warto z tego zrezygnować.

Domyślnie Elastisearch ustawia wielkość sterty jvm’a na 1 GB. Produkcyjnie w większości przypadków będzie to nie wystarczające. Aby to zmienić, możemy przejść do pliku \config\jvm.options i ustawić dwa następujące parametry:

Można to także zrobić przez zmienne środowiskowe, np. w Dockerze.

Jednak może to czasami nie zadziałać, i tak było także w moim przypadku, o czym więcej tutaj. Używam Dockera na Windowsie, więc może to dlatego 😀

 

Ok to tyle, jeśli chodzi o samego Elasticsearcha. Teraz zajmijmy się pobraniem danych z SQL Servera. Jednym z narzędzi, które mogłoby w tym pomóc to logstash. Jednak napotkałem z nim jeden główny problem. Moje rozwiązanie pobierania danych z SQL’a potrzebuje wiele workerów (wątków), które muszą współdzielić bezpiecznie stan, a nie znalazłem rozwiązania jak to zrobić w logstash. Zdecydowałem się więc napisać kawałek kodu, który pobierze i wyśle mi potrzebne dane. Po stronie C# użyłem dobrze znanego ORM’a – Dappera. Funkcjonalność SQL, która pierwsza przychodzi mi do głowy przy potrzebie podzieleniu danych to użycie OFFSET FETCH. Załóżmy, że na raz chcemy pobrać 5000 wierszy, wtedy zapytanie mogłoby wyglądać tak:

Wygląda spoko, jednak przy dużej ilości danych zapytanie to będzie wąskim gardłem. Dlaczego? Spójrzmy na plan zapytania:

OFFSET FETCH generuje skan indeksu. Skan dotyczy tylko offsetu i pobieranych wierszy, a nie całej tabeli. Czyli dla zapytania:

zostanie odczytanych 5005000 wierszy.

Prowadzi to do sytuacji, że im dalej w las tym zapytania będą bardziej kosztowne. Na moim laptopie powyższe zapytanie trwało 12 sekund. Jak to zrobić szybciej? Wykorzystam do tego indeks klastrowany i operator TOP, czyli zapytanie będzie wyglądało tak:

Ze względu na budowę indeksu klastrowanego takie zapytanie będzie bardzo wydajne nawet dla bardzo dużej ilości danych. Oczywiście, jeśli indeks jest założony na kolumnę po której wyszukujemy. Problem w tym, że nasze identyfikatory nie muszą być idealnie odwzorowane na liczbę wierszy, czyli np. zapytanie:

nie musi wcale zwracać 100 wierszy. Aby podzielić tabelę na równe paczki musiałem wygenerować listę, gdzie elementami tej listy będą kolejne identyfikatory co 5000 wierszy, czyli zaczynając od zera:

dostanę 5000 wierszy, a następnym Id będzie ostatni Id z poprzedniej paczki. Lista wyglądała mniej więcej tak:

Wygenerowanie takiej listy dla 110 mln wierszy było bardzo szybkie. Trwało kilka minut.

Ok, gdy mamy już gotową listę kolejnych Id, możemy zabrać się za wysyłanie danych do Elasticsearcha. Użyłem do tego biblioteki NEST. Powyższą listę Id załadowałem do kolekcji bezpiecznej na wątki:

Kod migrujący wygląda tak:

Do ponawiania requestów użyłem Polly. Warto tutaj dodać, że biblioteka NEST przy dodawaniu nowych dokumentów stara się zmapować kolumnę o nazwie Id w naszym modelu na Id dokumentu w Elasticsearchu. Ta sytuacja może doprowadzić do dużych spowolnień, ponieważ Elasticsearch będzie musiał najpierw sprawdzić, czy dokument o takim Id istnieje, a dopiero potem go ewentualnie dodać. Warto w takim wypadku zmienić nazwę kolumny z Id na inną i pozwolić Elasticsearch na auto-generowanie identyfikatorów dla dokumentów.

I to tyle, jeśli chodzi o bebechy tej migracji. Przy tym podejściu udało mi się zmigrować 110 mln wierszy w 2 godziny 30 minut. Co daje średnią na poziomie 12 tyś./s. Wynik ten mógłby zostać poprawiony na lepszym serwerze, ponieważ u mnie na laptopie procesor i pamieć była wysycona do granic możliwości 🙂

Co ciekawe, ta tabela w  SQL Serverze zajmuje 181.5 GB wraz z indeksem, a w Elasticsearchu 95.5 GB. Na koniec warto przywrócić ustawienia, które zmieniliśmy na początku:

Źródła: https://www.elastic.co/guide/en/elasticsearch/reference/current/tune-for-indexing-speed.html