PostgreSQL хранит данные на каких-то носителях. И между PostgreSQL и, например, магнитной поверхностью диска находится несколько кешей: кеш самого винчестера, кеш RAID-контроллера или винчестерной полки, кеш файловой системы на уровне операционной системы и кеш самого PostgreSQL. Если первыми перечисленными кешами мы практический не можем управлять, то последними, находящимися в ОЗУ сервера, управлять можем: например, выделяя больше ОЗУ под кеш PostgreSQL в ущерб кешу ОС, или наоборот. В официальной документации можно прочитать ничем не подтвержденные рекомендации, типа выделять под PostgreSQL четверть ОЗУ. Это вызывает сомнения. PostgreSQL в виде Postgres95 впервые появился в 1995 году и, кто знает, быть может и эти рекомендации относятся к тому же году. Поэтому появилась идея эксперимента с целью разобраться, как лучше распределять ОЗУ.
Сразу оговорюсь, что речь пойдет про выделенный под БД сервер, на котором помимо самого PostgreSQL и обслуживающей его инфраструктуры ничего нет. Если рассмотреть эту задачу умозрительно, то интуитивно понятно, что «своя рубашка ближе к телу», то есть со своим собственным кешем PostgreSQL должен работать оптимальнее. Например, в своем кеше PostgreSQL хранит информацию постранично, а размер страницы в PostgreSQL по умолчанию 8 Кб. В кеше ОС информация хранится тоже постранично, но размер страницы в памяти ОС и, обычно, файловой системы равен 4 Кб, то есть возможна фрагментация страницы PostgreSQL. Но на практике такие различия, связанные с тем, что в собственном кеше данные хранятся в более оптимальном виде, малозаметны. Другое отличие кешей заключается в том, как выбирается для удаления страница, чтобы записать новую. Кеш ОС работает просто: он удаляет ту страницу, к которой дольше всего не обращались. А кеш PostgreSQL пытается вести себя умнее, ведь данные бывают разными — более или менее полезными. Например, индексы или данные за последний месяц, к которым обращаются чаще, более полезны, чем данные, скажем, годичной давности, к которым обращаются редко. Поэтому PostgreSQL выставляет данным своеобразную оценку полезности и в первую очередь высвобождает наименее полезную информацию. А если PostgreSQL видит, что намечается последовательное чтение большой таблицы, которое может «вымыть» остальные данные из кеша, то он принимает меры, чтобы этого не случилось. Вот такую оптимизацию, связанную с интеллектуальным анализом полезности кеша, и должен был продемонстрировать эксперимент. Также с его помощью решался вопрос о том, насколько влияют на производительность PostgreSQL HugePages.
Довольно сложно найти документацию, описывающую алгоритмы работа кеша PostgreSQL. Для желающих изучить этот вопрос более углубленно, привожу ссылку: Inside the PostgreSQL Shared Buffer Cache
Идея проста. Создаю одну таблицу, размер которой больше ОЗУ сервера, и индекс к этой таблице, который занимает где-то 10% от размера таблицы. Мне показалось, что такая пропорция реалистична. Cначала «прогреваю» таблицу, потом индекс, чтобы они по максимуму находились в кеше. Измеряю время индексированного поиска. Запускаю неиндексированный поиск, который выполняет последовательное чтение всей таблицы, измеряю длительность его работы. А после этого повторяю эксперимент по индексированному поиску. Предполагаю, что в случае работы кеша PostgreSQL разница между поисками по индексу до и после поиска последовательным чтением таблицы будет минимальна, а в случае кеша ОС — заметна. Эксперимент буду повторять с различными пропорциями кешей ОС и PostgreSQL, а также с использованием HugePages и без них.
Стенд сделал из того, что было: ноут MSI, операционка сообщает о 8 ядрах процессора, 16 Гб ОЗУ (HugePages 2 Мб на 14 Гб), 0 swap. К тому же Linux ругался о том, что процессор перегревается и приходится сбрасывать частоту, что наверняка сказалось на повторяемости эксперимента. Софт: CentOS 8 и PostgreSQL 13.0.
Подробно опишу эксперимент, чтобы любой желающий мог его повторить в своих условиях. «Прогревание» выполняется с помощью функции pg_prewarm(), но поскольку в результате эксперимента выяснилось, что pg_prewarm хоть и заполняет кеш, но «прогревает» недостаточно, после неё дополнительно прогреваю с помощью pgbench. Потом через тот же pgbench замеряю длительность индексированного поиска, поиска через последовательное чтение и снова индексированного. Результаты складываю в CSV-файлы, которые потом импортирую в Exсel, там обрабатываю и строю графики. Видимо, из-за фрагментирования памяти иногда невозможно выделять память под HugePages большого размера. Поэтому цикл тестирования выглядит так: перезагружаю машину, из rc.local вызываю тестирующий скрипт, он прогоняет тесты с включенными HugePages от максимального размера кеша PostgreSQL к минимальному, потом скрипт повторяет то же самое с выключенными HugePages, после чего перезагружает машину. Размер HugePage равен 2 Мб.
Все файлы лежат в одной директории, вот их список:
postgresql.conf
Этот файл «инклюдится» в postgresql.conf базы данных с помощью директивы include. Он нужен для проведения эксперимента с разными настройками PostgreSQL.
shared_buffers.conf
Скриптом, который выполняет тестирование, в этот файл записывается размер кеша PostgreSQL.
init.sql
SQL-скрипт, который создает окружение в БД, выполняется перед экспериментом. Запускается с помощью psql база -f init.sql.
test_idx.sql
test_seq.sql
Здесь находятся SQL-запросы, которые выполняет pgbench во время тестирования.
config.sh
get_huge_pages
Вспомогательный скрипт. Он берет из config.sh значения для shared_buffers, запускает с этими настройками PostgreSQL, замеряет, сколько нужно HugePages для его работы в данной конфигурации, и записывает результат работы в файл huge_pages.sh.
huge_pages.sh
Здесь лежат результаты работы get_huge_pages, они используются в скрипте test.
dataHP.csv и dataNHP.csv
Результаты работы скрипта test, используются потом в Excel.
output.log
Вывод из скрипта test полезен, если там содержатся ошибки, а если их нет — он пустой.
Пример запуска тестирующего скрипта. Отредактировал файл, который уже был в CentOS:
/etc/rc.local
Собственно, тестирующий скрипт:
test
Сначала создаю базу данных скриптом init.sql, потом скриптом get_huge_pages я собираю информацию о том сколько нужно HugePages для различных размеров кэша PostgreSQL, после этого добавляю вызов скрипта test в /etc/rc.local и перезагружаю машину. За каждую перезагрузку проходит один цикл тестирования.
Здесь и далее по оси X отложены значения кеша PostgreSQL, ОЗУ около 16 Гб, вся неиспользуемая память используется, разумеется, как файловый кеш ОС. По оси Y отложено время выполнения запроса в миллисекундах. Синим цветом — без использования HugePages, оранжевым — с HugePages. Вид графика — коробочки с усиками, подробно про них можно прочитать в документации Excel; удобны тем, что показывают не только усредненные значения, но и разброс, и распределение данных. В каждой итерации было 27 тестов с HugePages и 27 без, итераций было 251, всего было 13554 тестов.
С увеличением кеша PostgreSQL график раздваивается. Без HugePages скорость выполнения запроса — а это простой поиск с использованием индекса — падает. С увеличением кеша PostgreSQL только с использованием HugePages PostgreSQL кеш начинает незначительно выигрывать у кеша файловой системы. Когда я тестировал два года назад на CentOS 7 и PostgreSQL 10, кеш файловой системы работал примерно с такой же скоростью, что и кеш PostgreSQL без HugePages, а добавление HugePages давало значимый выигрыш над кешем файловой системы. Из чего я могу сделать вывод, что за последние годы Linux научился гораздо эффективнее использовать свой файловый кеш.
Последовательное чтение таблицы. Тут заметно ускорение запроса, если кеш PostgreSQL больше кеша файловой системы. Что интересно (и для меня непонятно), на большей части графика использование HugePages замедляет выполнение запроса, и только при очень больших значениях кеша PostgreSQL выигрыш становится заметен. Не знаю, с чем это связано, наверное, разработчикам PostgreSQL есть еще над чем подумать.
Повторный поиск по индексу. Здесь и демонстрируется тот эффект, который я описывал в самом начале. Если использовать кеш файловой системы (задан небольшой размер кеша PostgreSQL), то при последовательном чтении таблицы индекс вымывается из кеша, что демонстрирует длительный повторный поиск по индексу. Этот эффект пропадает примерно тогда, когда кеш PostgreSQL становится достаточно большим, чтобы индекс хранился в нём, а не в кеше файловой системы, и график становится похож на график, который был до последовательного чтения таблицы: примерно 0,08 мс с HugePages и 0,12 мс без HugePages.
Выводы каждый сделает сам
Я считаю, что не стоит верить ничем не подтвержденным древним рекомендациям каких-то мохнатых годов о том, что кеш PostgreSQL должен быть равен четверти ОЗУ. Со своим кешем он работает заметно лучше.
Источник статьи: https://habr.com/ru/company/domclick/blog/534896/
Сразу оговорюсь, что речь пойдет про выделенный под БД сервер, на котором помимо самого PostgreSQL и обслуживающей его инфраструктуры ничего нет. Если рассмотреть эту задачу умозрительно, то интуитивно понятно, что «своя рубашка ближе к телу», то есть со своим собственным кешем PostgreSQL должен работать оптимальнее. Например, в своем кеше PostgreSQL хранит информацию постранично, а размер страницы в PostgreSQL по умолчанию 8 Кб. В кеше ОС информация хранится тоже постранично, но размер страницы в памяти ОС и, обычно, файловой системы равен 4 Кб, то есть возможна фрагментация страницы PostgreSQL. Но на практике такие различия, связанные с тем, что в собственном кеше данные хранятся в более оптимальном виде, малозаметны. Другое отличие кешей заключается в том, как выбирается для удаления страница, чтобы записать новую. Кеш ОС работает просто: он удаляет ту страницу, к которой дольше всего не обращались. А кеш PostgreSQL пытается вести себя умнее, ведь данные бывают разными — более или менее полезными. Например, индексы или данные за последний месяц, к которым обращаются чаще, более полезны, чем данные, скажем, годичной давности, к которым обращаются редко. Поэтому PostgreSQL выставляет данным своеобразную оценку полезности и в первую очередь высвобождает наименее полезную информацию. А если PostgreSQL видит, что намечается последовательное чтение большой таблицы, которое может «вымыть» остальные данные из кеша, то он принимает меры, чтобы этого не случилось. Вот такую оптимизацию, связанную с интеллектуальным анализом полезности кеша, и должен был продемонстрировать эксперимент. Также с его помощью решался вопрос о том, насколько влияют на производительность PostgreSQL HugePages.
Довольно сложно найти документацию, описывающую алгоритмы работа кеша PostgreSQL. Для желающих изучить этот вопрос более углубленно, привожу ссылку: Inside the PostgreSQL Shared Buffer Cache
Описание эксперимента
Идея проста. Создаю одну таблицу, размер которой больше ОЗУ сервера, и индекс к этой таблице, который занимает где-то 10% от размера таблицы. Мне показалось, что такая пропорция реалистична. Cначала «прогреваю» таблицу, потом индекс, чтобы они по максимуму находились в кеше. Измеряю время индексированного поиска. Запускаю неиндексированный поиск, который выполняет последовательное чтение всей таблицы, измеряю длительность его работы. А после этого повторяю эксперимент по индексированному поиску. Предполагаю, что в случае работы кеша PostgreSQL разница между поисками по индексу до и после поиска последовательным чтением таблицы будет минимальна, а в случае кеша ОС — заметна. Эксперимент буду повторять с различными пропорциями кешей ОС и PostgreSQL, а также с использованием HugePages и без них.
Описание стенда
Стенд сделал из того, что было: ноут MSI, операционка сообщает о 8 ядрах процессора, 16 Гб ОЗУ (HugePages 2 Мб на 14 Гб), 0 swap. К тому же Linux ругался о том, что процессор перегревается и приходится сбрасывать частоту, что наверняка сказалось на повторяемости эксперимента. Софт: CentOS 8 и PostgreSQL 13.0.
Подробно опишу эксперимент, чтобы любой желающий мог его повторить в своих условиях. «Прогревание» выполняется с помощью функции pg_prewarm(), но поскольку в результате эксперимента выяснилось, что pg_prewarm хоть и заполняет кеш, но «прогревает» недостаточно, после неё дополнительно прогреваю с помощью pgbench. Потом через тот же pgbench замеряю длительность индексированного поиска, поиска через последовательное чтение и снова индексированного. Результаты складываю в CSV-файлы, которые потом импортирую в Exсel, там обрабатываю и строю графики. Видимо, из-за фрагментирования памяти иногда невозможно выделять память под HugePages большого размера. Поэтому цикл тестирования выглядит так: перезагружаю машину, из rc.local вызываю тестирующий скрипт, он прогоняет тесты с включенными HugePages от максимального размера кеша PostgreSQL к минимальному, потом скрипт повторяет то же самое с выключенными HugePages, после чего перезагружает машину. Размер HugePage равен 2 Мб.
Все файлы лежат в одной директории, вот их список:
postgresql.conf
Этот файл «инклюдится» в postgresql.conf базы данных с помощью директивы include. Он нужен для проведения эксперимента с разными настройками PostgreSQL.
shared_buffers.conf
Скриптом, который выполняет тестирование, в этот файл записывается размер кеша PostgreSQL.
init.sql
SQL-скрипт, который создает окружение в БД, выполняется перед экспериментом. Запускается с помощью psql база -f init.sql.
test_idx.sql
test_seq.sql
Здесь находятся SQL-запросы, которые выполняет pgbench во время тестирования.
config.sh
get_huge_pages
Вспомогательный скрипт. Он берет из config.sh значения для shared_buffers, запускает с этими настройками PostgreSQL, замеряет, сколько нужно HugePages для его работы в данной конфигурации, и записывает результат работы в файл huge_pages.sh.
huge_pages.sh
Здесь лежат результаты работы get_huge_pages, они используются в скрипте test.
dataHP.csv и dataNHP.csv
Результаты работы скрипта test, используются потом в Excel.
output.log
Вывод из скрипта test полезен, если там содержатся ошибки, а если их нет — он пустой.
Пример запуска тестирующего скрипта. Отредактировал файл, который уже был в CentOS:
/etc/rc.local
Собственно, тестирующий скрипт:
test
Тестирование
Сначала создаю базу данных скриптом init.sql, потом скриптом get_huge_pages я собираю информацию о том сколько нужно HugePages для различных размеров кэша PostgreSQL, после этого добавляю вызов скрипта test в /etc/rc.local и перезагружаю машину. За каждую перезагрузку проходит один цикл тестирования.
Результаты
Здесь и далее по оси X отложены значения кеша PostgreSQL, ОЗУ около 16 Гб, вся неиспользуемая память используется, разумеется, как файловый кеш ОС. По оси Y отложено время выполнения запроса в миллисекундах. Синим цветом — без использования HugePages, оранжевым — с HugePages. Вид графика — коробочки с усиками, подробно про них можно прочитать в документации Excel; удобны тем, что показывают не только усредненные значения, но и разброс, и распределение данных. В каждой итерации было 27 тестов с HugePages и 27 без, итераций было 251, всего было 13554 тестов.
С увеличением кеша PostgreSQL график раздваивается. Без HugePages скорость выполнения запроса — а это простой поиск с использованием индекса — падает. С увеличением кеша PostgreSQL только с использованием HugePages PostgreSQL кеш начинает незначительно выигрывать у кеша файловой системы. Когда я тестировал два года назад на CentOS 7 и PostgreSQL 10, кеш файловой системы работал примерно с такой же скоростью, что и кеш PostgreSQL без HugePages, а добавление HugePages давало значимый выигрыш над кешем файловой системы. Из чего я могу сделать вывод, что за последние годы Linux научился гораздо эффективнее использовать свой файловый кеш.
Последовательное чтение таблицы. Тут заметно ускорение запроса, если кеш PostgreSQL больше кеша файловой системы. Что интересно (и для меня непонятно), на большей части графика использование HugePages замедляет выполнение запроса, и только при очень больших значениях кеша PostgreSQL выигрыш становится заметен. Не знаю, с чем это связано, наверное, разработчикам PostgreSQL есть еще над чем подумать.
Повторный поиск по индексу. Здесь и демонстрируется тот эффект, который я описывал в самом начале. Если использовать кеш файловой системы (задан небольшой размер кеша PostgreSQL), то при последовательном чтении таблицы индекс вымывается из кеша, что демонстрирует длительный повторный поиск по индексу. Этот эффект пропадает примерно тогда, когда кеш PostgreSQL становится достаточно большим, чтобы индекс хранился в нём, а не в кеше файловой системы, и график становится похож на график, который был до последовательного чтения таблицы: примерно 0,08 мс с HugePages и 0,12 мс без HugePages.
Выводы
Выводы каждый сделает сам
Я считаю, что не стоит верить ничем не подтвержденным древним рекомендациям каких-то мохнатых годов о том, что кеш PostgreSQL должен быть равен четверти ОЗУ. Со своим кешем он работает заметно лучше.Источник статьи: https://habr.com/ru/company/domclick/blog/534896/