Расшифровка доклада 2014 года Ильи Космодемьянского "Как PostgreSQL работает с диском".

Часть поста, конечно, устарела, но здесь рассмотрены фундаментальные моменты PostgreSQL при работе с диском, которые актуальны и сейчас.

Диски, память, цена, процессор — в таком порядке смотрят на характеристики сервера админы, покупающие машину под базу данных. Как эти характеристики взаимосвязаны? Почему именно они?

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

"Железо", настройки операционной системы, файловой системы и PostgreSQL: как и для чего выбирать хороший setup, что делать, если конфигурация "железа" не оптимальна, и какие ошибки могут сделать бесполезным самый дорогой RAID-контроллер. Увлекательное путешествие в мир батареек, "грязных" и "чистых" страниц, хороших и плохих SSD-дисков, покрасневших графиков мониторинга и ночных кошмаров системных администраторов.

Меня зовут Илья Космодемьянский. Я работаю в компании PostgreSQL-Consulting. Занимаюсь самыми разными вещами, связанными с Postgres, с его производительностью и тому подобными штуками.

Многие базы данных, в том числе Postgres, работают с диском. И если у вас есть какие-то проблемы с записью Write Ahead log, если у вас проблема с высоким IO в PostgreSQL, то этот доклад для вас. Будет некоторое количество теории и концентрированная выжимка рецептов, как бороться, если есть проблема с вводом-выводом, как правильно подходить к настройке PostgreSQL.

Мы рассмотрим:

Зачем базе данных диск?
Что особенного в работе с диском у PostgreSQL?
Потенциальные узкие места, где могут начаться проблемы с диском.
Мониторинг дисковой нагрузки. Как оценить эти проблемы? Как отслеживать их на регулярной основе, на какие параметры лучше смотреть?
Выбор аппаратного обеспечения для сервера с PostgreSQL.
Настройка. Как лучше настроить операционную систему и сам PostgreSQL для того, чтобы максимально хорошо он справлялся с большой нагрузкой на диск.

Во-первых, база данных апеллирует страничками. Я думаю, что все, кто с ними работают, это знают. Странички нужно читать с диска, записывать на диск.
Но прежде чем записать на диск, они должны быть записаны в Write Ahead Log, потому что это быстрее. У многих баз бывает проблема с записью Write Ahead Log, потому что много транзакций. И диски не успевают под Write Ahead Log с этим справиться.
Но самая главная проблема – это выполнение checkpoint, когда у нас происходит синхронизация WAL с блочным хранилищем, в котором эти странички живут перманентно.

У PostgreSQL есть своя специфика:

У PostgreSQL есть процесс autovacuum. Это подчистка предыдущих версий данных, которые уже выпали из области видимости транзакции. PostgreSQL выполняет их отложенную очистку с помощью demon autovacuum. При правильных настройках – это не очень сложная фоновая процедура. Но если настройки неправильные, то там может быть много всяких разных проблем.
Еще у PostgreSQL есть pg_clog. Это битовая маска с текущими статусами транзакции. И для ускорения доступа к этим вещам PostgreSQL кладет на диск такой файл pg_clog. И на ряде баз с очень большой нагрузкой OLTP, наступает перегрев на этом месте. Но для борьбы с этим можно использовать, например, RAM-диск. Этот файлик совершенно безопасно можно положить на RAM-диск. Если RAM-диск при этом у вас развалится, будут небольшие тормоза, пока PostgreSQL с этим не разберется и сделает новый файл. Но потерять его совершенно не критично. Эти данные используются только для ускорения, но не являются критичными данными.
И tmp, дисковые сортировки, хэширование – это общее для многих баз данных. Единственное, если у вас запросы свопятся на диск для того, чтобы произвести там сортировку, хэширование и т. д., то это ненормальная ситуация и не совместима с быстродействием базы данных. По-хорошему вам параметр work_mem (это память, которая выделяется на каждый воркер PostgreSQL) увеличить, чтобы эта штука влезала туда. И, естественно, смотреть на запрос в explain analyze – отображается ли информация о том, что запрос отсвопился на диск. И там тюнить уже в соответствии с этими вещами.

Как я уже упомянул, самое критичное – это checkpoints, так называемые, спайки. От английского «pik» — вершина. Отображаются на графиках дисковой утилизации и очень сильно ставят под вопрос о возможности обслуживания базой остальные транзакции.

