В Amplitude наша цель — предоставить простую в использовании интерактивную аналитику продуктов, чтобы каждый мог найти ответы на свои вопросы о продукте. Чтобы обеспечить удобство работы, Amplitude должен быстро предоставить эти ответы. Поэтому, когда один из наших клиентов пожаловался на то, сколько времени потребовалось для загрузки раскрывающегося списка свойств события в пользовательском интерфейсе Amplitude, мы приступили к детальному изучению проблемы.
Отслеживая задержку на разных уровнях, мы поняли, что одному конкретному запросу PostgreSQL потребовалось 20 секунд для завершения. Для нас это стало неожиданностью, так как обе таблицы имеют индексы в соединяемом столбце.
Медленный запрос
План выполнения PostgreSQL для этого запроса был для нас неожиданным. Несмотря на то, что в обеих таблицах есть индексы, PostgreSQL решил выполнить Hash Join с последовательным сканированием большой таблицы. Последовательное сканирование большой таблицы занимало большую часть времени запроса.
План выполнения медленого запроса
Я изначально подозревал, что это может быть из-за фрагментации. Но после проверки данных я понял, что в эту таблицу данные только добавляются и практически не удаляются оттуда. Так как очистка места с помощью VACUUM здесь не очень поможет, я начал копать дальше. Затем я попробовал этот же запрос на другом клиенте с хорошим временем ответа. К моему удивлению, план выполнения запроса выглядел совершенно иначе!
План выполнения того же запроса на другом клиенте
Интересно, что приложение A получило доступ только к 10 раз большему количеству данных, чем приложение B, но время отклика было в 3000 раз больше.
Чтобы увидеть альтернативные планы запросов PostgreSQL, я отключил хеш-соединение и перезапустил запрос.
Альтернативный план выполнения для медленного запроса
Ну вот! Тот же запрос завершается в 50 раз быстрее при использовании вложенного цикла вместо хэш-соединения. Итак, почему PostgreSQL выбрал худший план для приложения A?
При более тщательном рассмотрении предполагаемой стоимости и фактического времени выполнения для обоих планов предполагаемые соотношения стоимости и фактического времени выполнения были очень разными. Основным виновником этого несоответствия была оценка стоимости последовательного сканирования. PostgreSQL подсчитал, что последовательное сканирование было бы лучше, чем 4000+ сканирований индекса, но в действительности сканирование индекса было в 50 раз быстрее.
Это привело меня к параметрам конфигурации random_page_cost и seq_page_cost. Значения PostgreSQL по умолчанию 4 и 1 для random_page_cost, seq_page_cost, которые настроены для HDD, где произвольный доступ к диску дороже, чем последовательный доступ. Однако эти затраты были неточными для нашего развертывания с использованием тома gp2 EBS, которые являются твердотельными накопителями. Для нашего развертывания случайный и последовательный доступ практически одинаков.
Я изменил значение random_page_cost на 1 и повторил запрос. На этот раз PostgreSQL использовал Nested Loop, и запрос выполнялся в 50 раз быстрее. После изменения мы также заметили значительное снижение максимального времени отклика от PostgreSQL.
Общая производительность медленного запроса значительно улучшилась
Если вы используете SSD и используете PostgreSQL с конфигурацией по умолчанию, я советую вам попробовать настроить random_page_cost и seq_page_cost. Вы можете быть удивлены сильным улучшением производительности.
От себя добавлю, что я выставил минимальные параметры seq_page_cost = random_page_cost = 0.1, чтобы отдать приоритет данным в памяти (кэш) над процессорными операциями, так как у меня выделено большое количество ОЗУ для PostgreSQL (размер ОЗУ превышает размер базы на диске). Не очень понятно, почему сообщество postgres до сих пор использует настройки по умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD, а не для современных серверов. Надеюсь в скором времени это исправят.
Комментарии (36)
yurybx
16.03.2019 20:30Интересно, а есть рекомендации по оптимизации Microsoft SQL Server под твердотельные накопители?
yleo
16.03.2019 23:21M$ где-то хвалился что они сами детектируют SSD/HDD (точнее говоря измеряют метрики).
khanid
17.03.2019 12:40Ну здесь они душой вроде бы не кривили. Сколько я не копал, сильного изменения производительности относительно настроек по дефолту на твердотельных не заметил.
Всё равно самым узким местом была 1С, даже с учётом того, что базы размерами всего гигов по 50-100 были. Те, несколько процентов, что удалось отвоевать на конфигах mssql, всё равно терялись на уровне производительности логики вышестоящего приложения (того самого 1С).
SergeyMax
16.03.2019 21:32+3размер ОЗУ превышает размер базы на диске). Не очень понятно, почему сообщество postgres до сих пор использует настройки по-умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD
У вас не ОЗУ большое, у вас просто база маленькая)kmansoft
17.03.2019 18:57И вообще если у них база полностью умещается в ОЗУ то может быть им (для этой базы, м.б. справочник) нужен не Postgre?
puyol_dev2 Автор
17.03.2019 22:14Мое решение такие СУБД не поддерживает + у меня несколько баз на инстансе и планируется увеличение количества баз
ky0
17.03.2019 02:31Не очень понятно, почему сообщество postgres до сих пор использует настройки по-умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD, а не для современных серверов. Надеюсь в скором времени это исправят.
Не исправят, потому что это не ошибка. Параметры постгреса по умолчанию установлены таким образом, чтобы база запускалась на чём угодно, включая холодильники и кофеварки. Для оптимизации производительности его нужно настраивать (сюрприз!). Поздравляю автора статьи с этим открытием.edogs
17.03.2019 03:05Исправляют что-то не только из-за ошибок, но и из-за добавления удобства.
В mysql, например, раньше были примеры конфигов — my-small, my-large, my-medium, my-huge, в базе годились как и для холодильника (меньше 64мб), так и для вполне приличного вдс (1-2гб). И даже был отдельный пример для 4гб с подключением иннодб my-innodb-heavy-4G
Да, безусловно, в идеальном мире, базу данных должен настраивать администратор под конкретные задачи, блаблабла. Но в подавляющем большинстве случаев этих конфигов хватало для того, что бы на свежеподнятом вдс не напарываться на дурацкие проблемы прямо из коробки и не надо было звать дорогостоящего администратора или разбираться с этими нюансами самостоятельно.ky0
17.03.2019 11:06Безусловно, в идеальном мире можно настраивать базу только исходя из доступных ресурсов железа. Но на самом деле не менее важен профиль нагрузки, характер хранимых данных и другие вещи. Типовой конфигурации постгреса хватает для работы. Если вас перестала устраивать его производительность — значит, пора начать думать (хотя наверное правильнее было бы делать это сразу) или нанимать специалиста.
BugM
17.03.2019 03:12+2Подход не очень правильный. Автоконфигуратор от Постгреса для настройки под типовые конфигурации был бы очень полезен и приятен.
Юзкейс:
Надо поставить Постгрес вот на эту машину. Конфигурация машины известна, примерный профиль нагрузки тоже известен. Хочется в красивой менюшке потыкать кнопочки и готово. Более-менее оптимальные настройки выставлены.
Добавить возможность сохранить конфиг и применять его на другие такие же машины и вообще хорошо будет.
Envek
17.03.2019 10:28И вот ещё один, попроще: https://pgtune.leopard.in.ua/
Вбиваете количество ОЗУ, тип дисков и вам дают минимальные изменения, которые нужно внести в конфиг под эту конфигурацию.
force
17.03.2019 14:07Тут уж больно минимальные. Оценить конкретные стоимости seq/random/cpu/memory можно только на своей железке. И какой-нить pg_tune --analyze был бы очень полезен.
А сейчас вся эта оценка перекладывается на админа, а разработчики постгре умывают лапки.
morozovsk
18.03.2019 13:16спасибо за ссылку, pgtune меняет random_page_cost при смене типа дисков с HDD на SSD, а выше упомянутый pgconfigurator — нет.
puyol_dev2 Автор
17.03.2019 21:39Почему в коммерческих базах данных даже на версиях типа Express есть автонастройка параметров, тем более параметров планировщика запросов, а в СУБД, которая позиционирует себя как конкурент промышленным коммерческим СУБД — нет. Это для меня действительно сюрприз со знаком -
force
17.03.2019 14:03От себя добавлю, что я выставил минимальные параметры seq_page_cost = random_page_cost = 0.1, чтобы отдать приоритет данным в памяти над дисковыми
По-моему автор чего-то сильно нагородил здесь. Открываем документацию:
By default, these cost variables are based on the cost of sequential page fetches; that is, seq_page_cost is conventionally set to 1.0
Если я правильно понял, то выставив в 0.1 он просто ухудшил стоимость остальных операций в 10 раз (остальные это сканы на CPU), т.е. предпочёл использовать обращение к диску вместо использования процессора. Т.е. всё наоборот сделал, относительно заявленного.
puyol_dev2 Автор
17.03.2019 21:55Спасибо, исправил. Только смысл в том, что данные из ОЗУ выбираются быстрее, чем выполняются вычисления процессора. Поэтому стоимость процессорных операций понижена относительно данных в памяти
4p4
17.03.2019 14:46А как это всё на AWS? Там наверное из коробки настроено?
Tonkonozhenko
17.03.2019 15:03+1Если имеете в виду RDS, то нет. По дефолту 4. Приходится все настраивать руками.
tbicr
17.03.2019 15:51я может придераюсь и не знаю сколько и каких данных в таблицах, но где limit в запросе? если всё же аналитику считать, то скорее всего данный запрос будет всё хуже и хуже работать с увеличением данных.
chemtech
17.03.2019 18:25+1По поводу тюнинга PostgreSQL есть такой проект github.com/jfcoz/postgresqltuner
postgres
17.03.2019 22:41Грустно, когда люди работают с дефолтными настройкам
puyol_dev2 Автор
18.03.2019 08:03+1Грустно, что в 21 веке, когда активно развиваются и внедряются нейросети и ИИ, мы в самой технологичной отрасли вынуждены экспериментальным способом находить оптимальные настройки. Я бы понял это в 70х, 80х или даже в 90х, но не в 2020 году
ky0
18.03.2019 10:57+1Эксперимент тут ни при чём. Один раз вдумчиво прочитать 10-15 страниц официальной документации ради получения знаний, достаточных для 90% случаев настройки — довольно невысокая цена, как мне кажется… то, что люди, как обычно, начинают читать мануалы только когда всё встало колом — отдельная проблема, к постгресу отношения не имеющая.
puyol_dev2 Автор
18.03.2019 11:36Вы, видимо, особо не ознакамливались с документацией по Postgres, иначе бы знали, что она содержит более 3 тыс страниц, из них настройка сервера занимает 90 repo.postgrespro.ru/doc/pgpro/11.1.1/ru/postgres-A4.pdf так что не нужно про 10-15 страниц и 90% случаев. Это балабольство
Вообще, как работает процесс обучения — человек сталкивается с проблемой и ищет ее решение. Только адресный поиск информации с последующим ее закреплением может дать результат. Чтение о чем-то сферическом запишется в кратковременную память и растворится, так же, как институтские лекции по матану
И, конечно, нужно точно знать какой параметр может дать больший прирост производительности относительно других. Об этом в документации тоже не напишутky0
18.03.2019 11:44Предлагаю не переходить на личности и не понижать уровень дискуссии необоснованными утверждениями.
Я утверждаю, что для настройки постгреса, подходящей в подавляющем количестве случаев достаточно ознакомиться и осознать не все 3000 страниц и даже не 90. Знания о параметрах, связанных с оперативной памятью, I/O, bgwriter`ом и вакуумом расположены в документации достаточно компактно. Отправной точкой для, как вы выражаетесь, «адресного поиска» может служить любой онлайн-калькулятор вроде pgtune.leopard.in.ua.puyol_dev2 Автор
18.03.2019 11:57А еще неплохо бы выучить тогда архитектуру конкретной СУБД, чтобы понимать какой параметр ОЗУ или I/O или CPU будет оказывать большее или меньшее влияние на производительность. Ставить бездумно параметры с сайтов типа pgtune.leopard.in.ua и смотреть, что получится, не понимая их сути — не самая здравая идея
ky0
18.03.2019 12:35Вы опять приписываете мне идеи, которых я не высказывал.
Какой параметр в каждом конкретном случае окажет большее влияние — зависит от многих критериев, в том числе не связанных с железом и никакая автонастройка все их не в состоянии объять. Разумеется, бездумно ничего ставить не нужно, именно для этого я и рекомендую обратиться к документации, а точнее тем её немногим страницам, которые как раз описывают влияние на СУБД. Pgtune я привёл в качестве примера отправной точки, раз уж вы отказываете людям в способности загуглить, что в первую очередь стоит настраивать после установки постгреса.puyol_dev2 Автор
18.03.2019 19:13Однако почему-то в коммерческих СУБД, например в DB2, есть автонастройка параметров (AUTOMATIC) www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0005181.html + реализованы профили настроек для различных приложений через переменную DB2_WORKLOAD, например 1C, SAP и тд
ky0
18.03.2019 19:53Хорошо, когда у вас база, на которой крутится нечто, с уже хорошо хорошо известными параметрами и имеющее профиль настроек. Но это скорее исключение в инновационной сфере, а поддержка всяких энтерпрайзных продуктов — это тема отдельная и не очень-то относящаяся к постгресу по понятным причинам. Он скорее не для мамонтятины, а для чего-то нового.
Ну и я, если бы был на свою голову, DBA DB2 или там Оракла какого-нибудь, не стал бы безоговорочно доверять подобным автонастройкам и профилям, хотя бы до проведения натурных испытания со сравнением с собственной конфигурацией.puyol_dev2 Автор
18.03.2019 20:30Как правило реляционные СУБД используются для широко распространённых продуктов — различных бухгалтерских, ERP систем и других систем различного финансового учёта, да и вообще, для хранения больших объемом структурированной информации. Там мало нужны инновации и свои велосипеды. Системные требования давно прописаны и конфигурации известны. И именно для таких продуктов старается сейчас позиционировать себя PGSQL, в частности компания Postgres Pro активно пиарится для 1С. Потому что инновации — это конечно хорошо, но основная маржа будет с рядового потребителя с массовым продуктом
ky0
18.03.2019 20:39Ну так и все распространённые рецепты для постгреса тоже давно известны, благо, комьюнити у него обширное и активное. Никакой неподъёмной задачи конфигурация базы из себя не представляет и отсутствие автонастройки и профилей не является значимым недостатком (мы же всё ещё про IT-бизнес, у них обычно с экспертизой проблем нет).
Возвращаясь к теме статьи — то, что её автор узнал для себя про веса операций (при условии, что остальные настройки у него в порядке) так поздно и это стало для него открытием — досадное недоразумение, только и всего.puyol_dev2 Автор
18.03.2019 20:53Как я понимаю, каждый останется при своём мнении, но вам лично не стоит злоупореблять ошибкой хайндсайта
movnet
Спасибо но уже где-то было ...https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0
megapro17
Статью читать пробовали?