Search     or:     and:
 LINUX 
 Language 
 Kernel 
 Package 
 Book 
 Test 
 OS 
 Forum 
iakovlev.org

MySQL & PostgreSQL

Я написал цикл статей для сайта IBM, в которых сделал сравнительный анализ этих двух наиболее популярных open-source баз данных. Ссылки на cайт IBM:

Часть 1: Сравнительный анализ
Часть 2: Конфигурация MySQL
Часть 3: Конфигурация PostgreSQL
Часть 4: Масштабирование MySQL
Часть 5: Масштабирование PostgreSQL
Часть 6: Репликация MySQL

MySQL & PostgreSQL. Часть 1: Сравнительный анализ

  При сравнении MySQL и PostgreSQL исторически сформировался следующий подход:

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

Сегодня мы рассмотрим два наиболее популярных сервера баз данных, основанных на open-source. Вначале поговорим об истории развития и архитектуре программных продуктов, а затем перейдем к сравнительному анализу. Автор не ставит своей целью написать подробный учебник по MySQL и PostgreSQL – в рамках короткого цикла статей это невозможно. Скорее здесь идет речь о небольшом вводном курсе для тех, кто еще не определился с выбором ПО. В следующих статьях мы будем поочередно рассматривать различные аспекты использования MySQL и PostgreSQL.

1. История развития MySQL и PostgreSQL

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

  1. Коннект.
  2. Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
  3. Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
  4. Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
  5. Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

  1. Инициализация сервера.
  2. Менеджер коннектов.
  3. Менеджер потоков.
  4. Обработчик команд.
  5. Аутентификация.
  6. Парсер.
  7. Кеш.
  8. Оптимизатор.
  9. Табличный менеджер.
  10. Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
  11. Логирование.
  12. Репликация.
  13. Сетевое API.
  14. API ядра.

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

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.

Наиболее важный код находится в файле sql/mysqld.cc. В нем находятся базовые функции, которые не меняются со времен версии 3.22: init_common_variables() init_thread_environment() init_server_components() grant_init() // sql/sql_acl.cc init_slave() // sql/slave.cc get_options() handle_connections_sockets() create_new_thread() handle_one_connection() check_connection() acl_check_host() // sql/sql_acl.cc create_random_string() // sql/password.cc check_user() // sql/sql_parse.cc mysql_parse() // sql/sql_parse.cc dispatch_command() Query_cache::store_query() // sql/sql_cache.cc JOIN::optimize() // sql/sql_select.cc open_table() // sql/sql_base.cc mysql_update() // sql/sql_update.cc mysql_check_table() // sql/sql_table.cc

В хидере sql/sql_class.h определяются базовые классы: Query_arena, Statement, Security_context, Open_tables_state classes, THD. Обьект класса THD представляет собой дескриптор потока и является аргументом большого количества функций.

Сравнение MySQL и PostgreSQL: сходство и различия

ACID-стандарт

Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.

Производительность (performance)

Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что MySQL начинала разрабатываться с прицелом на скорость, а PostgreSQL с самого начала разрабатывалась как база с большим числом настроек и соответствием стандарту. PostgreSQL имеет ряд настроек, которые повышают скорость доступа:

  • парциальные индексы;
  • компрессия данных;
  • выделение памяти;
  • улучшенный кеш.

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

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

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

SMP

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

Хранимые процедуры

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на INSERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

  • SBR – statement based replication;
  • RBR – row based replication.

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVEPOINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

  • MyISAM – используется для хранения системных таблиц;
  • InnoDB – максимальное соответствие ACID, хранит данные с первичными ключами, кэширует инсерты, поддерживает компрессию, начиная с версии 5.1 – см. атрибут ROW_FORMAT=COMPRESSED;
  • NDB Cluster – движок, ориентированный на работу с памятью, кластерная архитектура, использующая синхронную репликацию;
  • ARCHIVE – поддерживает компрессию, не использует индексы;
  • а также: MERGE, MEMORY (HEAP), CSV.

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

Лицензирование

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

Заключение

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.

Часть 2: Конфигурация MySQL

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

Как конфигурировать MySQL

Конфигурировать MySQLможно как из командной строки при запуске, так и в конфиг-файле – как правило, это /etc/my.cnf. Чтобы проверить это наверняка, запустите команду:

  # /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
 

Конфиг-файл разбит на секции, каждая секция заключена в квадратные скобки. Параметры имеют три области видимости:

  • глобальные;
  • сессионные;
  • для конкретного обьекта.

Некоторые параметры могут входить сразу в несколько зон видимости, например sort_buffer_size может быть сессионным параметром.

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

Настройка памяти

Вначале нужно определить, сколько MySQLвообще может потреблять памяти в системе. Операционной системе тоже нужно оставить память для нормальной работы. Для 32-битного линуксового ядра, вообще говоря, одному процессу, в нашем случае MySQL-серверу, может быть отдано не более 2 ГБ памяти. Многие ограничения в этом плане будут сняты в версии 5.1. Память в MySQL более всего нужна для кеширования запросов, и для разных движков (engine) она настраивается по-разному, в частности:
    1. кеширование данных для движка MyISAM: данные берутся из .myd-файлов и кешируются операционной системой, 
 			а не самим движком, и не настраиваются в конфиге;
    2. кеширование индексов для движка MyISAM (key_buffer) – сюда нужно отдавать до 50% общей памяти, 
 			выделенной на весь кеш. По умолчанию в конфиге стоит один key_fuffer, 
 			но можно создать свой: key_buffer_1.key_buffer_size = 1G. 
 			Можно дать приказ на привязку конкретных таблиц (t1 и t2) к созданному буферу:
 
                mysql> CACHE INDEX t1, t2 IN key_buffer_1;
 
Более того, в конфиг можно добавить опцию init_file = , куда поместить аналогичные команды, и этот sql-файл будет загружен при старте сервера. Мониторить использование этого кеша можно с помощью команд SHOW STATUS и SHOW VARIABLES;
    3. кеш для движка InnoDB (innodb_buffer_pool_size): работает иначе, нежели кеш в MyISAM – сюда, 
 			кроме индексов, кешируются данные, буфер вставки, блокировки и другие структуры. 
 			Он также используется для кеширования записи. 
 			Сюда можно отдать до 80% всей физической памяти.
    4. табличный кеш (table_cache): в нем кешируются данные из файлов .frm, 
 			при этом уменьшается число обращений к диску. 
 			Здесь нужно найти баланс между числом открытых таблиц и размером самого кеша. 
 			В основном это относится к MyISAM.
 
В исходниках MySQL, в подкаталоге support-files, лежат примеры конфигов для разных моделей памяти: my-huge.cnf.sh, my-large.cnf.sh, my-small.cnf.sh. Фрагменты этих конфигов:
 huge:
 key_buffer = 384M   
 innodb_buffer_pool_size = 384M
 table_cache = 512
 
 large:
 key_buffer = 256M
 innodb_buffer_pool_size = 256M
 table_cache = 256
 
 small:
 key_buffer = 16K
 innodb_buffer_pool_size = 16M
 table_cache = 4
 

3. Настройка дисковой системы

Эти настройки могут существенно повлиять на производительность. Тут сразу возникает дилемма между скоростью записи и надежностью. MyISAM: этот движок по умолчанию сбрасывает закешированные индексы на диск каждый раз, когда происходит операция записи. Если в таблице идет много изменений, имеет смысл сгруппировать их. Это можно сделать с помощью блокировки таблиц (LOCK TABLES), которая временно останавливает запись до разблокировки. Временную блокировку записи можно реализовать также с помощью параметра delay_key_write.