Почему это происходит? В отличие от Oracle, у которого есть Log Writer и DBwriter, которые умеют отсвопливать дополнительные процессы, чтобы быстрее синхронная запись осуществлялась, PostgreSQL все делает через fsync. Он поддерживается многими платформами.

Fsync – это эгоистичный вызов в UNIX. И вызывается fsync большого количества страниц, то база очень слабо может что-то обслуживать еще. И для операционных систем это тоже очень большая нагрузка. Поэтому в основном речь пойдет о том, каким образом оптимизировать вот эти checkpoints, потому что они являются наиболее общей проблемой.

Как устроена картина мира? У PostgreSQL имеются shared_buffers, имеется кэш ОС, через который с диска и на диск происходит обмен страничками готовыми. Т. е. когда у нас страничка просто поднята в шаредную память с диска, она является чистой, в ней нет никакого изменения. Если мы сделали изменения: произвели update, insert хотя бы одного tuple в этой памяти, то страничка помечается как грязная.

Что такое checkpoint и как это все собрать воедино? Вот эти чистые странички, когда помечаются грязными, прежде всего происходит запись последовательная в WAL, необходимая для восстановления информации.
Когда эта информация записана, у нас еще в шаредной памяти висят грязные странички. Но информация о них записана в WAL и COMMIT возвращает, что транзакция успешно закоммитчена и все хорошо прошло.
Чтобы синхронизировать этот WAL обратно с блочным хранилищем, и чтобы грязных страничек в шаредной памяти не осталось, база вызывает функцию checkpoint. Он вызывается по некоторым событиям. Checkpoint занимается тем, что он из WAL данные переносит с помощью fsync в блочное хранилище.
Если у нас shared_buffers достаточно большие, то эта операция может быть весьма больной для всей базы данных и для всей ОС в целом, потому что через shared_buffers, через кэш ОС к дискам прокачивается большой объем данных. При этом прокачивается более-менее одновременно.

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

Почему хорошо смотреть именно дисковую утилизацию? У вас на ней будут пики. Когда у вас дисковая утилизация маленькая, то это значит, что база накапливает эти изменения, которые у вас есть в shared buffers. У вас там много грязных страниц. При этом все более-менее хорошо работает, потому что диск ничем не занят.

Как только у вас пошел checkpoint, пошел дамп этих грязных страниц через pdflush вниз на диск. И дисковая IO возрастает многократно. И вполне может быть, что вы упираетесь в 100 % дисковой утилизации. Это очень плохая ситуация, потому что fsync блокирует все, что только можно. Ничего больше операционная система делать не может и база, соответственно, тоже. И это может продолжаться некоторое время, и вы не можете банальный select сделать, если все совсем запущено.

И можно смотреть статистическую вьюху pg_stat_bgwriter, которая говорит о том, как checkpoint произошел, какие checkpoints произошли. И на основе нее можно делать разумный тюнинг. Как ей пользоваться я немножко позже покажу.

Вот pg_stat_bgwriter. Это взято с простой виртуалки, на которой толком ничего не настроено, просто коробочные настройки. И тут мы видим, что все достаточно плохо. У нас первые две строчки запрошены и по тайм-ауту срабатывают примерно в каких-то сопоставимых соотношениях. Нет такой ситуации, что одних много, других нет. И это, скорее всего значит, что у нас что-то настроено не оптимально.

И если мы посмотрим в такой ситуации на график дисковой утилизации, то там тоже все будет не очень хорошо. И это признак того, что нам надо какие-то гайки подкрутить.

Если у нас все по умолчанию идет, то с самого начала инсталляции базы, у нас считаются вот эти счетчики. Это не очень полезно для оптимизации, потому что характер профиля нагрузки на базу меняется чаще, чем с того момента, как мы поставили PostgreSQL. Поэтому полезно эту статистику сбрасывать периодически приведенной вот здесь командой. И анализировать за какой-то короткий период времени, например, за день. Когда очень много нагрузки, то лучше даже за какие-то более небольшие промежутки времени, например, за час или два часа. Т. е. периодически надо таким образом сбрасывать только вот эту статистику.

Что делать в ситуации, когда у нас все плохо с записью на диск?

Нужно обеспечить базу правильным hardware. Если более-менее нормальное железо, то у вас все будет более-менее неплохо.

