История 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.
У постгреса несколько сотен разработчиков по всему миру.
PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.
Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.
Клиентский запрос проходит следующие стадии.
Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.
MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.
Реализован стандарт SQL92.
С модульной точки зрения код MySQL можно разделить на следующие модули.
Порядок работы модулей следующий: сначала загружается первый модуль, который читает опции командной строки, конфиг-файлы, выделяет память, инициализирует глобальные структуры, загружает системные таблицы и передает управление менеджеру коннектов.
Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.
Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.
Как и любая СУБД PostgreSQL должна обслуживать запросы sql и возвращать результаты клиентским приложениям. Для этого механизм СУБД оптимизатор запросов принимает решение об оптимальном использовании ресурсов и строит план запроса. При этом он может опереться на используемых механизмы ускорения работы (индексов, кэшей в памяти, ресурсам процессора, данные статистики и т.д.).
Результаты запросов возвращаются клиентским подключениям
В Windows управляет всем файл службы pg_ctl.exe (останавливает, запускает, перезапускает).
Сам движок базы – это postgres.exe.
Оптимизатор запросов мало подвержен управлению со стороны разработчиков, поэтому к нему вернемся лишь косвенно в разделе анализа быстродействия.
Зато управление дисковым пространством имеет весомое значение. Как и в любой СУБД, для баз данных PostgreSQL лучше использовать RAID 10 для баз данных и отдельный дисковый массив под логии. Применение STORAGE систем может также положительно сказаться на всем быстродействии. Еще одна фича, ускоряющая работу за счет расположения данных на разных дисках, это табличные пространства (tablespaces).
Критически важные для администраторов многогигабайтных хранилищ данных, табличные пространства позволяют размещать большие таблицы и индексы на отдельных дисках или массивах, что повышает производительность запросов.
У PostgreSQL есть особенность в реализации хранения – это так называемый “кластер”. В данном случаи речь идет месте расположения каталога для баз данных. Плюс PostgreSQL организует единую файловую структуру, в которой отдельные файлы не соответствуют непосредственно таблицам или другим объектам базы данных.
При установке вы указываете “кластер” (читай каталог).
При установке PostgreSQL создает системную базу postgres и базу template1 как шаблон настроек для всех новых баз. Обычно в Linux-среде в каталоге /var/postgres/data находится некоторое количество служебных файлов для PostgreSQL, а в каталоге /var/postgres/data/base размещаются базы данных, каждая в своем отдельном каталоге.
К сожалению, реализованный в PostgreSQL способ хранения данных требует дополнительного обслуживания - периодической дефрагментации базы данных командой VACUUM.Примечание. Материализованные запросы (представления пользователя) view платформой не используются.
На данный момент (версия 9.1.2), в PostgreSQL имеются следующие ограничения:
|
Максимальный размер базы данных |
Нет ограничений |
|
Максимальный размер таблицы |
32 ТБайт |
|
Максимальный размер записи |
1,6 ТБайт |
|
Максимальный размер поля |
1 ГБайт |
|
Максимум записей в таблице |
Ограничено размером таблицы |
|
Максимум полей в таблице |
250—1600, в зависимости от типов полей |
|
Максимум индексов в таблице |
Нет ограничений |
Сильными сторонами PostgreSQL считаются:
pgAdmin - графическая оболочка проектирования и административная СУБД PostgreSQL для *nix и системы Windows.
pgAdmin пишется на языке программирования C и использует превосходный пакет разработчика платформы wxWidgets (когда-то wxWindows).
pgAdmin распространяется по лицензии PostgreSQL, т.е. также является свободным ПО.
1С:Предприятие 8 имеет некоторые особенности работы с СУБД PostgreSQL, связанные с использованием транзакционных блокировок:
режиме автоматического управления блокировками в транзакции используются табличные блокировки СУБД;
в режиме управляемых блокировок в транзакции используются блокировки записей и полей СУБД.
По умолчанию, PostgreSQL разрешает только соединения на локальной машине через сокеты домена Unix или TCP/IP соединения. Для того, чтобы другие машины смогли подключиться к базе вы должны изменить в postgresql.conf, разрешить host-авторизация в файле $PGDATA/pg_hba.conf и перестартовать сервер.
Заинтересовал меня СУБД PostgreSQL тремя моментами:
|
Ниже приводится таблица сравнения основных возможностей этих СУБД. Критерии сравнения выбирались сугубо из моих интересов/опыта и обзоров в инете. |
MySQL |
PostgreSQL |
|
Ответственный за код |
компания MySQL AB |
разные разработчики |
|
Сжатие данных при передаче |
+ |
+ |
|
Поддержка модели ACID |
+/- (InnoDB, Falcon) |
+ |
|
Поддержка SQL команд: insert ignore / replace |
+ |
- |
|
Поддержка внешних ключей |
+/- (InnoDB) |
+ |
|
Репликации |
+ |
+ (говорят в 8.4 стало все хорошо) |
|
Под запросы |
+ |
+ |
|
Полнотекстовые индексы |
+ (MyISAM) |
+ |
|
Частичное индексирование |
- |
+ |
|
Чистка после работы команд UPDATE и DELETE |
не нуждается |
VACUUM |
|
Система привилегий |
+ |
+/- (проще чем в MySQL) |
|
Хранение таблиц в файлах |
+ |
- |
|
Хранение/обработка географических данных |
+/- |
+ |
|
Лицензирование |
GNU GPL |
BSD |
Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.
Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что 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 имеет модульную архитектуру, и репликация входит в отдельные модули:
Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.
В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:
Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 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 – полное.
MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.
MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).
PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:
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. http://v8.1c.ru/overview/postgresql.htm
3. http://proft.me/2010/05/4/sravnenie-postgresql-vs-mysql/
5. http://www.ibm.com/developerworks/ru/library/os-mysql-postgresql/01/index.html?ca=drs-
6. http://www.postgresql.org/docs/8.1/interactive/supported-platforms.html
7. http://gis-lab.info/qa/postgis-install.html
7. http://postgresql.softsalad.ru/
8. http://www.kodges.ru/69979-postgresql-8-for-windows.html
|
|
 |