Для базы с небольшими таблицами рекомендуется установить параметр myisam_recover_options – для поиска и восстановления ошибок в случае аварии. Проверка параметра делается из командной строки:

mysql> SHOW VARIABLES LIKE 'myisam_recover_options';
 +------------------------+-------+
 | Variable_name          | Value |
 +------------------------+-------+
 | myisam_recover_options | OFF   | 
 +------------------------+-------+
 

InnoDB: этот движок имеет более эффективную возможность в случае аварий автоматически запускать процесс восстановления при следующем запуске. У него иной механизм работы: вместо постоянного сброса на диск он ведет специальный транзакционный лог. Этот лог имеет фиксированный размер, и при достижении конца изменения сбрасываются на диск – это делает специальный фоновый поток-диспетчер. Размер этого лога контролируется параметром innodb_log_file_size, по умолчанию он равен 5 MБ. Предел равен 4 ГБ, оптимальное значение для экстремальной производительности – 256 MБ. Сброс изменений в лог происходит с помощью буфера, размер которого устанавливается параметром innodb_log_buffer_size, его диапазон желательно варьировать в пределах от 1 до 8 MБ. Сброс буфера в лог и сброс самого лога на диск – это две разных операции. Сброс лога на диск может сопровождаться тем, что операционная система сама может закешировать эту запись, что крайне нежелательно; для предотвращения этого необходимо устанавливать параметр innodb_flush_log_at_trx_commit = 1. В противном случае нужно использовать RAID-контроллер.

InnoDB хранит свое табличное пространство с помощью виртуальной файловой системы, например:

  innodb_data_home_dir = /var/lib/mysql/
   innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G
 

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

В InnoDB есть так называемый специальный буфер повторной записи – doublewrite buffer, который используется при ошибках записи на диск. Это специальная область в табличном пространстве, представляющая собой копию последних записанных страниц. Иными словами, когда происходит запись данных на диск, они сначала пишутся в этот буфер, а потом уже повторно в основные таблицы. Смысл в том, что в случае ошибки правильные данные в любом случае окажутся либо там, либо там – это проверяется по контрольным суммам. Выключить повторный буфер можно с помощью параметра innodb_doublewrite = 0.

Данные полей, имеющих тип BLOB или TEXT, пишутся сразу на диск без буферизации во временные таблицы, каталог для них настраивается параметром tmpdir. Если есть возможность, эти поля можно сконвертировать в тип VARCHAR. Другой вариант – использовать для хранения BLOB файловую систему tmpfs, в этом случае уже сама операционная система будет буферизовать дисковые операции.

4. Настройка параллелизма (concurrency)

MyISAM: этот движок при удалении записи ее фактически не удаляет, а помечает как удаленную, не создавая дыр, а потом вставляет на ее место новую. Даже если таблица заблокирована, движок может остановить ее чтение и добавить новую запись в конец таблицы. Для вставки новых записей в конфиге есть параметр concurrent_insert. Если он равен 0, при вставке таблица будет блокироваться. Если он равен 1, запись будет добавляться в ''дыру'' таблицы, блокировки не будет. Если он равен 2, запись будет добавляться в конец таблицы без блокировки. С помощью параметра low_priority_updates можно понизить приоритет для INSERT, REPLACE, DELETE, UPDATE по отношению к операции SELECT.

InnoDB: этот движок имеет так называемый планировщик потоков, который с помощью параметра innodb_thread_concurrency может ограничить количество работающих потоков. По умолчанию он равен 0, т.е. нет ограничений. Движок имеет двухфазный механизм распараллеливания потоков, поток может быть переведен в ''спячку'' на innodb_thread_sleep_delay микросекунд (по умолчанию он равен 10000 микросекунд), при этом снижается нагрузка на общесистемный планировщик. Каждый работающий поток имеет определенное число так называемых тикетов, определяющих, сколько ему можно работать. Тикеты настраиваются опцией innodb_concurrency_tickets. Параметр innodb_commit_concurrency определяет число потоков, которые могут коммитить одновременно.

5. SHOW GLOBAL STATUS

Для выяснения узких мест полезно выполнять команду SHOW GLOBAL STATUS:

mysql>  SHOW GLOBAL STATUS;
 +-----------------------------------+----------+
 | Variable_name                     | Value    |
 +-----------------------------------+----------+
 | Aborted_clients                   | 0        | 
 | Aborted_connects                  | 2        | 
 | Binlog_cache_disk_use             | 0        | 
 | Binlog_cache_use                  | 0        | 
 | Bytes_received                    | 658      | 
 | Bytes_sent                        | 12538    | 
 ........
 
 

Aborted_clients – зависит от сетевых настроек, в частности, от параметра max_allowed_packet.

Binlog_cache_disk_use и Binlog_cache_use – если их значение велико, нужно увеличить параметр binlog_cache_size.

Bytes_received и Bytes_sent – проблемы могут быть во входящем или исходящем трафике.

Connections – общее число всех коннектов.

Created_tmp_tables – большое значение этого параметра говорит о том, что необходимо оптимизировать запросы.

Key_blocks_used – если он намного меньше, чем параметр конфига key_buffer_size, значит, последний нужно уменьшать.

Open_tables и Opened_tables – их большое значение указывает на то, что нужно увеличить параметр конфига table_cache.

Table_locks_waited – высокое значение этого параметра указывает на проблемы с параллелизмом.

Варианты решения: использование InnoDB, партицирование таблиц, оптимизация запросов.

Заключение

Подводя итог, можно сказать, что MySQL имеет механизм первоначальной базовой настройки на базе конфигурационных файлов. Знание особенностей конфигурации позволяет оптимизировать узкие и далеко не очевидные места. Кеширование запросов является ключом к оптимизации использования памяти. Для эффективной работы дисковой системы необходима правильная буферизация записываемых данных. Для настройки параллелизма нужно найти правильный приоритет между селектами и апдейтами. Все это можно протестировать с помощью команды SHOW GLOBAL STATUS. Конфигурация MySQL – это творческий процесс, от которого зависит дальнейшая жизнь самого сервера.

Рассматривались особенности настройки MySQL версий 5.0 и 5.1.

Часть 3: Конфигурация PostgreSQL

Конфигурация, идущая по умолчанию, в большинстве случаев не рассчитана на максимальное использование ресурсов – это справедливо как для PostgreSQL, так и для MySQL. Пути по умолчанию для каталогов можно изменить непосредственно при сборке из исходников. Некоторые параметры постгресу можно передавать из командной строки. Так же можно определить переменную окружения PGOPTIONS, а значения некоторых переменных можно изменить прямо в процессе выполнения запросов.

1. Как конфигурировать PostgreSQL

Сконфигурировать постгрес можно либо из командной строки, либо с помощью конфиг-файла. Из командной строки можно запустить команду postgres:

  postgres -c log_connections=yes -c log_destination='syslog'
 

Можно изменить переменную окружения:

  env PGOPTIONS='-c geqo=off' psql
 

Базовый конфигурационный файл для постгрес называется postgresql.conf, его настройка влияет на все базы. В нем находятся 10 основных разделов.

1-й раздел. FILE LOCATIONS – определяются каталоги, в которых лежат данные и конфиги.

  1. data_directory = 'ConfigDir' – каталог для хранения данных. Для изменения расположения еще двух конфигов – pg_hba.conf и pg_ident.conf – можно использовать команду postgres.

2-й раздел. CONNECTIONS AND AUTHENTICATION – задаются настройки для числа коннектов, SSL, TCP.

