Pull to refresh

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

Reading time 7 min
Views 26K
Привет, Хабрсообщество!

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

Входные данные моей задачи следующие: двухпроцессорная (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);
  • swork_mem в 1/20 RAM;
  • smaintenance_work_mem в 1/4 RAM;
  • smax_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.

Необходимо сделать следующие шаги:
  1. создать папки под таблицы, индексы и лог:
    #mkdir -p /disk1/postgresql/tables
    #mkdir -p /disk2/postgresql/ind
    #mkdir -p /disk3/postgresql/log
  2. сделать оунера 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

  3. зайти в клиент psql под postgres и создать два tablespace:
    CREATE TABLESPACE space_table LOCATION '/disk1/postgresql/tables'
    CREATE TABLESPACE space_index LOCATION '/disk2/postgresql/ind';

  4. если оунер вашей БД не postgres, а, например, myuser, то необходимо дать пользователю myuser права на созданные tablespace (можно выполнить также в клиенте):
    GRANT CREATE ON TABLESPACE space_table TO myuser;
    GRANT CREATE ON TABLESPACE space_index TO myuser;

  5. теперь под myuser 'ом можно изменить tablespace для таблиц и индексов:
    ALTER TABLE mytable SET TABLESPACE space_table;
    ALTER INDEX mytable SET TABLESPACE space_index;

  6. остановить демон постгреса, переместить папку с логом и сделать символическую ссылку на нее:
    #/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/pg_xlog

    Запускаем постгрес:
    #/usr/local/bin/rc.d/postgres.sh start

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


4. Partitioning


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

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

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

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

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

  3. создать таблицы-потомки вида:
    CREATE TABLE measure_data_y2011m06d06 (CHECK(measure_time = DATE '2011-06-06' AND measure_time DATE '2011-06-13')
    ) INHERITS (measure_data_master);

  4. создать индексы для таблиц-потомков:
    CREATE INDEX measure_data_y2011m06d06_key ON measure_data_y2011m06d06(measure_time);

  5. необходимо, чтобы при вставке новой строки, она записывалась в нужную таблицу-потомка. Создадим для этого триггерную функцию:
    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;

  6. ну и сам триггер, который вызовет функцию:
    CREATE TRIGGER insert_measure_trigger
        BEFORE INSERT ON measure_data_master
        FOR EACH ROW EXECUTE PROCEDURE measure_insert_trigger();


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

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

Articles