Комментарии 44

А для чего тут нужен пгбаунсер? Что если пул сервиса будет напрямую в базу ходить?

Можно и без него, если все тщательно настроить. Но ошибиться легко, изменяя что-нибудь несвязанное, а цена ошибки весьма высока. Особенно если база коммунальная, а не выделенная под сервис. Поэтому в Авито его добавляют ко всем сервисам. Тем более, оверхеда он добавляет не так много при правильном подходе.

Получается там всякие таймауты выставляются и глобальный пул иои он не для этого?

Это connection pooler. Т.е. серверных соединений меньше, чем клиентских, и они переиспользуются под разные клиентские соединения. Ну и таймауты всякие тоже есть.

Я тут еще подумал. Если только сервис обращается в пгпул, то с соединенмями все ок. Но если база общая, то из-за новых пассажиров опять будет мультиплексирование, от которого избавились в статье выравниванием пулов. Или я что-то путаю?

Такое может быть, если перед коммунальной базой один общий pgbouncer без разделения пулов между пользователями. У нас пулы для каждого сервиса отделены, поэтому такая проблема не возникает.

а можно план запроса с индексами посмотреть? Что-то меня терзают сомнения. Я имею в виду в сыром виде

Так на картинке используемые индексы указаны. Если что это PEV, он визуализирует план на основе вывода EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON).


А какие сомнения терзают?

Если захотите таки поделится паланом, то вывод EXPLAIN (ANALYZE, BUFFERS)
сюда (можно с запрсом) explain.tensor.ru
полученную ссылку опубилковать

За наводку на тулзу, конечно, спасибо. Она шикарна.


Вот план запроса. Прошу учесть, что это свежеснятый план. Оригинал, который в статью картинкой вставлен, у меня не сохранился. Статья все-таки написана по боевому кейсу, а оптимизация проводилась еще в июле-августе. С тех пор и структура БД немного поменялась, и запрос, и железо. Я попытался в меру сил приблизить запрос к тому, что описан в статье. Но все же напрямую сравнивать нельзя. Но суть та же.

Исходя из задачи поиска на координатной плоскости и с сортировкой по удаленности, кажется, что стоит использовать gist-индекс. А из-за скалярного ключа в условии сортировки — btree_gist, но точно не обычный btree.

btree_gist выглядит интересно, надо попробовать. Пока не понимаю, за счет чего может быть ускорение. Да и в документации прямо пишут:


these operator classes will not outperform the equivalent standard B-tree index methods

Поясните, если не трудно

Посмотрел пристально на сам запрос, и меня терзают смутные сомнения… Поправьте, если я не прав:
1. Берем для переданной точки ближайшие точки каждого из провайдеров в рамках заданных константных отклонений.
2. С полученных точек берем теги, и ищем по ним (почему без провайдера?) в переданной области.

Но это значит, что для вообще любой исходной точки ситуация ситуация может быть просчитана заранее с помощью анализа ближайших — кажется, тут будет в тему диаграмма Вороного. Тогда никакой JOIN вообще не потребуется, и все сведется к поиску в области среди точек, ассоциированных с областью переданных координат.
почему без провайдера?

Тэги уникальны насквозь по всем провайдерам, поэтому провайдер не нужен.


А так да, ищем ближайшую точку для каждого провайдера, берем от них тэги, потом ищем связанные по тэгам точки в пределах области.


кажется, тут будет в тему диаграмма Вороного

Звучит очень круто, но как применить на практике? Заранее вокруг каждой точки строим область, в которой она будет являться ближайшей? Сможем ли мы делать это быстро и, желательно, в Постгресе? Как хранить полигоны в базе, как их индексировать и как проверять попадание координат в один из полигонов?


Вообще, направление выглядит перспективно. Мне надо почитать и найти ответы на вопросы выше. Подсказки и ссылки приветствуются!

Как раз определение точки полигону должно хорошо решаться с помощью SP-GiST, postgis и компании. А дальше банально — заранее вычисляем все полигоны, раз набор точек меняется существенно реже их поиска, для каждого полигона заранее формируем набор соответствующих целевых точек с сортировкой по удаленности.