3-й раздел. RESOURCE USAGE – настройки ресурсов.

  1. shared_buffers – общий буфер в мегабайтах, откуда берутся считанные с диска данные, куда пишутся изменения, которые потом будут сброшены на диск. Минимум – 128 КБ, максимум = max_connections*16 КБ. По умолчанию стоит 32 MБ. Чрезмерное увеличение этого параметра приведет к тому, что ядро перестанет отдавать память.
  2. temp_buffers = 8 MБ (min 800 КБ) – буфер для каждой сессии.
  3. max_prepared_transactions = 5 – максимальное число одновременных транзакций. Его можно сделать равным max_connections. Если вы не используете транзакции, его можно обнулить.
  4. work_mem = 1 MБ (min 64 КБ) – количество памяти для встроенной сортировки и хэширования таблиц (ORDER BY, DISTINCT, JOIN). Каждая сессия, каждая сортировка съедает эту порцию памяти. Предел равен свободной памяти, разделенной на максимальное число одновременных коннектов.
  5. maintenance_work_mem = 16 MБ (min 1 MБ) – максимум памяти, используемый для таких операций, как VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Увеличение параметра может улучшить работу команды VACUUM, а также ускорить восстановление дампа. Этот параметр может быть сопоставим с размером самого большого индекса.
  6. max_files_per_process = 1000 (min 25) – максимальное число открытых файлов для одной сессии.
  7. vacuum_cost_delay = 0 (0–1000 milliseconds) – влияет на частоту выполнения команды VACUUM. Если у вас большие таблицы и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягивая частоту ее выполнения по времени.
  8. bgwriter_delay = 200 ms (10–10000 ms) – в постгресе есть фоновый процесс, который периодически сбрасывает буфер на диск. Этот параметр определяет интервал, на который засыпает этот процесс.
  9. max_fsm_relations = 1000 (min 100) – максимально возможное количество таблиц, для которых будет отслеживаться свободное место, должно быть не меньше общего количества таблиц во всех базах.

4-й раздел. WRITE AHEAD LOG – внутренние настройки синхронизации, коммитов, чекпойнтов, архивация.

  1. checkpoint_segments = 3 – указывает количество транзакций, после которых происходит физический сброс данных на диск, при интенсивной записи параметр можно увеличить.
  2. checkpoint_timeout = 5min – интервал, через который происходит сброс транзакций на диск.
  3. fsync = on – этот параметр можно выключить (off), это разгрузит I/O, при этом транзакции перестанут периодически сбрасываться на диск, что, конечно, рискованно.
  4. synchronous_commit = on – включение/отключение асинхронного механизма транзакций. Рекомендуется отключать этот параметр вместо fsync(), потому что это надежнее. Аналогичный эффект дает команда SET LOCAL synchronous_commit TO OFF внутри самой транзакции, что еще более гибко.
  5. full_page_writes = on – перед тем как записать страницу данных на диск, строится образ записываемой страницы на диске, потом страница сбрасывается на диск. В случае краха потерянная страница будет восстановлена с образа. Это увеличивает нагрузку на I/O, но гарантирует надежность.
  6. wal_buffers = 64 КБ (min 32 КБ) – буфер журнала одной транзакции, по умолчанию 64 КБ.
  7. commit_delay, commit_siblings – параметры временной задержки между попаданием транзакции в буфер и сбросом ее на диск.
  8. archive_mode = off – архивация логов
  9. archive_command = '' – задать внешнюю команду для логирования, например:
        archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
     
     

5-й раздел. QUERY TUNING – конфигурация планировщика запросов.

  1. enable_hashjoin = on ; enable_indexscan = on ;enable_mergejoin = on – включает план запроса для соответствующих типов.
  2. effective_cache_size = 128 MБ – параметр используется для построения плана запроса. Чем больше будет этот параметр, тем меньше вероятность, что оптимизатор при построении плана обратится к диску. Обычно отводят до 50% доступной памяти.
  3. default_statistics_target = 10 – задает объем статистики, собираемой командой ANALYZE. Увеличение параметра может повысить качество планировщика, увеличивая при этом время.

6-й раздел. ERROR REPORTING AND LOGGING – ротация постгрессовских логов, детализация логов.

7-й раздел. RUNTIME STATISTICS – настройка статистики по запросам, индексам.

8-й раздел. AUTOVACUUM PARAMETERS – в постгресе ''мусор'' из таблиц (например, удаленные записи) убирается с помощью команды VACUUM в параллельном процессе.

  1. autovacuum = on – включение.
  2. autovacuum_max_workers = 3 – число процессов.
  3. autovacuum_naptime = 1min – интервал между командами vacuum.

9-й раздел. CLIENT CONNECTION DEFAULTS – настройки по умолчанию для клиентов.

  1. search_path = '"$user",public' – задаёт порядок, в котором производится поиск по схемам, если на объект (таблица, тип данных, функция и т.д.) ссылаются только по имени, без указания компонентов схемы. Таким образом поддерживается разделяемое использование базы данных (когда у пользователей нет индивидуальных схем, все используют разделяемые общие), индивидуальные схемы для пользователей и их сочетания.
  2. default_transaction_isolation = 'read committed' – уровень изоляции по умолчанию. Возможны также 'read uncommitted', 'repeatable read', 'serializable'.
  3. datestyle = 'iso, dmy' – формат вывода даты и времени. Возможны варианты: ISO, Postgres, SQL, German. Для времени – DMY, MDY, YMD.
  4. max_connections = 100 – число одновременных коннектов, слишком большое увеличение может привести к проблемам.

10-й раздел. LOCK MANAGEMENT – настройка блокировок.

  1. deadlock_timeout = 1 s – продолжительность времени в секундах между проверками на дедлок. Для загруженных серверов имеет смысл это значение увеличить.
  2. max_locks_per_transaction = 64 (min 10) – максимум для числа одновременных блокировок.

2. Настройка аутентификации2

По умолчанию PostgreSQL настроен так, что каждый локальный пользователь может подсоединиться к базе, совпадающей по названию с регистрационным именем клиента при условии, что такая база данных уже создана. Для настройки доступа к серверу существует конфиг-файл pg_hba.conf . Он состоит из записей, каждая из которых регулирует правила получения доступа для конкретной машины или для целой группы IP. Следующая запись разрешает доступ всем пользователям к базам, совпадающим по названию:

  local all all ident sameuser
 

Следующая запись разрешает пользователю serg с определенного ip-шника работать с базой test:

  host    test   serg     123.321.123.321 trust
 

Здесь:

host – возможно сетевое соединение, local – только локальное, trust – полностью доверяем, ident – доступ по записи, md5 – авторизация по зашифрованному паролю.

3. Настройка дисковой системы

Дисковая подсистема является для PostgreSQL определяющей. Поэтому, если есть возможность, то лучше применить именно её.

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

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

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

4. Настройка параллелизма (concurrency)

Ситуация concurrency возникает в том случае, когда две и более сессии пытаются получить доступ к одним и тем же данным. Ключом к управлению этой ситуацией в постгресе является контроль транзакций – Multiversion Concurrency Control (MVCC). Это делается на основе мгновенных снимков состояния базы,так называемых snapshot. Каждая транзакция изолирована внутри своей сессии. При этом достигается разрешение конфликтов блокировок между читающими и пишущими запросами. Хотя блокировки на уровне таблиц и строк никто не отменял, тем не менее использование MVCC дает лучшую производительность.

