Предлагаю вам ознакомиться с расшифровкой доклада Николая Самохвалова «Промышленный подход к настройке PostgreSQL: эксперименты над базами данных» Shared_buffers = 25% — это много или мало? Или просто так? Как узнать, подходит ли эта, довольно устаревшая, рекомендация в вашем конкретном случае? Пришло время подойти к вопросу выбора параметров postgresql.conf «по-взрослому».
Не с помощью слепых «автотюнеров» или устаревших советов из статей и блогов, а на основе:
- строго выверенные эксперименты на базах данных, проводимые автоматически, в больших количествах и в условиях, максимально приближенных к «боевым»,
- глубокое понимание особенностей СУБД и ОС.
Мы поговорим об экспериментах с базами данных.
Это история, которая длится чуть больше полугода.
Немного обо мне.
Опыт работы с Postgres более 14 лет. Основан ряд компаний социальных сетей.
Postgres использовался и используется везде.
Также группа RuPostgres на Meetup, 2 место в мире.
Мы постепенно приближаемся к 2000 человек.
RuPostgres.org. А на ПК различных конференций, в том числе и Highload, я с самого начала отвечаю за базы данных, в частности Postgres. 
А в последние несколько лет я возобновил свою консультационную практику по Postgres в 11 часовых поясах отсюда.
И когда я сделал это несколько лет назад, у меня был некоторый перерыв в активной ручной работе с Postgres, наверное, с 2010 года.
Я был удивлен, насколько мало изменилась рутина работы администратора базы данных и сколько ручного труда все еще приходится использовать.
И я сразу подумал, что здесь что-то не так, надо больше всего автоматизировать.
А поскольку все это было удаленно, то большая часть клиентов находилась в облаках.
И, очевидно, многое уже автоматизировано.
Подробнее об этом позже.
То есть все это привело к мысли, что должен быть ряд инструментов, т. е.
некая платформа, которая будет автоматизировать практически все действия администратора базы данных, чтобы можно было управлять большим количеством баз данных.
Этот отчет не будет включать:
- «Серебряные пули» и заявления типа — поставьте 8 ГБ или 25%shared_buffers и все будет хорошо.
О Shared_buffers много не будет.
- Хардкорные «внутренности».
Что случится?
- Будут принципы оптимизации, которые мы будем применять и развивать.
Будут всякие идеи, возникающие по ходу дела и различные инструменты, которые мы создаём по большей части в Open Source, т. е.
делаем основу в Open Source. Тем более, что у нас есть тикеты, вся коммуникация практически Open Source. Вы можете увидеть, что мы делаем сейчас, что будет в следующем релизе и т. д.
- Также будет некоторый опыт использования этих принципов, этих инструментов в ряде компаний: от небольших стартапов до крупных компаний.
Как это все развивается?
Во-первых, основная задача администратора базы данных, помимо обеспечения создания инстансов, развертывания резервных копий и т. д., — найти узкие места и оптимизировать производительность.
Сейчас это устроено так.
Смотрим мониторинг, что-то видим, но каких-то деталей нам не хватает. Мы начинаем копать более тщательно, обычно руками, и понимаем, что с этим так или иначе делать.
И есть два подхода.
Pg_stat_statements — это решение по умолчанию для выявления медленных запросов.
И анализ логов Postgres с помощью pgBadger. Каждый подход имеет серьезные недостатки.
В первом подходе мы выбросили все параметры.
И если мы увидим группы SELECT * FROM таблицы, где столбец равен "?" или «$», начиная с Postgres 10. Мы не знаем, сканирование ли это индекса или последовательное сканирование.
Это во многом зависит от параметра.
Если подставить туда редко встречающееся значение, то это будет сканирование индекса.
Если подставить туда значение, занимающее 90% таблицы, то последовательное сканирование будет очевидным, потому что Postgres знает статистику.
И это большой недостаток pg_stat_statements, хотя кое-какая работа ведется.
Самый большой недостаток анализа журналов заключается в том, что вы, как правило, не можете позволить себе «log_min_duration_statement = 0».
И об этом мы тоже поговорим.
Соответственно, вы не видите всей картины.
А какой-то запрос, который очень быстрый, может потреблять огромное количество ресурсов, но вы его не увидите, потому что он ниже вашего порога.
Как администраторы баз данных решают обнаруженные проблемы? 
Например, мы обнаружили какую-то проблему.
Что обычно делается? Если вы разработчик, то вы будете делать что-то на каком-то экземпляре разного размера.
Если вы администратор базы данных, то у вас есть постановка.
И он может быть только один.
И он отставал на шесть месяцев.
И ты думаешь, что пойдешь на производство.
И даже опытные администраторы баз данных потом проверяют в продакшене, на реплике.
А бывает, создают временный индекс, убеждаются, что он помогает, сбрасывают его и отдают разработчикам, чтобы они поместили его в файлы миграции.
Вот такая ерунда сейчас творится.
И это проблема.
- Настройте конфигурации.
- Оптимизируйте набор индексов.
- Измените сам SQL-запрос (это самый сложный метод).
- Добавьте мощности (самый простой способ в большинстве случаев).
С этими вещами происходит много всего.
В Postgres много дескрипторов.
Нам предстоит многое узнать.
В Postgres много индексов, спасибо также организаторам этой конференции.
И все это необходимо знать, и именно это заставляет людей, не являющихся администраторами баз данных, чувствовать, что администраторы баз данных практикуют черную магию.
То есть нужно 10 лет учиться, чтобы начать нормально все это понимать.
И я борец с этой черной магией.
Я хочу сделать все, чтобы была технология, и во всем этом не было никакой интуиции.
Примеры из жизни 
Я наблюдал это как минимум в двух проектах, включая свой собственный.
В другом сообщении блога говорится, что значение 1000 для default_statistict_target является хорошим.
Хорошо, давайте попробуем это в производстве.
И вот мы, используя наш инструмент два года спустя, с помощью экспериментов на базах данных, о которых мы говорим сегодня, можем сравнить то, что было и что стало.
И для этого нам нужно создать эксперимент. Он состоит из четырех частей.
- Первое — это окружающая среда.
Нам нужна часть оборудования.
И когда я прихожу в какую-то компанию и подписываю договор, я говорю, чтобы мне предоставили то же оборудование, что и на производстве.
Для каждого вашего Мастера мне нужен хотя бы один такой аппарат. Либо это инстанс виртуальной машины в Amazon или Google, либо мне нужна точно такая же железка.
То есть я хочу воссоздать окружающую среду.
А в понятие среды мы включаем мажорную версию Postgres.
- Вторая часть является объектом нашего исследования.
Это база данных.
Его можно создать несколькими способами.
Я покажу вам, как это сделать.
- Третья часть – нагрузка.
Это самый трудный момент.
- И четвертая часть — это то, что мы проверяем, т. е.
что с чем будем сравнивать.
Допустим, мы можем изменить один или несколько параметров в конфиге, либо создать индекс и т. д.
Мы запускаем эксперимент. Вот pg_stat_statements. Слева то, что произошло.
Справа - то, что произошло.
Слева default_statistics_target = 100, справа = 1000. Мы видим, что это нам помогло.
В целом все стало лучше на 8%.
Но если прокрутить вниз, то будут группы запросов от pgBadger или от pg_stat_statements. Есть два варианта.
Мы увидим, что некоторые запросы упали на 88%.
И тут на помощь приходит инженерный подход. Мы можем копать глубже, потому что задаемся вопросом, почему он затонул.
Вам нужно понять, что произошло со статистикой.
Почему большее количество сегментов в статистике приводит к худшим результатам.
Либо можем не копать, а сделать «ALTER TABLE… ALTER COLUMN» и вернуть 100 бакетов обратно в статистику этого столбца.
И тогда еще одним экспериментом мы сможем убедиться, что этот патч помог.
Все.
Это инженерный подход, который помогает нам видеть общую картину и принимать решения на основе данных, а не интуиции.
Пара примеров из других областей.
CI-тесты в тестировании существуют уже много лет. И ни один проект в здравом уме не сможет обойтись без автоматизированных тестов.
В других отраслях: в авиации, в автомобилестроении, когда мы проверяем аэродинамику, у нас тоже есть возможность ставить эксперименты.
Мы не будем запускать что-то с чертежа прямиком в космос или сразу какую-то машину на трассу не выставим.
Например, есть аэродинамическая труба.
Мы можем сделать выводы из наблюдений за другими отраслями.
Во-первых, у нас особая среда.
Это близко к производству, но не близко.
Его главная особенность в том, что он должен быть дешевым, повторяемым и максимально автоматизированным.
А еще должны быть специальные инструменты для проведения детального анализа.
Скорее всего, когда мы запускаем самолет и летим, у нас меньше возможностей изучить каждый миллиметр поверхности крыла, чем в аэродинамической трубе.
У нас есть больше диагностических инструментов.
Мы можем позволить себе перевозить более тяжелые вещи, которые мы не можем позволить себе поднять в воздух в самолете.
То же самое и с Постгресом.
В некоторых случаях мы можем включить полное ведение журнала запросов во время экспериментов.
И мы не хотим делать это на производстве.
Мы можем даже запланировать включить это с помощью auto_explain. И, как я уже сказал, высокий уровень автоматизации означает, что мы нажимаем кнопку и повторяем действия.
Так и должно быть, чтобы было много экспериментов, чтобы было на потоке.
Нэнси CLI — основа «лаборатории баз данных» 
И поэтому мы сделали это.
То есть я говорил об этих идеях в июне, почти год назад. И у нас уже есть так называемый Nancy CLI с открытым исходным кодом.
Это основа для создания лаборатории баз данных.
Нэнси — Он находится в открытом исходном коде, на Gitlab. Вы можете сказать это, вы можете попробовать это.
Я предоставил ссылку на слайдах.
Вы можете нажать на него, и он будет там помощь во всех отношениях.
Конечно, многое еще находится в стадии разработки.
Там много идей.
Но это то, чем мы пользуемся почти каждый день.
И когда у нас появится идея — почему когда мы удаляем 40 000 000 строк, всё сводится к IO, тогда мы можем провести эксперимент и посмотреть подробнее, чтобы понять, что происходит, а затем попытаться исправить это на лету.
То есть мы проводим эксперимент. Например, подправим что-нибудь и посмотрим, что получится в итоге.
А на производстве мы этого не делаем.
В этом суть идеи.
Где это может работать? Это может работать локально, т. е.
вы можете делать это где угодно, даже можете запустить на MacBook. Нам нужен докер, поехали.
Вот и все.
В каком-то случае вы можете запустить его на каком-либо оборудовании или на виртуальной машине где угодно.
А еще есть возможность удаленно запускать в Amazon в EC2 Instance, спотами.
И это очень крутая возможность.
Например, вчера мы провели более 500 экспериментов на экземпляре i3, начиная с самого младшего и заканчивая i3-16-xlarge. А 500 экспериментов обошлись нам в 64 доллара.
Каждый длился 15 минут. То есть за счет того, что там используются споты, это очень дешево - скидка 70%, посекундная тарификация Амазона.
Вы можете многое сделать.
Вы можете провести настоящее исследование.
Поддерживаются три основные версии Postgres. Не так уж и сложно допилить некоторые старые и новую 12-ю версию тоже.
Мы можем определить объект тремя способами.
Этот:
- Файл дампа/sql.
- Основной способ — клонировать каталог PGDATA. Как правило, он берется с резервного сервера.
Если у вас есть обычные бинарные резервные копии, вы можете сделать оттуда клоны.
Если у вас есть облака, то облачный офис, такой как Amazon и Google, сделает это за вас.
Это важнейший способ клонировать реальное производство.
Вот как мы раскрываемся.
- И последний способ подойдет для исследования, когда вы хотите понять, как что-то работает в Postgres. Это пгбенч.
Вы можете сгенерировать с помощью pgbench. Это всего лишь один вариант «db-pgbench».
Ты скажи ему, какой масштаб.
И все будет генерироваться в облаке, как заявлено.
И загружаем:
- Мы можем выполнить загрузку в одном потоке SQL. Это самый примитивный способ.
- И мы можем эмулировать нагрузку.
И мы можем подражать этому, прежде всего, следующим образом.
Нам нужно собрать все логи.
И это больно.
Я покажу вам, почему.
И с помощью pgreplay играем, который встроен в Нэнси.
- Или другой вариант. Так называемая крафтовая нагрузка, которую мы делаем с определенным усилием.
Анализируя текущую нагрузку на боевую систему, мы вытягиваем топовые группы запросов.
И с помощью pgbench мы можем эмулировать эту нагрузку в лаборатории.
- Либо нам придется выполнить какой-то SQL, т.е.
мы проверим какую-то миграцию, создадим там индекс, выполним там ANALAZE. И мы посмотрим, что произошло до вакуума и после вакуума.
В общем, любой SQL.
- Либо меняем один или несколько параметров в конфиге.
Мы можем попросить нас проверить, например, 100 значений в Amazon для нашей терабайтной базы данных.
И уже через несколько часов вы будете иметь результат. Как правило, на развертывание терабайтной базы данных у вас уйдет несколько часов.
Но патч в разработке есть, у нас возможна серия, т.е.
можно последовательно использовать одни и те же pgdata на одном сервере и проверять.
Postgres перезапустится, и кэши будут сброшены.
И можно управлять грузом.
- Приходит каталог с кучей разных файлов, начиная со снимков pg. статистика ***.
И самое интересное там — pg_stat_statements, pg_stat_kcacke. Это два расширения, которые анализируют запросы.
А pg_stat_bgwriter содержит не только статистику pgwriter, но и по контрольной точке и по тому, как сами бэкенды вытесняют грязные буферы.
И все это интересно посмотреть.
Например, когда мы настраиваемshared_buffers, очень интересно посмотреть, сколько каждый заменил.
- Журналы Postgres также приходят. Два журнала – журнал подготовки и журнал воспроизведения загрузки.
- Относительно новая функция — FlameGraphs.
- Кроме того, если вы использовали параметры pgreplay или pgbench для воспроизведения загрузки, то их вывод будет нативным.
И вы увидите задержку и TPS. Можно будет понять, как они это увидели.
- Системная информация.
- Базовые проверки ЦП и ввода-вывода.
Это больше для инстанса EC2 в Amazon, когда вы хотите запустить 100 одинаковых инстансов в потоке и запустить там 100 разных запусков, то у вас будет 10 000 экспериментов.
И нужно следить, чтобы вам не попался бракованный экземпляр, который уже кем-то притесняется.
Другие активны на этом железе, а у вас осталось мало ресурсов.
Такие результаты лучше отбросить.
И с помощью sysbench от Алексея Копытова делаем несколько коротких проверок, которые придут и их можно будет сравнить с другими, т.е.
вы поймете, как ведет себя ЦП и как ведет себя IO.
В чем заключаются технические трудности на примере разных компаний?
Допустим, мы хотим повторить реальную загрузку с использованием журналов.
Это отличная идея, если она написана на pgreplay с открытым исходным кодом.
Мы используем это.
Но чтобы это работало хорошо, необходимо включить полное ведение журнала запросов с параметрами и временем.
Есть некоторые сложности с продолжительностью и меткой времени.
Мы опустошим всю эту кухню.
Главный вопрос – можете ли вы себе это позволить или нет? 
https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408 Проблема в том, что его может не быть в наличии.
Прежде всего, вы должны понять, какой поток будет записываться в журнал.
Если у вас есть pg_stat_statements, вы можете использовать этот запрос (ссылка будет доступна на слайдах), чтобы примерно понять, сколько байт будет записано в секунду.
Смотрим на длину запроса.
Мы пренебрегаем тем фактом, что параметров нет, но мы знаем длину запроса и знаем, сколько раз в секунду он выполнялся.
Таким образом мы можем приблизительно оценить, сколько байтов в секунду.
Мы можем ошибиться вдвое больше, но именно так мы точно поймем порядок.
Мы видим, что этот запрос выполняется 802 раза в секунду.
И видим, что будет записано bytes_per sec — 300 кБ/с плюс-минус.
И, как правило, такой поток мы можем себе позволить.
Но! Дело в том, что существуют разные системы логирования.
И по умолчанию люди обычно используют «syslog».
А если у вас есть системный журнал, то у вас может быть такая картина.
Возьмем pgbench, включим логирование запросов и посмотрим, что произойдет. 
Без логирования – это столбец слева.
Мы получили 161 000 TPS. С системным журналом — это в Ubuntu 16.04 на Amazon, получаем 37 000 TPS. А если перейти на два других метода логирования, то ситуация будет намного лучше.
То есть мы ожидали, что оно упадет, но не в такой степени.
А на CentOS 7, в котором еще и Journald участвует, переводя логи в бинарный формат для удобного поиска и т.д., то там вообще кошмар, 44 раза роняем в TPS. 
И этим люди живут. И зачастую в компаниях, особенно крупных, это изменить очень сложно.
Если вы можете уйти от системного журнала, пожалуйста, откажитесь от него.
- Оцените IOPS и поток записи.
- Проверьте свою систему журналирования.
- Если прогнозируемая нагрузка слишком велика, рассмотрите возможность отбора проб.
У нас есть pg_stat_statements. Как я уже сказал, оно должно быть там.
А мы можем взять и описать каждую группу запросов особым образом в файле.
И тогда мы сможем использовать очень удобную возможность в pgbench — это возможность вставлять несколько файлов с помощью опции «-f».
Он понимает много "-f".
И с помощью «@» в конце вы можете указать, какую долю должен иметь каждый файл.
То есть можно сказать, что так делают в 10% случаев, а это в 20%.
И это приблизит нас к тому, что мы видим в производстве.
Как мы будем понимать, что у нас есть в производстве? Какую долю и как? Это немного в сторону.
У нас есть еще один товар Postgres-проверка .
Также база в Open Source. И мы сейчас активно его развиваем.
Он родился по немного другим причинам.
По причинам, что мониторинга недостаточно.
То есть приходишь, смотришь базу, смотришь на проблемы, которые есть.
И, как правило, вы делаете проверку здоровья.
Если вы опытный администратор базы данных, то вы выполняете health_check. Мы рассмотрели использование индексов и т. д. Если у вас есть OKmeter, то отлично.
Это крутой мониторинг для Postgres. OKmeter.io – установите пожалуйста, там все сделано очень хорошо.
Это платно.
Если у вас его нет, то обычно у вас его немного.
В мониторинге обычно есть CPU, IO, а дальше с оговорками и всё.
И нам нужно больше.
Нам нужно посмотреть как работает автовакуум, как работает чекпоинт, в io нужно отделить чекпоинт от bgwriter и от бэкендов и т.д. Проблема в том, что когда ты помогаешь большой компании, она не может что-то быстро реализовать.
Они не могут быстро купить OKmeter. Возможно, они купят его через шесть месяцев.
Некоторые посылки они не могут быстро доставить.
И мы пришли к мысли, что нам нужен специальный инструмент, который не требует ничего устанавливать, т. е.
на продакшене вообще ничего не надо устанавливать.
Установите его на свой ноутбук или на сервер наблюдения, откуда вы будете его запускать.
И он будет анализировать много чего: и операционную систему, и файловую систему, и сам Postgres, делая какие-то легкие запросы, которые можно запустить прямо в продакшн и ничего не выйдет из строя.
Мы назвали это Postgres-проверкой.
Говоря медицинским языком, это регулярный осмотр здоровья.
Если это автомобильная тематика, то это как ремонт. Вы проводите техническое обслуживание своего автомобиля каждые шесть месяцев или год, в зависимости от марки.
Вы занимаетесь обслуживанием своей базы? То есть регулярно ли вы проводите глубокие исследования? Это необходимо сделать.
Если делаете бэкапы, то делайте проверку, это не менее важно.
И у нас есть такой инструмент. Активно оно начало появляться лишь около трёх месяцев назад. Он еще молод, но его уже много.
Сбор наиболее «влиятельных» групп запросов — отчет К003 в Postgres-проверке И есть группа отчетов К.
Пока три отчета.
И есть такой отчет К003. Есть топ из pg_stat_statements, отсортированный по total_time. Когда мы сортируем группы запросов по total_time, вверху мы видим группу, которая больше всего нагружает нашу систему, т.е.
потребляет больше ресурсов.
Почему я даю названия группам запросов? Потому что мы выбросили параметры.
Это уже не запросы, а группы запросов, т. е.
они абстрагированы.
А если мы оптимизируем сверху вниз, мы облегчим наши ресурсы и отсрочим момент, когда нам понадобится обновление.
Это очень хороший способ сэкономить.
Возможно, это не очень хороший способ заботиться о пользователях, ведь мы можем не видеть редких, но очень досадных случаев, когда человек ждал 15 секунд. В общем, они настолько редки, что мы их не видим, а имеем дело с ресурсами.
Что произошло в этой таблице? Мы сделали два снимка.
Postgres_checkup выдаст вам дельту по каждой метрике: общее время, звонки, строки,shared_blks_read и т. д. Вот и все, дельта рассчитана.
Большая проблема с pg_stat_statements заключается в том, что он не помнит, когда он был сброшен.
Если pg_stat_database запоминает, то pg_stat_statements не запоминает. Вы видите, что есть число 1 000 000, но мы не знаем, откуда мы отсчитали.
И вот мы знаем, вот у нас есть два снимка.
Мы знаем, что дельта в данном случае составила 56 секунд. Очень короткий разрыв.
Сортировка по общему_времени.
И тогда мы можем дифференцировать, т. е.
делить все метрики по длительности.
Если мы разделим каждую метрику на продолжительность, мы получим количество звонков в секунду.
Далее, total_time в секунду — мой любимый показатель.
Он измеряется в секундах, в секунду, т.е.
сколько секунд понадобилось нашей системе для выполнения данной группы запросов в секунду.
Если вы видите там больше секунды в секунду, значит, вам пришлось отдать больше одного ядра.
Это очень хороший показатель.
Можно понять, что этому другу, например, нужно минимум три ядра.
Это наше ноу-хау, ничего подобного я нигде не видел.
Обратите внимание – это очень простая вещь – секунда в секунду.
Иногда, когда ваш ЦП загружен на 100%, то по полчаса в секунду, то есть вы потратили полчаса на выполнение только этого запроса.
Далее мы видим количество строк в секунду.
Мы знаем, сколько строк в секунду он вернул.
И тут тоже есть интересная вещь.
Сколько общих_буферов мы читаем в секунду из самого общего_буфера.
Попадания уже были, а строки мы брали из кэша операционной системы или с диска.
Первый вариант быстрый, а второй может быть быстрым, а может и нет, в зависимости от ситуации.
И второй способ дифференциации — разделить количество запросов в этой группе.
Во втором столбце у вас всегда будет один запрос, разделенный на каждый запрос.
И тут интересно — сколько миллисекунд было в этом запросе.
Мы знаем, как этот запрос ведет себя в среднем.
На каждый запрос требовалось 101 миллисекунда.
Это традиционная метрика, которую нам нужно понять.
Сколько строк в среднем вернул каждый запрос? Мы видим 8, что эта группа возвращается.
В среднем сколько было взято из кэша и прочитано.
Мы видим, что все хорошо кэшируется.
Твердые хиты для первой группы.
Теги: #Системное администрирование #Администрирование сервера #Администрирование баз данных #postgresql #sql
-
Социальные Профили Онлайн
19 Oct, 24 -
Выберите Прибыльный Источник Трафика
19 Oct, 24 -
Топ-10 Вирусных Видео, Февраль 2010 Г.
19 Oct, 24 -
Аутсорсинг Интернет-Магазинов
19 Oct, 24
