Серия контента:
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.
У постгреса несколько сотен разработчиков по всему миру.
2. Архитектура MySQL и PostgreSQL
PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.
Для каждого клиента на сервере создается новый процесс (не поток!). Для работы с такими клиентскими процессами сервер использует семафоры.
Клиентский запрос проходит следующие стадии.
- Коннект.
- Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
- Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
- Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
- Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.
Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.
MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.
Реализован стандарт SQL92.
С модульной точки зрения код MySQL можно разделить на следующие модули.
- Инициализация сервера.
- Менеджер коннектов.
- Менеджер потоков.
- Обработчик команд.
- Аутентификация.
- Парсер.
- Оптимизатор.
- Табличный менеджер.
- Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
- Логирование.
- Репликация.
- Сетевое API.
- 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 представляет собой дескриптор потока и является аргументом большого количества функций.
3. Сравнение MySQL и PostgreSQL: сходство и различия
ACID-стандарт
Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.
Производительность (performance)
Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что MySQL начинала разрабатываться с прицелом на скорость, а PostgreSQL с самого начала разрабатывалась как база с большим числом настроек и соответствием стандарту. PostgreSQL имеет ряд настроек, которые повышают скорость доступа:
- парциальные индексы;
- компрессия данных;
- выделение памяти;
- улучшенный кеш.
MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.
Компрессия
PostgreSQL лучше сжимает и разжимает данные, позволяя сохранить больше данных на дисковом пространстве. При этом компрессионные данные читаются быстрее с диска.
MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.
На мульти-процессорности 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 имеет модульную архитектуру, и репликация входит в отдельные модули:
- Slony-I – основной механизм репликации в постгресе, производительность падает в квадратичной зависимости от числа серверов;
Репликация в 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.
Мы обсудим вопросы настройки MySQL и PostgreSQL.
Ресурсы для скачивания
static.content.url=http://www.сайт/developerworks/js/artrating/
Zone=Open source, Linux
ArticleID=779830
ArticleTitle=MySQL & PostgreSQL: Часть 1. Сравнительный анализ
В связи со стремительной девальвацией рубля, покупать СУБД Microsoft SQL стало очень дорого, а для некоторых компаний стоимость этих лицензий стала совсем «неподъемной». В данный момент чтобы развернуть сервер Microsoft SQL для 20 пользователей необходимо купить такие лицензии:
1 лицензия на операционную систему (WinSvrStd 2012R2)
20 лицензий на подключение к серверу (WinSvrCAL 2012)
1 лицензия на сервер СУБД (SQLSvrStd 2014)
20 лицензий на подключение к СУБД (SQLCAL 2014)
Ориентировочная стоимость такого пакета 275 000 руб., что для компании, в которой всего 20 человек достаточно дорого. Данных затрат можно избежать, если создать сервер СУБД на свободном ПО. Поставить операционную систему семейства Linux и бесплатную версию СУБД - PostgreSQL . На таком сервере без проблем можно развернуть сервер 1С предприятия, а также другие роли, которые потенциально могут быть совмещены c ролью баз данных, например WebServer или файловое хранилище.
Так как использовать свободное ПО очень привлекательно с финансовой точки зрения, было решено проверить, на сколько это хорошо с точки зрения производительности.
Тестирование производительности 1С:
Для выполнения теста было взято оборудование и программное обеспечение, указанное в таблице 1. Физический сервер для обоих стендов использовался один и тот же, менялось только ПО. Настройки обоих СУБД использовались по-умолчанию и в статье мы их подробно не расписываем. Дистрибутив PostGreSQL с соответсвующими патчами были взят с сайта компании 1С, версия - последняя из доступных на данном сайте.
Таблица 1. Тестовые стенды
|
Характеристики |
Стенд №1 |
Стенд №2 |
|
|
Операционная система |
CentOS 6 |
Windows Server 2012R2 |
|
|
PostgreSQL 9.3.3 |
Microsoft SQL Server 2012R2 |
||
|
Центральный процессор |
Intel Core i 5 3330 (3.0 Ghz ) |
||
|
Оперативная память |
24 GB DDD 3 1333 Ghz |
||
|
Жесткий диск |
SSD 240 Gb Intel |
||
Для начала был выполнен «тест Гилева», который показал незначительное преимущество стенда номер 2, против стенда со свободным ПО.
Результаты смотрим ниже, разница в значениях получилась всего 3%.
Для информации: «тест Гилева» - популярный синтетический тест 1С, который выполняет ряд стандартных операций – чем быстрее тест выполняется, тем выше оценка. Оценка выполняется в условных единицах. Полученную оценку можно сравнить с прилагаемой к тесту шкале, которая покажет на сколько высока производительность текущей системы.
Рисунок 1. Результат теста Гилева. Стенд №2 СУБД MS SQL
Рисунок 2. Результат теста Гилева. Стенд №1 СУБД PostgreSQL
Далее решено было тестирование выполнить по методике APDEX . Суть метода заключается в измерении времени выполнения основных операций в 1С, замеры проводятся несколько раз на протяжении определенно периода времени. Далее полученный результат сравнивается с приемлемым временем выполнения той или иной операции.
Для этого взяли реальную рабочую базу одной из самых тяжелых конфигураций 1С, характеристики базы указаны в таблице №2.
Таблица 2. Характеристики тестовой базы
Замерялось время выполнения 7-ми стандартных операций с объектами в базе. Каждый тест выполнялся 10 раз и выводилось среднее значение. Замеры проводились с использованием толстого клиента через локальную сеть. Клиент устанавливался на рабочую станцию под управлением Windows 7. Тесты также пробовали запускать с клиента установленного на Ubuntu Linux , но он работал не стабильно и все тесты решено было выполнять только с клиента на Windows .
Таблица 3. Результаты APDEX
|
Ключевая операция |
Время выполнения в секундах |
Отклонение |
|||
|
Стенд №2 (MSSQL ) |
Стенд №1 (Свободное ПО) |
||||
|
Открытие документа |
Заказ клиента | ||||
|
Проведение документов |
Заказ клиента | ||||
|
Проведение нового документа |
Документ объект: Заказ клиента | ||||
|
Сформирован отчет |
Анализ доходов расходов | ||||
|
Сформирован отчет |
Ведомость по партиям товаров | ||||
|
Сформирован отчет |
Ведомость по товарам на складах | ||||
|
Сформирован отчет |
Расчеты с клиентами | ||||
В среднем наша реальная база при использовании MSSQL
работала на 45% быстрее, чем на стенде со свободным ПО. На некоторых тестах отрыв был очень значителен, а на таких как, например проведение нового документа составлял всего 11%.
Вывод:
1C на СУБД MSSQL работает примерно в 1,5 быстрее, чем на PostgreSQL. Соответственно, если есть возможность купить или арендовать лицензии MSSQL, лучше использовать его для более высокой производительности. Для небольших и ненагруженных баз можно попробовать использовать версию MSSQL Express. Тестов с ней мы не проводили, поэтому она может показать себя по производительности как лучше так и хуже PostgreSQL. Данная редакция ограничена использованием 1 процессора и 1 Гб ОЗУ, также не работает с базами более 10Гб. Если база дорастет до такого размера, то она остановиться и перестанет работать полностью, но как показывает практика, если в базе работает 15-20 пользователей, то комфортно можно работать при размере базы 4-5ГБ, далее база начинает сильно тормозить.
Оценка «тестом Гилева» показывает крайне незначительное превосходство MSSQL, что позволяет сделать предположение о том, что другие базы 1С могут работать на PostgreSQL так же хорошо, как и на MSSQL, а возможно и быстрее. Перед выбором СУБД рекомендуем провести тесты на своей конкретной базе и сравнить полученные результаты.
Использование СУБД PostgreSQL для развертывания на нем 1С является приемлемым решением в условиях ограниченного бюджета. База будет работать не так быстро как на MSSQL , но зато не нужно платить за лицензии.
В конце 2017 года мы провели новые тесты и опубликовали их в очередной статье .
Ещё одним способом сэкономить при использовании 1С является решение - взять сервер 1С в аренду .
Системная интеграция. Консалтинг
Трудно найти организацию, в которой не используются бухгалтерские системы от 1С – даже в мегахолдингах, где давно внедрён SAP или OEBS, они почти всегда используются на том или ином участке. Отрадно, что российское прикладное ПО стало фактическим стандартом для наших компаний, но есть одна тонкость: столь же фактическим стандартом для самого 1C:Предприятия стало использование Microsoft SQL Server в качестве СУБД.
В среде практикующих 1С-ников наиболее распространено мнение, что без коммерческих СУБД от американских производителей ничего хорошего не выйдет, дескать, несколько сотен пользователей неизбежно требуют установки базы на MS SQL, Oracle Database или IBM DB2 в этом случае. Про работу под управлением свободой СУБД PostgreSQL известные нам мнения практиков расходились, но в диапазоне от «совсем не работает» до «пригодно для нескольких десятков пользователей, не более».
Был и ряд правдоподобных объяснений столь скромным оценкам: и активное использование платформами 1С механизмов временных таблиц (которые в Постгресе реализованы слишком «честно» – с транзакционным DDL, всеми возможностями по восстановлению), и особенности работы с текстовыми данными (тогда как в области многоязычных текстов ванильный Постгрес, опять же, слишком консервативен, используя не самые высокопроизводительные системные библиотеки), и ряд других менее значимых аспектов.
Но мы тайно верили в Постгрес, тем более, что в сборке заявлялось о решении всех тех проблем, которыми скептики оправдывали выбор коммерческих СУБД. К тому же нам было важно получить показатели назначения для аппаратно-программного комплекса – построенной на основе санкционно безопасного оборудования и софта машины баз данных для СУБД, разработанной IBS совместно с Postgres Professional.
Из тиражируемых приложений самым очевидным применением для такой машины должны стать, конечно же, системы 1C. И результаты проведённых бенчмарков полностью перевели нас из разряда «тайно верующих» (и даже сомневающихся) в категорию «убеждённых»: теперь мы можем смело утверждать, что 1C:Предприятие версии 8.3 на сборке Postgres Pro EE 1.5 для Скалы-СР / Postgres Pro работает лучше, чем на MS SQL 2012 на том же оборудовании со всеми возможными оптимизациями.
Итак, некоторые детали экспериментов. В плане тестирования производительности у 1С всё системно и научно – есть типовая конфигурация «Стандартный нагрузочный тест », на которой и запускается бенчмарк, поэтапно добавляющий новых пользователей в нагрузку до тех пор, пока приложение работает достаточно отзывчиво для комфортной работы. (Более точно – пользователи добавляются до тех пор, пока стандартный показатель производительности приложений Apdex не опускается ниже порога в 0,85, и максимальное число таких эффективно работающих пользователей и считается результатом бенчмарка.)
Мы использовали версию 8.3.9.1850 1С:Предприятия, стандартный нагрузочный тест в версии 2.0.17.36. Изначально было решено никаких скидок Постгресу не давать: делаем максимальные оптимизации на MS SQL на узле из комплекса Скала-СР / Postgres Pro (ставим Windows на «голое железо», настраиваем по всем канонам , для скорости – делаем ramdisk для временных таблиц), а потом – возвращаем тот же узел в комплекс Скала-СР, накатываем Linux и Postgres Pro EE, и на нём одном (без доступных в комплексе кластерных фишек) – прогоняем тот же тест.
Тест первый: начинаем со 100 рабочих мест, нагрузка 50/50 – половина формирует документы, половина – отчёты. Тест второй: начинаем с 400, нагрузка 70/30. MS SQL «закончился» в первом тесте на 360 пользователях, на втором – на 540, притом ограничителем в обоих пусках стала работа с локальным вводом-выводом, при том, что загрузить процессор удалось в среднем лишь на 30%. Postgres Pro в первом тесте дошёл до 440 рабочих мест, а на втором – до 660, а упёрлось на сервере БД всё в процессор, уходящий в загрузку более 90% на «максимальных пользователях».
Для 1С, где количество одновременно работающих пользователей – самый проблемный ограничивающий фактор, это замечательный результат, а главное, говорящий о том, что эти важнейшие российские прикладные системы могут не просто функционировать и без западных коммерческих СУБД, но даже делать это заметно лучше. 
В преддверии своего доклада на конференции PGCONF.RUSSIA 2015 я поделюсь некоторыми наблюдениями о важных различиях между СУБД MySQL и PostgreSQL. Этот материал будет полезен всем тем, кого уже не устраивают возможности и особенности MySQL, а также тем, кто делает первые шаги в Postgres. Конечно, не стоит рассматривать этот пост как исчерпывающий список различий, но для принятия решения в пользу той или иной СУБД его будет вполне достаточно.
Репликация
Тема моего доклада «Асинхронная репликация без цензуры, или почему PostgreSQL завоюет мир», и репликация одна из самых больных тем для нагруженных проектов использующих MySQL. Проблем много - корректность работы, стабильность работы, производительность - и на первый взгляд они выглядят несвязанными. Если же посмотреть в историческом контексте, то мы получаем интересный вывод: MySQL репликация имеет столько проблем потому, что она не была продумана, а точкой невозврата была поддержка storage engine (подключаемых движков) без ответов на вопросы «как быть с журналом?» и «как различным storage engine участвовать в репликации». В 2004 году в PostgreSQL рассылке пользователь пытался «найти» storage engine в исходном коде PostgreSQL и сильно удивился, что их нет. В процессе дискуссии кто-то предложил добавить эту возможность PostgreSQL, и один из разработчиков ответил «Ребята, если мы так сделаем, у нас будут проблемы с репликацией и с транзакциями между движками».The problem is that many storage management systems… often do their own WAL and PITR. Some do their own buffer management, locking and replication/load management too. So, as you say, its hard say where an interface should beссылка на это письмо в postgresql mailing list
abstracted.
Прошло более 10 лет, и что мы видим? В MySQL есть раздражающие проблемы с транзакциями между таблицами разных storage engine и у MySQL проблемы с репликацией. За эти десять лет у PostgreSQL появились подключаемые типы данных и индексы, а также есть репликация - т. е. преимущество MySQL было нивелировано, в то время как архитектурные проблемы MySQL остались и мешают жить. В MySQL 5.7 попытались решить проблему производительности репликации, распараллелив её. Поскольку проект на работе очень чувствителен к производительности репликации в силу своего масштаба, я попытался протестировать, стало ли лучше. Я нашёл, что параллельная репликация в 5.7 работает медленней однопоточной в 5.5, и лишь в отдельных случаях - примерно также. Если вы сейчас используете MySQL 5.5 и хотите перейти на более свежую версию, то учтите, что для высоконагруженных проектов миграция невозможна, поскольку репликация просто перестанет успевать выполняться.
После доклада на highload, в Oracle приняли к сведению разработанный мной тест и сообщили, что попытаются исправить проблему; недавно мне даже написали, что смогли увидеть параллелизм на своих тестах, и выслали настройки. Если не ошибаюсь, при 16 потоках появилось незначительное ускорение по сравнению с однопоточной версией. К сожалению, до сих пор не повторил свои тесты на предоставленных настройках - в частности потому, что с такими результатами наши проблемы всё равно остаются актуальными.
Точные причины такой регрессии производительности неизвестны. Было несколько предположений - например, Кристиан Нельсен, один из разработчиков MariaDB, у себя в блоге писал о том, что могут быть проблемы с перфоманс-схемой, с синхронизацией тредов. Из-за этого наблюдается регрессия в 40%, которая видна на обычных тестах. Oracle-разработчики это опровергают, и меня даже убедили, что её нет, видимо, я вижу какую-то другую проблему (и сколько же их всего?).
В MySQL репликации проблемы со storage engine усугубляются выбранным уровнем репликации - они логические, в то время как в PostgreSQL - физические. В принципе, у логической репликации есть свои преимущества, она позволяет сделать больше всяких интересных штук, об этом в докладе я тоже упомяну. Но PostgreSQL даже в рамках своей физической репликации уже сводит все эти преимущества на нет. Иными словами, почти все, что есть в MySQL, уже можно сделать и в PostgreSQL (либо будет можно в ближайшем будущем).
На реализацию низкоуровневой физической репликации в MySQL можно не надеяться. Проблема в том, что там вместо одного журнала (как в PostgreSQL) их получается два или четыре - смотря как посчитать. PostgreSQL просто коммитит запросы, они попадают в журнал, и этот журнал используется в репликации. PostgreSQL-репликация суперстабильна, потому что она использует тот же журнал, что и при операциях восстановления после сбоев. Этот механизм давно написан, хорошо оттестирован и оптимизирован.
В MySQL ситуация другая. У нас есть отдельный журнал InnoDB и журнал репликации, и нужно коммитить и туда, и туда. А это two-phase commit между журналами, который по определению работает медленно. То есть мы не можем просто взять и сказать, что мы повторяем транзакцию из InnoDB-журнала - приходится разбираться, что за запрос, запускать его заново. Если даже это логическая репликация, на уровне строчек, то эти строчки нужно искать в индексе. И мало того, что приходится сделать большое количество работы, чтобы выполнить запрос - он при этом снова будет писаться в свой InnoDB-журнал уже на реплике, что для производительности явно нехорошо.
В PostgreSQL в этом смысле архитектура на порядок продуманней и лучше реализована. Недавно в нём анонсировали возможность под названием Logical Decoding - которая позволяет сделать всякие интересные штуки, которые очень тяжело сделать в рамках физического журнала. В PostgreSQL это надстройка сверху, logical decoding позволяет работать с физическим журналом так, будто он логический. Именно эта функциональность скоро уберёт все преимущества MySQL репликации, кроме, возможно, размера журнала - statement-based репликация MySQL будет выигрывать - но у statement-based репликации MySQL есть совершенно дикие проблемы в самых неожиданных местах, и не стоит считать её хорошим решением (про это всё я тоже буду говорить в докладе).
Кроме того, для PostgreSQL есть триггерная репликация - это Tungsten, который позволяет делать то же самое. Триггерная репликация работает следующим образом: ставятся триггеры, они заполняют таблицы или пишут файлы, результат отправляется на реплику и там применяется. Именно через Tungsten, насколько я знаю, делают миграцию из MySQL в PostgreSQL и наоборот. В MySQL же логическая репликация работает прямо на уровне движка, и другой ее сделать сейчас уже нельзя.
Документация
У PostgreSQL документация гораздо лучше. В MySQL она формально вроде даже есть, но смысл отдельных опций понять бывает тяжело. Вроде написано, что они делают, но чтобы понять, как их правильно настраивать, нужно использовать неофициальную документацию, искать статьи на эти тему. Часто нужно понимать архитектуру MySQL, без этого понимания настройки выглядят какой-то магией.Например, так «выстрелила» компания Percona: они вели MySQL Performance Blog, и в этом блоге было множество статей, в которых рассматривались отдельные моменты эксплуатации MySQL. Это принесло бешеную популярность, привело клиентов в консалтинг, позволило привлечь ресурсы для запуска разработки собственного форка Percona-Server. Существование и востребованность MySQL Performance Blog доказывают, что официальной документации просто недостаточно.
У PostgreSQL фактически все ответы есть в документации. С другой стороны, я слышал много критики при сравнении документации PostgreSQL со «взрослой» Oracle. Но это, на самом деле, очень важный показатель. MySQL с взрослым Oracle никто не пытается сравнивать вообще - это было бы смешно и нелепо - а PostgreSQL уже начинают сравнивать вполне серьезно, PostgreSQL-коммьюнити эту критику слышит и работает над улучшением продукта. Это говорит о том, что он по своим возможностям и производительности начинает конкурировать со столь мощной системой как Oracle, на которой работают мобильные операторы и банки, в то время как MySQL остаётся сидеть в нише веб-сайтов. И проекты-гиганты, доросшие до большого количества данных и пользователей, хлебают горе с MySQL большой ложкой, постоянно упираясь в его ограничения и архитектурные проблемы, которые невозможно исправить, затратив разумное количество сил и времени.
Примером таких крупных проектов на PostgreSQL является 1C: PostgreSQL идёт как опция вместо Microsoft SQL, а Microsoft SQL действительно фантастическая СУБД, одна из самых мощных. PostgreSQL может заместить MS SQL, а попытка заместить его MySQL… давайте опустим завесу жалости над этой сценой, как писал Марк Твен.
Стандарты
PostgreSQL соответствует стандартам SQL-92, SQL-98, SQL-2003 (реализованы все его разумные части) и уже работает над SQL-2011. Это очень круто. Для сравнения, MySQL не поддерживает даже SQL-92. Кто-то скажет, что в MySQL такая цель просто не ставилась разработчиками. Но нужно понимать, что разница между версиями стандарта заключается не в мелких изменениях - это новые функциональные возможности. То есть в тот момент, когда MySQL говорил: «Мы не будем следовать стандарту», они не просто вносили какие-то мелкие различия, из-за которых MySQL тяжело поддержать, они еще закрывали дорогу к реализации многих нужных и важных возможностей. Там до сих пор нет нормально оптимизатора. То, что там называется оптимизацией, в PostgreSQL называется «парсер» плюс нормализации. В MySQL это лишь план выполнения запросов, без разделения. И MySQL к поддержке стандартов придут еще очень нескоро, поскольку на них давит груз обратной совместимости. Да, они хотят, но лет через пять, может, что-нибудь у них появится. В PostgreSQL есть уже все и сейчас.Производительность и сложность администрирования
С точки зрения простоты администрирования сравнение не в пользу PostgreSQL. MySQL администрировать гораздо проще. И не потому, что в этом смысле он лучше продуман, а просто гораздо меньше умеет делать. Соответственно, и настраивать его проще.У MySQL есть проблема со сложными запросами. Например, MySQL не умеет спускать группировку в отдельные части union all. Разница между двумя запросами - на нашем примере группировка по отдельным таблицам и union all сверху работала в 15 раз быстрее, чем union all и потом группировка, хотя оптимизатор должен оба запроса приводить в одинаковый, эффективный план выполнения запроса. Нам придется делать генерацию таких запросов руками - т. е. тратить время разработчиков на то, что должна делать база.
«Простота» MySQL вытекает, как можно увидеть выше, из крайне бедных возможностей - MySQL работает просто хуже и требует больше времени и усилий во время разработки. В противоположность этому, у PostrgreSQL есть гистограммы и нормальный оптимизатор, и он выполнит такие запросы эффективно. Но если есть гистограммы, значит, есть их настройки - как минимум bucket size. Про настройки нужно знать и в отдельных случаях их менять - следовательно, нужно понимать, что это за настройка, за что она отвечает, уметь распознавать такие ситуации, увидеть выбрать оптимальные параметры.
Изредка случается, что умелость PostrgreSQL может помешать, а не помочь. В 95% случаев все хорошо работает - лучше, чем MySQL, - а какой-то один дурацкий запрос работает гораздо медленнее. Или всё работает хорошо, а потом внезапно (с точки зрения пользователя) по мере роста проекта некоторые запросы стали работать плохо (стало больше данных, стал выбираться другой план выполнения запроса). Скорее всего, для исправления достаточно запустить analyze или немножко покрутить настройки. Но нужно знать, что делать и как это делать. Как минимум, нужно прочитать документацию PostgreSQL на эту тему, а читать документацию почему-то не любят. Может потому, что в MySQL от неё мало помощи? :)
Подчеркну, что PostgreSQL в этом смысле не хуже, просто он позволяет отложить проблемы, а MySQL сразу их вываливает и приходится тратить время и деньги на их решение. В этом смысле MySQL работает всегда стабильно плохо, и еще на этапе разработки люди эти особенности учитывают: делают все максимально простым способом. Это относится только к производительности, точнее, к способам её достижения и к её прогнозируемости. В плане корректности и удобства PostgreSQL на голову выше MySQL.
Так что же выбрать?
Чтобы определиться с выбором между MySQL и PostgreSQL для конкретного проекта, прежде всего нужно ответить на другие вопросы.Во-первых, какой опыт есть у команды? Если вся команда имеет 10 лет опыта работы с MySQL и нужно запуститься как можно быстрее, то не факт, что стоит менять знакомый инструмент на незнакомый. Но если сроки не критичны, то стоит попробовать PostgreSQL.
Во-вторых, нужно не забывать про проблемы эксплуатации. Если у вас не высоконагруженный проект, то с точки зрения производительности разницы между этими двумя СУБД нет. Зато у PostgreSQL есть другое важное преимущество: он более строгий, делает больше проверок за вас, дает меньше возможности ошибиться, и это в перспективе огромное преимущество. Например, в MySQL приходится писать собственные инструменты для верификации обычной ссылочной целостности базы. И даже с этим могут быть проблемы. В этом смысле PostgreSQL инструмент более мощный, более гибкий, разрабатывать на нем приятнее. Но это во многом зависит от опыта разработчика.
Подводя итог: если у вас простенький интернет-магазин, нет денег на админа, нет серьезных амбиций перерасти в большой проект и есть опыт работы с MySQL - то берите MySQL. Если предполагаете, что проект будет популярным, если он большой, его будет тяжело переписать, если в нём сложная логика и связи между таблицами - возьмите PostgreSQL. Даже из коробки он у вас будет работать, поможет в разработке, сэкономит время, и вам проще будет расти.