В стандартном SQL есть четыре уровня изоляции транзакций, которые по-разному обеспечивают следующее поведение.

  1. Транзакция не должна видеть незакомиченных данных от другой транзакции (dirty read).
  2. Одни и те же данные, прочитанные 2 раза подряд внутри одной транзакции, не должны отличаться (nonrepeatable read).
  3. Один и тот же запрос внутри транзакции должен возвращать один и тот же результат (phantom read).

В постгресе фактически реализованы два уровня изоляции – Read Committed и Serializable, остальные два уровня похожи на них. Read Committed – уровень изоляции по умолчанию в постгресе. Это настраивается с помощью конфигурационного параметра default_transaction_isolation = 'read committed'.

5. Мониторинг

В постгресе есть несколько системных таблиц, которые дают полезную информацию о состоянии базы данных: pg_stat_activity, pg_stat_database, pg_stat_user_tables, pg_stat_user_indexes, pg_locks:

select datname,now()-query_start as duration,current_query from
   pg_stat_activity;
 select datname, case when blks_read = 0 then 0 else blks_hit /
   blks_read end as ratio from pg_stat_database;
 select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100
   else seq_scan / idx_scan end as ratio from pg_stat_user_tables
   order by ratio desc;
 select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
   order by n_tup_upd desc;
 select indexrelname,idx_tup_read,idx_tup_fetch,case when
   idx_tup_fetch = 0 then 100 else idx_tup_read / idx_tup_fetch end as
   ratio from pg_stat_user_indexes order by ratio desc;
 select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
   order by n_tup_upd desc;
 select l.mode,d.datname,c.relname,l.granted,l.transactionid from
   pg_locks as l left join pg_database as d on l.database= d.oid left
   join pg_class as c on l.relation = c.oid;
 

Заключение

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

Cтатья ссылается на версию PostgreSQL 8.3.

Часть 4: Масштабирование MySQL

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

1. Основные понятия

Для начала нужно определиться с терминами:

  1. Производительность (performance) – способность приложения отвечать таким требованиям, как время реакции, пропускная способность.
  2. Пропускная способность (capacity) – нагрузка, число пользовательских сессий, с которыми может справиться приложение.
  3. Масштабируемость (scalability) – способность, потенциал приложения поддерживать еще большую производительность, способность повысить пропускную способность без снижения производительности.
  4. Доступность (availability) – временной процент, когда приложение способно отвечать на запросы. Например, когда говорят, что приложение доступно на 99.999% в году, это значит, что за весь год оно стоит всего пять минут.
  5. Отказоустойчивость (fault tolerance) – способность приложения управлять падениями. Рано или поздно любое приложение падает. Когда это происходит, приложение должно продолжать вести себя так, как будто ничего не происходит.

2. Масштабирование MySQL

Когда вы покупаете более мощный сервер с более дорогим железом, вы масштабируете вверх – scaling up. Другой вариант масштабирования – по горизонтали – scaling out – когда база разносится по нескольким серверам. Третий вариант – данные могут быть актуальными и не очень, часть их может быть заархивирована – scaling back. Четвертый вариант – доступ к удаленным данным – federation (в MySQL такой вариант ограничен).

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

Если ваше приложение масштабируемое, вы просто добавляете новый сервер, и производительность автоматически улучшается. Если нет, вы пытаетесь оптимизировать железо либо sql-запросы. На улучшение производительности напрямую влияет корректная индексация таблиц или использование другого движка (storage engine). Но у любой sql-оптимизации рано или поздно наступает предел. Тогда вы начинаете делать апгрейд железа.

3. Вертикальное масштабирование (scaling up)

Этот вариант работает до определенного уровня. Во-первых – деньги. Scaling up – это, сразу скажем, бездарное финансовое решение. Есть определенный слой высокопроизводительного железа, которое к тому же проприетарное и узкоспециализированное, со своими ограничениями. MySQL архитектурно устроена таким образом, что в ней нет безграничных возможностей для вертикального масштабирования, в этом случае многоядерность хороша до определенного момента. Пределом являются 8 CPU и 14 дисков – превышение этих параметров уже не даст эффекта. Способность мастер-сервера (master server) к эффективному использованию многоядерности не переносится автоматически на слэйв-сервер (slave server), у последнего ограниченные возможности в этом плане.

В общем, масштабирование вверх – это не лучшее решение.

4. Горизонтальное масштабирование (scaling out)

Есть несколько вариантов для горизонтального масштабирования.

  1. При масштабировании по горизонтали вы "размазываете" данные по нескольким серверам с помощью репликации, и далее используете slave-серверы на чтение. При этом происходит разделение (partition) данных по нескольким нодам. Нода – функциональный блок в MySQL, это может быть отдельный сервер. Ноды бывают четырех типов:
    • активный master-сервер и пассивный репликационный slave-сервер;
    • master-сервер и несколько slave-серверов;
    • активный сервер со специальным механизмом репликации – distributed replicated block device (DRBD);
    • SAN-кластер.

    В большинстве случаев все серверы в ноде имеют одинаковые данные.

  2. Функциональное разделение: данные разбиваются на таблицы так, что они никогда не джойнятся между собой, например в случае портала – это данные для форума, новостей и поддержки, которые разносятся по разным серверам.
  3. Data sharding – механическое разделение огромных объемов однотипных данных на несколько частей (shard). Рассмотрим пример: есть блог, состоящий из трех типов обьектов – пользователи, посты, комментарии. На первом этапе мы выполняем функциональное масштабирование: разносим обьекты по трем разным нодам. При существенном увеличении объема данных внутри каждой из этих трех нод мы выполняем data sharding и разносим каждую из трех нод на отдельные подноды, используя в качестве критерия ID-пользователя. С точки зрения реализации это наиболее трудоемкий вариант. Но для неограниченного повышения пропускной способности (capacity) это наиболее подходящий вариант.

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

В качестве примера с дублированием можно привести следующую модель. Имеется сайт для обсуждения книг. Нам понадобятся две основных выборки: все комментарии для данной книги и все комментарии для данного пользователя. Функционально мы разносим пользователей и книги отдельно. Комментарии мы не будем выносить в третий функциональный блок, а поместим его в блок пользователя, при этом в комментариях имеются user ID и post ID. И далее дублируем ID-шники комментариев во второй – книжный – функциональный блок. Далее делаем data sharing внутри каждой ноды.

После того как данные разбиты на порции (shard), возникнет следующая проблема: реализовать запрос, для которого потребуются данные из разных порций (shard). Например, в случае с комментарием для книг может понадобиться выборка по наиболее активным пользователям – так называемые cross-shard запросы. В данном случае вступает в силу распараллеливание запросов на уровне приложения. Можно построить специальное отдельное хранилище, которое будет хранить готовые сложные выборки и периодически обновлять их в фоновом режиме. Все опять упирается в трудозатраты.

Чем хорош шардинг с точки зрения производительности? Представьте себе таблицу на 100 ГБ, которую вы разбиваете на 100 частей по одному гигабайту и разносите на 100 нод. В один прекрасный момент возникнет необходимость в создании индекса. Для одногигабайтовой ноды такой индекс может уместиться в память и работать очень быстро. Или вам понадобилось выполнить команду ALTER TABLE: для стогигабайтовой таблицы она будет вести себя совершенно иначе, чем для одногигабайтовой.

Шардинг хорош для перемещения данных с одной ноды на другую: нода устанавливается в режим read-only, и данные перемещаются – обычно это делается с помощью mysqldump. При этом происходит частичная блокировка нод. Здесь речь идет о полном переносе всего шарда, а не частичном переносе данных из одного шарда в другой, частичный перенос вообще не рекомендуется.

