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

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

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

• PGCluster;
• Slony-I – основной механизм репликации в постгресе, производительность падает в квадратичной зависимости от числа серверов;
• DBBalancer;
• pgpool;
• PostgreSQL table comparator;
• SkyTools;
• Sequoia;
• Bucardo.

Репликация в 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

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 имя_базы
 

После этого на мастер-сервере нужно запустить скрипт, в нем будет запущена утилита 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;
 
 

  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
 
 

  "someuser" "same_password_as_in_server"
 

  $ psql -h 127.0.0.1 -p 6543 pgbouncer
 

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

  log_bin   = mysql-bin
   server_id = 10
 

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
 

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
 

mysql> SHOW PROCESSLIST\G
 
 

  sync_binlog=1
 

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
 

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;
 

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

     postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"
     

 	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 - список реплицируемых баз
 			     

	
 			        host    replication     replicator      5.6.7.8/32       md5
 			        

 			            sudo -u postgres pg_basebackup -h 1.2.3.4 -D /var/lib/pgsql/9.1/data -U replicator -v -P
 			            

 			        	thepassword
 			        	

 			        	    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 = ''
 			        			

 			        			    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
 			        					    

	
 			        			sudo -u postgres psql -x -c "select * from pg_stat_replication;"
 			        						

 			        			 standby_mode = off