В запросе X/Y-диапазоны заданы интервалами, но подозреваю, что взять обычное расстояние будет лучше для задачи.

Да, искать в квадрате вокруг точки — это из-за применения обычных btree. Кружок заданного радиуса будет даже лучше.

Кажется, базисты называют подход с увеличением железа вместо запроса оптимизацией кредиткой (потому что если БД в облаке, просто платим больше и получаем более мощное железо).
Такой подход полезен как временное решение или когда все другие резервы исчерпаны. Ну либо действительно, когда организовано так, что оптимизировать запрос, в который всё упирается, дороже, чем кучу оперативки поставить.


Как я понял, у вас кейс вида "две таблички с горячими данными, в которых надо очень быстро выполнить конкретный запрос", то почему бы не выбрать какую-то in-memory базу, позволяющую хранить минимально необходимый набор полей без накладных расходов, имеющихся у postgresql и быстро искать в памяти?

Замечу, что аппаратные ресурсы выросли примерно в четыре раза (16Гб -> 64Гб), а производительность — в сто раз. Это довольно эффективное использование кредитки :)


Я в ваших словах слышу критику самого подхода, поэтому, думаю, надо его пояснить. Свою задачу я вижу как найти решение, удовлетворяющее целям бизнеса, в пределах заданных ограничений: по срокам, наличным ресурсам, функциональности и качеству, — именно это представляется мне грамотным инженерным подходом. Поэтому здесь мы сначала собрали требования, выработали атрибуты качества, приоритизировали их и выработали решение, их удовлетворяющее. Надеюсь, в следующей статье удастся рассказать подробнее про этот метод.


Выбор постгрес здесь был совершенно неочевиден; долгое время постгрес оставался в аутсайдерах, уступая сфинксу и эластику. Но по совокупности факторов таки выиграл.


Конкретно in-memory базу рассматривали, но в итоге отказались: трудно обеспечить быстрое восстановление после сбоя + усложненная поддержка за счет еще одной технологии в команде.

Да нет, это не критика. Если за доп.ресурсы не надо платить по часам — подход имеет право на жизнь. Что касается мультипликации производительности, так считать неправильно. Вы заменили дисковое чтение на чтение с памяти, перенеся данные в кеш. На выходе, теоретически, в пределе можно получить ускорение кратное разности скорости диска и памяти. Учитывая то, что у вас объём активно используемой части данных близок к полному объёму БД (как я понял). Как я понял, у вас скорость диска ограничена, а значит мы говорим не о SSD (который где-то на порядок медленнее оперативной памяти), а о некотором модельном носителе.

Кстати, что касается планов. Есть классный инструмент — explain.tensor.ru, рисует планы на порядок понятнее, и даёт гораздо больше информации чем непонятная картинка в статье.

Да, объем используемой части близок к объему БД. Старые данные мы в этих таблицах не храним, уносим в архив. Диск ограничен квотой на IOPS для lxc контейнера с базой.


Тулзу оценил!

Думаю тут можно в десяток другой раз ускорить заюзав геоиндексы, ну и переписав запрос.

Ну, а в Вашем варианте некоторые части индексов просто бессмысленны.
CREATE INDEX send_idx ON send(lon, lat, active_from, active_until)
;
CREATE INDEX receive_idx ON receive(tag_from_id, lon, lat)
;

Замените на
CREATE INDEX send_idx ON send(lon)
;
CREATE INDEX receive_idx ON receive(tag_from_id, lon)
;

Запросы станут чуууточку быстрее.

Хорошее замечание! Изначально мы как раз смотрели в сторону геоиндексов, причем не только в постгрес (spgist, postgis), но и в эластике и редисе. В итоге пришли к выводу, что геоиндексы здесь не совсем подходят. Проблема в том, что нужно искать одновременно и по координатам, и по другим полям, а r-деревья с этим плохо справляются. А как бы вы их применили здесь, чтобы получить прирост в десяток раз?