С точки зрения пространства имен шардинг можно делать по-разному:

  1. каждый шард – это отдельная база данных, т.е. доступ к одной таблице, раскиданной по нодам, будет таким: shard_database_1.my_table, shard_database_2.my_table;
  2. таблица разносится по разным нодам внутри одной базы данных: database_name.my_table_1, database_name.my_table_2;
  3. комбинированный метод: shard_database_1.my_table_1,shard_database_1.my_table_2.

На уровне приложения динамическое формирование селектов может выглядеть так:

  $sql = "SELECT book_id, book_title FROM shard_database_%d.my_table_%d... ";
   $res = mysql_query(sprintf($sql, $shardno, $tableno), $conn);
 

5. Фиксированный и динамический sharding

Есть два основных подхода, по которым происходит разделение данных между нодами – фиксированный и динамический. Первый вариант зависит только от партиционного ключа. В качестве примера можно привести диапазон ID-шников от 1 до 1000000, разбитый на 10 частей, при этом ID=543210 попадает в шестую ноду. У фиксированного подхода есть несколько недостатков:

  1. при возрастании объема данных усложняется балансировка нагрузки между нодами;
  2. фиксированный диапазон может оказаться мал, его придется увеличивать, что приведет к перемещению данных внутри нод.

Альтернативой для первого подхода является динамическое разделение данных. Пример: создается таблица с двумя полями – ID пользователя и ID ноды:

CREATE TABLE user_to_shard (
    user_id INT NOT NULL,
    shard_id INT NOT NULL,
    PRIMARY KEY (user_id)
 );
 

По ID-шнику пользователя всегда можно найти его ноду, в противном случае добавляется новая нода. Позже ноду можно изменить и перенести данные. Правда, за все нужно расплачиваться: цена этому – дополнительный ресурс в виде directory server – специальной сканирующей ноды. Конечно, такую таблицу можно закешировать. Динамический подход дает возможность гибко управлять данными, перераспределять их между нодами. Логику управления данными можно будет разбивать на несколько слоев. Динамический подход хорош там, где диапазон сканирования не очень велик. На практике бывает так, что приходится смешивать фиксированный и динамический подходы.

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

http://shards.hibernate.org – реализация расширения на java для open-source библиотеки Hibernate object-relational mapping (ORM), используется в Google. Этот интерфейс позволяет прозрачно для пользователя хранить и получать данные.

http://www.hivedb.org – open-source фреймворк HiveDB с использованием MySQL. Написан на java и разработан для создания, использования и управления распределенных хранилищ данных.

6. Кластеризация

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

NDB Cluster – движок на основе синхронных репликаций и автоматического разделения данных по нодам. Он ведет себя адекватно для небольших наборов данных и простых запросов.

Заключение

Пора подвести итог: за кадром остались другие аспекты – Load Balancing, failover/failback и т.д. Масштабирование в MySQL – это нетривиальный процесс, в котором нет решений на все случаи жизни. Решения, лежащие на поверхности, не всегда приводят к максимальному результату – это касается вертикального масштабирования. Горизонтальное масштабирование предъявляет высокие требования к знанию не только архитектуры MySQL, но и архитектуры на уровне приложений, что приводит к большим трудозатратам. Готовое кластерное решение – NDB Cluster – на данный момент находится на стадии эволюции. Если вы решитесь заняться масштабированием MySQL, вы должны отдавать себе отчет в том, что готовых решений на все случаи жизни нет.

Часть 5: Масштабирование PostgreSQL

В PostgreSQL бывает несколько вариантов репликации.

  1. Асинхронный Master/Slave. Master – это сервер, на который идут модифицирующие запросы. Slave – сервер, который дублирует его. Носит асинхронный характер: если падает сетевое соединение между серверами, то после восстановления Slave сервер автоматически возобновляет репликацию.
  2. Синхронный Master/Slave: модификации Master синхронно дублируются на Slave.
  3. Синхронный Multi-Master: имеется несколько серверов, которые одновременно принимают на себя модифицирующие запросы insert, delete, update. До того момента, пока все серверы не подтвердили получение изменений, изменения не коммитятся: все серверы ждут.

Для масштабирования PostgreSQL существует несколько готовых решений. При этом можно получить различные преимущества, такие как:

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

Сегодня мы рассмотрим наиболее популярные готовые решения, которые можно разделить на две группы – системы репликации и пулы соединений.

  1. Slony-I – асинхронная (master to multiple slaves) репликация: http://slony.info/.
  2. Pgpool-II – синхронный мульти-мастер репликации: http://pgfoundry.org/projects/pgpool/.
  3. Pgcluster – синхронный мульти-мастер репликации: http://pgfoundry.org/projects/pgcluster/.
  4. PL/Proxy – прокси от компании Skype: http://pgfoundry.org/projects/plproxy/.
  5. PgBouncer – менеджер соединений для PostgreSQL: https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer.

1. Slony-I

Slony – система репликации реального времени, позволяющая организовать синхронизацию нескольких серверов PostgreSQL по сети. Slony использует триггеры PostgreSQL для привязки к событиям INSERT/DELETE/UPDATE и хранимые процедуры для выполнения действий. Наши соотечественники вдоволь порезвились с аббревиатурами названий базовых утилит пакета.

Основные понятия системы:

  • кластер – набор баз данных;
  • нода – база данных, которая может быть распределена;
  • репликационный набор – набор таблиц, которые могут быть реплицированы между нодами кластера;
  • источник (или провайдер) и подписчик – у каждой репликации есть одна нода-источник, остальные ноды могут быть подписчиками;
  • slon – демон, присутствующий на каждой ноде и управляющий ее репликациями;
  • slonik – командная утилита для общего управления нодами.

Система Slony с точки зрения администратора состоит из двух главных компонентов – репликационного демона slon и административной консоли slonik. Slonik – это основная командная утилита для управления, slon следит в фоновом режиме за репликацией.

Сначала создается пользователь (slony). На каждой из нод кластера этот пользователь также должен быть создан, там же должен присутствовать демон slon. От лица этого пользователя с любой из нод (slave server) нужно будет подключаться к головному серверу – master server – с помощью команды:

  psql -d имя_базы_данных -h имя_мастер_хоста.com -U slony
 

На мастер-сервере нужно создать базу данных:

  createdb -O имя_пользователя -h имя_слэйв_хоста.com имя_базы
 

Для таблиц, которые будут реплицироваться, обязательно должен присутствовать primary key. После этого на слэйв-сервере запускаем команду:

   pg_dump -s имя_базы | psql -U slony -h имя_слэйв_хоста.com имя_базы
 

pg_dump -s сделает дамп структуры БД.

После этого на мастер-сервере нужно запустить скрипт, в нем будет запущена утилита slonik, которая создаст репликационный набор с перечисленными в скрипте таблицами. Для каждой реплицируемой таблицы утилита выполнит команду:

  store listen ( origin = 1, provider = 1, receiver = 2 );
 

Далее нужно подписать слэйв-сервер, выполнив на нем аналогичный скрипт, в котором будет запущена утилита slonik, которая выполнит команду:

  subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
 

После создания подписки нужно запустить демона slon на мастере и на слэйве:

  slon customers_rep "dbname=имя_базы user=slony"
 

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

Чего нет в slony: система не занимается выявлением упавших нод.

