Ключевые слова:database, postgresql, pgpooll, balance, proxy, (найти похожие документы)
From: Dmitry Stasyuk
Date: Mon, 21 Nov 2009 17:02:14 +0000 (UTC)
Subject: Учебное руководство по pgpool-II
Оригинал: http://undenied.ru/2009/03/04/uchebnoe-rukovodstvo-po-pgpool-iipgpool-II это прослойка, работающая между серверами PostgreSQL и
клиентами СУБД PostgreSQL. Она предоставляет следующие функции:
* Объединение соединений
pgpool-II сохраняет соединения с серверами PostgreSQL и использует
их повторно в случае если новое соединение устанавливается с теми
же параметрами (т.е. имя пользователя, база данных, версия
протокола). Это уменьшает накладные расходы на соединения и
увеличивает производительность системы вцелом.
* Репликация
pgpool-II может управлять множеством серверов PostgreSQL.
Использование функции репликации данных позволяет создание
резервной копии данных в реальном времени на 2 или более
физических дисков, так что сервис может продолжать работать без
остановки серверов в случае выхода из строя диска.
* Балансировка нагрузки
Если база данных реплицируется, то выполнение запроса SELECT на
любом из серверов вернет одинаковый результат. pgpool-II использует
преимущество функции репликации для уменьшения нагрузки на каждый
из серверов PostgreSQL распределяя запросы SELECT на несколько
серверов, тем самым увеличивая производительность системы вцелом. В
лучшем случае производительность возрастает пропорционально числу
серверов PostgreSQL. Балансировка нагрузки лучше всего работает в
случае когда много пользователей выполняют много запросов в одно и
тоже время.
* Ограничение лишних соединений
Существует ограничение максимального числа одновременных соединений
с PostgreSQL, при превышении которого новые соединения
отклоняются. Установка максимального числа соединений, в то же
время, увеличивает потребление ресурсов и снижает
производительность системы. pgpool-II также имеет ограничение на
максимальное число соединений, но "лишние" соединения будут
поставлены в очередь вместо немедленного возврата ошибки.
* Параллельные запросы
Используя функцию параллельных запросов можно разнести данные на
множество серверов, благодаря чему запрос может быть выполнен на
всех серверах одновременно для уменьшения общего времени
выполнения. Параллельные запросы работают лучше всего при поиске в
больших объемах данных.
pgpool-II общается по протоколу бэкенда и фронтенда PostgreSQL и
располагается между ними. Таким образом, приложение базы данных
(фронтенд) считает что pgpool-II - настоящий сервер PostgreSQL, а
сервер (бэкенд) видит pgpool-II как одного из своих клиентов. Поскольку
pgpool-II прозрачен как для сервера, так и для клиента, существующие
приложения, работающие с базой данных, могут использоваться с pgpool-II
практически без изменений в исходном коде.
Ниже представлен перевод учебного руководства по pgpool-II.
Оригинал руководства доступен по адресу
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.htmlУчебное руководство по pgpool-II
Добро пожаловать в Учебное руководство по pgpool-II. С его помощью вы
можете научиться устанавливать, настраивать и запускать параллельные
запросы или осуществлять репликацию используя pgpool-II. Мы
подразумеваем что вы уже владеете знаниями о простых операциях с
PostgreSQL, так что, пожалуйста, при необходимости обращайтесь к
документации PostgreSQL.
1. Давайте начнем!
Перед тем как использовать репликацию или параллельные запросы мы
должны научиться устанавливать и настраивать pgpool-II и узлы базы
данных.
1.1. Установка pgpool-II
Установка pgpool-II очень проста. В каталоге, в который вы распаковали
архив
с исходными текстами, выполните следующие команды.
$ ./configure
$ make
$ make install
Скрипт configure собирает информацию о вашей системе и использует ее
в процедуре компиляции. Вы можете указать параметры в командной
строке скрипта configure чтобы изменить его поведение по-умолчанию,
такие, например, как каталог установки. pgpool-II по-умолчанию будет
установлен в каталог /usr/local.
Команда make скомпилирует исходный код, а make install установит
исполняемые файлы. У вас должно быть право на запись в каталог
установки.
В этом учебном руководстве мы будем устанавливать pgpool-II в каталог
по-умолчанию /usr/local.
Обратите внимание: для работы pgpool-II необходима библиотека
libpq для PostgreSQL 7.4 или более поздней версии (3 версия протокола).
Если скрипт configure выдает следующее сообщение об ошибке,
возможно не установлена библиотека libpq или она не 3 версии.
configure: error: libpq is not installed or libpq is old
Если библиотека 3 версии, но указанное выше сообщение все-таки
выдается, ваша библиотека libpq, вероятно, не распознается скриптом
configure.
Скрипт configure ищет библиотеку libpq начиная от каталога
/usr/local/pgsql. Если вы установили PostgreSQL в каталог
отличный от /usr/local/pgsql используйте параметры командной
строки --with-pgsql или --with-pgsql-includedir
вместе с параметром --with-pgsql-libdir при запуске скрипта
configure.
1.2. Файлы конфигурации
Параметры конфигурации pgpool-II хранятся в файле pgpool.conf. Формат
файла: одна пара "параметр = значение" в строке. При установке
pgpool-II автоматически создается файл pgpool.conf.sample. Мы
рекомендуем скопировать его в файл pgpool.conf, а затем
отредактировать по вашему желанию.
$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
pgpool-II принимает соединения только с localhost на порт 9999. Если
вы хотите принимать соединения с других хостов, установите для
параметра listen_addresses значение `*'.
listen_addresses = 'localhost'
port = 9999
Мы будем использовать параметры по-умолчанию в этом руководстве.
1.3. Настройка команд PCP
У pgpool-II есть интерфейс для административных целей - получить
информацию об узлах базы данных, остановить pgpool-II и т.д. - по
сети. Чтобы использовать команды PCP, необходима идентификация
пользователя. Эта идентификация отличается от идентификации
пользователей в PostgreSQL. Имя пользователя и пароль нужно указывать
в файле pcp.conf. В этом файле имя пользователя и пароль указываются
как пара значений, разделенных двоеточием (:). Одна пара в строке.
Пароли зашифрованы в формате хэша md5.
postgres:e8a48653851e28c69d0506508fb27fc5
При установке pgpool-II автоматически создается файл pcp.conf.sample.
Мы рекомендуем скопировать его в файл pcp.conf и отредактировать.
$ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
Для того чтобы зашифровать ваш пароль в формате хэша md5 используете
команду pg_md5, которая устанавливается как один из исполняемых
файлов pgpool-II. pg_md5 принимает текст в параметре командной строки
и отображает текст его md5 хэша.
Например, укажите "postgres" в качестве параметра командной строки и
pg_md5 выведет текст хэша md5 в стандартный поток вывода.
$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
Команды PCP выполняются по сети, так что в файле pgpool.conf должен
быть указан номер порта в параметре pcp_port.
Мы будем использовать значение по-умолчанию для параметра pcp_port 9898
в этом руководстве.
pcp_port = 9898
1.4. Подготовка узлов базы данных
Теперь нам нужно настроить серверы бэкендов PostgreSQL для pgpool-II.
Эти серверы могут быть размещены на одном хосте с pgpool-II или на
отдельных машинах. Если вы решите разместить серверы на том же хосте,
для всех серверов должны быть установлены разные номера портов. Если
серверы размещены на отдельных машинах, они должны быть настроены так
чтобы могли принимать сетевые соединения от pgpool-II.
В этом руководстве мы разместили три сервера в рамках одного хоста
вместе с pgpool-II и присвоили им номера портов 5432, 5433, 5434
соответственно. Для настройки pgpool-II отредактируйте файл
pgpool.conf как показано ниже.
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_hostname2 = 'localhost'
backend_port2 = 5434
backend_weight2 = 1
В параметрах backend_hostname, backend_port, backend_weight укажите
имя хоста узла базы данных, номер порта и коэффициент для
балансировки нагрузки. В конце имени каждого параметра должен быть
указан идентификатор узла путем добавления положительного целого
числа начиная с 0 (т.е. 0, 1, 2, .).
Параметры backend_weight все равны 1, что означает что запросы
SELECT равномерно распределены по трем серверам.
1.5. Запуск/Остановка pgpool-II
Для старта pgpool-II выполните в терминале следующую команду.
$ pgpool
Указанная выше команда, однако, не печатает протокол своей работы
потому что pgpool отсоединяется от терминала. Если вы хотите показать
протокол работы pgpool, укажите параметр -n в командной строке при
запуске pgpool. pgpool-II будет запущен как процесс не-демон и
терминал не будет отсоединен.
$ pgpool -n &
Протокол работы будет печататься на терминал, так что рекомендуемые
для использования параметры командной строки, например, такие.
$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
Параметр -d включает генерацию отладочных сообщений.
Указанная выше команда постоянно добавляет выводимый протокол работы
в файл /tmp/pgpool.log. Если вам нужно ротировать файлы протоколов,
передавайте протоколы внешней команде, у которой есть функция ротации
протоколов. Вам поможет, например, cronolog.
$ pgpool -n 2>&1 | /usr/sbin/cronolog
--hardlink=/var/log/pgsql/pgpool.log
'/var/log/pgsql/%Y-%m-%d-pgpool.log' &
Чтобы остановить процесс pgpool-II, выполните следующую команду.
$ pgpool stop
Если какие-то из клиентов все еще присоединены, pgpool-II ждет пока
они не отсоединятся и потом завершает свою работу. Если вы хотите
завершить pgpool-II насильно, используйте вместо этой следующую
команду.
$ pgpool -m fast stop
2. Ваша первая репликация
Репликация включает копирование одних и тех же данных на множество
узлов базы данных.
В этом разделе мы будем использовать три узла базы данных, которые мы
уже установили в разделе "1. Давайте начнем!", и проведем вас шаг
за шагом к созданию системы репликации базы данных. Пример данных для
репликации будет сгенерирован программой для тестирования pgbench.
2.1. Настройка репликации
Чтобы включить функцию репликации базы данных установите значение
true для параметра replication_mode в файле pgpool.conf.
replication_mode = true
Если параметр replication_mode равен true, pgpool-II
будет отправлять копию принятого запроса на все узлы базы данных.
Если параметр load_balance_mode равен true, pgpool-II
будет распределять запросы SELECT между узлами базы данных.
load_balance_mode = true
В этом разделе мы включили оба параметра replication_mode и
load_balance_mode.
2.2. Проверка репликации
Для отражения изменений, сделанных в файле pgpool.conf,
pgpool-II должен быть перезапущен. Пожалуйста обращайтесь к разделу
"1.5 Запуск/Остановка pgpool-II".
После настройки pgpool.conf и перезапуска pgpool-II, давайте проверим
репликацию в действии и посмотрим все ли работает хорошо.
Сначала нам нужно создать базу данных, которую будем реплицировать.
Назовем ее "bench_replication". Эту базу данных нужно создать на всех
узлах. Используйте команду createdb через pgpool-II и база данных
будет создана на всех узлах.
$ createdb -p 9999 bench_replication
Затем мы запустим pgbench с параметром -i. Параметр -i
инициализирует базу данных предопределенными таблицами и данными в них.
$ pgbench -i -p 9999 bench_replication
Указанная ниже таблица содержит сводную информацию о таблицах и
данных, которые будут созданы при помощи pgbench -i. Если на всех
узлах базы данных перечисленные таблицы и данные были созданы,
репликация работает корректно.
Имя таблицы Число строк
branches 1
tellers 10
accounts 100000
history 0
Для проверки указанной выше информации на всех узлах используем простой
скрипт на shell.
Приведенный ниже скрипт покажет число строк в таблицах branches,
tellers, accounts и history на всех узлах базы данных (5432, 5433,
5434).
for port in 5432 5433 5434; do
echo $port
for table_name in branches tellers accounts history; do
echo $table_name
psql -c "SELECT count(*) FROM $table_name" -p $port bench_replication
done
done
3. Ваш первый параллельный запрос
Данные из разных диапазонов сохраняются на двух или более узлах базы
данных параллельным запросом. Это называется распределением (часто
используется без перевода термин partitioning прим. переводчика).
Более того, одни и те же данные на двух и более узлах базы данных
могут быть воспроизведены с использованием распределения.
Чтобы включить параллельные запросы в pgpool-II вы должны установить
еще одну базу данных, называемую "Системной базой данных" ("System
Database") (далее будем называть ее SystemDB).
SystemDB хранит определяемые пользователем правила, определяющие
какие данные будут сохраняться на каких узлах бызы данных. Также
SystemDB используется чтобы объединить результаты возвращенные узлами
базы данных посредством dblink.
В этом разделе мы будем использовать три узла базы данных, которые мы
установили в разделе "1. Давайте начнем!", и проведем вас шаг за
шагом к созданию системы баз данных с параллельными запросами. Для
создания примера данных мы снова будем использовать pgbench.
3.1. Настройка параллельного запроса
Чтобы включить функцию выполнения параллельных запросов установите
для параметра parallel_mode значение true в файле pgpool.conf.
parallel_mode = true
Установка параметра parallel_mode равным true не запустит
параллельные запросы автоматически. Для этого pgpool-II нужна
SystemDB и правила определяющие как распределять данные по узлам базы
данных.
Также SystemDB использует dblink для создания соединений с pgpool-II.
Таким образом, нужно установить значение параметра listen_addresses
таким образом чтобы pgpool-II принимал эти соединения.
listen_addresses = '*'
Внимание: Репликация не реализована для таблиц, которые
распределяются посредством параллельных запросов и, в то же время,
репликация может быть успешно осуществлена. Вместе с тем, из-за того
что набор хранимых данных отличается при параллельных запросах и при
репликации, база данных "bench_replication", созданная в разделе "2.
Ваша первая репликация" не может быть повторно использована.
replication_mode = true
load_balance_mode = false
ИЛИ
replication_mode = false
load_balance_mode = true
В этом разделе мы установим параметры parallel_mode и load_balance_mode
равными true, listen_addresses равным `*', replication_mode равным
false.
3.2. Настройка SystemDB
В основном, нет отличий между простой и системной базами данных.
Однако, в системной базе данных определяется функция dblink и
присутствует таблица, в которой хранятся правила распределения
данных. Таблицу dist_def необходимо определять. Более того, один из
узлов базы данных может хранить системную базу данных, а pgpool-II
может использоваться для распределения нагрузки каскадным
подключеним.
В этом разделе мы создадим SystemDB на узле с портом 5432. Далее
приведен список параметров конфигурации для SystemDB
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
На самом деле, указанные выше параметры являются параметрами
по-умолчанию в файле pgpool.conf. Теперь мы должны создать
пользователя с именем "pgpool" и базу данных с именем "pgpool" и
владельцем "pgpool".
$ createuser -p 5432 pgpool
$ createdb -p 5432 -O pgpool pgpool
3.2.1. Установка dblink
Далее мы должны установить dblink в базу данных "pgpool". dblink -
один из инструментов включенных в каталог contrib исходного кода
PostgreSQL.
Для установки dblink на вашей системе выполните следующие команды.
$ USE_PGXS=1 make -C contrib/dblink
$ USE_PGXS=1 make -C contrib/dblink install
После того как dblink был установлен в вашей системе мы добавим
функции dblink в базу данных "pgpool". Если PostgreSQL установлен в
каталог /usr/local/pgsql, dblink.sql (файл с определениями функций)
должен быть установлен в каталог /usr/local/pgsql/share/contrib.
Теперь выполним следующую команду для добавления функций dblink.
$ psql -f /usr/local/pgsql/share/contrib/dblink.sql -p 5432 pgpool
3.2.2. Создание таблицы dist_def
Следующим шагом мы создадим таблицу с именем "dist_def", в которой
будут храниться правила распределения данных. Поскольку pgpool-II уже
был установлен, файл с именем system_db.sql должен буть установлен в
/usr/local/share/system_db.sql (имейте в виду что это учебное
руководство и мы использовали для установки каталог по-умолчанию -
/usr/local). Файл system_db.sql содержит директивы для создания
специальных таблиц, включая и таблицу "dist_def".
Выполните следующую команду для создания таблицы "dist_def".
$ psql -f /usr/local/share/system_db.sql -p 5432 -U pgpool pgpool
Все таблицы в файле system_db.sql, включая "dist_def", создаются в
схеме "pgpool_catalog". Если вы установили параметр system_db_schema
на использование другой схемы вам нужно, соответственно,
отредактировать файл system_db.sql.
Описание таблицы "dist_def" выглядит так как показано ниже. Имя
таблицы не должно измениться.
CREATE TABLE pgpool_catalog.dist_def (
dbname text, -- имя базы данных
schema_name text, -- имя схемы
table_name text, -- имя таблицы
col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- столбец-ключ для распределения данных
col_list text[] NOT NULL, -- список имен столбцов
type_list text[] NOT NULL, -- список типов столбцов
dist_def_func text NOT NULL, -- имя функции распределения данных
PRIMARY KEY (dbname, schema_name, table_name)
);
Записи, хранимые в таблице "dist_def", могут быть двух типов.
* Правило Распределения Данных (col_name, dist_def_func)
* Мета-информация о таблицах (dbname, schema_name, table_name,
col_list, type_list)
Правило распределения данных определяет как будут распределены данные
на конкретный узел базы данных. Данные будут распределены в
зависимости от значения столбца "col_name". "dist_def_func" - это
функция, которая принимает значение "col_name" в качестве агрумента и
возвращает целое число, которое соответствует идентификатору узла
базы данных, на котором должны быть сохранены данные.
Мета-информация используется для того чтобы переписывать запросы.
Параллельный запрос должен переписывать исходные запросы так чтобы
результаты, возвращаемые узлами-бэкендами, могли быть объединены в
единый результат.
3.2.2. Создание таблицы replicate_def
В случае если указана таблица, для которой производится репликация в
выражение SQL, использующее зарегистрированную в dist_def таблицу
путем объединения таблиц, информация о таблице, для которой
необходимо производить репликацию, предварительно регистрируется в
таблице с именем replicate_def. Таблица replicate_def уже была
создана при обработке файла system_db.sql во время создания таблицы
dist_def. Таблица replicate_def описана так как показано ниже.
CREATE TABLE pgpool_catalog.replicate_def (
dbname text, -- имя базы данных
schema_name text, -- имя схемы
table_name text, -- имя таблицы
col_list text[] NOT NULL, -- список имен столбцов
type_list text[] NOT NULL, -- список типов столбцов
PRIMARY KEY (dbname, schema_name, table_name)
);
3.3. Установка правил распределения данных
В этом учебном руководстве мы определим правила распределения данных,
созданных программой pgbench, на три узла базы данных. Тестовые
данные будут созданы командой "pgbench -i -s 3'' (т.е. масштабный
коэффициент равен 3). Для этого раздела мы создадим новую базу данных
с именем "bench_parallel".
В каталоге sample исходного кода pgpool-II вы можете найти файл
dist_def_pgbench.sql. Мы будем использовать этот файл с примером для
создания правил распределения для pgbench. Выполните следующую
команду в каталоге с распакованным исходным кодом pgpool-II.
$ psql -f sample/dist_def_pgbench.sql -p 5432 pgpool
Ниже представлено описание файла dist_def_pgbench.sql.
В файле dist_def_pgbench.sql мы добавляем одну строку в таблицу
"dist_def". Это функция распределения данных для таблицы accounts. В
качестве столбца-ключа указан столбец aid.
INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'accounts',
'aid',
ARRAY['aid', 'bid', 'abalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)'],
'pgpool_catalog.dist_def_accounts'
);
Теперь мы должны создать функцию распределения данных для таблицы
accounts. Заметим, что вы можете использовать одну и ту же функцию
для разных таблиц. Также вы можете создавать функции с использованием
языков отличных от SQL (например, PL/pgSQL, PL/Tcl, и т.д.).
Таблица accounts в момент инициализации данных хранит значение
масштабного коэффициента равное 3, значения столбца aid от 1 до
300000. Функция создана таким образом что данные равномерно
распределяются по трем узлам базы данных.
Следующая SQL-функция будет возвращать число узлов базы данных.
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)
RETURNS integer AS $$
SELECT CASE WHEN $1 > 0 AND $1 <= 1 THEN 0
WHEN $1 > 1 AND $1 <= 2 THEN 1
ELSE 2
END;
$$ LANGUAGE sql;
3.4. Установка правил репликации
Правило репликации - это то что определяет какие таблицы должны быть
использованы для выполнения репликации.
Здесь это сделано при помощи pgbench с зарегистрированными таблицами
branches и tellers.
Как результат, стало возможно создание таблицы accounts и выполнение
запросов, использующих таблицы branches и tellers.
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'branches',
ARRAY['bid', 'bbalance', 'filler'],
ARRAY['integer', 'integer', 'character(88)']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'tellers',
ARRAY['tid', 'bid', 'tbalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)']
);
Подготовленный файл Replicate_def_pgbench.sql находится в каталоге
sample. Команда psql запускается с указанием пути к исходному коду,
определяющему правила репликации, например, как показано ниже.
$ psql -f sample/replicate_def_pgbench.sql -p 5432 pgpool
3.5. Проверка параллельного запроса
Для отражения изменений, сделанных в файле pgpool.conf,
pgpool-II должен быть перезапущен. Пожалуйста обращайтесь к разделу
"1.5 Запуск/Остановка pgpool-II".
После настройки pgpool.conf и перезапуска pgpool-II,
давайте проверим хорошо ли работают параллельные запросы.
Сначала нам нужно создать базу данных, которая будет распределена. Мы
назовем ее "bench_parallel". Эту базу данных нужно создать на всех
узлах. Используйте команду createdb посредством pgpool-II и база
данных будет создана на всех узлах.
$ createdb -p 9999 bench_parallel
Затем запустим pgbench с параметрами -i -s 3. Параметр -i
инициализирует базу данных предопределенными таблицами и данными.
Параметр -s указывает масштабный коэффициент для инициализации.
$ pgbench -i -s 3 -p 9999 bench_parallel
Созданные таблицы и данные в них показаны в разделе
"3.3. Установка правил распределения данных".
Один из способов проверить корректно ли были распределены данные -
выполнить запрос SELECT посредством pgpool-II и напрямую на бэкендах
и сравнить результаты. Если все настроено правильно база данных
"bench_parallel" должна быть распределена как показано ниже.
Имя таблицы Число строк
branches 3
tellers 30
accounts 300000
history 0
Для проверки указанной выше информации на всех узлах и посредством
pgpool-II используем простой скрипт на shell. Приведенный ниже скрипт
покажет минимальное и максимальное значение в таблице accounts
используя для соединения порты 5432, 5433, 5434 и 9999.
for port in 5432 5433 5434i 9999; do
echo $port
psql -c "SELECT min(aid), max(aid) FROM accounts" -p $port bench_parallel
done
