Antywzorce PostgreSQL: opowieść o iteracyjnym udoskonalaniu wyszukiwania według nazwy lub „optymalizacji tam iz powrotem”

Rekord tysięcy menadżerów z biur sprzedaży w całym kraju naszego systemu CRM dziesiątki tysięcy kontaktów dziennie — fakty dotyczące komunikacji z potencjalnymi lub obecnymi klientami. A do tego trzeba najpierw znaleźć klienta, i to najlepiej bardzo szybko. I dzieje się to najczęściej po imieniu.

Nic więc dziwnego, że po raz kolejny analizując „ciężkie” zapytania w jednej z najbardziej obciążonych baz danych – naszej własnej Konto firmowe VLSI, znalazłem „na górze” żądanie „szybkiego” wyszukiwania według nazwy do kart organizacyjnych.

Co więcej, dalsze badania ujawniły interesujący przykład najpierw optymalizacja, a potem spadek wydajności żądanie z jego sekwencyjnym udoskonalaniem przez kilka zespołów, z których każdy działał wyłącznie w najlepszych intencjach.

0: czego chciał użytkownik?

[KDPV stąd]

Co użytkownik zwykle ma na myśli, mówiąc o „szybkim” wyszukiwaniu po nazwie? Prawie nigdy nie okazuje się, że jest to „uczciwe” wyszukiwanie takiego podciągu ... LIKE '%роза%' - bo wtedy wynik obejmuje nie tylko 'Розалия' и 'Магазин Роза'Ale 'Гроза' i nawet 'Дом Деда Мороза'.

Użytkownik zakłada na poziomie codziennym, jaki mu zapewnisz szukaj według początku słowa w tytule i spraw, aby był on bardziej odpowiedni zaczynać z weszła. I zrobisz to prawie natychmiast - dla wejścia międzyliniowego.

1: ogranicz zadanie

Co więcej, dana osoba nie wejdzie specjalnie 'роз магаз', więc musisz wyszukiwać każde słowo według prefiksu. Nie, użytkownikowi znacznie łatwiej jest odpowiedzieć na szybką podpowiedź dotyczącą ostatniego słowa, niż celowo „niedookreślić” poprzednie - spójrz, jak radzi sobie z tym dowolna wyszukiwarka.

ogólnie rzecz biorąc, prawidłowo sformułowanie wymagań dla problemu to więcej niż połowa rozwiązania. Czasami uważna analiza przypadków użycia może znacząco wpłynąć na wynik.

Co robi programista abstrakcyjny?

1.0: wyszukiwarka zewnętrzna

Oj, szukanie jest trudne, nie chce mi się w ogóle nic robić - oddajmy to devopsom! Pozwól im wdrożyć wyszukiwarkę zewnętrzną względem bazy danych: Sphinx, ElasticSearch,...

Opcja działająca, choć pracochłonna pod względem synchronizacji i szybkości zmian. Ale nie w naszym przypadku, ponieważ dla każdego klienta wyszukiwanie odbywa się wyłącznie w ramach danych jego konta. A dane mają dość dużą zmienność - i jeśli menedżer wszedł teraz na kartę 'Магазин Роза', to po 5-10 sekundach może już pamiętać, że zapomniał wskazać tam swój adres e-mail, a chce go znaleźć i poprawić.

Dlatego - dajmy szukaj „bezpośrednio w bazie”. Na szczęście PostgreSQL pozwala nam to zrobić, a nie tylko jedną opcję - przyjrzymy się im.

1.1: „uczciwy” podciąg

Trzymamy się słowa „podciąg”. Ale w przypadku wyszukiwania indeksu według podciągu (a nawet wyrażeń regularnych!) istnieje doskonałe rozwiązanie moduł pg_trgm! Dopiero wtedy konieczne będzie prawidłowe sortowanie.

Spróbujmy przyjąć następującą płytkę, aby uprościć model:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

Przesyłamy tam 7.8 mln rekordów prawdziwych organizacji i je indeksujemy:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

Poszukajmy pierwszych 10 rekordów dla wyszukiwania międzyliniowego:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[patrz na explain.tensor.ru]