Во-первых, ни в коем случае не надо покупать дешевый RAID-контроллер. Что называется дешевым RAID-контроллером? Основная цена RAID-контроля – это чип, который на нем есть, его собственные мозги. Если этого чипа нет и контроллер стоит, условно говоря, 100 долларов за новый не б/у контроллер, то, скорее всего, этого чипа там нет. И этот контроллер будет использовать вашу CPU.

Если контроллер использует вашу CPU, то у меня для вас плохие новости, потому что любой software RAID будет лучше, потому что будет происходить драка за ресурсы там, где ее не должно быть.
RAID-контроллер должен быть с батарейкой. Т. е. у вас должен быть кэш. И этот кэш должен быть забэкаплен батарейкой. И если ваша система упадет, то у вас эти данные останутся.

Как это связано с быстродействием? С быстродействием это связано напрямую. Если у вас есть батарейка, то fsync проходит диск, данные попали на батарейку. И после этого fsync возвращает, что он успешно записал. А уже батарейка гарантирует, что данные будут записаны. Если батарейки нет, например, ее нет или она не поставлена, то начинаются проблемы такого рода, что пока достоверно не записаны данные на диск, fsync не отвечает и у вас ситуация с checkpoints еще больше усугубляется. Поэтому, когда вы работаете с RAID, то батарейка должна присутствовать и быть исправной.
Производителя RAID я не буду рекомендовать, потому что это не так критично. Т. е. я не рекламирую какого-то конкретного производителя. Если у вас есть опыт работы с каким-то конкретным RAID и вы знаете как его настраивать, и все хорошо, то пользуйтесь им. Мы больше всего любим megaraid или perc, что в какой-то мере одно и тоже, поэтому у этих систем неизвестно нам неприятных фокусов за долгие годы опыта. И у них консервативные утилиты управления, т. е. все гайки на привычных местах и с ними работать удобно.

Например, HP тоже делает очень хороший RAID-контроллеры, но у них ситуация обратная. Мы несколько раз за последнее время напоролись на такие проблемы, когда вы покупаете не топовые диски, вставляете их в топовый контроллер и диски искусственно шейпятся по скорости, потому что производитель заботится о том, чтобы покупали только самые дорогие диски. И этого никак нельзя понять, кроме как сравнить несколько подномеров. Это нехорошая ситуация. И особенно, когда берут сервера в аренду у хостера, то на такие вещи наступают. Вот таких штук надо остерегаться.
Для RAID имеет смысл соблюдать вот такие настройки. Т. е. должен быть включен write back cache. И io mode должен быть direct. Не потому что асинхронный – это плохо, а потому что PostgreSQL умет fsyng простой.
И Disk Write Cache Mode. Если у вас не самые топовые диски энтерпрайзные, то его лучше выключать. Если у вас хороший SSD серверного класса, у которого есть конденсатор, то он там должен быть включен, потому что эта штука гарантирует то, что у вас при падении железа, конденсатор успеет сохранить на флешке то, что у вас на диск с контроллера попало.

Чтобы все работало хорошо дефолтная рекомендация, кроме специфичных случаев, используйте маленькие SAS. Они уже есть достаточно скоростные. И при этом вы выигрываете в скорости seek чисто за счет физики, вернее даже геометрии. Потому что банально по небольшому диску головки двигаться нужно меньше и расстояние там меньше. Соответственно, происходит это все быстрее.
Не используйте десктопного класса SSD. В SSD сейчас все верят как в панацею, что оно спасет мир. Это не совсем так, потому что SSD бывает очень разное. Поставив не очень хороший SSD, вы рискуете получить скорость сопоставимую с SATA и еще кучу проблем приобретете с тем, что он может элементарно отказать, развалиться. У него может оказаться меньше циклов перезаписи, чем производитель гарантирует.
И я бы еще не советовал использовать SSD only инсталляцию для базы данных, для PostgreSQL в частности. Потому что помимо тех вещей, где SSD дает выигрыш, у вас есть еще Write Ahead Log, у вас есть еще temp, у вас есть еще много всякой такой записи, которая никак не ускоряется SSD, потому что последовательная запись WAL – это не очень хорошая вещь для SSD. Сокращает количество циклов перезаписи и вам приходится часто менять эти SSD. При этом они могут сгорать достаточно непредсказуемо. И вы не получаете никакого выигрыша, кроме хлопот. Вам нужно будет приставлять админа с лопатой, чтобы он подкидывал туда как в топку новые винты. Это не самая лучшая идея.
Что касается RAID, то тут, я думаю, никакой Америки не открою, лучше использовать 10, потому что иногда велик соблазн сэкономить и получить чуть-чуть места за счет 5-го или 6-го. Но, во-первых, этот RAID более медленный. А, во-вторых, более уязвимый, потому что сгорел диск и у вас начались серьезные проблемы. Stripe быстрее, но тут вопрос сохранности данных. Если у вас stripe развалился, то все плохо.
И если у вас нет возможности хорошие диски поставить, то надо выбирать. С включенным синхронным коммитом, когда база данных выполняет коммит и до того, пока все коммиты не попали на диске, на батарейку или куда-то еще, не возвращает и ждет. И без нормального RAID с батарейкой или хорошо настроенного массива – это никогда не будет работать быстро. Поэтому если у вас SATA диски или какая-то имеется нагрузка, то эту штуку лучше ставить в off, но при этом понимать, что вы можете потерять последние транзакции, которые закоммитчены.