2. Pgpool-II

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

  1. Создает высокопроизводительную сетевую структуру между нодами, кластерами и пользователями.
  2. Синхронно реплицирует данные на множество серверов без остановки.
  3. Балансирует нагрузку, распределяя ее между нодами кластера.
  4. Распараллеливает запросы за счет разнесения данных по нодам.
  5. Failover – обнаружение отказа и переключение нагрузки.

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

  load_balance_mode = true
 

Для включения параллельных запросов:

  parallel_mode = true
 
 

Клиентские запросы будут обслуживаться одной машиной – мастер-хостом на условном порту 12345, на котором мы настраиваем конфиг. Для запуска выполняем на мастер-сервере команду:

   $ pgpool -n &
 

Создаем на мастере базу данных. Для клонирования базы данных на прописанных хостах нужно в запущенной утилите pgpool выполнить команду:

  $ createdb -p 12345 имя_реплицируемой_базы_данных
   $ pgbench -i -p 9999 имя_реплицируемой_базы_данных
 

Для включения параллельных запросов нужна системная база с именем pgpool и пользователь с тем же именем. Параметры также прописываются в таблице. Создание pgpool:

   $ createuser -p 5432 pgpool
    $ createdb -p 5432 -O pgpool pgpool
 

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

3. Pgcluster

Для его установки необходимо поставить патч на postgresql. Схема работы сложнее, чем в pgpool. Pgcluster состоит из трех типов серверов:

  • балансер-сервер (front-end);
  • кластерный сервер(данные);
  • репликационный сервер.

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

4. PL/Proxy

PL/Proxy – это прокси-язык, используемый для вызова удаленных процедур и разделения данных между нодами. Проект представляет собой компилируемую си-шную библиотеку libplproxy.so. После её инсталляции необходимо запустить sql-скрипт из этого же пакета:

  >> psql -f plproxy.sql mydatabase
 

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

CREATE FUNCTION get_user_email(i_username text)
 RETURNS SETOF text AS $$
   CONNECT 'dbname=remote_dbname';
   SELECT email FROM users WHERE username = $1;
 $$ LANGUAGE plproxy;
 

и потом вызвать ее:
  SELECT * from get_user_email($1);.
 

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

CREATE OR REPLACE FUNCTION get_user_email(i_username text)
 RETURNS SETOF text AS $$
   CLUSTER 'usercluster';
   RUN ON hashtext(i_username) ;
   SELECT email FROM users WHERE username = i_username;
 $$ LANGUAGE plproxy;
 

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

CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
 RETURNS integer AS $$
      INSERT INTO users (username, email) VALUES ($1,$2);
      SELECT 1;
 $$ LANGUAGE SQL;
 
 Для прокси-базы:
 CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
 RETURNS integer AS $$
   CLUSTER 'usercluster';
   RUN ON hashtext(i_username);
 $$ LANGUAGE plproxy;
 
 

5. PgBouncer

Это пул коннектов для постгрес от компании Skype. Существуют три режима управления.

  1. Session Pooling. Клиенту выделяется соединение с сервером; оно приписано ему в течение всей сессии и возвращается в пул только после отсоединения клиента.
  2. Transaction Pooling. Клиент владеет соединением только в течение транзакции.
  3. Statement Pooling. Соединение возвращается назад в пул сразу после завершения запроса.

К достоинствам PgBouncer относится:

  • малое потребление памяти (менее 2 КБ на соединение);
  • отсутствие привязки к одному серверу баз данных;
  • реконфигурация настроек без рестарта.

Базовая утилита запускается так:

  pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
 

Простой пример для конфига:

[databases]
 template1 = host=127.0.0.1 port=5432 dbname=template1
 [pgbouncer]
 listen_port = 6543
 listen_addr = 127.0.0.1
 auth_type = md5
 auth_file = users.txt
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = someuser
 
 

Нужно создать файл пользователей users.txt примерного содержания:

  "someuser" "same_password_as_in_server"
 

Админский доступ из консоли к базе данных pgbouncer:

  $ psql -h 127.0.0.1 -p 6543 pgbouncer
 

Здесь можно получить различную статистическую информацию с помощью команды SHOW.

Заключение

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

Часть 6: Репликация MySQL

MySQL имеет встроенную репликацию, которая может послужить основой для нагруженных распределенных приложений.

Основная проблема, решаемая при репликации – синхронизация данных между серверами. Возможны различные топологии: один мастер (master) – много слэйвов (slave), много мастеров и т.д. Реплицировать можно весь сервер целиком, одну базу, одну таблицу. MySQL поддерживает на данный момент один тип репликаций: логическую репликацию (statement) . Построчная репликация (row-based) появится начиная с версии 5.1. Обе репликации имеют асинхронный характер, т.е. нет никаких гарантий, что процесс может длиться строго фиксированный промежуток времени. Репликация не работает вниз, т.е. база версии 5.0 не может быть реплицирована на версию 4.0. Основным условием репликации является включение бинарного логирования на мастере.

Что достигается с помощью репликации?

  1. Распределение данных: данные можно копировать по разным дата-центрам.
  2. Распределение нагрузки: запросы можно разносить между разными серверами с помощью простого базового round-robin DNS или используя Linux Virtual Server (LVS).
  3. Бэкапы: репликация упрощает архивацию.
  4. Устойчивость: наличие слэйвов позволяет уменьшить риск отказов системы.

1. Как работает репликация MySQL

Процесс репликации состоит из трех основных фаз:

  1. происходит добавление записи в бинарный лог на мастере;
  2. добавленные записи копируются из лога мастера слэйв-сервером в свой лог;
  3. слэйв реплицирует свой лог в свою базу данных.

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

2. Как конфигурировать master/slave

Для настройки репликации необходимо:

  1. на каждом сервере настроить репликационный аккаунт;
  2. сконфигурировать мастер и слэйв;
  3. настроить на слэйве коннект и репликацию.

На мастере и на слэйве нужно выполнить команду:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
     -> TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';
 

На мастере нужно сделать изменения в конфиге my.cnf:

  log_bin   = mysql-bin
   server_id = 10
 

Перезапускаем мастера и выполняем команду SHOW MASTER STATUS:

mysql> SHOW MASTER STATUS;
 +------------------+----------+
 | File             | Position |
 +------------------+----------+
 | mysql-bin.000001 |       98 |
 ...
 

На слэйве делаем аналогичные настройки в конфиге:

log_bin           = mysql-bin
 server_id         = 2
 relay_log         = mysql-relay-bin
 log_slave_updates = 1
 read_only         = 1
 

Здесь relay_log – промежуточный репликационный лог. log_slave_updates включает обмен данными между промежуточным и основным логами.

3. Запуск slave

Для запуска процесса репликации на слэйве нужно запустить команду:

mysql> CHANGE MASTER TO MASTER_HOST='server1',
     -> MASTER_USER='repl',
     -> MASTER_PASSWORD='p4ssword',
     -> MASTER_LOG_FILE='mysql-bin.000001',
     -> MASTER_LOG_POS=0;
 

После этого нужно сделать проверку:

mysql> SHOW SLAVE STATUS\G
 ********************* 1. row *******************
        Slave_IO_State:
           Master_Host: server1
           Master_User: repl
           Master_Port: 3306
         Connect_Retry: 60
       Master_Log_File: mysql-bin.000001
   Read_Master_Log_Pos: 4
        Relay_Log_File: mysql-relay-bin.000001
         Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 
 

Теперь запускаем репликацию:

mysql> START SLAVE;
 

Проверка:

