Zachęcam do zapoznania się z transkrypcją raportu z początku 2016 roku autorstwa Władimira Sitnikowa "PostgreSQL i JDBC – wyciśnijmy z nich wszystko"
Dzień dobry! Nazywam się Władimir Sitnikow. Pracuję od 10 lat w firmie NetCracker. Głównie zajmuję się wydajnością. Wszystko, co związane z Javą, wszystko, co związane z SQL – to, co kocham.
Dziś opowiem o tym, z czym się spotkaliśmy w firmie, gdy zaczęliśmy używać PostgreSQL jako serwera baz danych. Głównie pracujemy z Javą. Ale to, co dzisiaj przedstawię, dotyczy nie tylko Javy. Jak pokazała praktyka, zdarza się to także w innych językach.
Będziemy mówić o:
- wybierać dane.
- o zapisywaniu danych.
- a także o wydajności.
- I o pułapkach, które tam są ukryte.
Zacznijmy od prostego pytania. Wybieramy jeden wiersz z tabeli według klucza podstawowego.
Baza znajduje się na tym samym hoście. I całe to zajmuje 20 milisekund.
Te 20 milisekund – to bardzo dużo. Jeśli masz takich 100 zapytań, to tracisz czas na to, aby te zapytania przejść, czyli w wirtualny sposób tracisz czas.
Nie lubimy tego robić, więc patrzymy, co baza nam oferuje. Baza oferuje nam dwie opcje wykonania zapytań.
Pierwsza opcja – to proste zapytanie. Dlaczego jest dobre? Ponieważ bierzemy je i wysyłamy, i nic więcej.
Baza ma także rozszerzone zapytanie, które jest bardziej przebiegłe, ale bardziej funkcjonalne. Można osobno wysłać zapytanie do parsowania, wykonywania, wiązania zmiennych itp.
Super rozszerzone zapytanie – to, czego nie będziemy omawiać w tej prezentacji. Może mamy coś do wyciągnięcia od bazy danych i istnieje taki katalog życzeń, który jest w jakiejś formie sformułowany, czyli to, co chcemy, ale obecnie jest to niemożliwe i w najbliższym roku. Dlatego po prostu zapisaliśmy to i będziemy chodzić, trząsąc podstawowymi ludźmi.
A to, co możemy zrobić, to proste zapytanie i rozszerzone zapytanie.
Jaka jest specyfika każdego podejścia?
Proste zapytanie dobrze używać do jednorazowego wykonania. Raz wykonane i zapomniane. I problem w tym, że nie obsługuje binarnego formatu danych, czyli do jakichś systemów o wysokiej wydajności się nie nadaje.
Rozszerzone zapytanie – pozwala zaoszczędzić czas podczas parsowania. To jest to, co zrobiliśmy i zaczęliśmy używać. To bardzo, bardzo nam pomogło. Istnieje nie tylko oszczędność na parsowaniu. Jest także oszczędność na przesyłaniu danych. Przesyłanie danych w formacie binarnym jest znacznie bardziej efektywne.
Przejdźmy do praktyki. Tak wygląda typowa aplikacja. To może być Java i tak dalej.
Stworzyliśmy statement. Wykonaliśmy polecenie. Stworzyliśmy close. Gdzie jest błąd? Jaki jest problem? Nie ma problemów. Tak jest napisane we wszystkich książkach. Tak trzeba pisać. Jeśli chcesz maksymalnej wydajności, pisz tak.
Jednak praktyka pokazała, że to nie działa. Dlaczego? Ponieważ mamy metodę „close”. I gdy to robimy, z perspektywy bazy danych wygląda to jak praca palacza z bazą danych. Powiedzieliśmy „PARSE EXECUTE DEALLOCATE”.
Po co te zbędne tworzenie i ładowanie statements? Nikt ich nie potrzebuje. Ale zwykle w PreparedStatement tak właśnie jest, kiedy je zamykamy, zamykają wszystko na bazie danych. To nie jest to, czego chcemy.
Chcemy, jak zdrowi ludzie, pracować z bazą. Raz bierzemy i przygotowujemy nasz statement, a potem wykonujemy go wiele razy. W rzeczywistości wiele razy – to jeden raz na całe życie aplikacji. I na różnych REST używamy tego samego id statementa. To jest nasz cel.
Jak możemy to osiągnąć?
Bardzo prosto – nie trzeba zamykać statements. Pisze się tak: „prepare” „execute”.
Jeśli uruchomimy coś takiego, to jasne, że gdzieś coś się przepełni. Jeśli nie jest jasne, to można zmierzyć. Weźmy i napiszmy benchmark, w którym taki prosty sposób. Tworzymy statement. Uruchamiamy na jakiejś wersji sterownika i widzimy, że dość szybko sypie z utratą całej pamięci, która nam została.
Jasne, że takie błędy łatwo naprawić. Nie będę o nich mówić. Ale powiem, że w nowej wersji działa znacznie szybciej. Metoda nieprzydatna, ale mimo to.
Jak pracować poprawnie? Co musimy w tym celu zrobić?
W rzeczywistości aplikacje zawsze zamykają statements. We wszystkich książkach piszą, aby je zamykać, w przeciwnym razie dojdzie do wycieków pamięci.
I PostgreSQL nie potrafi cache'ować zapytań. Każda sesja musi dla siebie sama tworzyć tę pamięć podręczną.
I również nie chcemy tracić czasu na parsowanie.
I jak zwykle mamy dwa warianty.
Pierwsza opcja – bierzemy i mówimy, że zróbmy wszystko w PgSQL. Tam jest pamięć podręczna. Wszystko się cache'uje. Będzie znakomicie. To przetestowaliśmy. Mamy 100500 zapytań. Nie działa. Nie zgadzamy się na ręczne przekształcanie zapytań w procedury. Nie, nie.
Mamy drugą opcję – wziąć i sami to zrealizować. Otwieramy źródła, zaczynamy działać. Działamy. Okazało się, że nie jest to tak trudne do zrobienia.
To się pojawiło w sierpniu 2015 roku. Teraz mamy już bardziej nowoczesną wersję. I wszystko działa doskonale. Działa tak dobrze, że nie wprowadzamy żadnych zmian w aplikacji. Nawet przestaliśmy rozważać PgSQL, tj. to nam wystarczyło, aby zredukować wszystkie koszty do prawie zera.
Odpowiednio, przygotowane zapytania serwera aktywują się przy piątym wykonaniu, aby nie marnować pamięci w bazie danych na każde jednorazowe zapytanie.
Można zapytać – gdzie liczby? Co otrzymujecie? I tutaj nie podam liczb, bo dla każdego zapytania są inne.
Nasze zapytania wyglądały tak, że na zapytaniach OLTP spędzaliśmy około 20 milisekund na parsowanie. Tam było 0,5 milisekundy na wykonanie, 20 milisekund na parsowanie. Zapytanie – 10 KiB tekstu, 170 rzędów planu. To zapytanie OLTP. Pyta o 1, 5, 10 rzędów, czasem więcej.
Ale zupełnie nie chcieliśmy tracić 20 milisekund. Zredukowaliśmy to do zera. Wszystko jest świetnie.
Co możecie z tego wynieść? Jeśli korzystacie z Javy, to bierzecie nowoczesną wersję sterownika i cieszycie się.
Jeśli korzystacie z jakiegoś innego języka, to pomyślcie – może to również jest dla was? Ponieważ z punktu widzenia końcowego języka, na przykład, jeśli PL 8 lub macie LibPQ, to nie jest dla was oczywiste, że tracicie czas nie na wykonanie, a na parsowanie i warto to sprawdzić. Jak? Wszystko jest darmowe.
Z wyjątkiem tego, że są błędy, jakieś szczególne przypadki. O tym właśnie teraz będziemy mówić. Większość będzie o archeologii przemysłowej, o tym, co znaleźliśmy, na co natrafiliśmy.
Jeśli zapytanie jest generowane dynamicznie. Tak się zdarza. Ktoś łączy ciągi, w rezultacie otrzymuje zapytanie SQL.
Czym to jest złe? Jest złe tym, że za każdym razem otrzymujemy inną linię.
I ta różna linia musi ponownie obliczyć hashCode. To naprawdę jest zadanie CPU – znalezienie długiego tekstu zapytania w istniejącym hashu nie jest takie proste. Dlatego prosty wniosek – nie generujcie zapytań. Przechowujcie je w jakiejś jednej zmiennej. I cieszcie się.
Następny problem. Typy danych są ważne. Są ORM-y, które mówią, że nie ma znaczenia, jaki NULL, niech będzie jakiś. Jeśli to Int, mówimy setInt. A jeśli NULL, to niech zawsze będzie VARCHAR. I co za różnica, jaki tam tak naprawdę NULL? Baza danych sama wszystko zrozumie. A taka wizja nie działa.
W praktyce bazie danych nie jest wszystko jedno. Jeśli za pierwszym razem powiedziałeś, że to liczba, a za drugim, że to VARCHAR, to niemożliwe jest ponowne użycie przygotowanych poleceń serwera. I w takim przypadku musimy na nowo stworzyć nasze polecenie.
Jeśli wykonujesz to samo zapytanie, uważaj, aby typy danych w kolumnie się nie myliły. Należy zwracać uwagę na NULL. To częsty błąd, który mieliśmy po tym, jak zaczęliśmy używać PreparedStatements.
Dobrze, włączono. Może wzięliśmy jakiś sterownik. I wydajność spadła. Wszystko stało się złe.
Jak to się dzieje? To błąd czy funkcjonalność? Niestety, nie udało się ustalić – to błąd czy funkcjonalność. Ale jest całkiem prosty scenariusz, aby odtworzyć ten problem. Całkowicie niespodziewanie nas zaskoczył. Polega na zapytaniu dosłownie z jednej tabeli. Oczywiście mieliśmy więcej takich zapytań. Zazwyczaj obejmowały dwie lub trzy tabele, ale jest taki scenariusz odtworzenia. Weź swoją bazę dowolnej wersji i reprodukuj.
Chodzi o to, że mamy dwie kolumny, z których każda jest zaindeksowana. W jednej kolumnie znajduje się milion wierszy z wartością NULL. A w drugiej kolumnie tylko 20 wierszy. Kiedy wykonujemy bez powiązanych zmiennych, wszystko działa dobrze.
Jeśli zaczniemy wykonywać ze związanymi zmiennymi, czyli wykonujemy znak '?' lub '$1' dla naszego zapytania, to co finalnie otrzymujemy?
Pierwsze wykonanie – jak należy. Drugie – trochę szybciej. Coś się zbuforowało. Trzecie-czwarte-piąte. Potem bum – i tak jakoś. I najgorsze jest to, że dzieje się to przy szóstym wykonaniu. Kto by pomyślał, że trzeba zrobić właśnie sześć wykonań, aby zrozumieć, jaki tam naprawdę jest plan wykonania?
Kto jest winny? Co się stało? Baza danych zawiera optymalizację. I jest ona jakby zoptymalizowana pod ogólny przypadek. I, odpowiednio, od jakiegoś momentu przechodzi na ogólny plan, który, niestety, może okazać się innym. Może być taki sam, a może być inny. I istnieje jakieś wartość progowa, która prowadzi do takiego zachowania.
Co można z tym zrobić? Tutaj, oczywiście, trudniej coś przypuszczać. Jest proste rozwiązanie, którego używamy. To +0, OFFSET 0. Pewnie znacie takie rozwiązania. Po prostu bierzemy i dodajemy do zapytania „+0” i wszystko jest w porządku. Pokażę później.
I jest jeszcze jedna opcja – uważać na plany. Programista powinien nie tylko napisać zapytanie, ale także 6 razy powiedzieć „explain analyze”. Jeśli 5, to nie będzie odpowiednie.
I jest jeszcze trzecia opcja – napisać list do pgsql-hackers. Napisałem, ale na razie nie jest jasne – to błąd czy cecha.
A podczas gdy my myślimy – czy to błąd czy cecha, naprawmy to. Weźmy nasze zapytanie i dodajmy „+0”. Wszystko jest w porządku. Dwa znaki i nawet nie musimy myśleć, jak tam to wygląda. Bardzo prosto. Po prostu zabroniliśmy bazie danych używać indeksu za tą kolumną. Nie mamy indeksu za kolumną „+0” i wszystko, baza danych nie używa indeksu, wszystko jest w porządku.
Oto zasada 6 explainów. W aktualnych wersjach należy to robić 6 razy, jeśli macie powiązane zmienne. Jeśli nie macie powiązanych zmiennych, to robimy to tak. I w końcu to właśnie to zapytanie zawodzi. Sprawa nie jest trudna.
Wydawałoby się, ile można? Gdzieś błąd, tam błąd. Naprawdę błąd jest wszędzie.
Spójrzmy jeszcze raz. Na przykład mamy dwa schematy. Schemat A z tabelą Ы i schemat B z tabelą Ы. Zapytanie – wybrać dane z tabeli. Co w takim razie będzie? Będzie błąd. Będzie wszystko, co wyżej wymienione. Zasada jest taka – błąd wszędzie, będziemy mieć wszystko wymienione powyżej.
Teraz pytanie: „Dlaczego?”. Wydawałoby się, że jest dokumentacja, która mówi, że jeśli mamy schemat, to jest zmienna „search_path”, która mówi, gdzie szukać tabeli. Wydawałoby się, że zmienna istnieje.
Jaki jest problem? Problem w tym, że server-prepared statements nie podejrzewają, że search_path może być zmieniane przez kogoś. Ta wartość pozostaje jakby stała dla bazy danych. I niektóre części mogą nie przechwycić nowych wartości.
Oczywiście, zależy to od wersji, na której testujesz. Zależy od tego, jak bardzo różnią się twoje tabele. A wersja 9.1 po prostu wykona stare zapytania. Nowe wersje mogą wykryć oszustwo i powiedzieć, że masz błąd.
Jak to naprawić? Jest prosty przepis – nie rób tak. Nie zmieniaj search_path podczas działania aplikacji. Jeśli zmieniasz, to lepiej utworzyć nowe połączenie.
Można to przedyskutować, tzn. otworzyć, omówić, dodać. Może przekonamy twórców bazy danych, że w przypadku, gdy ktoś zmienia wartość, baza danych powinna informować klienta: „Zobacz, tutaj wartość została zaktualizowana. Może powinieneś zresetować instrukcje, odtworzyć je?”. Obecnie baza danych zachowuje się w ukryty sposób i w żaden sposób nie informuje, że gdzieś wewnętrznie instrukcje się zmieniły.
I znowu podkreślę – to, co nie jest typowe dla Javy. To samo zobaczymy w PL/pgSQL jeden do jednego. Ale tam to zostanie odwzorowane.
Spróbujmy jeszcze raz wybrać dane. Wybieramy, wybieramy. Mamy tabelę z milionem wierszy. Każdy wiersz ma około kilobajta. Około gigabajta danych. A mamy roboczą pamięć w maszyny Java wynoszącą 128 megabajtów.
Jak zwykle, korzystamy z przetwarzania strumieniowego, jak zalecają wszystkie książki. Tzn. otwieramy resultSet i czytamy stamtąd dane stopniowo. Czy to zadziała? Czy nie padnie z powodu braku pamięci? Czy będzie czytać po trochu? Zaufajmy bazie, zaufajmy Postgresowi. Nie wierzymy. Padniemy OutOfMemory? Kto miał OutOfMemory? A kto udało się po tym naprawić? Ktoś udało się naprawić.
Jeśli masz milion wierszy, nie można po prostu tak wybierać. Konieczne jest OFFSET/LIMIT. Kto jest za takim rozwiązaniem? A kto jest za tym, że trzeba bawić się w autoCommit?
Tutaj jak zwykle, najbardziej nieoczekiwane rozwiązanie okazuje się być poprawne. I jeśli nagle wyłączysz autoCommit, to pomoże. Dlaczego tak? Nauce tego nie wiadomo.
Ale domyślnie wszyscy klienci łączący się z bazą danych Postgres wybierają dane w całości. PgJDBC w tym przypadku nie jest wyjątkiem, wybiera wszystkie wiersze.
Istnieje wariant w temacie FetchSize, tzn. można na poziomie pojedynczej instrukcji powiedzieć, że tutaj, proszę, wybieraj dane po 10, 50. Ale to nie działa, dopóki nie wyłączysz autoCommit. Wyłączyłeś autoCommit – zaczyna działać.
Jednak przechodzenie przez kod i wszędzie ustawianie setFetchSize jest niewygodne. Dlatego zrobiliśmy taką konfigurację, która dla całego połączenia ustala wartość domyślną.
To powiedzieliśmy. Ustawiliśmy parametr. I co nam z tego wyszło? Jeśli wybieramy po małej ilości, na przykład po 10 wierszy, to mamy dość duże obciążenia. Dlatego trzeba ustawiać tę wartość na około setki.
W idealnym przypadku, oczywiście, warto by nauczyć się ograniczać także w bajtach, ale przepis jest taki: ustawiamy defaultRowFetchSize na więcej niż sto i cieszymy się.
Przejdźmy teraz do wstawiania danych. Wstawianie jest prostsze, są różne opcje. Na przykład, INSERT, VALUES. To dobry wybór. Można też mówić „INSERT SELECT”. W praktyce to to samo. Nie ma żadnej różnicy w wydajności.
Książki mówią, że należy wykonywać Batch statement, książki mówią, że można wykonywać bardziej złożone komendy z wieloma nawiasami. A w Postgres jest świetna funkcja – można robić COPY, tzn. robić to szybciej.
Jeśli zmierzymy, można ponownie odkryć kilka interesujących rzeczy. Jak chcemy, aby to działało? Chcemy nie parsować i nie wykonywać zbędnych komend.
W praktyce TCP na to nie pozwala. Jeśli klient jest zajęty wysyłaniem zapytania, to baza danych w swoich próbach wysłania nam odpowiedzi, nie odczytuje zapytań. W efekcie klient czeka na bazę danych, aż ona odczyta zapytanie, a baza danych czeka na klienta, aż on odczyta odpowiedź.
Dlatego klient zmuszony jest okresowo wysyłać pakiet synchronizacji. Zbędne interakcje sieciowe, zbędna utrata czasu.
Im więcej ich dodajemy, tym gorzej się to robi. Sterownik jest dość pesymistyczny i dodaje je dość często, około co 200 wierszy, w zależności od rozmiaru wierszy itd.
Czasami wystarczy poprawić jeden wiersz, a wszystko przyspiesza dziesięciokrotnie. Tak bywa. Dlaczego? Jak zazwyczaj, gdzieś taka stała była już używana. A wartość "128" oznaczała – nie używać batchingu.
Dobrze, że to nie trafiło do oficjalnej wersji. Odkryliśmy to, zanim zaczęliśmy wydawać wersję. Wszystkie wartości, które podaję, opierają się na nowoczesnych wersjach.
Zmieńmy parametry. Mierzymy InsertBatch prosty. Mierzymy InsertBatch wielokrotny, tzn. to samo, ale z wieloma wartościami. Sprytny ruch. Nie każdy umie to zrobić, ale to dość prosty sposób, znacznie łatwiejszy niż COPY.
Można robić COPY.
Można to robić na strukturach. Ogłoś domyślny typ użytkownika, przekaż tablicę i wstaw bezpośrednio do tabeli.
Jeśli otworzysz link: pgjdbc/ubenchmsrk/InsertBatch.java, znajdziesz ten kod na GitHubie. Możesz zobaczyć, jakie konkretne zapytania są generowane. Nie jest to kluczowe.
Uruchomiliśmy to. I pierwsze, co zrozumieliśmy, to to, że nie można nie używać batch – to po prostu niemożliwe. Wszystkie warianty batchingu są równe zeru, to znaczy czas wykonania jest praktycznie równy zeru w porównaniu do jednorazowego wykonania.
Wstawiamy dane. To dość prosta tabela. Trzy kolumny. I co tutaj widzimy? Widzimy, że wszystkie te trzy warianty są mniej więcej porównywalne. A COPY jest oczywiście lepsze.
To wtedy, gdy wstawiamy kawałkami. Kiedy mówiliśmy, że jedno wartość VALUES, dwa wartość VALUES, trzy wartość VALUES lub podaliśmy je 10 przez przecinek. To obecnie w poziomie. 1, 2, 4, 128. Widać, że Batch Insert, który jest zaznaczony na niebiesko, znacznie ułatwia sytuację. To znaczy, że gdy wstawiasz po jednym lub nawet po cztery, sytuacja poprawia się dwukrotnie, tylko dlatego, że w VALUES trochę więcej wciśnęliśmy. Mniej operacji EXECUTE.
Używanie COPY przy małych objętościach jest ekstremalnie nieperspektywiczne. Nawet nie zaznaczyłem pierwszych dwóch. Idą w niebo, to znaczy te zielone liczby dla COPY.
COPY należy używać, gdy masz objętość danych większą niż sto wierszy. Koszty związane z otwarciem tego połączenia są duże. I, szczerze mówiąc, w tę stronę nie kopałem. Optymalizowałem batch, COPY – nie.
Co robimy dalej? Mierzymy. Rozumiemy, że trzeba używać albo struktur, albo sprytnego batchu, łączącego kilka wartości.
Co należy wynieść z dzisiejszego referatu?
- PreparedStatement – to nasze wszystko. Znacząco poprawia wydajność. To daje dużą beczkę dziegciu.
- I trzeba robić EXPLAIN ANALYZE 6 razy.
- I należy rozcieńczać OFFSET 0, oraz stosować sztuczki takie jak +0, aby naprawić pozostały procent naszych problematycznych zapytań.
Źródło: habr.com