С одной стороны, ничего в этом страшного нет, потому что база у вас все равно будет консистентная, но с другой стороны, ваше приложение считает, что на эти транзакции получен положительный ответ – коммит. А вы эти транзакции потеряли и, скорее всего, можете никогда об этом не узнать. Эта ситуация неприятная. Только если у вас некритичные данные, можно такие штуки делать.

Файловая система. И как обеспечить максимальную производительность с базой?

Во-первых, c noatime все знают, что с ним будет медленнее, чем без него. Но не все знают, что лучше еще отключать барьер. Есть такая опция barrier, которая включена на большинстве правильных файловых систем типа xfs или ext4 по умолчанию.

Что это дает? Когда у вас есть запись журнала в файловой системе, данные для журнала записались или, наоборот, данные для inodes, а соответствующие этим данным для журнала данные для inodes не записались. И получается такая ситуация, что если в этот момент обрушится напряжение, то вы не сможете восстановить систему средствами встроенного журнала. В такой ситуации в Linux вызывается scall, которые останавливает дальнейший дамп на диск. И ядерный буфер начинает перестраиваться, чтобы для этих данных журнала нашлись данные inodes, которые надо записать.

Если у вас 128 GB shared buffers, то вы имеете очень большую проблему и вам никак не помогает ваш хороший RAID-контроллер. Потому что он никак не используется. За счет этого барьера начинается проблемы. Обратите внимание, что в Debian это можно посмотреть, только если включить полную выдачу, иначе это не показывается.

Еще полезно понимать, что, если у вас есть вкусная и удобная для админа система типа zfs, где гибко все настраивается, все можно переносить partition туда, partition сюда. И, как правило, это не про перфоманс. Т. е. для тестовой среды это работает. Для production это медленно и надо использовать простую xfs или ext4 в Linux, соответственно.

Что касается операционной системы, то у нее устроена такая же штука типа базы данных. Т. е. есть чистые, грязные страницы. Их надо дампить на диск.

По умолчанию почему-то это контролируется параметрами, выставленными в такое значение: vm.dirty_ratio=20 и vm.dirty_background_ratio=10. Это по умолчанию для большинства дистрибутивов.

Что это такое? Pdflush, когда скидывает грязные страницы на диск, не начинает работать, если не накоплено 10 или 20 % от оперативной памяти. В большинстве случаев это очень много. Представьте, что у вас 128 GB оперативной памяти, у вас 20 % от этого дела. Дорогой и хороший кэш на RAID – 1-2 GB, редко бывает больше. А то, может быть и 512 MB. Соответственно, вы эффективно забиваете весь кэш этой штукой. При этом, когда эта штука еще накапливается, pdflush не работает и не использует это окно, когда можно более безболезненно эти вещи сдамбить на диск.

(В посте Настройка параметров ядра Linux для оптимизации PostgreSQL написано: установка vm.dirty_background_ratio в значение 5% повышает производительность до ~ 25% в большинстве случаев.)