mysql> SHOW SLAVE STATUS\G
 *************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: server1
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 164
              Relay_Log_File: mysql-relay-bin.000001
               Relay_Log_Pos: 164
       Relay_Master_Log_File: mysql-bin.000001
       Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
 

Запущен реплицирующий тред, который ждет сообщений от мастера. Если на мастере произойдут изменения, мы это увидим, запустив повторно команду SHOW SLAVE STATUS.

На мастере и на слэйве можно запустить команду, которая покажет информацию о тредах:

mysql> SHOW PROCESSLIST\G
 
 

Для мастера рекомендуется в конфиге поставить параметр:

  sync_binlog=1
 

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

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

innodb_flush_logs_at_trx_commit=1 # синхронизация всех коммитов на диск
 innodb_support_xa=1               # начиная с версии MySQL 5.0
 innodb_safe_binlog                # только для версии MySQL 4.1
 

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

skip_slave_start
 read_only
 

4. Различия в репликации

MySQL, включая версию 5.0, поддерживает только логическую (statement-based) репликацию. Когда слэйв реплицирует данные, фактически он выполняет тот же самый SQL-запрос, который выполнял мастер. У этого метода есть свои преимущества – он прост в реализации, размер лога при этом компактен. Но есть и недостатки: время выполнения запросов на мастере и на слэйве может сильно различаться. Некоторые выражения не реплицируются корректно, например функция CURRENT_USER(). Есть также проблемы с триггерами и хранимыми процедурами.

В версии 5.1 появится поддержка построчной (row-based) репликации. Преимущество этого варианта в том, что каждое выражение может быть реплицировано корректно и эффективно. При этом может возрасти размер лога, который уже нельзя будет инспектировать с помощью утилиты mysqlbinlog.

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

mysql> INSERT INTO global_table(col1, col2, sum_col3)
     -> SELECT col1, col2, sum(col3)
     -> FROM my_table
     -> GROUP BY col1, col2;
 

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

Зато, с другой стороны, если взять выражение:

  mysql> UPDATE enormous_table SET col1 = 0;
 

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

В версии 5.1 будет динамическое переключение между логической и построчной репликациями. По умолчанию будет применяться логическая репликация, это можно настраивать с помощью параметра binlog_format.

5. Топологии репликации

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

Существует несколько топологических вариантов.

  1. Мастер и несколько слэйвов.
  2. Мастер-мастер в режиме active-active.
  3. Мастер-мастер в режиме active-passive.
  4. Мастер-мастер и несколько слэйвов.
  5. Кольцо.
  6. Дерево или пирамида.

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

Тут можно реализовать следующие идеи:

  • – каждый слэйв исполняет свою роль – например, выборочное индексирование;
  • использовать слэйв для аварийного восстановления;
  • использовать слэйв для бэкапа или разработки.

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

Мастер-мастер и несколько слэйвов: здесь можно назначить один или более слэйвов на каждый мастер. Эта схема уменьшает трафик между мастером и слэйвом.

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

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

Заключение

Подведем итог: реализация репликаций в MySQL имеет ряд недостатков, над которыми активно ведется работа. Google выпустил несколько патчей, улучшающих возможности репликации. Ведется работа над multimaster и построчной репликациями, которые появятся в будущих версиях. Имеются планы по автоматической конфигурации слэйвов. Все это вселяет уверенность в том, что одна из самых востребованных на сегодняшний день open-source баз данных MySQL станет еще продуктивнее, еще быстрее, еще надежнее.

Часть 7: Репликация PostgreSQL

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

Первый подход: изменять данные позволяется только одному серверу - его называют мастером. Остальные читающие ноды называются слэйвами. Ноды, которые находятся в состоянии ожидания данных от мастера и недоступны в этот момент, называются standby-нодами.

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

Есть 2 основных варианта решения этой проблемы - синхронный и асинхронный.

   1. Что такое синхронная и асинхронная репликация.
   2. Виды репликаций.
   3. Асинхронный мульти-мастер.
   4. Итоги.
 

1. Что такое синхронная и асинхронная репликация.

Основным достоинство синхронной репликации является то, что на всех нодах будут соблюдены принципы ACID. Как работает синхронная репликация ? В ее основе лежит 2-фазный сетевой протокол. Первая фаза: мастер рассылает всем нодам SQL-запрос. Каждая нода проверяет корректность запроса, и если все нормально, отсылает OK. Если все ноды до одной сделали подтверждение, вступает в силу вторая фаза: мастер отсылает команду на коммит запроса. Если на первой фазе хоть одна нода не присылает OK, то мастер вместо коммита разошлет команду abort на запрос. Недостатки: во-первых, это порождает сетевой траффик по обмену подтверждениями. Во-вторых, падает производительность, поскольку нужно ждать, пока все ноды будут синхронизированы.

Как работает асинхронная репликация? Это однофазный процесс. Мастер выполняет свои коммиты, но не занимается одновременной отсылкой запросов по нодам. Вместо этого он складывает их в очередь и делает отложенную рассылку запросов. Нода при получении запроса просто коммитит его. Реализация асинхронной репликации зависит полностью от правильной логики приложения. Главный недостаток - это возможность рассинхронизации данных по нодам. Но у метода есть масса достоинств: уменьшение сетевого трафика, повышение производительности. У мастера, что называется, развязываются руки - не нужно ничего контролировать и синхронизировать.

2. Виды репликаций.

С точки зрения атомарности в постгрес реализованы 2 вида репликаций. Логическая репликация (ее еще называют logical или statement replication) выполняется на уровне sql-запросов. Она проста с точки зрения реализации. Физическая (построчная или row-based) основана на записях, которые лежат в транзакционном логе. Она более производительна и прозрачна.

Решения можно разбить по следующим категориям:

1. Общий дисковый массив (Shared Disk Failover). Для базы данных имеется копия. В случае ее отказа происходит монтирование копии. В качестве варианта возможно использование сетевой файловой системы.

2. Теплый Standby (Warm Standby Using Point-In-Time Recovery (PITR)). Данные из лога WAL (write-ahead log) асинхронно транслируется с мастера на слэйв, где тут же применяются. В случае останова мастера имеем его копию. Минус в том, что во время этого процесса слэйвом пользоваться нельзя.

3. Репликация мастер-слэйв (Master-Slave Replication). Один мастер асинхронно отсылает данные на read-only слэйвы. Репликации на слэйвы могут выполняться как в синхронном, так и в асинхронном порядке. Вся пишущая нагрузка падает на мастера, все чтение должно идти на слэйвы. Если мастер останавливается, его может заменить слэйв. Этот тип репликации реализован в Slony-I (trigger-based replication), CommandPrompt Mammoth Replicator (Log-based replication), EnterpriseDB Replication Server (Trigger-based replication).

4. Мультимастер (Multimaster Replication). В отличие от мастер-слэйв схемы, здесь может быть несколько мастеров. Каждый мастер выполняет сначала свой запрос у себя, потом синхронизирует его на другом мастере. Недостаток в том, что возможны конфликты между мастерами на уровне транзакций. В постгресе используется асинхронный мультимастер. Схема реализована в Bucardo, rubyrep, PgPool-II, PgCluster, Sequoia.

5. Разделение данных (Data Partitioning). Одна таблица разбивается на несколько частей и разносится по нескольким нодам. Если запрос делается к данным, которые лежат на нескольких нодах, может быть создана копия с мастера для всей таблицы на одну из нод в режиме read-only. Пример реализации - plproxy.

3. Асинхронный мульти-мастер.

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

1. update конфликт

2. конфликт уникальности

3. delete конфликт

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

