FreeBSD + PostgreSQL: тюнинг сервера БД

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

Входные данные моей задачи следующие: двухпроцессорная (Intel Xeon) машинка, 8 хардов по 500Гб и 12Гб оперативки. И полный, в том числе физический, доступ к этому добру. Задача: организовать быстрый сервер БД на основе ОС FreeBSD и PostgreSQL.

1. RAID

Правильное разбиение имеющихся хардов на рейды нам понадобится для такой возможности PostgreSQL, как tablespacing (об это ниже). Свои 8 хардов я разбил на пары, организовав таким образом: две пары объеденил в RAID1 и две пары в RAID0 (вообще, для наших целей нужно минимум 6 хардов — две пары объеденить в RAID1, остальные 2 оставить как есть). При наличии же большего числа хардов можно придумать что-нибудь и понадежнее, типа RAID5, RAID10 и т.п., но есть вероятность, что работать это будет несколько медленнее. Не буду вдаваться в подробности как организовывать рейды, т.к. в железе я не силен, скажу лишь только, что никаких контроллеров не трогал, т.к. на серваке после биоса грузится утилита, которая позволяет это сделать программно.

2. Установка ОС, сервера БД и использование своего ядра

Сначала просто ставим фряху на первый RAID1. Я ставил дистрибутив FreeBSD 8.2 Release AMD64 со всеми файлами. 64-х битная версия нужна, чтобы система «видела» всю оперативную память.

Теперь самое интересное: для чего нам компилить ядро и какие параметры изменять? Это нужно, чтобы разрешить серверу PostgreSQL использовать столько ресурсов, сколько потребуется для высокой нагруженности. Итак, какие параметры БД нас интересуют. В книге Алексея Васильева «Работа с Postgresql. Настройка, масштабирование» рекомендованы следующие параметры для высоконагруженных БД (файл postgresql.conf):

• shared_buffers = 1/8 RAM или больше (но не более 1/4);
• work_mem в 1/20 RAM;
• maintenance_work_mem в 1/4 RAM;
• max_fsm_relations в планируемое кол–во таблиц в базах * 1.5;
• max_fsm_pages в max_fsm_relations * 2000;
• fsync = true;
• wal_sync_method = fdatasync;
• commit_delay = от 10 до 100;
• commit_siblings = от 5 до 10;
• effective_cache_size = 0.9 от значения cached, которое показывает free;
• random_page_cost = 2 для быстрых cpu, 4 для медленных;
• cpu_tuple_cost = 0.001 для быстрых cpu, 0.01 для медленных;
• cpu_index_tuple_cost = 0.0005 для быстрых cpu, 0.005 для медленных;
• autovacuum = on;
• autovacuum_vacuum_threshold = 1800;
• autovacuum_analyze_threshold = 900;

Эти опции нас действительно устраивают, кроме двух:

1) Максимальное число соединений

Зависит от конкретной ситуации. У меня работает скрипт в кроне (коннектиться к БД и заносит данные), я посчитал, что должно хватить 256:

max_connection = 256;

Но умолчальная конфигурация FreeBSD не предусматривает такое значение для числа соединений. Если вы выставите такое значение и попытаетесь запустить демон постгреса, то ничего не выйдет. Нужно увеличивать соответствующие параметры системы. Для этого и соберем свое ядро. Возьмем умолчальный конфиг ядра GENERIC, сделаем копию с именем KERNEL_MAX_PERF, отредактируем KERNEL_MAX_PERF следующим образом: изменим число семафоров, добавив к дефолтным опциям строки:

options SEMMNI = 512
options SEMMNS = 1024
options SEMUME = 64
options SEMMNU = 512

(это значения для max_connection = 256).

2) Максимальный объем оперативной памяти, который может занять PostgreSQL (это важно при объемных запросах). За него отвечает параметр shared_buffers в postgresql.conf. Насчет значения для этой величины существуют разные рекомендации. Я пришел к выводу, что если это выделенный под БД сервер, то можно одному процессу отдать почти весь объем оперативки минус то, что нужно системе на свои нужды. Я выделил 8Гб из 12. Для того, чтобы система разрешила выставить нужное нам значение для shared_buffers, в ядре необходимо изменить опцию SHMMAXPGS, значение которой вычисляется по формуле:

SHMMAXPGS = shared_buffers / PAGE_SIZE

в моем случае shared_buffers = 8Гб, PAGE_SIZE = 4Кб для всех i386, значит

SHMMAXPGS = 8 * 1024 * 1024 / 4 = 2097152)

теперь можем записать параметр SHMMAX (вычисляется в ядре динамически). Итак, пишем в конфиг ядра:

options SHMMAXPGS = 2097152
options SHMMAX = "(SHMMAXPGS*PAGE_SIZE + 1)"

Осталось скомпилировать ядро с конфигом KERNEL_MAX_PERF. Сама процедура компиляции ядра проста, здесь я вас отсылаю к официальному ману.