Насчет убрать лишние поля из индексов — тоже очень логично. Мы так пробовали. Казалось, что долгота должна быть достаточно селективна, и широта в индексе необязательна. Но перформанс тесты показали, что с таким набором полей все же немного быстрее. Возможно, дело в том, что службы доставки передают нам координаты терминалов с ограниченной точностью, в итоге некоторые терминалы имеют одинаковую долготу, даже если находятся в разных городах. Впрочем, сокращенный индекс может иметь смысл, т.к. он меньше; возможно, еще вернемся к нему.

остальные поля не имеют смысла, т.к. индексы не умеют по второй части индекса range если первая тоже range, т.е. так работать будет только первая часть индекса
index_part_1 > :val1 AND index_part_2 > :val2
а вот так будут использоваться обе части индекса
index_part_1 = :val1 AND index_part_2 > :val2
и так тоже будут использоваться все части индекса
index_part_1 = :val1 AND index_part_2 = :val2 AND index_part_3 = :val3 AND index_part_4 = :val4 AND index_part_5 > :val5

но все эти манипуляции бессмысленны, Вы попробуйте создать геоиндекс, дело 15 минут на тест, у вас всё залетает, производительность на порядок увеличится
Проблема в том, что нужно искать одновременно и по координатам, и по другим полям

Эти рассуждения ничтожны, т.к. у вас используется только первая часть индекса в первом запросе и во втором запросе 2 части. Но второй запрос всё равно лучше переписать на использование геоиндекса, он будет на порядки селективнее. И соответственно производительность тоже вырастет минимум на порядок.
Кстати, что значит поле tag_from_id?
Напишите мне в личку, помогу запрос переписать и можем вместе затестить, мне тоже интересно на сколько порядков производительность вырастет.

Ещё вопрос: в выборку у нас не попадают поля из таблицы send, запрос точно правильный?
Ещё вопрос: в выборку у нас не попадают поля из таблицы send, запрос точно правильный?

send нужен, чтобы найти tag_from_id. Потом tag_from_id подается на вход receive. Именно поэтому на receive сделан индекс receive(tag_from_id, lon, lat).


остальные поля не имеют смысла, т.к. индексы не умеют по второй части индекса range если первая тоже range, т.е. так работать будет только первая часть индекса

В моем представлении, таки будет работать. Да, базе придется перебрать все lat в записях, попавших под lon between ? and ?. Но зато для фильтрации не понадобиться поход в таблицу, можно смотреть прямо в индекс. Это подтверждается результатами нагрузочных тестов: заметно небольшое (незначительное) улучшение при включении lat в индекс.


Вы попробуйте создать геоиндекс, дело 15 минут на тест, у вас всё залетает, производительность на порядок увеличится

Я-то создам, мне не лень повозиться, чтобы разобраться. Вопрос в том, что конкретно создавать. Я уже не один геоиндекс здесь попробовал и пока результата не добился. Если вы про create index on receive using spgist(lat,lon), то он дает весьма существенную просадку по производительности.


Надо все-таки достать сырой план запроса. Тут проблема в том, что стенд, на котором проводилось тестирование, уже не доступен. Статья написана о событиях июля-августа; с тех пор структура БД и запрос немного усложнились. Но я попробую.

про геоиндексы: я бы пошел чуть с другой стороны и попробовал в таблицу добавить доп поле с h3 или s2 индексом и искал по нему, в первую очередь
а может быть даже только по нему(если позволяют бизнес-требования), убрав из общего индекса и предикатов поиска широту/долготу
Возможно, если исходно доступен населенный пункт, то можно создать дополнительную таблицу, где для каждого селения предрассчитать все пункты приема и выдачи в округе без учета дополнительных условий, а потом выбирать уже из них. Минусом, конечно, будет более сложная поддержка (данные надо будет пересчитывать при изменении в исходных таблицах).

Не совсем понял. Допустим, есть Москва (1000 терминалов) и Владивосток (500 терминалов). Создадим для каждого таблицу… а что в нее класть? Считать мы хотим цену между любыми двумя терминалами. Действительно, для всех терминалов в пределах одного города цена будет одинаковой, но у разных служб доставок границы города могут отличаться. Поясните, пожалуйста, как это будет поддержано схемой с таблицей для каждого города.

