Оптимизация настроек Postgresql-сервера

    Общий рейтинг статьи: 0 (проголосовало 0 )
    Опубликовано:  [просмотров 1012]


    Сегодня, я совершенно случайно обнаружил, что база данных Postgresql на Zabbix-сервере основательно выросла, но самое интересное, это то, что она была совершенно не оптимизирована. Собственно СУБД была установлена из репозитария, залит дамп и на этом настройка была завершена.

    Сейчас у меня есть типовая установка Postgresql с достаточно большой базой и накопленная Zabbix-ом статистика. Соответственно я могу провести типовые операции оптимизации СУБД Postgresql и посмотреть на реальные результаты которые даст каждый этап оптимизации. Думаю, это небольшое исследование будет полезно коллегам системным администраторам.

    Отличная книга по оптимизации настроек баз данных Postgresql

    В качестве наиболее полного руководства по оптимизации настроек Postgresql можно использовать книгу "А. Ю. Васильева aka leopard" расположенную по адресу http://postgresql.leopard.in.ua/html/ (это web-версия книги), но для удобства чтения на мобильных устройствах вы так же можете скачать PDF/EPUB/Mobi-версию с официального сайта или у нас (ссылки представлены ниже):

    Как показывает практика, большие руководства читают только в крайнем случае и в большинстве случаев наиболее востребованным форматом являются быстрые чек-листы и наборы команд и параметров "как сделать чтобы было хорошо".

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

    Основные команды диагностики Postgresql

    К оптимизации сервера Postgresql нельзя приступать не выяснив основные метрики сервера исходя из которых мы будем "плясать" и первым параметром который нам необходимо знать является объем оперативной памяти установленной на сервере баз данных.

    Узнаем объем установленной на сервере оперативной памяти:

    # free

    Из результатов работы команды нас интересует параметр Mem и в моем случае на сервере установлено 2 гб оперативной памяти:

    total used free shared buff/cache available
    Mem: 2048232 1080452 102008 151820 865772 626832

    Следующим этапом, переходим в SQL-консоль сервера баз данных и собираем сведения по обслуживаемым базам данных.

    # su postgres
    $ psql

    Как получить сведения о размерах баз данных на сервере Postgresql:

    Представленные примеры запросов необходимо выполнять в SQL-консоли.

    # \l+

    В результате выполнения этой команды мы получим основные сведения о базах данных на сервере включая их объемы.

    Определение размеров баз данных на сервере

    Как вы видите объем базы данных zabbix 9.5 гб. 

    Обратите внимание, что команду l+ вы не сможете выполнить как SQL-запрос, эта команда доступна только в SQL-консоли сервера, а SQL-запрос выполняющий аналогичный функционал выглядит следующим образом:

    # SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
    AS size FROM pg_database;

    В дальнейшем я буду приводить примеры только SQL-команд.

    Размеры таблиц в базе данных (дисковый объем):

    Общий объем базы данных нам конечно знать важно, но еще больший интерес представляют размеры отдельных таблиц в базе данных и для определения размеров таблиц вы можете использовать следующий SQL-запрос:

    # SELECT relname as "Table", 
    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

    Результат будет выглядеть следующим образом:

    Объемы таблиц в базе данных

    • Size (pg_total_relation_size) - общий объем данных занимаемый на диске включая все связанные индексы
    • External Size (pg_total_relation_size - pg_relation_size) - объем дополнительных "рабочих данных" (форки таблицы)

    Текущую базу данных конечно же требуется сменить (при подключении мы не указали с какой базой будем работать и по умолчанию выбрана база postgres).

    # \connect zabbix

    Размеры таблиц в базе данных (число строк в таблице):

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

    SQL-запрос для получения сведений о числе строк в таблицах:

    # SELECT
    nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE
    nspname NOT IN ('pg_catalog', 'information_schema') AND
    relkind='r' ORDER BY reltuples DESC;

    Результат выполнения запроса:

    Число строк в таблицах базы данных Postgresql

    Общая оптимизация конфигурации Postgresql

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

    shared_buffers = RAM/2..8

    Количество памяти, выделенной PostgreSQL для совместного кеша страниц и эта память разделяется между всеми процессами PostgreSQL. И в моем случае (2 гб оперативной памяти), я установил значение равным 1024MB

    temp_buffers = 256MB

    Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии, рекомендуется устанавливать от 64МБ до 256МБ и я проведя несколько экспериментов с разными базами пришел к выводу, что оптимально использовать 256МБ если объем оперативной памяти позволяет.

    work_mem = RAM/32..64 или 32MB..128MB

    Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая  часть сессий почти всегда висит в ожидании. Так как Zabbix активными поддерживает 2-3 подключения к базе данных, а остальные находятся в состоянии idle (что можно проверить при помощи ps ax | grep postgres) то я установил параметр равным 128MB.

    maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

    Лимит памяти для обслуживающих задач, например вакуум, автовакуум или создания индексов. Я в свою очередь установит равным 256MB.

    effective_cache_size = RAM - shared_buffers

    Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. Этот параметр я подсмотрел в отчете по аудиту одного из серверов который заказывали сторонней компании.

    effective_io_concurrency = 1

    Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID - 2 или больше. Диск на этом сервере один и тот виртуализованный, посему в моем случае я выбираю 1.

    random_page_cost =1

    Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PostgreSQL к выбору планов с сканированием всей таблицы (PostgreSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). Примерные значения: 1.5-2.0 для RAID, 1.1-1.3 для SSD и аналогично предыдущему параметру в моем случае имеет смысл выбрать 1.

    autovacuum = on

    Включение автовакуума. Выключать автовакуум я крайне не рекомендую и не слушайте разного рода "специалистов".

    autovacuum_max_workers = NCores/4..2 но не меньше 4

    Количество процессов автовакуума. Общее правило - чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса. В моем случае поставим равным 2.

    autovacuum_naptime = 20s

    Время между запусками автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.

    bgwriter_delay = 20ms

    Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на  checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.

    bgwriter_lru_multiplier = 4.0 и bgwriter_lru_maxpages = 400

    Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше чем было записано в прошлый цикл умноженное на bgwriter_lru_multiplier, но не больше чем  gwriter_lru_maxpages.

    synchronous_commit = off

    Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность. В моем случае потеря нескольких каммитов в случае сбоя питания особой роли не играет, поэтому конечно ставим в off.

    checkpoint_segments = 32..256

    Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint  приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB. Система не является критичной и выбираем максимальное значение 256.

    checkpoint_completion_target = 0.5..0.9

    Степень "размазывания" checkpoint'a. Скорость записи во время checkpoint'а регулируется так, что бы время checkpoint'а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target. Не особо важный параметр и можем поставить его 0.5.

    fsync = on

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

    commit_delay = 1000 и commit_siblings = 5

    Групповой коммит нескольких транзакций. Имеет смысл включать, если темп транзакций превосходит 1000 TPS. Иначе эффекта не имеет.

    Обслуживание базы данных (Вакуум/Анализ/Переиндексация)

    Как вы наверное знаете, удаление данных из таблицы Postgresql фактически эти данные не удаляет, а лишь отмечает данные как неиспользуемые, что приводит к появлению "дырок" в файлах данных. Теоретически, процесс автовакуум в фоновом режиме приводит таблицы в порядок, но на деле не все так радужно как хотелось бы. Как показывает практика, имеет смысл выделять время на проведение обслуживание базы данных, если это конечно позволяет технический процесс эксплуатации (например база должна быть доступна 24/7 с максимальным аптаймом).

    Операция VACUUM может быть произведена при помощи SQL-запроса применительно к отдельной таблице базы данных, так и при помощи утилиты vacuumdb и при обслуживании некритичных баз данных я рекомендую использовать именно эту утилиту в режиме full.

    Очистка базы данных VACUUM

    Для проведения полного вакуума с принудительной блокировкой (операция будет завершена быстрее) выполните от имени пользователя postgres следующую команду:

    $ vacuumdb -f -F zabbix

    В результате выполнения полного вакуума объем базы на диске уменьшился с 9.5G до 7.6G и по времени обработка заняла 30 минут.

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

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

    Переиндексация базы данных Postgresql

    Для переиндексации в свою очередь существует своя утилита - reindexdb:

    $ reindexdb zabbix

    Переиндексацию таблицы баз данных можно выполнить при помощи SQL-запроса:

    # REINDEX TABLE table_for_reindex;

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

    Для проведения принудительного анализа элементов базы данных используется утилита vacuumdb, но с другими параметрами:

    $ vacuumdb --analyze-only zabbix

    Устновка параметров конфигурации SQL-командой ALTER SYSTEM SET

    Вы так же можете установить параметры конфигурации сервера при помощи SQL-команды ALTER SYSTEM SET, такой подход используется в основном при работе с кластером серверов Postgresql. 

    После перезагрузки сервера баз данных параметры установленные параметры будут записаны в файл postgresql.auto.conf, они перекрывают параметры установленные в главном конфигурационном файле и действуют на весь кластер.

    Например для сервера баз данных с восемью гигобайтами оперативной памяти использовал следующие SQL команды конфигурирования:

    # ALTER SYSTEM SET log_checkpoints = 'on';
    # ALTER SYSTEM SET log_connections = 'on';
    # ALTER SYSTEM SET log_disconnections = 'on';
    # ALTER SYSTEM SET log_temp_files = '0';
    # ALTER SYSTEM SET log_autovacuum_min_duration = '0';
    # ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ';
    # ALTER SYSTEM SET shared_preload_libraries = 'auto_explain';
    # ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
    # ALTER SYSTEM SET auto_explain.log_min_duration = '3s';
    # ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.001';
    # ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.001';
    # ALTER SYSTEM SET autovacuum_naptime = '1s';
    # ALTER SYSTEM SET autovacuum_max_workers = '10';
    # ALTER SYSTEM SET autovacuum_work_mem = '1GB';
    # ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '1';
    # ALTER SYSTEM SET bgwriter_delay = '200';
    # ALTER SYSTEM SET bgwriter_lru_maxpages = '800';

    Оптимизация ядерных параметров

    При подготовке базы данных Oracle в требованиях по "предполетной подготовке" указываются несколько обязательных ядерных параметров без которых вы не сможете запустить СУБД.

    В требованиях к СУБД Postgresql такого нет, но я смотрел отчет по аудиту одной зарубежной компании и там они приводили очень похожие на Oracle параметры которые необходимо было применить к параметрам /etc/sysctl.conf для оптимизации производительности Postgresql, есть конечно подозрение, что они это просто скопипастили с доки по Oracle мотивируя это тем, что хуже не будет.

    Приведу в качестве примера "ядерные параметры" предложенные оптимизаторами с небольшой расшифровкой, а вопрос использовать их или нет оставлю на ваше усмотрение.

    net.ipv4.icmp_echo_ignore_broadcasts = 1 - Игнорировать ICMP сообщения, отправленные на широковещательный или групповой адрес. Эта опция из области безопасности и может помочь при ряде специфичных сетевых атак, каким боком она относится к базам данных непонятно, но можно включить и хуже не будет.

    net.ipv4.conf.all.rp_filter = 1 - Включение проверки обратного адреса. Это из области маршрутизации и к базам данных вообще никакого отношения не имеет так как обычно на серверах баз данных один интрефейс.

    net.ipv4.ip_local_port_range = 12345 63000 - Задает диапазон портов используемых для исходящих соединений, мне не доводилось видеть системы которые бы задействовали такой диапазон полностью кроме как торрент-взаимодействие.

    net.ipv4.netfilter.ip_conntrack_max = 129034 - Максимальное количество одновременных соединений. Как и все представленные выше параметры предназначены для поддержания огромного пула одновременных подключений и они скорее из области web-серверов чем баз данных.

    net.ipv4.tcp_tw_reuse = 1 - Разрешаем повторное использование TIME-WAIT сокетов, этой опцией никогда не пользовался и точно сказать есть ли от нее профит не могу.

    net.ipv4.tcp_tw_recycle = 1 - Разрешает удаление сокетов в состоянии ожидения. Вот тут я бы точно ничего менять не стал и по умолчанию этот параметр задан в ноль.

    net.core.somaxconn = 16384 - Максимальное число сокетов ожидающих соедениения. Выше 15000 рекомендуется для высоконагруженных WEB-проектов. 

    net.ipv4.tcp_max_syn_backlog = 16384 - Максимальное число ожидающих соединения запросов и как вы наверное поняли применительно к коррекно написанным приложениям которые правильно взаимодействуют с СУБД это конечно перебор.

    net.core.netdev_max_backlog = 2500 - Количество пакетов в очереди на обработку когда ядро не успевает их обрабатывать. Это уже ближе к СУБД.

    net.ipv4.tcp_max_tw_buckets = 258068 - Максимальное количество сокетов в режиме ожидания.

    net.core.rmem_default = 262144 - Размер буфера приема данных по умолчанию.

    net.core.rmem_max = 16777216 - Соответственно максимальное значение буфера приема данных.

    net.core.wmem_max = 16777216 - Аналогично для буфера передачи данных.

    net.ipv4.tcp_rmem = 4096 87380 16777216 -  Количество страниц памяти, разрешенное для всех TCP сокетов (минимум, по умолчанию, максимум) на прием.

    net.ipv4.tcp_wmem = 4096 65536 16777216 - Аналогично вышестоящему но для передачи данных.

    net.ipv4.tcp_syncookies = 1 - А вот это рекоментуют отключать, а не включать.

    net.ipv4.tcp_fin_timeout = 15 - Максимальное время ожидания перед закрытием сокета у зависших соединений. 

    net.ipv4.tcp_timestamps = 0 - Разрешает/запрещает использование временных меток (timestamps), в
    соответствии с RFC 1323 и в рамках локальной сети эту опцию надо включать, а не выключать. Тем более заявлена обратная совместимость.

    Все перечисленные опции в целом направленны на оптимизацию работы сетевого стека Linux и в качестве эксперимента можно попробовать, но обратите внимание на мои комментарии, так как некоторые опции оптимизаторы предложили как раз противоположными "оптимизации".

    fs.file-max = 8000000 - Максимальное число одновременно открытых файлов. В Ubuntu Linux этот параметр по умолчанию равен 1628709 и мне не удавалось его перешагнуть.

    fs.inotify.max_user_watches = 65536 - Максимальное количество одновременно отслеживаемых файлов (проверка на изменение) и по умолчанию установлено 524288. Используется в разного рода механизмах синхронизации по изменению файла.

    Наибольший интерес представляют опции работы с виртуальной памятью и кэшированием. И именно по ним я постараюсь пройти максимально подробно.

    vm.vfs_cache_pressure = 1000 - Логика действия этого параметра мне не совсем понятна и гуглением я нашел, что эта опция влияет на отношение ядра к освободившимся от кэшированных оъектов файловой системы страницам ОЗУ. Значение по умолчанию — 100. Уменьшение этого значения заставляет ядро придерживать эти страницы почаще для своих нужд в будущем. Увеличение значения заставляет ядро чаще отдавать эта страницы для нужд других программ, т.е. при увеличении этого значения величина cached в выводе top будет расти медленнее. Для СУБД с собственным кэшем это действительно имеет значение.

    vm.page-cluster = 0 - эта опция контролирует количество страниц памяти, которые будут записываться в своп за один раз. Значение «0» означает 1 страницу, «1» — 2 страницы, 2 — 4 страницы и т.д. по экспоненте. При высоких значениях может очень сильно понизить отзывчивость системе на операциях сброса страниц памяти в своп. По умолчанию в Ubuntu имеет значение 3.

    vm.max_map_count = 8388608 - Максимальное количество памяти которое может быть выделено одному процессу на сервере баз данных (если он ничем больше не занимается) смело ставим равными объему оперативной памяти.

    vm.dirty_ratio = 2 - Значение этого параметра означает долю свободной системной памяти в процентах, по достижении которой процесс, ведущий запись на диск, инициирует запись «грязных» данных из дискового кэша на диск.

    vm.dirty_background_ratio = 2 - Логику этой опции я не очень понимаю и в чем ее отличие от предыдущей.

    vm.dirty_writeback_centisecs = 500 - Периодичность сброса кэша на диск в сотых долях секунды.

    vm.dirty_expire_centisecs = 3000 - Параметр указывает как долго данные могут быть в кэше перед сбросом на диск. Задается так же в сотых долях секунды.

    vm.vfs_cache_pressure = 1000 - Тут загадка для меня и я так понял, что он конкретно делает но рекомендуют ставить 1000 и больше для обычных дисков и 50 и меньше для SSD. 

    vm.overcommit_memory = 1 - Режим выделения памяти который позволяет выделить приложению памяти больше чем есть на самом деле. И режим единица лучше ставить только с очень кривыми приложениями СУБД к ним не относится и для СУБД надо вообще этот режим работы выключить.

    vm.zone_reclaim_mode = 1 - Эта опция вообще из виртуализации и к базам данных отношения не имеет.

    vm.overcommit_ratio = 80 - Эта опция относится к vm.overcommit_memory в режиме 2.

    vm.swappiness = 0 - Параметр определяет то при каком % свободной оперативной памяти задействуется файл подкачки. При таком режиме система не будет использовать файл подкачки пока не закончится свободная память и лишь потом произойдет сброс страниц в своп.

    Заключение

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


    Связанные записи в блоге

    Обсуждение статьи

    Ваш комментарий: