Sugeruję przeczytanie transkrypcji raportu Władimira Sitnikowa z początku 2016 r. „PostgreSQL i JDBC wyciskają cały sok”
Dzień dobry Nazywam się Władimir Sitnikov. Pracuję w NetCracker od 10 lat. A najbardziej zależy mi na produktywności. Wszystko, co jest związane z Javą, wszystko, co jest związane z SQL, jest tym, co kocham.
A dzisiaj opowiem o tym, co spotkaliśmy w firmie, gdy zaczęliśmy używać PostgreSQL jako serwera baz danych. Pracujemy głównie w Javie. Ale to, co dzisiaj wam opowiem, nie dotyczy tylko Javy. Jak pokazała praktyka, dzieje się tak również w innych językach.
Będziemy rozmawiać:
- o próbkowaniu danych.
- O zapisywaniu danych.
- A także o wydajności.
- I o podwodnych grabiach, które tam są zakopane.
Zacznijmy od prostego pytania. Wybieramy jeden wiersz z tabeli na podstawie klucza podstawowego.
Baza danych znajduje się na tym samym hoście. A całe to rolnictwo zajmuje 20 milisekund.
Te 20 milisekund to dużo. Jeśli masz 100 takich żądań, to tracisz czas na sekundę na przewijanie tych żądań, czyli marnujemy czas.
Nie lubimy tego robić i patrzymy, co baza nam za to oferuje. Baza danych oferuje nam dwie możliwości realizacji zapytań.
Pierwsza opcja to prosta prośba. Co w tym dobrego? Fakt, że to bierzemy i wysyłamy, i nic więcej.
Baza danych posiada również zaawansowane zapytanie, które jest trudniejsze, ale bardziej funkcjonalne. Możesz osobno wysłać żądanie analizy, wykonania, powiązania zmiennych itp.
Super rozszerzone zapytanie to coś, czego nie omówimy w bieżącym raporcie. Być może chcemy czegoś z bazy danych i istnieje lista życzeń, która została w jakiejś formie uformowana, czyli tego właśnie chcemy, ale jest to niemożliwe ani teraz, ani w przyszłym roku. Więc po prostu to nagraliśmy i będziemy potrząsać głównymi ludźmi.
Możemy zrobić proste zapytanie i rozszerzone zapytanie.
Co jest specjalnego w każdym podejściu?
Proste zapytanie jest dobre do jednorazowego wykonania. Raz zrobione i zapomniane. No i problem w tym, że nie obsługuje binarnego formatu danych, czyli nie nadaje się do niektórych systemów o dużej wydajności.
Zapytanie rozszerzone – pozwala zaoszczędzić czas na analizowaniu. Tak zrobiliśmy i zaczęliśmy stosować. To naprawdę nam pomogło. Oszczędności można uzyskać nie tylko na parsowaniu. Istnieją oszczędności na transferze danych. Przesyłanie danych w formacie binarnym jest znacznie wydajniejsze.
Przejdźmy do ćwiczeń. Tak wygląda typowa aplikacja. Może to być Java itp.
Stworzyliśmy oświadczenie. Wykonał polecenie. Utworzono blisko. Gdzie tu jest błąd? Jaki jest problem? Bez problemu. Tak jest napisane we wszystkich książkach. Tak to powinno być napisane. Jeśli chcesz maksymalnej wydajności, napisz w ten sposób.
Ale praktyka pokazała, że to nie działa. Dlaczego? Ponieważ mamy metodę „zamknij”. A kiedy już to zrobimy, z punktu widzenia bazy danych okazuje się, że jest to jak palacz pracujący z bazą danych. Powiedzieliśmy: „PARSE EXECUTE DEALLOCATE”.
Po co to całe dodatkowe tworzenie i rozładowywanie instrukcji? Nikt ich nie potrzebuje. Ale to, co zwykle dzieje się w „PreparedStatements”, polega na tym, że kiedy je zamykamy, zamykają one wszystko w bazie danych. To nie jest to, czego chcemy.
Chcemy, niczym zdrowi ludzie, pracować z bazą. Nasze oświadczenie przyjęliśmy i przygotowaliśmy raz, a następnie realizujemy je wielokrotnie. Tak naprawdę wiele razy – zdarza się to raz w życiu aplikacji – były one analizowane. Używamy tego samego identyfikatora instrukcji w różnych RESTach. To jest nasz cel.
Jak możemy to osiągnąć?
To bardzo proste – nie ma potrzeby zamykania wyciągów. Zapisujemy to tak: „przygotować”, „wykonać”.
Jeśli uruchomimy coś takiego, to jasne jest, że coś gdzieś się przeleje. Jeśli nie jest jasne, możesz spróbować. Napiszmy benchmark wykorzystujący tę prostą metodę. Utwórz oświadczenie. Uruchamiamy go na jakiejś wersji sterownika i stwierdzamy, że dość szybko ulega awarii wraz z utratą całej posiadanej pamięci.
Oczywiste jest, że takie błędy można łatwo skorygować. Nie będę o nich rozmawiać. Ale powiem, że nowa wersja działa znacznie szybciej. Metoda jest głupia, ale jednak.
Jak pracować poprawnie? Co musimy w tym celu zrobić?
W rzeczywistości aplikacje zawsze zamykają instrukcje. We wszystkich książkach mówią, żeby to zamknąć, w przeciwnym razie pamięć wycieknie.
A PostgreSQL nie wie, jak buforować zapytania. Konieczne jest, aby każda sesja tworzyła tę pamięć podręczną dla siebie.
Nie chcemy też tracić czasu na analizowanie.
I jak zwykle mamy dwie możliwości.
Pierwsza opcja jest taka, że bierzemy to i mówimy, że zawińmy wszystko w PgSQL. Jest tam skrytka. Buforuje wszystko. To okaże się świetne. Widzieliśmy to. Mamy 100500 żądań. Nie działa. Nie zgadzamy się na ręczne przekształcanie żądań w procedury. Nie? Nie.
Mamy drugą opcję - weźmy i pokrójmy sami. Otwieramy źródła i zaczynamy wycinać. Widzieliśmy i widzieliśmy. Okazało się, że nie jest to takie trudne.
Pojawiło się to w sierpniu 2015 r. Teraz jest bardziej nowoczesna wersja. I wszystko jest świetne. Działa na tyle dobrze, że w aplikacji nic nie zmieniamy. I nawet przestaliśmy myśleć w stronę PgSQL, czyli to nam w zupełności wystarczyło, aby zredukować wszystkie koszty ogólne niemal do zera.
W związku z tym instrukcje przygotowane przez serwer są aktywowane przy piątym wykonaniu, aby uniknąć marnowania pamięci w bazie danych na każde jednorazowe żądanie.
Można zapytać – gdzie są liczby? Co otrzymujesz? I tutaj nie będę podawać liczb, bo każda prośba ma swoją.
Nasze zapytania były takie, że analizowanie zapytań OLTP zajęło około 20 milisekund. Wykonanie trwało 0,5 milisekundy, a parsowanie 20 milisekund. Zapytanie – 10 KiB tekstu, 170 linijek planu. To jest żądanie OLTP. Żąda 1, 5, 10 linii, czasem więcej.
Ale w ogóle nie chcieliśmy marnować 20 milisekund. Zmniejszyliśmy to do 0. Wszystko jest wspaniałe.
Co możesz stąd zabrać? Jeśli masz Javę, bierz nowoczesną wersję sterownika i ciesz się.
Jeśli mówisz innym językiem, to pomyśl – może Ty też tego potrzebujesz? Bo z punktu widzenia języka finalnego, jeśli np. PL 8 lub masz LibPQ, to nie jest dla ciebie oczywiste, że spędzasz czas nie na wykonaniu, a na parsowaniu i to warto sprawdzić. Jak? Wszystko jest bezpłatne.
Z wyjątkiem błędów i pewnych osobliwości. I teraz o nich porozmawiamy. Większość będzie dotyczyć archeologii przemysłowej, tego, co znaleźliśmy, na co natknęliśmy się.
Jeśli żądanie jest generowane dynamicznie. Zdarza się. Ktoś skleja ze sobą ciągi znaków, w wyniku czego powstaje zapytanie SQL.
Dlaczego jest zły? Jest to złe, ponieważ za każdym razem otrzymujemy inny ciąg znaków.
I hashCode tego innego ciągu należy przeczytać ponownie. To naprawdę zadanie obciążające procesor – znalezienie długiego tekstu żądania nawet w istniejącym hashu nie jest takie proste. Dlatego wniosek jest prosty – nie generuj żądań. Zapisz je w jednej zmiennej. I raduj się.
Następny problem. Typy danych są ważne. Istnieją ORM, które mówią, że nie ma znaczenia, jaki jest rodzaj NULL, niech będzie jakiś. Jeżeli Int, to mówimy setInt. A jeśli NULL, niech zawsze będzie to VARCHAR. A co to za różnica, ile w końcu jest NULL? Sama baza danych wszystko zrozumie. A to zdjęcie nie działa.
W praktyce baza danych w ogóle się tym nie przejmuje. Jeśli za pierwszym razem powiedziałeś, że jest to liczba, a za drugim razem, że jest to VARCHAR, wówczas niemożliwe jest ponowne użycie instrukcji przygotowanych przez serwer. I w tym przypadku musimy odtworzyć nasze oświadczenie.
Jeśli wykonujesz to samo zapytanie, upewnij się, że typy danych w kolumnie nie są pomieszane. Musisz uważać na NULL. Jest to częsty błąd, który pojawiał się po rozpoczęciu korzystania z BasedStatements
OK, włączone. Może zabrali kierowcę. A produktywność spadła. Sprawy się pogorszyły.
Jak to się stało? Czy jest to błąd czy funkcja? Niestety nie udało się ustalić, czy jest to błąd, czy funkcja. Istnieje jednak bardzo prosty scenariusz odtworzenia tego problemu. Zupełnie niespodziewanie zaatakowała nas. A polega na pobieraniu próbek dosłownie z jednej tabeli. Takich próśb było oczywiście więcej. Z reguły zawierały dwa lub trzy stoły, ale jest taki scenariusz odtwarzania. Pobierz dowolną wersję ze swojej bazy danych i zagraj w nią.
Chodzi o to, że mamy dwie kolumny, z których każda jest indeksowana. W jednej kolumnie NULL znajduje się milion wierszy. Druga kolumna zawiera tylko 20 linii. Kiedy wykonujemy bez powiązanych zmiennych, wszystko działa dobrze.
Jeśli zaczniemy wykonywać z powiązanymi zmiennymi, tj. wykonamy znak „?” lub „1 $” za naszą prośbę, co ostatecznie otrzymamy?
Pierwsza egzekucja jest zgodna z oczekiwaniami. Drugi jest trochę szybszy. Coś zostało zapisane w pamięci podręcznej. Trzeci, czwarty, piąty. Potem huk - i coś w tym stylu. A najgorsze jest to, że dzieje się to przy szóstej egzekucji. Kto wiedział, że trzeba wykonać dokładnie sześć egzekucji, aby zrozumieć, jaki był faktyczny plan egzekucji?
Kto jest winny? Co się stało? Baza danych zawiera optymalizację. I wydaje się, że jest zoptymalizowany dla przypadku ogólnego. I odpowiednio, w pewnym momencie przechodzi na plan ogólny, który niestety może okazać się inny. Może się okazać, że będzie tak samo, a może będzie inaczej. Istnieje pewien rodzaj wartości progowej, która prowadzi do takiego zachowania.
Co możesz z tym zrobić? Tutaj oczywiście trudniej jest cokolwiek założyć. My stosujemy proste rozwiązanie. To jest +0, OFFSET 0. Na pewno znasz takie rozwiązania. Po prostu bierzemy to i dodajemy „+0” do żądania i wszystko jest w porządku. Pokażę ci później.
Jest jeszcze jedna opcja - spójrz uważniej na plany. Programista musi nie tylko napisać prośbę, ale także 6 razy powiedzieć „wyjaśnij analizę”. Jeśli będzie 5, to nie zadziała.
Jest jeszcze trzecia opcja - napisz list do hakerów pgsql. Napisałem jednak, że nie jest jeszcze jasne, czy jest to błąd, czy funkcja.
Zastanawiamy się, czy jest to błąd, czy funkcja, ale naprawmy to. Weźmy naszą prośbę i dodajmy „+0”. Wszystko w porządku. Dwa symbole i nie musisz nawet myśleć o tym, jak to jest i co to jest. Bardzo prosta. Po prostu zabroniliśmy bazie danych używania indeksu w tej kolumnie. Nie mamy indeksu na kolumnie „+0” i tyle, baza danych nie korzysta z indeksu, wszystko jest w porządku.
To jest zasada 6 wyjaśniająca. Teraz w obecnych wersjach musisz to zrobić 6 razy, jeśli masz powiązane zmienne. Jeśli nie masz powiązanych zmiennych, właśnie to robimy. I w końcu to właśnie ta prośba kończy się niepowodzeniem. To nie jest trudna sprawa.
Wydawałoby się, ile jest możliwe? Błąd tu, błąd tam. Właściwie błąd jest wszędzie.
Przyjrzyjmy się bliżej. Na przykład mamy dwa schematy. Schemat A z tabelą S i schemat B z tabelą S. Zapytanie – wybierz dane z tabeli. Co będziemy mieli w tym przypadku? Będziemy mieli błąd. Będziemy mieć wszystkie powyższe. Zasada jest taka – błąd jest wszędzie, będziemy mieli wszystkie powyższe.
Teraz pytanie brzmi: „Dlaczego?” Wydaje się, że istnieje dokumentacja mówiąca, że jeśli mamy schemat, to istnieje zmienna „search_path”, która mówi nam, gdzie szukać tabeli. Wydawałoby się, że istnieje zmienna.
Jaki jest problem? Problem polega na tym, że instrukcje przygotowane przez serwer nie podejrzewają, że ścieżka wyszukiwania może zostać przez kogoś zmieniona. Wartość ta pozostaje niejako stała dla bazy danych. A niektóre części mogą nie nabrać nowych znaczeń.
Zależy to oczywiście od wersji, na której testujesz. Zależy od tego, jak poważnie różnią się Twoje tabele. Wersja 9.1 po prostu wykona stare żądania. Nowe wersje mogą wykryć błąd i poinformować Cię o błędzie.
Jak to leczyć? Jest prosty przepis – nie rób tego. Nie ma potrzeby zmiany ścieżki wyszukiwania podczas działania aplikacji. W przypadku zmiany lepiej utworzyć nowe połączenie.
Można dyskutować, czyli otwierać, dyskutować, dodawać. Może uda nam się przekonać twórców baz danych, że gdy ktoś zmieni wartość, baza danych powinna poinformować o tym klienta: „Spójrz, twoja wartość została tutaj zaktualizowana. Może trzeba zresetować instrukcje i utworzyć je na nowo?” Teraz baza danych zachowuje się w tajemnicy i w żaden sposób nie raportuje, że stwierdzenia uległy zmianie gdzieś w środku.
I jeszcze raz podkreślę - jest to coś nietypowego dla Javy. To samo zobaczymy w PL/pgSQL jeden do jednego. Ale zostanie tam odtworzony.
Spróbujmy dokonać większej selekcji danych. Wybieramy i wybieramy. Mamy tabelę z milionem wierszy. Każda linia to kilobajt. Około gigabajta danych. I mamy pamięć roboczą w maszynie Java o pojemności 128 megabajtów.
My, zgodnie z zaleceniami we wszystkich książkach, używamy przetwarzania strumieniowego. Oznacza to, że otwieramy zestaw wyników i stopniowo czytamy stamtąd dane. Czy to zadziała? Czy wypadnie z pamięci? Poczytasz trochę? Zaufajmy bazie danych, zaufajmy Postgresowi. Nie wierzymy w to. Czy wypadniemy z OutOFMemory? Kto doświadczył OutOfMemory? Komu udało się to później naprawić? Komuś udało się to naprawić.
Jeśli masz milion wierszy, nie możesz po prostu wybierać. Wymagane jest PRZESUNIĘCIE/LIMIT. Kto jest za tą opcją? A kto jest zwolennikiem zabawy z autoCommit?
Tutaj jak zwykle najbardziej nieoczekiwana opcja okazuje się słuszna. A jeśli nagle wyłączysz autoCommit, to pomoże. Dlaczego? Nauka o tym nie wie.
Ale domyślnie wszyscy klienci łączący się z bazą danych Postgres pobierają całe dane. PgJDBC nie jest pod tym względem wyjątkiem; wybiera wszystkie wiersze.
Istnieje wariacja na temat motywu FetchSize, tzn. można na poziomie osobnej wypowiedzi powiedzieć, że tutaj należy wybierać dane po 10, 50. Ale to nie zadziała, dopóki nie wyłączymy autoCommit. Wyłączyłem autoCommit - zaczyna działać.
Jednak przeglądanie kodu i ustawianie setFetchSize wszędzie jest niewygodne. Dlatego dokonaliśmy ustawienia, które będzie wskazywało wartość domyślną dla całego połączenia.
To właśnie powiedzieliśmy. Parametr został skonfigurowany. I co dostaliśmy? Jeśli wybierzemy małe kwoty, jeśli na raz wybierzemy np. 10 wierszy, to mamy bardzo duże koszty ogólne. Dlatego wartość tę należy ustawić na około sto.
Idealnie byłoby oczywiście, gdybyś nadal musiał nauczyć się ograniczać to w bajtach, ale przepis jest następujący: ustaw defaultRowFetchSize na więcej niż sto i bądź szczęśliwy.
Przejdźmy do wstawiania danych. Wkładanie jest łatwiejsze, istnieją różne opcje. Na przykład WSTAW, WARTOŚCI. To dobra opcja. Możesz powiedzieć „INSERT SELECT”. W praktyce jest to samo. Nie ma różnicy w wydajności.
Książki mówią, że musisz wykonać instrukcję Batch, książki mówią, że możesz wykonywać bardziej złożone polecenia za pomocą kilku nawiasów. A Postgres ma cudowną funkcję - możesz wykonać COPY, czyli zrobić to szybciej.
Jeśli to zmierzysz, możesz ponownie dokonać ciekawych odkryć. Jak chcemy, żeby to działało? Chcemy nie analizować i nie wykonywać niepotrzebnych poleceń.
W praktyce TCP nam na to nie pozwala. Jeżeli klient jest zajęty wysyłaniem żądania, to baza danych nie odczytuje żądań przy próbach przesłania nam odpowiedzi. Efekt końcowy jest taki, że klient czeka, aż baza danych odczyta żądanie, a baza danych czeka, aż klient odczyta odpowiedź.
Dlatego klient jest zmuszony okresowo wysyłać pakiet synchronizacji. Dodatkowe interakcje sieciowe, dodatkowa strata czasu.
A im więcej ich dodamy, tym będzie gorzej. Sterownik jest dość pesymistyczny i dodaje je dość często, mniej więcej raz na 200 linii, w zależności od rozmiaru linii itp.
Zdarza się, że poprawisz tylko jedną linijkę i wszystko przyspieszy 10 razy. Zdarza się. Dlaczego? Jak zwykle, taka stała została już gdzieś użyta. A wartość „128” oznaczała, że nie należy używać przetwarzania wsadowego.
Dobrze, że nie zostało to uwzględnione w oficjalnej wersji. Odkryto przed rozpoczęciem wydawania. Wszystkie znaczenia, które nadaję, opierają się na współczesnych wersjach.
Spróbujmy. Mierzymy w prosty sposób InsertBatch. Mierzymy InsertBatch wielokrotnie, czyli to samo, ale wartości jest wiele. Trudne posunięcie. Nie każdy może to zrobić, ale jest to taki prosty ruch, znacznie łatwiejszy niż KOPIUJ.
Możesz zrobić KOPIUJ.
Można to zrobić na strukturach. Zadeklaruj domyślny typ użytkownika, przekaż tablicę i INSERT bezpośrednio do tabeli.
Jeśli otworzysz link: pgjdbc/ubenchmsrk/InsertBatch.java, to ten kod będzie dostępny na GitHubie. Możesz zobaczyć, jakie żądania są tam generowane. To nie ma znaczenia.
Uruchomiliśmy. Pierwszą rzeczą, z której zdaliśmy sobie sprawę, było to, że nieużywanie partii jest po prostu niemożliwe. Wszystkie opcje dozowania są zerowe, czyli czas realizacji jest praktycznie zerowy w porównaniu do jednorazowej realizacji.
Wstawiamy dane. To bardzo prosty stół. Trzy kolumny. I co tu widzimy? Widzimy, że wszystkie trzy opcje są z grubsza porównywalne. A COPY jest oczywiście lepsza.
To jest moment, w którym wstawiamy kawałki. Kiedy powiedzieliśmy, że jedna wartość WARTOŚCI, dwie wartości WARTOŚCI, trzy wartości WARTOŚCI lub wskazaliśmy 10 z nich oddzielonych przecinkiem. To jest teraz tylko poziome. 1, 2, 4, 128. Można zauważyć, że wkładka Batch, narysowana na niebiesko, sprawia, że czuje się znacznie lepiej. Oznacza to, że jeśli wstawisz pojedynczo lub nawet cztery na raz, efekt będzie dwa razy lepszy, po prostu dlatego, że upchnęliśmy trochę więcej w WARTOŚCIACH. Mniej operacji EXECUTE.
Używanie COPY na małych wolumenach jest wyjątkowo mało obiecujące. Na dwóch pierwszych nawet nie rysowałem. Idą do nieba, czyli te zielone numerki za COPY.
Z funkcji COPY należy korzystać, gdy masz co najmniej sto wierszy danych. Narzut związany z otwarciem tego połączenia jest duży. I szczerze mówiąc, nie kopałem w tym kierunku. Zoptymalizowałem partię, ale nie KOPIUJ.
Co robimy potem? Próbowaliśmy tego. Rozumiemy, że musimy użyć struktur lub sprytnej kąpieli, która łączy w sobie kilka znaczeń.
Co warto wynieść z dzisiejszego raportu?
- „PreparedStatement” jest dla nas wszystkim. Daje to dużo w zakresie produktywności. Powoduje dużą klapę w maści.
- I musisz wykonać WYJAŚNIJ ANALIZĘ 6 razy.
- Musimy rozcieńczyć OFFSET 0 i sztuczki takie jak +0, aby poprawić pozostały procent naszych problematycznych zapytań.
Źródło: www.habr.com