Cóż, takie... 26ms, 31MB odczyt danych i ponad 1.7 tys. przefiltrowanych rekordów – na 10 wyszukiwanych. Koszty ogólne są zbyt wysokie. Czy nie ma czegoś bardziej wydajnego?

1.2: wyszukiwanie według tekstu? To FTS!

Rzeczywiście, PostgreSQL zapewnia bardzo potężne możliwości wyszukiwarka pełnotekstowa (Wyszukiwanie pełnotekstowe), w tym możliwość wyszukiwania przedrostkowego. Doskonała opcja, nie musisz nawet instalować rozszerzeń! Spróbujmy:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[patrz na explain.tensor.ru]

Tutaj zrównoleglenie wykonywania zapytań trochę nam pomogło, skracając czas o połowę 11 ms. A w sumie musieliśmy czytać 1.5 raza mniej 20MB. Ale tutaj im mniej, tym lepiej, bo im większy wolumen odczytamy, tym większe jest ryzyko, że dostaniemy chybienie pamięci podręcznej, a każda dodatkowa strona danych odczytanych z dysku to potencjalny „hamulec” żądania.

1.3: nadal lubisz?

Poprzednia prośba jest dobra dla wszystkich, ale spełni się tylko wtedy, gdy będziesz ją wyciągać sto tysięcy razy dziennie 2TB odczytać dane. W najlepszym przypadku z pamięci, ale jeśli masz pecha, to z dysku. Spróbujmy więc go zmniejszyć.

Pamiętajmy, co chce zobaczyć użytkownik pierwsze „które zaczynają się od…”. Więc to jest w najczystszej formie wyszukiwanie prefiksów przez text_pattern_ops! I dopiero jeśli „nie mamy” aż 10 rekordów, których szukamy, to będziemy musieli dokończyć ich czytanie za pomocą wyszukiwania FTS:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[patrz na explain.tensor.ru]

Świetne wykonanie - całość 0.05 ms i nieco ponad 100 KB Czytać! Tylko my zapomnieliśmy sortuj według nazwyaby użytkownik nie zgubił się w wynikach:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[patrz na explain.tensor.ru]

Oj, coś już nie jest tak piękne - wydaje się, że jest indeks, ale sortowanie przelatuje obok niego... To oczywiście jest już wielokrotnie skuteczniejsze niż poprzednia opcja, ale...

1.4: „zakończ z plikiem”

Istnieje jednak indeks, który pozwala wyszukiwać według zakresu i nadal normalnie korzystać z sortowania - zwykłe drzewo!

CREATE INDEX ON firms(lower(name));

Jedynie żądanie o to będzie musiało zostać „odbrane ręcznie”:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[patrz na explain.tensor.ru]

Doskonale – sortowanie działa, a zużycie zasobów pozostaje „mikroskopijne”, tysiące razy skuteczniejsze niż „czysty” FTS! Pozostaje tylko złożyć to w jedno żądanie:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

Należy zauważyć, że wykonywane jest drugie podzapytanie tylko wtedy, gdy pierwszy zwrócił mniej niż oczekiwano ostatni LIMIT Liczba linii. Mówię o tej metodzie optymalizacji zapytań już napisałem wcześniej.

Więc tak, mamy teraz na stole zarówno btree, jak i gin, ale statystycznie tak się okazuje mniej niż 10% żądań dochodzi do wykonania drugiego bloku. Czyli przy takich typowych dla zadania ograniczeniach, udało nam się zredukować całkowite zużycie zasobów serwera niemal tysiąckrotnie!

1.5*: możemy obejść się bez pliku

Powyżej LIKE Uniemożliwiono nam użycie nieprawidłowego sortowania. Można go jednak „ustawić na właściwej ścieżce”, podając operator USING:

Domyślnie zakłada się ASC. Dodatkowo możesz określić nazwę konkretnego operatora sortowania w klauzuli USING. Operator sortowania musi należeć do grupy operatorów mniejszego lub większego niż pewnej rodziny operatorów drzewa B. ASC zwykle równoważne USING < и DESC zwykle równoważne USING >.

W naszym przypadku „mniej” oznacza ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[patrz na explain.tensor.ru]

2: jak prośby stają się kwaśne