Лучше перестраивать на vm.dirty_bytes. Это альтернативная настройка. Если в какое-то значение ее устанавливается, то первый вариант настройки эффективно становится равен нулю. И по этим байтам, чтобы оно достоверно помещалось в кэш батарейки. Тогда у вас ситуация будет существенно лучше, чем по этим дефолтным.

Если у вас более простая инсталляция и нет кэша на RAID, то тем более вам нужны еще меньшие эти значения поставить, потому что, если много оперативной памяти, вам ввод-вывод убьет с гарантией. Эти вещи надо достоверно менять.

И нужно настроить postgresql.conf, чтобы checkpoints писались поаккуратней, получше.

Wal_buffers – это те файлы логов, которые пишет PostgreSQL. Их можно сделать чуть-чуть побольше, чтобы накапливалось больше изменений, после этого происходил checkpoint. И большим скопом данные переносились, и не получалось так, что у вас постоянная такая пила на дисковой утилизации, что постоянно диски чем-то заняты и никогда нет ситуации с оптимальным вводом-выводом.
Если вы исходите из идеи, что у вас по мере накопления грязных один большой flush происходил на диск, то ставите checkpoint segment в какое-то более-менее большое значение. Здесь значение 256. А у меня кое-где есть и 1000 сегментов между checkpoints, если это оправдано, можно ставить высокие значения. Но надо понимать, что в такой ситуации у вас уже будет checkpoint, например, не через 48 MB накопление грязных страниц, а через 4 GB. Это довольно много для большинства не очень продвинутых RAID-контроллеров.
В такой ситуации вам нужно исключить checkpoints по тайм-ауту, чтобы они не происходили слишком часто и не перегружать таким паразитным вводом-выводом систему. Поэтому вы выкручиваете checkpoint_timeout на максимум. По умолчанию, по-моему, это час. И тогда все checkpoints при большой записи будут происходить только при накоплении сегментов с разумной частой. В pg_stat_bgwriter у вас одно значение будет 0, другое будет расти соответственно тому, с какой частотой у вас происходит checkpoints.

Тут можно применять две идеологии. Можно наоборот сделать. Checkpoints сделать только по тайм-ауту, выставив какое-то разумное значение. А сегменты поставить большими, чтобы чекпоинтилось через какие-то промежутки времени, сугубо исходя из того, какова вам нужна скорость восстановления в случае аварии. Потому что если у вас checkpoint происходит чисто по сегментам, то у вас немного дольше займет время восстановления, чем если он у вас будет происходить по тайм-ауту.

Есть такой параметр, как checkpoint_completion_target, который мы советуем выставлять в 0,7-0,9. Это процент времени до следующего checkpoint, за который предыдущий должен завершится. Т. е. это такое средство, чтобы размазать дисковую нагрузку между двумя checkpoints, чтобы она постепенно спадала и не было слишком интенсивных записей в момент checkpoint, а потом более-менее никаких.

Если бы она была выставлена в 0,1, то это означало бы, что необходимо за 10 % того времени, которое происходит между checkpoints, скинуть все грязные страницы на диск. И это была бы очень-очень интенсивная запись, что не особо хорошо.

Как понять, что все классно и мы все сделали правильно?

У PostgreSQL есть прекрасная утилита, как pg_test_fsync. Вы смотрите, как вы настроили ОС, как вы настроили hardware и смотрите сколько iops, эмуляции этого нормального жизненного процесса checkpoints PostgreSQL, сколько операций ввода-вывода вы получили.

Если вы получаете вот такие небольшие цифры (это виртуалка на моем ноутбуке), то значит, что это не очень хорошо. Значит, вы что-то не достроили. Например, барьер не отключили. Я, по-моему, его здесь не отключал. Либо у вас там плохие диски и т. д.

Если у вас цифры отличаются на порядок в лучшую сторону, то, по крайней мере, на стороне операционной системы и файловой системы у вас проблем нет.

Но здесь не вся выдача показана. Там она длиннее, там разные тесты производятся с разными размерами страниц и т. д. Посмотрите и изучите ее на досуге. Когда вы собираете hardware на новый сервер, эта штука очень полезна для того, чтобы на нее посмотреть.

И еще небольшой hack, который очень сложно истолковать. И поэтому мало, кто его в тупую советует.

Помимо checkpoint грязные страницы в PostgreSQL может списывать bgwriter, т. е. сам бэкенд. И это в ряде случаев полезно.