Загружаем ОС со своим ядром, устанавливаем PostgreSQL последней версии (у меня это была версия 9.0.4), сначала для проверки стартуем PostgreSQL с умолчальным конфигом. Если все ок, меняем параметры в postgresql.conf на оговоренные выше, делаем рестарт PostgreSQL. Запустилось — едем дальше.

Замечание: если по какой-либо причине не удалось скомпилить ядро с выставленными параметрами, то можно их прописать в sysctl.conf:

kern.ipc.shmall = 2097152
kern.ipc.shmmax = 8589938688
kern.ipc.semmap = 256

и запускать фряху с умолчальным ядром GENERIC.

3. Tablespacing

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

Вот здесь нам и понадобятся наши рейды. Напомню, что у нас имеется четыре раздела: два RAID1 и два RAID0. На первом RAID1 у нас установлена операционка и постгрес. На втором RAID1 будем хранить таблицы нашей БД. Предположим, что он монтирован как /disk1. На первом RAID0 будем хранить индексы. Пусть он будет монтирован в файловой системе как /disk2. Логам оставим второй RAID0, предполагаем, что он монтирован как /disk3.

Необходимо сделать следующие шаги:
создать папки под таблицы, индексы и лог:

mkdir -p /disk1/postgresql/tables
mkdir -p /disk2/postgresql/ind
mkdir -p /disk3/postgresql/log

сделать оунера postgres для этих папок, а у остальных отнять все права (напомню, что postgres – это пользователь, который заводится при установке PostgreSQL, если установку производить стандартным образом по официальному ману):

chown -R postgres /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log
chmod -R go-rwx /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log

зайти в клиент psql под postgres и создать два tablespace:

CREATE TABLESPACE space_table LOCATION '/disk1/postgresql/tables';
CREATE TABLESPACE space_index LOCATION '/disk2/postgresql/ind';

если оунер вашей БД не postgres, а, например, myuser, то необходимо дать пользователю myuser права на созданные tablespace (можно выполнить также в клиенте):

GRANT CREATE ON TABLESPACE space_table TO myuser;
GRANT CREATE ON TABLESPACE space_index TO myuser;

теперь под myuser ‘ом можно изменить tablespace для таблиц и индексов:

ALTER TABLE mytable SET TABLESPACE space_table;
ALTER INDEX mytable SET TABLESPACE space_index;

остановить демон постгреса, переместить папку с логом и сделать символическую ссылку на нее:

/usr/local/bin/rc.d/postgres.sh stop
mv /usr/local/pgsql/data/pg_xlog /disk3/postgresql/log
cd /usr/local/pgsql/data
ln -s /disk3/postgresql/log

Запускаем постгрес:

/usr/local/bin/rc.d/postgres.sh start

Если все сделали правильно, демон должен запуститься.

4. Partitioning

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

У меня довольно типичная ситуация: в кроне работает скрипт, собирая статистику по некоему измерению. По веб-интерфейсу пользователь может эту статистику посмотреть. За неделю в таблицу инсертится примерно 10млн строк. Если все писать в одну таблицу, то вас проклянут. Работать все это будет ужасно медленно.

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

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

Итак, есть у нас таблица measure_data_master, куда мы пишем свои измерения. Допустим, в качестве временного промежутка устраивает одна неделя. Поехали:
для мастер-таблицы measure_data_master НЕ делать никаких ограничений целостности check и НЕ создавайте индексов
в конфиг postgresql.conf редактировать опцию:

constraint_exclusion = on

создать таблицы-потомки вида:

CREATE TABLE measure_data_y2011m06d06 (
  CHECK (measure_time >= DATE '2011-06-06' AND measure_time < DATE '2011-06-13' )
) INHERITS (measure_data_master);

создать индексы для таблиц-потомков:

CREATE INDEX measure_data_y2011m06d06_key ON measure_data_y2011m06d06 (measure_time);

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

CREATE OR REPLACE FUNCTION measure_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF ( NEW.measure_time >= DATE '2011-06-06' AND
     NEW.measure_time < DATE '2011-06-13') THEN
    INSERT INTO measure_data_y2011m06d06 VALUES (NEW.*);
  ELSIF ( NEW.measure_time >= DATE '2011-06-13' AND
      NEW.measure_time < DATE '2011-06-20' ) THEN
    INSERT INTO measure_data_y2011m06d13 VALUES (NEW.*);
  ...
  ELSIF ( NEW.measure_time >= DATE '2011-12-19' AND
      NEW.measure_time < DATE '2011-12-26' ) THEN
    INSERT INTO measure_data_y2011m12d19 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Date out of range. Fix the measure_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

ну и сам триггер, который вызовет функцию:

CREATE TRIGGER insert_measure_trigger
  BEFORE INSERT ON measure_data_master
FOR EACH ROW EXECUTE PROCEDURE measure_insert_trigger();

Конечно, писать такие большие запросы неудобно. Я написал скрипт на php, который создает таблицы и все что для них нужно на целый год вперед.

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