Второй тип ошибок происходит тогда, когда происходит создание дубля для уникального обьекта.

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

Для избежания конфликтов уникальности прежде всего необходимо установить правильную политику присвоения ID новым обьектам: множество id-шников должно быть разбито на несколько диапазонов, и каждому мастеру должен быть отдан его диапазон.

Для выявления и профилактики update-конфликтов нужно использовать тайминг (timestamps). При его возникновении с помощью тайминга нужно определить, какой коммит должен произойти раньше, и отменить неправильный коммит.

Один из вариантов реализации этого варианта включает в себя т.н. MASREP (Multi-master ASynchronous REPlication), который фактически является параллельным независимым сервером баз данных и выступает в роли клиента основной базы данных. Он принимает от всех мастеров запросы типа insert, update, delete и формируют очереди для каждого мастера, куда складывает эти запросы. Атомарная операция выполняется в течение т.н. репликационного цикла, во время которого запрос сначала отсылается мастеру, потом делается пауза, после которой начинается следующий репликационный цикл.

MASREP состоит из 5 частей: extractor, dispatcher, collector, injector, conflict resolver. Экстрактор занимается тем, что сортирует запросы и следит за тем, чтобы один и тот же запрос не выполнялся дважды на одном мастере. Диспатчер рассылает запросы. Коллектор собирает запросы для рассылки. Инжектор опрашивает мастера на выполнение запроса. Резольвер разрешает конфликты.

Синхронная репликция в PostgreSQL 9.1

WAL - или Write-Ahead Logging - или опережающее логирование - стандартный метод обеспечения целостности данных. При этом вначале на диск происходит запись лога изменений в какой-то таблице, и только потом на основе этого лога на диске происходит изменение самой таблицы. Это существенно уменьшает нагрузку на диск и повышает производительность самой базы данных.

Появившаяся в постгресе 9.х репликация "из коробки" (streaming replication) снована на WAL. Слэйв-сервер может коннектиться по сети к мастер-серверу с помощью протокола TCP, получать от него WAL-логи и сохранять их у себя. При этом на мастере должен быть заведен особый пользователь с ролью REPLICATION.

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

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

На конкретном примере рассмотрим, как реализовать синхронную репликацию для постгреса 9.1. У нас есть два хоста, на которых стоит центос. Мастер - его ip-шник условно 1.2.3.4 Слэйв - его ip-шник условно 5.6.7.8

1. Как я уже сказал, На мастере нужно создать нужного пользователя:

     postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"
     
2. На мастере в postgresql.conf прописываем параметры:
 	listen_address = '*'
        wal_level = hot_standby
 	max_wal_senders = 3
        checkpoint_segments = 8
 	wal_keep_segments = 8 
        synchronous_standby_names = 'my_db'
 
 			     
      listen_address - это ip-шник, с которого будет стучаться слэйв. Для простоты мы включаем все ip-шники
      wal_level - этим параметром мы указываем, в каком обьеме нужно писать логи.
      hot_standby указывает на то, что на слэйве можно делать только чтение.
      max_wal_senders - число одновременно подключаемых слэйвов
      checkpoint_segments - максимальное число лог-файлов, записываемых между транзакциями
      wal_keep_segments - минимальное число лог-файлов, которое лежит в каталоге pg_xlog
      synchronous_standby_names - список реплицируемых баз
 			     
3. На мастере в конфиге pg_hba.conf прописываем реплицируемый слэйв:
	
 			        host    replication     replicator      5.6.7.8/32       md5
 			        
4. Переходим к слэйву. Останавливаем постгрес и удаляем все содержимое каталога /var/lib/pgsql/9.1/data. Я указал каталог по умолчанию, в котором на центосе лежит база постгреса. На других дистрибутивах этот каталог будет другим.

5. Запускаем на слэйве утилиту постгреса pg_basebackup и копируем данные с мастера:

 			            sudo -u postgres pg_basebackup -h 1.2.3.4 -D /var/lib/pgsql/9.1/data -U replicator -v -P
 			            
набираем пароль:
 			        	thepassword
 			        	
6. На слэйве создаем файл recovery.conf
 			        	    tandby_mode = 'on'
 		        		    primary_conninfo = 'host=1.2.3.4 port=5432 user=replicator password=thepassword  application_name=my_db'
 		        		    trigger_file = '/tmp/postgresql.trigger'
 		        		    restore_command = ''
 			        			
7. Перезапускаем мастер и слэйв Если все получилось, в логах постгреса на слэйве можно найти примерно следующее:
 			        			    LOG:  entering standby mode
 			        	          	    LOG:  consistent recovery state reached at 0/31DA1198
 			        			    LOG:  redo starts at 0/31DA1108
 			        			    LOG:  streaming replication successfully connected to primary
 			        					
Соответственно на мастере:
 
 			        			    LOG:  standby "my_db" is now the synchronous standby with priority 1
 			        					    
8. Проверка репликаций: запускаем на мастере команду, и если все работает, нам должна вернуться таблица:
	
 			        			sudo -u postgres psql -x -c "select * from pg_stat_replication;"
 			        						
Итак, все работает.

Теперь представим, что мастер рухнул. Что делать в этом случае ? Идем на слэйв и делаем следующее: в recovery.conf прописываем

 			        			 standby_mode = off
 			        						  
Перезапускаем слэйв - и получаем полную работающую копию мастера.

4. Подведем итоги.

Настало время в этом цикле статей подвести итоги и определиться. Что мы имеем на данный момент? Факты - упрямая вещь: PostgreSQL - open-source база данных, соответствующая стандартам SQL-99, имеющая богатый набор инструментов для повышения производительности. У нее многочисленное сообщество разработчиков, она имеет более свободную лицензию, в то время как в MySQL в последнее время проявляются, прямо скажем, нехорошие тенденции в лице ее нынешнего законного преобладателя Oracle. Теперь пользователям MySQL прийдется подписывать лицензию-соглашение с самой Oracle. MySQL хорош на плоских запросах с отключенными транзакциями, в то время как PostgreSQL может поддерживать более серьезную нагрузку, сложные запросы параллельно с записью в базу данных. Наличие в PostgreSQL MVCC (multi-version concurrency control) - это большой плюс. Это позволяет выполнять одновременно читающую и пишущую транзакцию в одной таблице, при этом не происходит блокировки самой таблицы. Такой механизм позволяет откатить неудачную транзакцию. В MySQL частично реализован MVCC (InnoDB), с ограничениями на производительность.

Механизм работы транзакционного лога также сильно отличается. В PostgreSQL WAL (Write-Ahead Log) поддерживается атомарность на уровне событий в таблице. WAL позволяет откатить состояние обьекта на произвольный момент времени. Такая транзакция называется физической, или построчной, trigger-based, row-based - как хотите, так и называйте. В MySQL транзакция приципиально иная - логическая (statement-based), в ней только предполагается добавление физической транзакции в будущих версиях начиная с версии 5.1.

PostgreSQL развивается достаточно динамично, в то время как развитие MySQL приостановилось в силу известных событий. Ниша, которую занимает PostgreSQL, более широкая, и потенциал у нее выше. Ниша MySQL скромнее, она оправдывает себя как хранилище для некритичных по нагрузке и производительности баз данных. Когда вы делаете свой выбор - PostgreSQL или MySQL - вы должны понимать разницу между ними и радоваться тому, что у вас есть свобода в выборе инструмента для хранения данных.

Оставьте свой комментарий !

Ваше имя:
Комментарий:
Оба поля являются обязательными

 Автор  Комментарий к данной статье