Почему это полезно? Потому что checkpoint массово списывает много грязных страниц. И в этот момент нагрузка на дисковую подсистему самая максимальная. Bgwriter может списывать некую страницу, которая грязная, но давно не использовалась. Она утонула по кэшу вниз. И если она больше никому не нужна, она может быть списана на диск самим бэкендом.

В отличие от checkpoint это происходит в фоновом режиме и не сильно загружает на запись все это дело. Обычно bgwriter не занят интенсивной записью. И он может себе позволить некоторое количество страниц списать и тем самым разгрузить checkpoint.

Вот эти три параметра регулируют то, каким образом страница падает в кэше. И полезно их на максимум, который возможен, выкрутить. Максимум здесь указан. Это 10 000, 1 000 и 10. Вот эти параметры ставятся. И в такой ситуации мы наблюдаем, что у нас checkpoint разгружается от лишней записи и больше нагрузки ложится на bgwriter. И ситуация с большими checkpoints выравнивается на экстремальных нагрузках по update, insert, когда много лога генерируется, там много грязных страниц. Эта настройка правильная, ее нужно использовать.

Про что я еще забыл сказать и о чем обязательно нужно упомянуть? В PostgreSQL есть очистка устаревших версий – autovacuum. С autovacuum есть неприятная вещь. С ним очень многие люди мучаются, потому что autovacuum включили, он работает третий день, занял очень много места и все плохо. И люди решают его выключить, чтобы он не мешал. И его выключают.

Потом он снова стартует и пытается работать все дольше, потому что предыдущих версий накопилось много. И дальше заканчивается все печально: по таблице 40 MB банальный select идет 30 секунд или что-нибудь в этом роде. И сначала злобно обвиняется PostgreSQL, а потом ищутся виноватые.

Такая ситуация регулярно бывает. И нужно знать, как правильно настраивать autovacuum, чтобы таких проблем не было.

Autovacuum из коробки настроен не агрессивно. Что это значит? Есть два параметра: autovacuum_vacuum_scale_factor и autovacuum_analyze_scale_factor. По дефолту один из них выставлен в 20 %. Что это значит 0,02? Это значит, что у вас пришли изменения в таблицу. Изменилось 20 % записи. От них остались предыдущие версии, потому что PostgreSQL реально не делает updates, он делает insert нового tuple и делает delete. Delete – эффективно никакой не delete, а просто он убирает из области видимости текущего скопа транзакций данную версию. И tuple продолжает лежать на диске, ее autovacuum должен вычистить.

Вот у вас накопилось 20 % данных в таблице. Представьте, что будет, если у вас миллиард записей. И после этого вакуум начал мучительно несколько часов подряд это все дело переколбашивать. И это большой overhead по диску, потому что ему надо это дело писать. И, соответственно, куча проблем такого свойства, что у вас длинные транзакции, ddl, которые соответственно мешаются с autovacuum. И база чувствует себя плохо.