Зачем вводить таблицы на каждый город? Posgres поддерживает json, т.е. потребуется одна таблица с тремя колонками id-города, json_array(id-пунтов-приема), json_array(id-пунктов-выдачи). Можно наверно даже и в hash-таблицу в Go записать.
Считать мы хотим цену между любыми двумя терминалами.

Ничего не меняется. Просто в вашем случае вы ограничиваете выборку пунктов по широте и долготе, используя индекс. Я же предложил ограничить выборку заранее предрассчитанными данными (а потом к ней уже применять фильтры и считать цены).

Кажется, понял. Предлагаете группировать терминалы по городу и использовать json, чтобы хранить все терминалы города.


Группировку терминалов с одинаковыми правилами доставки мы делаем (это поле tag_from_id). Чаще всего тэг — это и есть город. Но не всегда. Более того, понятие города у всех разное; даже в одной службе доставки в разное время к городу могут относиться разные терминалы.


Насчет JSON — наверное, можно и так, надо пробовать. Асимптотическая сложность вроде бы та же самая.

Не совсем терминалы города, а ближайшие/подходящие. Так, если рассматривать не мегаполисы, то для деревень мне кажется разумным будет предлагать отделения в соседних областных центрах, но это все зависит от того, что именно требуется.
P.S. Если геоиндексы настолько хороши, как народ обещает, то в моем предложении скорее всего смысла не особо много.
Нужно подобрать конфигурацию базы, которая могла бы вместить 27 Гб в памяти

pg_total_relation_size считает уже с индексами, т.е. вам должно хватить и 21Gb


pg_total_relation_size ( regclass ) → bigint
Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

в linux:


$ sysctl -w kernel.sched_autogroup_enabled=0
$ sysctl -w kernel.sched_migration_cost_ns=5000000
$ renice -n -15 -p $(pidof pgbouncer)

в pg:


shared_buffers=32Gb

Посмотрите в explain analyze сколько тратится на planning time примерно такой оверхед будет делать pg на каждую транзакцию. Попробуйте пострелять через session пулинг, или напрямую в pg с препарированными запросами.

pg_total_relation_size считает уже с индексами, т.е. вам должно хватить и 21Gb

Класс, спасибо!


Да, тоже опасался, что планирование такого здорового запроса будет занимать вечность. Но нет, все довольно быстро. Как считаете, есть ли смысл пробовать "describe" режим (с анонимными prepared statements)?

simple query protocol позволяет отправлять несколько запросов за раз, это как минимум небезопасно, и это отдается на откуп драйверу и разработчику. Из плюсов можно внутри запроса проставлять параметры для транзакции типа set local statement_timeout='50ms'; begin; select pg_sleep(1), вместо statement_timeout, можно втыкать и synchronous_commit, т.е. управлять write concern с гранулярностью до транзакций. И все это будет за 1 round trip.


extended query protocol исключит возможность делать sql-инъекции на более низком уровне. С выставленным log_min_statement_duration в 0 можно будет смотреть в логах на каком этапе (parse, bind, execute), сколько ms тратиться, например, только в pg13 измеряется Total time spent planning the statement. До него эту метрику можно узнать только косвенно по разнице avg_query_time в pgbouncer-e и total_time/calls из pg_stat_statements внутри pg.


Именно по перфомансу разница между "describe" и PreferSimpleProtocol = true вряд ли будет заметной.

И действительно :) Но! 1) Сервис на Go, 2) начало истории тоже в этом хабе, а там код таки есть. А так да, конечно, немного за уши притянуто

Кстати, как более опытный автор, вы бы что посоветовали, оставить статью в хабе Go или все-таки убрать?

Мне кажется, есть более релевантные хабы — например, Devops или Cloud имеют к проблеме гораздо больше отношения чем конкретный язык.
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

Информация

Дата основания
2007
Местоположение
Россия
Сайт
avito.tech
Численность
1 001–5 000 человек
Дата регистрации

Блог на Хабре