Teraz pozostawiamy naszą prośbę do „gotowania” na sześć miesięcy lub rok i ze zdziwieniem znajdujemy ją ponownie „na górze” ze wskaźnikami całkowitego dziennego „pompowania” pamięci (buforuje wspólne trafienie) W 5.5TB – czyli nawet więcej niż pierwotnie.

Nie, oczywiście, nasza firma się rozrosła i nasze obciążenie pracą wzrosło, ale nie w tym samym stopniu! Oznacza to, że coś tu jest podejrzane - rozwiążmy to.

2.1: narodziny stronicowania

W pewnym momencie inny zespół programistów chciał umożliwić „przeskoczenie” z szybkiego wyszukiwania indeksu dolnego do rejestru z tymi samymi, ale rozszerzonymi wynikami. Co to jest rejestr bez nawigacji po stronach? Spieprzmy to!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

Teraz możliwe było pokazanie rejestru wyników wyszukiwania z ładowaniem „strona po stronie” bez stresu dla programisty.

Oczywiście, w rzeczywistości z każdą kolejną stroną danych czytanych jest coraz więcej (wszystko z poprzedniego razu, które odrzucimy, plus niezbędny „ogon”) - czyli jest to wyraźny antywzór. Ale bardziej poprawne byłoby rozpoczęcie wyszukiwania przy kolejnej iteracji od klucza zapisanego w interfejsie, ale o tym innym razem.

2.2: Chcę czegoś egzotycznego

W pewnym momencie deweloper chciał różnicować otrzymaną próbkę danymi z innej tabeli, o którą całe poprzednie żądanie zostało wysłane do CTE:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

A mimo to nie jest źle, bo podzapytanie jest oceniane tylko dla 10 zwróconych rekordów, jeśli nie...

2.3: DISTINCT jest bezsensowne i bezlitosne

Gdzieś w procesie takiej ewolucji od drugiego podzapytania Stracony NOT LIKE warunek. Oczywiste jest, że po tym UNION ALL zaczął wracać niektóre wpisy dwukrotnie - najpierw znaleziony na początku linii, a następnie ponownie - na początku pierwszego słowa tej linii. W limicie wszystkie rekordy drugiego podzapytania mogą odpowiadać rekordom pierwszego.

Co robi programista zamiast szukać przyczyny?.. Nie ma wątpliwości!

• dwukrotnie większy oryginalne próbki
• zastosuj WYRÓŻNIJaby uzyskać tylko pojedyncze wystąpienia każdej linii

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

Oznacza to, że jasne jest, że ostatecznie wynik jest dokładnie taki sam, ale szansa na „przelot” do drugiego podzapytania CTE stała się znacznie wyższa, a nawet bez tego wyraźnie bardziej czytelny.

Ale nie to jest najsmutniejsze. Ponieważ deweloper poprosił o wybranie DISTINCT nie dla konkretnych, ale dla wszystkich pól na raz rekordów, wówczas pole sub_query — wynik podzapytania — zostało tam automatycznie uwzględnione. Teraz do wykonania DISTINCT, baza danych musiała już zostać wykonana nie 10 podzapytań, ale wszystkie <2 * N> + 10!

2.4: przede wszystkim współpraca!

Tak więc programiści żyli dalej - nie zawracali sobie tym głowy, ponieważ użytkownik najwyraźniej nie miał dość cierpliwości, aby „dostosować” rejestr do znaczących wartości N z chronicznym spowolnieniem otrzymywania każdej kolejnej „strony”.

Aż do czasu, gdy przyszli do nich programiści z innego działu i chcieli zastosować tak wygodną metodę do wyszukiwania iteracyjnego - czyli bierzemy kawałek z jakiejś próbki, filtrujemy go według dodatkowych warunków, losujemy wynik, potem następny kawałek (co w naszym przypadku osiąga się przez zwiększenie N) i tak dalej, aż zapełnimy ekran.

Ogólnie rzecz biorąc, w złowionym okazie N osiągnęło wartości prawie 17Ki w ciągu zaledwie jednego dnia „w całym łańcuchu” zrealizowano co najmniej 4 tys. takich żądań. Ostatni z nich odważnie zeskanował 1 GB pamięci na iterację...

Razem

Źródło: www.habr.com