По-хорошему эту штуку надо выставлять в минимально возможные показатели. Например, 0,001-0,01. Т. е. эффективнее меньше 0,001 ставить не получается, потому что слишком часто будет происходить autovacuum по данной таблице. (Уточнение: Алексей Леский предлагает выставлять autovacuum_vacuum_scale_factor и autovacuum_analyze_scale_factor в 5% — https://habr.com/ru/post/501516/) В такой ситуации у вас autovacuum будет срабатывать тогда, когда нужно. Т. е. маленькая порция данных изменилась, autovacuum пришел, быстренько прошел по этой таблице. После этого у вас нет такого overhead по дисковому вводу-выводу и т. д., у вас все autovacuums быстренько отрабатывают и начинают заниматься другими табличками.

Какой тут может быть подводный камень? По дефолту у вас три воркера autovacuum. Если все три постоянно работают, т. е. 98-100 % времени, то это значит, что, скорее всего, у вас не хватает этих воркеров. Для таблички выставлено, что при изменении 0,1 % нужно сделать autovacuum. И когда этот момент наступает, нет свободного воркера, чтобы это выполнить. У вас образуется очередь. Табличка тем временем продолжает апдейтиться, жить, изменяться. И реально, когда освобождается воркер autovacuum, чтобы по ней пройтись и переработать, в ней уже легко может быть заменено 20-30-50 %. И вы получаете длинный autovacuum, который часами работает, всему мешает. Поэтому если у вас процессы работают часто, то имеет смысл подбавить – поставить там 10-20, но так, чтобы хватило work_mem на это дело, чтобы autovacuums успевали все это дело хорошо отработать.

Там есть еще autovacuum_analyze_scale_factor. Это то, с какой частотой будет происходить analyze. Он менее критичен для производительности, хотя там тоже есть свои проблемы, потому что он откручивает счетчик транзакций 32-х битный на прежнее состояние. Поэтому его нельзя делать так, чтобы он по 50 % таблички ходил, но он может быть настроен чуть менее агрессивно. Например, в 0,02-0,03-0,04. Это для того, чтобы demon обновлял статистику планировщика, чтобы у вас всегда планировщик выбирал адекватный план, зная, сколько и чего у вас изменилось в табличке.

Вопросы

Я правильно понял, что autovacuum занимается удалением уже удаленных записей из таблиц?

Он занимается именно удалением, потому что, когда просто происходит delete, то сам tuple остается. Ему прописывается xmax, т. е. самый максимальный transaction id, где этот tuple еще виден.

*Если из базы данные практически не удаляются, то autovacuum можно поставить примерно на раз в сутки?

Нет, не стоит этого делать. Потому что таких баз, где данные никогда не удаляются и не апдейтятся – не бывает. У вас, например, есть pg_catalog, в котором хранится очень много служебной информации. И при отключении вакуума глобально, у вас на pg_catalog’е тоже вакуум отключится. И потрясающие эффекты получаются. Т.е. тормоза начинаются совершенно непредсказуемые. Потому что, чтобы посмотреть какой тип данных и его проверить, приходится обращаться к распухшей таблице служебной. И, соответственно, производительность падает очень сильно.

Добрый день! Есть ли какие-то особенности в настройке PostgreSQL при запусках в виртуалке? Например, KVM с драйвером VirtIO ?

Есть особенности PostgreSQL в таких виртуалках, а именно возникают различные проблемы с вводом-выводом прежде всего из-за непредсказуемого latency диска. Подходы, в принципе, все те же самые, только такие агрессивные настройки checkpoint выставить невозможно. Потому что виртуальный IO это дело не прожует.

Второй момент состоит в том, что, наверное, на виртуалках лучше жить с отключенным синхронным коммитом, потому что тогда немножко получше получается производительность. Это так же, как для медленных дисков делается, именно из-за этих проблем с производительностью виртуальных дисков. Но надо понимать, что у вас три коммита произошло. Они накопились где-то в LibvitIO. И в этот момент у вас что-то упало по другой причине. И вы рискуете эти транзакции потерять. Часто с виртуалками это делают, но надо знать свои риски.

Илья, спасибо за доклад. У меня три вопроса. Первый вопрос. Вы упоминали, что вы используете fsync. Вы используете именно fsync или datasync?

В большинстве случаев между ними большая разница. Но эта утилита pg_test_fsync покажет как раз, какой fsync лучше работает на вашей платформе с вашими настройками, с вашим ядром. И вы можете посмотреть на этой платформе и включить в postgresql.conf тот, который у вас работает быстрее. И это правильный подход.

Спасибо. Второй вопрос. Вы упоминали, что на дисках не на SSD, особенно на слабых HDD дисках эффективно отключать write cache. Можете как-то прокомментировать? Потому что write cache позволяет рандомные writes ускорять.

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

В ситуации, если у вас на диске есть кэш, но нет конденсатора, проблема заключается в том, что у вас RAID со своего кэша прокинул страничку на диск. Она с точки зрения базы данных, с точки зрения системы записалась. А на самом деле в этот момент у вас умирает диск по каким-то причинам и эти данные не доходят до storages. И вы имеете не консистентную поломанную базу. Эта единственная причина, из-за которой этот кэш надо отключать, если у вас там нет конденсатора.

Понятно. И третий вопрос. Планируется ли каким-то образом pgtune расширять, т. е. некоторые настройки для RAIDs и т. д., чтобы он делал это как-то автоматически? Т. е. ему говоришь, что у меня есть RAID с таким-то кэшом, а он дальше все делает.

Это довольно сложный вопрос. Разработчики дискутируют об этом уже очень давно. И поскольку это не суперкритичная для производительности или для маркетинга фича, то дискуссии очень длинные, развесистые и ни к чему не приводящие. Если обратили внимание, то shared buffers чуть-чуть стало больше, work_mem стало чуть больше, но это все в направлении того же самого. А когда это дойдет до операционной системы совершенно не понятно. Я думаю, что не в ближайшем будущем. Потому что у PostgreSQL есть идея, что он должен заводиться в любой кофемолке, куда бы его не поставили, а тут нужно смотреть на много параметров, потому что обо всем, что я рассказал, это очень linux-специфичные вещи. И мы рекомендуем в основном на хороших и последних версиях ядра Linux PostgreSQL гонять. И под то разнообразие платформ, которое сейчас поддерживает PostgreSQL сделать такой wizard – это очень сложно, и никто за это не берется пока что. Поэтому имеет смысл составить такой чек-лист: на что посмотреть, что проверить. И, соответственно, по нему действовать.

Насколько эффективно PostgreSQL работает на BSD? Насколько там идут потери в производительности?

Он там работает. Но у FreeBSD есть очень много вещей, которые в силу вектора ее развития, отстают от Linux и отстают здорово. Хрестоматийный пример – это huge pages. Huge pages появились в BSD позже. Нормальная поддержка для PostgreSQL, которая в 9.4 есть, она работает только с Linux. Т. е. вы, например, будете иметь overhead по использованию маленьких страниц памяти. На больших шаредных буферах легко могут быть разницы по скорости, если у вас такой специфический workload. С производительностью sync на диск тоже. На современных ядрах, старше 3.3 линуксовых, есть много оптимизации. Сообщество разработчиков ядра очень плотно сотрудничает с разработчиками PostgreSQL и MySQL. И там тоже производительность может отличаться если не на порядок, то в разы.

К сожалению, BSD, хотя и надежная, и удобная в администрировании система, она от этих вещей отстает. И не стоит забывать, что многие из оптимизаций ядра более-менее вносятся такими игроками как Oracle или IBM для их баз. Они спонсируют эти разработки. Сами частично их делают. А на FreeBSD никакие классические коммерческие базы данных не поддерживаются. Соответственно, BSD они обходят своим вниманием.

Что бы вы порекомендовали для баз, в которых отсутствуют апдейты как класс, у них только добавляются новые записи? И также для read only базы или базы, которая обновляется раз в месяц?

Если поток inserts большой, то по записи рекомендации все те же самые. Апдейтов, конечно, нет, но оптимизировать запись можно точно таким же способом. Единственный неприятный здесь момент в том, что, если у вас база только на insert, то это значит, что она у вас неуклонно монотонно растер. И эта ситуация уже плохая, потому что, как правило, это какие-то time base inventory, когда события пишутся по timestamp’у. Это означает, что эффективно вы имеете горячую голову, по которой вы делаете всю свою аналитику и очень холодный архив, который реально борется с этой горячей головой при рандомном чтении за шаредные буфера, за ввод-вывод, за прочие вещие.

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

Плюс для read only баз, где много чтения, имеет смысл использовать классическую архитектуру с SSD, когда у вас вся база живет на SAS’ах, а SSD живут только под отдельным tablespace. И те таблицы, с которых идет наиболее интенсивное чтение, выносить на этот tablespace SSD, особенно их индексы, чтобы у вас чтение было максимально быстрым и кэш прогревался при старте базы наиболее эффективно и быстро за счет этих SSD. Тогда вы можете всякие касты типа random page cost опустить по сравнению с дефолтом. И тогда у вас оптимизатор будет выбирать более оптимальные планы. У этих таблиц будут более быстрые selects. Они будут прочитаны в память и будут лучше работать.

Можно ли поподробней про архивирование?

Поподробней история такая. Например, есть у вас партиционированная таблица. И вы держите горячую голову 3-4 недели, т. е. сколько вам реально нужно по этой аналитике ездить. Все, что дальше, вы, например, схлопываете в одну большую партицию и стараетесь к ней не ходить лишний раз, чтобы не мешалось в вводе-выводе. Либо эту последнюю таблицу вы дампите и уносите на другую машину, которая не имеет онлайновые OLTP нагрузки по этой агрегации. И если вам нужно получить эти данные, вы туда идите и там гоняете эти запросы, чтобы не портить картину на главной базе.

Как PostgreSQL работает с диском. Илья Космодемьянский