@varanio12 окт 2015 в 07:25

Возможности PostgreSQL, которых нет в MySQL, и наоборот

7 мин

103K

PostgreSQL *

Из песочницы

+122

313

Комментарии 313

@maximw 12 окт 2015 в 08:07

Я думаю, вместо CTE в Mysql можно использовать View. Не так гибко, конечно, но похоже по функционалу.,

@mird 12 окт 2015 в 08:13

Хочу посмотреть как вы через вьюхи сделаете рекурсию.

@maximw 12 окт 2015 в 08:16

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

@baltazorbest 12 окт 2015 в 16:38

Не сужусь говорить как View работает в postgres, но в mysql при больших объемах данных она работает медленно… Сужу по личному опыту.
P.S. Еще одним не большим плюсом mysql как по мне является возможность использовать конструкции вида:
SELECT field FROM table t JOIN database.table2 t2 ON t2.column_id = t.id… Когда я пробовал постгрес я так и не увидел как можно нормально работать сразу с 2 базами.
P.S.S. Может кто посоветует литературу для старта в изучении postgres?

@vsergey 13 окт 2015 в 08:03

для запросов из нескольких баз вы можете использовать dblink
www.postgresql.org/docs/9.3/static/contrib-dblink-function.html

@baltazorbest 13 окт 2015 в 08:07

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

@Fesor 13 окт 2015 в 08:15

Как по мне не привычнее делать запросы с участием таблиц из разных баз данных.

@zzashpaupat 13 окт 2015 в 10:00

В Postgres чаще используют схемы в рамках одной базы, и между ними запросы ходят отлично. А вот в MySQL отказались от схем, но зато сделали возможными запросы между базами.
При этом в Postgres можно сделать CREATE FOREIGN TABLE и делать запросы к другому серверу как к своей таблице, с версии 9.5 будет возможность сделать IMPORT FOREIGN SCHEMA.
Не знаю, есть ли такие возможности в MySQL, глубоко не копал.

@VolCh 16 окт 2015 в 02:55

В MySQL CREATE DATABASE и CREATE SCHEMA полные синонимы.

Существует FEDERATED engine, позволяющая создавать в локальной базе таблицы, ссылающиеся на таблицы в других базах (инстансах, локальных или удаленных) MySQL. Обычно по умолчанию этот движок отключен, но вообще он есть. На версии 5.1 работал, скажем так, странно, а более новые не смотрел.

@BlessMaster 15 окт 2015 в 14:22

Как тут уже написал zzashpaupat, внешний источник данных можно явно объявить через CREATE FOREIGN TABLE и делать запросы к двум базам таким образом. Как приятный довесок другой базой могут быть не только базы Postgres, но, и тот же MySQL, Redis, Mongo, MemCache, простые файлы на диске — всё, на что будет написан соответствующий враппер. Гуглить FDW.

Вот: http://pgxn.org/tag/fdw/, если покопаться, даже к твиттеру есть.

Но вообще, запросы к двум базам — это достаточно экстремальное занятие класса «100 избранных способов прострелить себе ногу», особенно, если мы используем СУБД чуть больше, чем хранилище для статей в любимом бложике и мы рассчитываем на согласованное состояние данных под нагрузками и в распределённых системах, это хозяйство требует хорошо прокачанных скиллов уровнем не ниже совета джедаев :-) Не уверен, что «начинающему пользователю» это «ну очень надо».

@baltazorbest 15 окт 2015 в 14:34

Спасибо большое за ссылки. Крутой конечно механизм внешних источников.

@zzashpaupat 15 окт 2015 в 15:52

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

@96467840 29 окт 2015 в 14:34

я в таких случаях обычно пишу запрос в исходной БД в результате которого будет SQL запрос на вставку в другую БД. как правило 90% проблем миграции это решает. как вариант в xml формат.

@VolCh 16 окт 2015 в 03:05

В мускуле схема с двумя базами одного инстанса позволяет держать согласованность и т. п. обычными средствами, просто где-то в REFERENCE указываем не table3.field4, а database2.table3.field4. По сути это просто нэймспэйс, позволяющий структурировать таблицы, вьюхи и т. п.

@varanio 16 окт 2015 в 06:30

Ну тогда это аналог посгресового термина «схема»

@VolCh 16 окт 2015 в 08:45

CREATE DATABASE и CREATE SCHEMA в мускуле полные синонимы :)

@zzashpaupat 16 окт 2015 в 08:56

Они-то синонимы, но создается при этом база, а не схема.

@VolCh 16 окт 2015 в 11:34

А что считать базой, а что схемой?

@knutov 28 окт 2015 в 03:41

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

Вместо CTE можно использовать вложенные запросы. Выглядит синтаксически чуть иначе, разницы нет.

@Obramko 12 окт 2015 в 08:14

> Нет нормального аналога phpmyadmin. По сути, большинство известных мне крутых посгресистов работают с SQL в командной строке, к чему сложно привыкнуть поначалу. Нет, есть всякие pgMyAdmin и т.д., но каждый из них имеет какую-то ущербность. Возможно платные есть хорошие, я не проверял.

Есть pgAdmin III. Он хорош. А визуализация EXPLAIN-а — это вообще праздник.

> Транзакционность. CREATE TABLE, ALTER TABLE и т.д., как и простые запросы, можно выполнить в одной транзакции или откатить транзакцию на середине, если что-то не так.

А вот с TRUNCATE-ом не так все хорошо, что немного неочевидно. Увы.

@varanio 12 окт 2015 в 08:24

угу, есть еще c enum проблемы

@ekho 12 окт 2015 в 09:23

Некоторые возможно уже решены сторонними разработками: dklab.ru/lib/dklab_postgresql_enum

@el777 12 окт 2015 в 09:39

Использовал его во времена mysql, на постгресе как-то обходился без него.
На самом деле, даже лучше без него — определяйте значение констант на уровне приложения. Иначе каждый раз при добавлении значения вам нужно будет alter базы. Зачем это в высоконагруженной системе?

@varanio 12 окт 2015 в 09:47

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

@youROCK 12 окт 2015 в 10:40

Ну кстати в Percona (и наверное уже в MySQL) альтер таблицы с ENUM не пересобирает таблицу целиком, если вы только добавляете новое значение поля.

@deemytch 14 окт 2015 в 19:28

Я как раз первый раз собираюсь их использовать в проде. Можно ли подробности?

@varanio 14 окт 2015 в 19:45

невозможно добавить еще одно значение в тип внутри транзакции.

test=> begin; alter type mood add value 'test';
BEGIN
ERROR:  ALTER TYPE ... ADD cannot run inside a transaction block

@gubber 12 окт 2015 в 09:36

У PgAdmin, есть один существенный недостаток — не отображает clob-ы :(.

@elderos 12 окт 2015 в 09:53

Есть pgAdmin III. Он хорош.

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

@Imposeren 12 окт 2015 в 10:23

Да, когда последний раз им пользовался через двойной ssh туннель, то всё постоянно падало при первом удобном случае )=

@skobkin 13 окт 2015 в 10:53

А еще он через ssh-подключение бекап не может сделать.

@DmitryKoterov 24 окт 2015 в 22:31

Посмотрите у меня на гитхабе, там есть phpPgAdmin на стероидах — пропатченны так, что им удобнее стало пользоваться. Рекомендую в нем первым делом отключить в конфиге фреймовость — без фреймов в разы удобнее.

@DmitryKoterov 24 окт 2015 в 22:33

… я имею в виду — удобно им пользоваться для браузинга под данным в таблицах. Менять схему, конечно, как было неудобно, так и осталось — для этого лучше применять, например EMS (он работает и под wine, кстати).

@Anarchist 12 окт 2015 в 14:01

Есть еще ru.wikipedia.org/wiki/PhpPgAdmin

Но вообще, привыкнув к командной строке, я так и не смог перейти на GUI — всегда недостаточно функционала.

@Evengard 12 окт 2015 в 08:14

Посоветуйте хорошую статью по настройке pgbouncer-а, автоваккуума и проч. пожалуйста!

@itcoder 12 окт 2015 в 09:32

По поводу автоваккуума, посмотрите презентации Ильи Космодемьянского, он на последнем PgDay как раз про это рассказывал
http://pgday.ru/ru/papers/31 Так же много интересного есть в видео записях того же летного PgDay 2015. p.s надеюсь организаторы мне не сломают руку за ссылку.

@varanio 13 окт 2015 в 04:14

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

@BlessMaster 12 окт 2015 в 12:41

Вообще очень рекомендую вот эту замечательную книгу: http://postgresql.leopard.in.ua/
Помогает понять и решить очень многое.

@printercu 12 окт 2015 в 08:17

В pg_hba можно правила для всех добавить, потом только CREATE USER будет достаточно:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

@itcoder 12 окт 2015 в 08:28

не сочтите за рекламу, но альтернатива pgMyAdmin для Postgres есть в продуктах jetBreans, панельку Database если настроить,
в запросах работает и автодополнение функций, названий таблиц и полей, скорость написания запросов можно в разы повысить + сохраняется история и все под рукой.

@format1981 12 окт 2015 в 08:52

Еще можно pgAdmin использовать.

@gaelpa 12 окт 2015 в 09:29

+ фича о которой почему-то не все знают: при просмотре таблицы по F4 можно перейти от строки на связанные с ней внешними ключами (в обе стороны) строки других таблиц.
А по Ctrl+Q открывается попап со «сводкой» в виде этой строки и связанных с ней строк.

@franzose 12 окт 2015 в 22:27

Кстати, вопрос по Database в PhpStorm. Как сделать, чтобы уже созданные функции в редакторе показывали полный набор возвращаемых полей (когда возвращаем таблицу) вместо «пустого» set of record? И еще в передаваемых в функцию массивах вместо _int8 чтоб стоял указанный bigint[] и т.п…

@Honeyman 12 окт 2015 в 23:00

JetBrains начал выносить эту «панельку» в самостоятельный продукт, 0xDBE. Пока получается неплохо.

@PHmaster 13 окт 2015 в 23:11

Больше всего меня порадовал инструмент сравнения схем двух баз данных. Удобно, например, по необходимости сравнивать тестовую/девелоперскую базу с продакшеном. В результате выдается список запросов для обновления схемы продакшена. Миграции для ленивых, для небольших проектов, где нет времени/средств заморачиваться со специализированными инструментами или писать свои. Не всегда, правда, все сходу срабатывает без ручной доводки (ну например, если есть поле NOT NULL без дефолтного значения), но все же мне очень помогает.

@bRUtality 12 окт 2015 в 08:38

Про менеджеры добавлю.
Лучший из бесплатных — PgAdmin, из платных — EMS SQL Manager for PosgreSQL.
Это по моему опыту.

@Stepanow 12 окт 2015 в 08:48

EMS безумно глючный. По моему опыту. Но более функционального действительно ничего не попадалось.

@bRUtality 12 окт 2015 в 08:51

Кроме долгой прогрузки метаданных, на вскидку других претензий к нему не припомню.

@zelenin 12 окт 2015 в 10:28

а Navicat? пользуюсь — нареканий нет. (уже ниже написали)

@Pilat 12 окт 2015 в 09:24

EMS просто никакой. А вот https://www.dbvis.com/ вполне рабочий. Да и сам PgAdminIII неплох.

@Ualde 12 окт 2015 в 09:27

По-моему, из бесплатных все же лучший HeidiSQL. А из платных брали лицензию Navicat Premium.

@bRUtality 12 окт 2015 в 10:52

HeidiSQL что-то виснет у меня наглухо при попытке смотреть свойства мастер-таблицы. Подобного поведения не встречал в PgAdmin, ни в ValentinaStudio.

@faiwer 12 окт 2015 в 14:17

Попробовал HeidiSQL для Postgre… Постоянно падает. А новая версия так при этом ещё и теряет все свои конфиги (оО). Правда я под wine-ом запускаю. Попробовал pgAdmin… Не глючит, да. Но как им вообще пользоваться, пока не очень понимаю. Правда в postgreSQL я ещё совсем чайник.

@Sway 12 окт 2015 в 10:01

Лучше Navicat Premium из платных я ничего не припомню. По крайней мере в нем есть всё, что нужно и оно вполне стабильно работает, если привыкнуть к некоторым глюкам. Пробовал многие другие — намного более глючные или нет какой-то информации/функционала для продвинутого использования.
Из бесплатных — PgAdmin. Но, на сколько помню, он не умеет соединяться через ssh

@Urvin 12 окт 2015 в 13:34

Умеет, конечно же.

@Sway 12 окт 2015 в 14:05

Уже умеет =) Версия, которая шла с postgresql 9.2 не имела такого функционала (только что проверил, там по теме была только вкладка SSL). С того момента я его и не использовал.

@kolu4iy 12 окт 2015 в 08:41

А есть что почитать про автовакуумы? Мне в наследство достался zabbix на postgres, так autovacuum доставил мне много истинной боли…

@chulim 12 окт 2015 в 08:56

сталкивался с такой особенностью.
в pg нельзя изменить кодировку базы. т.е. против mysql-ного

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

в Postgres нужно сделать бекап -> создать новую базу с нужной кодировкой -> залить дамп -> грохнуть старую базу.

зы, возможно отстал от жизни, и данная проблема уже решена.

@bRUtality 12 окт 2015 в 09:04

Увы, не решена. Ставим себе в привычку указывать utf8 при создании новой базы.

@Sway 12 окт 2015 в 09:54

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

@VolCh 12 окт 2015 в 11:18

Кодировка ладно, а вот коллэйт может меняться во время жизни приложения по объективным (независящим от разработчиков) причинам, например с украинского на русский.

@Sway 12 окт 2015 в 11:24

Все-равно это остается редкой задачей. Можно разок и из дампа восстановить.

@galaxy 12 окт 2015 в 14:03

Ну это-то как раз есть: www.postgresql.org/docs/9.4/static/collation.html

@vanxant 12 окт 2015 в 19:07

Ну хз, хз. Есть исторические базы в однобайтовой кодировке. К которой, например, прикрутили новую версию софта, теперь в юникоде. И чего теперь?

@PHmaster 13 окт 2015 в 23:15

Ну типичный единичный случай. Dump-Drop-Create-Restore. Это ж не каждый день делать придется, а один раз за жизнь проекта.

@resurection 12 окт 2015 в 21:37

чтобы исправить косяк того, кто базу создавал. И это правильно. Накосячил — страдай.

Может и ALTER TABLE тоже убрать? Чтобы все всегда сразу создавали правильную структуру или страдали.

@Sway 12 окт 2015 в 22:22

ALTER TABLE вообще-то имеет кучу разных возможностей, которые нацелены в том числе на расширение существующего функционала, а не только на изменение. Кстати, на эту тему — колонку из varchar в int в postgresql переделать нельзя. Тут тот же принцип: накосячил — страдай.

Давайте на пальцах прикинем частоту использования этих конструкций и их альтернативы:
1. ALTER DATABASE databasename CHARACTER SET enc COLLATE collation — 0.01% (хотя меньше). Для этой задачи есть альтернативный способ решения без потери данных. Трудоемкость способа небольшая — около 4х команд (дамп, удаление бд, создание новой, восстановление из дампа), хотя затраченное время сильно зависит от объема БД. Вероятно, реализация этой команды будет затрачивать аналогичное количество времени.
2. ALTER TABLE table и т.д. — 99.99%. Альтернативный способ слишком трудоемкий для задачи, которая так часто используется.

Вывод: ALTER DATABASE databasename CHARACTER SET enc COLLATE collation — очень редко используемый функционал, требующий довольно сложной реализации (если бы было все так просто — давно бы уже сделали, не из вредности же разработчики не делают его).
Приговор: нет смысла тратить время на его реализацию.

Так понятнее моя точка зрения?

@galaxy 12 окт 2015 в 22:38

Кстати, на эту тему — колонку из varchar в int в postgresql переделать нельзя

Можно:

alter TABLE table_name ALTER COLUMN column_name TYPE int USING column_name::int;

Если, конечно, данные сконвертятся.

@Sway 12 окт 2015 в 22:51

Хм… Похоже, мне пора освежить свои знания о postgresql…

@medvoodoo 12 окт 2015 в 09:17

Не написали про обязательное приведение типов при работе в постресе(если чар сравнивать с интом будет ошибка, нужно делать что-то типа char_value::int = int_value)

В select id, region from table1 group by region будет ошибка, т.к. id он однозначно выбрать не может.

Две особенности, с которыми столкнулся на заре моей любви к посгрес.

p.s. Хранимки на python рулят :)

@varanio 12 окт 2015 в 09:19

насчет group by, вроде как в mysql 5.7 сделали такое же поведение по умолчанию

@Artiomtb 12 окт 2015 в 09:26

Одно из преимуществ postgres — наличие GIN/GIST индексов, которые позволяют искать в текстовом поле по условию like '%some text%'.

Необходимо под суперпользователем активировать расширение:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Пример создания индекса для поля product_name таблицы products:

CREATE INDEX products_name_index ON products USING GIN (lower(product_name) gin_trgm_ops);

Теперь, при запросе

SELECT * FROM products where lower(product_name) like '%test%'

будет использоваться индекс products_name_index.

По личным замерам в таблице на 10 млн строк поиск без индекса выполнялся около 2с, с индексом — 0.1 сек.

P.S. можно вообще использовать тип поля citext, который хранит без учета регистра (полезная фича для хранения e-mail и т.д.). Тогда в индексе и запросе lower вообще не нужен.

@maxru 12 окт 2015 в 09:32

Нет нормального аналога phpmyadmin.

И не нужен, есть EMS SQL Manager.
Веб-морда к БД в продакшене это что-то с чем-то.

@el777 12 окт 2015 в 09:41

Из платных клиентов еще NaviCat хорош.

@TerminusMKB 13 окт 2015 в 08:47

Вот для себя так и решил. Для удобной работы с данными — Navicat, для администрирования, просмотра статистики и анализа планов запросов — pgAdmin

@VolCh 12 окт 2015 в 09:47

Кроме функциональных индексов хорошая фича — частичные индексы. А уж если часто нужно выполнять запросы типа… WHERE is_active = 1 AND func(field1, field2) = value то мускул отдыхает вообще. Или когда нужно ограничение на уникальность не глобально на всю таблицу, например, номер счёта должен быть уникальным только среди выставленных и не отмененных счетов, а в черновиках и отмененных может быть любым — на мускуле такую задачу решить можно только на уровне приложения, а база не поможет.

@romy4 12 окт 2015 в 09:48

INSERT IGNORE и INSERT ON DUPLICATE KEY UPDATE

я даже на мускуле отказался от этой фичи для сохранения неразрывности последовательности AUTOINCREMENT ID

Нет нормального аналога phpmyadmin.

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

@SamDark 12 окт 2015 в 10:24

А зачем вам неразрывность последовательности AUTOINCREMENT ID?

@romy4 12 окт 2015 в 15:52

Для предварительной генерации чего-нибудь, когда знаешь, что выбирая WHERE id > 50 LIMIT 50 у тебя точно будут id с 51 по 100.

@Fesor 12 окт 2015 в 18:31

Плохая это практика, завязываться на ID. Да и потом откатываем транзакцию и мы уже потеряли следующий ID.

@romy4 13 окт 2015 в 06:18

На MyISAM нет транзакций.

Минусующие — идите в жопу. Не зная задачи — не понимаете, почему выбран метод из «плохой практики».

@VolCh 13 окт 2015 в 07:53

Транзакции могут быть на уровне приложения.

@Bozaro 13 окт 2015 в 08:37

В контекте ACID, как минимум Durability требует поддержки со стороны СУБД.

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

@Envek 13 окт 2015 в 11:14

До тех пор, пока вы не объясните свою задачу и не обоснуете выбор «плохой практики», вас так и будут минусовать. За необоснованный выбор «плохой практики».

@antage 12 окт 2015 в 09:52

Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить

Точно такая же ситуация и с mysql. Так что весьма странно записывать это в недостатки postgresql.

@SamDark 12 окт 2015 в 10:25

Ну не… MySQL из коробки заводится даже на самом ужасном железе. Если взять MariaDB, так и того лучше. Тюнить если и надо, то по минимуму.

@zelenin 12 окт 2015 в 10:34

postgres аналогично заводится. речь о том, чтобы выжать максимум производительности по сравнению с mysql.

@SamDark 12 окт 2015 в 10:45

У меня с дефолтами не влез по памяти на довольно ущербной железяке…

@zelenin 12 окт 2015 в 10:51

крутится несколько проектов на самом дешевом тарифе от digitalocean. Еще ущербнее железяка?

@SamDark 12 окт 2015 в 12:22

М… нет :)

@antage 12 окт 2015 в 10:44

Ну так и postgresql заводится с настройками из коробки. На низких нагрузках любая база заводится из коробки. Но автор говорил про большие нагрузки:

Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать.

А для серьезных нагрузок на mysql нужно настраивать, как минимум, key_buffer (для myisam, по дефолту 8МБ), query_cache_size (кэш запросов запрещен по дефолту), innodb_buffer_pool_size (для innodb, по дефолту 8МБ), innodb_flush_log_at_trx_commit.

@mtyurin 12 окт 2015 в 09:57

for update skip locked

@mtyurin 12 окт 2015 в 09:58

postgresql — никому не принадлежит, и полный порядок с комунити, нет разброда с форками

@kaamos 12 окт 2015 в 10:29

Михаил, я этот FUD довольно часто слышу от представителей Postgres Community, но никогда не мог добиться ответа на два вопроса:

1. В чём собственно заключается «разброд»? Например, общее количество дистрибутивов Linux даже подсчёту не поддаётся, но никто не говорит, что это плохо (кроме адептов BSD, конечно ;). Говорят, что это хорошо, open source, экосистема, конкуренция и вот это всё.

2. Где собственно «форки» MySQL. Единственным настоящим форком является MariaDB. Всё остальное (собственно, WebscaleSQL и Percona Server) являются скорее набором расширений, т.к. 1) постоянно и регулярно объединяют upstream со своими патчами и 2) предлагают патчи для включения в upstream.

@mtyurin 12 окт 2015 в 13:52

так так, вы линукс то с майсиквелем не ровняйте)

разброд:
1) отсутствие тру бинарной репликации из-за архитектуры «плагабл» движков
2) хватит первого

форки — да ну ладно, их точно более двух, плюс движки. ну и «постоянно и регулярно объединяют upstream» как-то не очень звучит

@kaamos 12 окт 2015 в 14:13

Извините, но это не ответ. Вы мне сейчас пересказываете доклад Олега Царёва, я бы рад поговорить про этот доклад, но это уход от темы.

Мы обсуждаем разброд форков. Значит «разброд» — это отсутствие бинарной репликации. Допустим, но я стесняюсь спросить, а причём тут форки-то? Есть форки, где эта проблема решена?

И собственно форки. Мы насчитали только MariaDB, но вообще их «точно больше двух»? Про регулярное обновление, как я могу вас убедить? Вот, например, Percona Server версии x.y.z выходит после релиза MySQL x.y.z не позднее, чем через месяц. И да, в нём содержится весь функционал MySQL x.y.z + все перконовские патчи. Это достаточно убедительно?

@mtyurin 12 окт 2015 в 15:31

это ответ. в субд нет бинарной репликации.

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

@kaamos 12 окт 2015 в 15:35

Мда. Я, признаться, был лучшего мнения. Бинарная репликация есть, конечно, но дело и не в этом. В чём «разброд форков» мы так и не выяснили. Ну и ладно.

PS. Кому-то не принять патч от кого-то конечно никто не мешает. Так же как Fedora не обязана быть стопроцентной копией Debian или ArchLinux. Однако почему это не проблема для Linux, но вселенская проблема для MySQL, я тоже так и не понял.

@mtyurin 12 окт 2015 в 15:51

так. форков точно не два. есть как минимум oracle / maria / percona — в подавляющем числе случаев это разные ветки. при этом это разные компании — они конкурируют, их цели вообще говоря могут пересекаться, а с учетом маневров оракла, это всё выглядит так себе. эти компании в том числе двигают разные движки (еще один привет бинарному логу).

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

а бинарная репликация — просто яркая особенность, одна из.

@kaamos 12 окт 2015 в 16:04

Я уже объяснил, что MariaDB — это единственный «форк» MySQL, то есть проект, который развивается независимо. Ни Percona, ни WebscaleSQL форками не являются, они жёстко привязаны к Oracle MySQL (вы посмотрите что ли в мой профиль).

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

Ничего не понял, но пожалуйста, примеры «форков» из прикладного ПО, которые почему-то никому не мешают: OpenOffice/LibreOffice, GNU Emacs/XEmacs/Aquamacs, Konqueror/WebKit/Chrome.

Это, подчеркну, примеры тру форков, то есть когда-то код был общий, но после какой-то точки проекты развиваются независимо. Чего точно нельзя сказать про Percona и WebscaleSQL.

Чувствую, мне пора писать первый пост на Хабр. «Как правильно критиковать MySQL». Его есть за что критиковать, но то, как вы это делаете — хочется обнять и плакать, честное слово. Нельзя критиковать, просто повторяя где-то услышанные мантры, надо ж разбираться в предмете.

@mtyurin 12 окт 2015 в 16:44

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

@kaamos 12 окт 2015 в 17:02

Хорошо, у меня персонально для Вас, уважаемый Михаил, есть пуленепробиваемый пример форков. Вот, говорят, список бывших и ныне живущих «форков» и дистрибутивов PostgreSQL: wiki.postgresql.org/wiki/PostgreSQL_derived_databases

В этом контексте хотелось понять: почему форки PostgreSQL — это «полный порядок с комунити, нет разброда с форками», а форки MySQL — это таки «разброд», ужас, нет бинарной репликации, патчи не примут, звучит как-то не очень, выглядит так себе, и т.д.?

@mtyurin 12 окт 2015 в 17:10

тааак, отлично, вернулись к базам от текстовых редакторов.

то, что вы привели — это «derived forks and rebranded distributions» это не альтернативные ветки postgres а (bsd лайк лицензия располагает). никто не купил postgres (нечего покупать) и не стал в защиту делать maria_pg.

@kaamos 12 окт 2015 в 17:16

Так вот, теперь, когда мы говорим на одном языке, MariaDB — это «derived fork». А WebscaleSQL и Percona Server — это «rebranded distributions». Что с ними не так и чем они отличаются от 43 СУБД из того списка?

Да, PostgreSQL не принадлежит коммерческой организации. Но дальше-то что?

@mtyurin 12 окт 2015 в 17:27

> MariaDB — это «derived fork»

угу, вы это им расскажите, интересный будет разговор.

mariadb.org/en/about
«MariaDB An enhanced, drop-in replacement for MySQL»

и отказ от innodb

@kaamos 12 окт 2015 в 17:40

Я не вижу противоречий между «derived fork», «enhanced drop-in replacement». Первый описывает происхождение проекта, второй — краткое описание самого проекта.

Никакого отказа от InnoDB в MariaDB нет. Можно использовать XtraDB или InnoDB на выбор. Где вы все эти сплетни собираете?

@mtyurin 12 окт 2015 в 17:55

да какие сплетни? одно делается в замену другому: не как отнаследуюсь и что-то добавлю, а как замена.

@kaamos 12 окт 2015 в 18:01

Ох, XtraDB — это «derived fork» и «ennhanced drop-in replacement» InnoDB. Унаследованный, да. Это я вам как разработчик XtraDB говорю.

Кстати, у меня теперь отрицательная карма, какой-то поклонник PostgreSQL постарался. Я не знаю, как здесь всё работает, но по-моему посты с отрицательной кармой писать нельзя. Печаль…

@mtyurin 12 окт 2015 в 19:39

разработчик XtraDB с минусовой кармой на хабре… ) хабр — это нечто, болотце порядочное)

ну ок. вы, так сказать, лицо заинтересованное, ну ладно, допустим. возможно у вас там так принято считать и «drop-in replacement» это как бы без конфликтов.

но! mariadb.com/kb/en/mariadb/using-innodb-instead-of-xtradb майсикуель прекрасен конечно и вот как это идет на пользу? мы не будем за стабильность, давай rps тут, а стабильность там, и не одно с другим, а одно за место другого?!

@kaamos 12 окт 2015 в 19:52

Я не очень понял вопрос. Вот есть InnoDB. И есть XtraDB = InnoDB + delta. Разработчики MariaDB предлагают выбор: по умолчанию XtraDB, но если пользователь хочет, он может перейти на ванильный InnoDB одной строчкой в конфиге.

Там не написано, что InnoDB — это только стабильность, а XtraDB — это только performance. Там перечислены теоретические причины, по которым этот выбор вообще может понадобиться.

@mtyurin 12 окт 2015 в 20:21

теоретические, да. тем более интересно, если это касается top critical части субд, теоретические рассуждения на этот счет. может самолет и упадет, если быстро полетим, а может и нет, сложно сказать.

@kaamos 12 окт 2015 в 20:25

Ну так и самолёты падают. И в PostgreSQL баги бывают, да? Я вот попользвался PostgreSQL 5 минут и сразу нашёл баг. Правда я знал где искать ;)

@mtyurin 12 окт 2015 в 20:34

> попользвался PostgreSQL 5 минут и сразу нашёл баг

«самолет упал» и вы не смогли восстановиться?
какой баг?

@kaamos 12 окт 2015 в 20:57

Да вот же:

test=# CREATE TABLE d1 (d FLOAT);
CREATE TABLE
test=# INSERT INTO d1 VALUES (1.7976931348623157E+308);
INSERT 0 1
test=# SELECT * FROM d1;;
           d
-----------------------
 1.79769313486232e+308
(1 row)

test=# INSERT INTO d1 VALUES (1.79769313486232e+308);
ERROR:  "179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" is out of range for type double precision

Это, чтобы было понятно, я записываю правильное FLOAT число. Оно сохраняется. Но при попытке его прочитать PostgreSQL печатает его неправильно. И сам же его не может импортировать.

То есть, вставил число, сделал дамп базы, а дамп потом не импортируется. Круто? :) Я в курсе про extra_float_digits — оно исправляет одни случаи, и ломает другие. Мы в MySQL это давно проходили и исправили. Поэтому я знал, где копнуть :)

@mtyurin 12 окт 2015 в 21:20

> я записываю правильное FLOAT число. Оно сохраняется.

www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-FLOAT
8.1.3. Floating-Point Types

это «фича», всё описано, без всяких «может упадет»

www.postgresql.org/docs/9.4/static/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS

this is especially useful for dumping float data that needs to be restored exactly

@kaamos 12 окт 2015 в 21:33

Да я понимаю, что «документировано». Но invalid data, как ни крути.

И в MySQL этот test case работает без всякого подкручивания конфига А если в PostgreSQL я выставлю extra_float_digits, то получу правильные значения для одних чисел и «мусор» для других. И как быть, если чисел у меня много?

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

@mtyurin 12 окт 2015 в 21:42

invalid data — даже сессия не упала, не то что движок! и о потери базы целиком никто даже не говорит.

> PostgreSQL не очень разобрались в предмете

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

@mtyurin 12 окт 2015 в 21:44

а на счет флоатов — это как бы сразу скользкая тема, хранить их в базе в таком виде, да еще и на граничных величинах — весьма сомнительное занятие.

@mtyurin 12 окт 2015 в 21:38

set extra_float_digits = 3 на сессию дампа и всё работает

@kaamos 12 окт 2015 в 22:19

Увы и ах, печатать больше чем DBL_DIG (15) цифр нельзя. Почитайте определение DBL_DIG. То есть, extra_float_digits — это костыль, чтобы исправить один случай и сломать другие.

@mtyurin 12 окт 2015 в 22:49

что же там ломается?

а дамп по дефолту в pg юзает доп цифры
http://www.postgresql.org/message-id/flat/20090909185152.GA7893@kehcheng.Stanford.EDU#20090909185152.GA7893@kehcheng.Stanford.EDU

вот пример комунити — написал, разобрали

@kaamos 13 окт 2015 в 06:33

Ну, то есть pg_dump вовремя подставляет костыль. Что конечно замечательно, но:

1. невалидные данные с extra_float_digits остаются невалидными данными
2. костыль остаётся костылём (подробнее ниже, там ещё один товарищ жаждет объяснений)
3. одними дампами проблема не ограничивается. Ну, например, с дефолтным extra_float_digits:

est=# create table d2(a float8, b float8);
CREATE TABLE
test=# insert into d2 values (2, 2.0000000000000004);
INSERT 0 1
test=# select * from d2;
 a | b
---+---
 2 | 2
(1 row)
test=# select a = b from d2;
 ?column?
----------
 f
(1 row)

Может для PostgreSQL это нормально, но MySQL себе такого не позволяет.

> вот пример комунити — написал, разобрали

Мне вот интересно, вы действительно считаете, что в MySQL community нет списков рассылок, форумов и прочего, где пользователям отвечают на вопросы?

@galaxy 12 окт 2015 в 23:31

То есть, вставил число, сделал дамп базы, а дамп потом не импортируется. Круто? :)

Тестировали или умозрительно злорадствуете? Все импортируется.
А здесь тоже тупой постгрес виноват:

postgres=# select 2.1::real - 2;

      ?column?
--------------------
 0.0999999046325684

Мы в MySQL это давно проходили и исправили

Ну расскажите нам, как и что вы исправили

@kaamos 13 окт 2015 в 07:12

Ну расскажем, чего уж там.

> Тестировали или умозрительно злорадствуете? Все импортируется.

Да, мы с Михаилом уже выяснили, что pg_dump подставляет костыль. Но всё сложнее, см. ниже.

> А здесь тоже тупой постгрес виноват:

Нет, здесь виноват злой стандарт IEEE 754, который не имеет точного бинарного представления для десятичных чисел 2.1 или 0.1. Соответственно, мы видим округлённую бесконечную двоичную дробь. Причём в силу потери точности результат для ::real выглядит хуже, чем для ::float8. Но это речь не об этой проблеме.

Проблема заключается в том, что стандарт IEEE 754 (точнее, выбранный формат данных) определяет максимальное число значимых цифр для каждого типа данных, для которых гарантируется преобразование без потерь из десятичного (строкового) представления во внутреннее (бинарное) и обратно. Для double precision aka float8 это 15 значимых десятичных цифр (константа DBL_DIG). Для чисел с бОльшим количеством значимых цифр преобразование туда-обратно может быть без потерь, а может быть и с потерями — это зависит от числа и деталей реализации, стандарт ничего не гарантирует.

Вот когда PostgreSQL работает DBL_DIG (т.е. с extra_float_digits=0), то он может печатать невалидные данные в разных граничных случаях, или просто работать неинтуитивно с точки зрения приложений, как я показал на примерах.

А когда он начинает печатать все числа с DBL_DIG + extra_float_digits, он нарушает стандарт. Это может выражаться как в относительно безобидных вещах, типа:

test=# select 0.3::float8;
        float8
----------------------
 0.299999999999999989

(технически ответ верный, но мог бы показать более короткое эквивалентное представление: 0.3)

или

test=# select .1::float8;
        float8
----------------------
 0.100000000000000006
(1 row)

(здесь ответ верный до DBL_DIG значимых цифр, но потом идёт «мусор» в виде 006)

Кстати, вот этот «мусор» за пределами 15 значимых цифр имеет неопределённое содержимое, т.е. зависит от реализации, ОС, версии libc, процессора и флагов компиляции.

Этот мусор может иметь и более опасные последствия. Поскольку стандарт не гарантирует преобразование без потерь при печати чисел с числом десятичных цифр > DBL_DIG, данные могут искажаться при экспорте/импорте. Например, сделали дамп гео данных на Linux/Intel, а импортировали на Solaris/Sparc. Данные «чуть-чуть» уплылили. А потом ещё «чуть-чуть». А потом самолёты начинают падать ;)

> Ну расскажите нам, как и что вы исправили

MySQL для чисел с плавающей точкой работает так:

1. Выбирает наиболее короткое представление («0.3» напечатается как «0.3», а не как «0.299999999999999989»)
2. Избегает «мусора», т.е. любая напечатанная цифра является значимой и не зависит от ОС/libc/процессора и т.д. («0.1» напечатается как «0.1»
3. Автоматически выбирается «безопасное» для преобразования без потерь количество десятичных цифр. Нет никаких костылей типа extra_float_digits. Если число напечатано, значит MySQL сможет его считать обратно при любой конфигурации, ОС, libc, процессора и флагов компиляции.

Ну как-то так.

@Vayun 13 окт 2015 в 08:01

Этот мусор может иметь и более опасные последствия. Поскольку стандарт не гарантирует преобразование без потерь при печати чисел с числом десятичных цифр > DBL_DIG, данные могут искажаться при экспорте/импорте.

Стандарт гарантирует минимум 3 лишних цифры, но рекомендует не иметь таких ограничений вообще.

Мусор никому не мешает, до тех пор пока «Conversion to an external character sequence must be such that conversion back using round to even will recover the original number.»

@kaamos 13 окт 2015 в 08:27

> Мусор никому не мешает, до тех пор пока «Conversion to an external character sequence must be such that conversion back using round to even will recover the original number.»

О том о речь. «До тех пор, пока», то есть undefined behavior, на которое PostgreSQL расчитывает при экпорте/импорте. И где-то как-то оно работает, пока не случится ой.

@Vayun 13 окт 2015 в 09:02

«До тех пор, пока» это часть стандарта, так что никакого undefined behavior нет.

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

Если мы хотим однозначное соответствие между бинарным и десятичным представлениями, то выводить надо DBL_DIG+2=17 цифр.

Никакого undefined behavior, все по стандарту, просто значение по умолчанию реализует одно поведение, если надо другое ставим extra_float_digits=2 и все.

@kaamos 13 окт 2015 в 09:47

> Разница в том что если мы хотим видеть только достоверные десятичные цифры, то выводить надо DBL_DIG=15 цифр.

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

> Если мы хотим однозначное соответствие между бинарным и десятичным представлениями, то выводить надо DBL_DIG+2=17 цифр.

Не существует никакого однозначного соответствия для DBL_DIG+2=17 цифр. А PostgreSQL вообще использует DBL_DIG+3=18. Оно впрочем и для DBL_DIG не существует, это не о том.

> Никакого undefined behavior, все по стандарту, просто значение по умолчанию реализует одно поведение, если надо другое ставим extra_float_digits=2 и все.

Нет, не всё. Почитайте определение FLT_DIG/DBL_DIG. И ещё почему PostgreSQL сначала использовал extra_float_digits=2, а потом вдруг стал extra_float_digits=3 при экспорте. Тут ссылка уже была.

@Vayun 13 окт 2015 в 10:39

Не существует никакого однозначного соответствия для DBL_DIG+2=17 цифр. А PostgreSQL вообще использует DBL_DIG+3=18. Оно впрочем и для DBL_DIG не существует, это не о том.

Согласен, «однозначное соответствие» неудачно сказано. Имелось ввиду что из DBL_DIG+2=17 цифр можно однозначно получить оригинальное бинарное преставление (десятичное представление в этом случае не однозначное). Для double разницы между 17 и 18 нет (IEEE754 гарантирует правильное округление до 20 цифр включительно)

Нет, не всё. Почитайте определение FLT_DIG/DBL_DIG. И ещё почему PostgreSQL сначала использовал extra_float_digits=2, а потом вдруг стал extra_float_digits=3 при экспорте. Тут ссылка уже была.

Использует extra_float_digits=3 потому что FLT_DIG=6, а для однозначной конвертации надо 9 (что есть в IEEE754 кстати). По-хорошему нужны отдельные extra_float_digits для double и float.

В общем, в Postgres решили что длина представления должна быть фиксированной. Поэтому она получается разной в зависимости от того хотим мы видеть только верные десятичные цифры (DBL_DIG) или хотим иметь возможность восстановить оригинальное бинарное представление (DBL_DIG+2). Оба свойства одновременно можно получить только используя представления переменной длины.

Фиксированная длина, это возможно не оптимальное решение, и защищать я его не собираюсь. Моя критика была направлена на необоснованное использования термина «undefined behaviour», этого в Postgres нет.

@kaamos 12 окт 2015 в 20:05

И отдельным комментом про подключаемые движки (раз пост мне не дают написать). Это безусловно усложняет архитектуру, но есть и плюсы. Разные движки заточены под разные цели, нельзя иметь один единственный «православный» движок, который оптимален на любых нагрузках.

Например, сильной стороной InnoDB как и всех движков на B-Tree (включая PostgreSQL) является primary key lookups на полностью закешированных данных. На таких нагрузках вряд ли PostgreSQL сможет соревноваться с InnoDB, хотя было бы интересно проверить.

Слабая сторона B-Tree движков — интенсивная запись в базу, особенно когда dataset не умещается в память. Особенно когда много индексов, или нужна компрессия. Для таких нагрузок какой-нибудь TokuDB покажет результаты, которые не снились ни InnoDB, ни PostgreSQL.

Ещё раз, тезисно: 1) нет идеального способа хранить данные; 2) MySQL предлагает варианты с помощью движков, PostgreSQL — нет. 3) да, возможность иметь подключаемые движки имеет свою цену. Архитектура усложняется, сервер не всегда может «срезать углы», как если бы он работал с одним движком. Да, приходится дублировать журналы. Нет, это не так плохо, как рассказывает Олег в презентации :)

@varanio 12 окт 2015 в 20:29

я не очень понимаю, при чем тут движки. Postgres не предлагает варианты с помощью движков, но он предлагает кучу видов индексов, не только b-tree (еще Hash, GiST, SP-GiST, GIN)
К примеру, для задачи, где нужно много записи и индексы занимают много места — в 9.5 вводят индекс нового типа BRIN. Почти бесплатен при записи, но хуже на чтении.
Кроме того, насколько я понимаю, в посгресе можно кластеризовать таблицу под конкретный индекс. Это то, что в innodb сделано для primary key

@kaamos 12 окт 2015 в 20:42

Ну разные типы индексов — это только часть решения. В MySQL/InnoDB тоже предлагает специализированные индексы, хоть и в меньшем количестве.

Hash, GiST, GIN вам никак не помогут при интенсивной записи. Если BRIN помогает — хорошо, но вот TokuDB умеет ещё компрессию. Причём алгоритмы компрессии (surprise!) тоже pluggable. Хочется сильнее сжимать но медленние — есть zlib. А хочется слабее, но быстрее — есть quicklz. А можно вообще свой алгоритм добавить. Как-то так. Это я ещё не касаюсь сильно специализированных движков типа NDB.

По поводу кластерных индексов — если я правильно читаю документацию, в PostgreSQL это «одноразовая» операция. Можно кластеризовать готовые данные, но новые данные будут храниться опять как попало. В InnoDB данные кластеризованы всегда. А в TokuDB вообще можно иметь несколько кластерных ключей одновременно. Опять же, такое никак не сделать добавлением нового типа индекса, да?

@mtyurin 12 окт 2015 в 21:04

ну в целом понятно, удел движков — кеши, ок

@mtyurin 12 окт 2015 в 20:31

бррр… само по себе «подключаемое» это очень хорошо. ( постгрес весь максимально плагабл, но без ущерба (как минимум, без отсутствия контроля над) acid-у в узле.)

но блин! если вы при этом получаете ограничение на утилизацию железа, тем что реплика не успевает проигрывать транзакциии! как же это если, это не ужасно?

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

@kaamos 12 окт 2015 в 20:47

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

— в базе данных живут гномики;
— в огороде бузина, а в MySQL — binary log
— у нас в проекте тормозила репликация
— у нас в проекте репликация CPU-bound, а значит у всех должна быть такой
— мы взяли альфа-версию MySQL 5.7, которая должны была решить наши проблемы
— но она не решила
— почему — мы так и не разобрались
— вопросы?

Нет, правда.

@mtyurin 12 окт 2015 в 20:54

да, всё верно. печально. и то что мария вместо решения всего этого предлагает еще больше rps (хотя cpu уже и так нет) но при этом теоретически больше краша (а еще страшнее, что без возможности восстановления) я называю «разброд»

@kaamos 12 окт 2015 в 21:02

Я к тому, что из того доклада вообще ничего не понятно. Ни в чём конкретно была проблема, ни что было сделано для решения.

@mtyurin 12 окт 2015 в 21:06

ничего не было сделано, где комунити? (разброд: это не мое, то не сё, а там оракл, поддержки нет, все хотят денег) у человека одна из крупнейших в европе систем была.

@kaamos 12 окт 2015 в 21:14

А я откуда знаю, где комунити? С крупнейшей-то в европе системой можно было и у Перконы поддержку купить. Но нет

@mtyurin 12 окт 2015 в 21:29

вот именно, хрен кто что будет развивать. плати бабки — вот это и разброд, другой подход к снаряду, люди по другому видят развитие программного продукта и место коммунити в нём.

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

@kaamos 12 окт 2015 в 21:38

Ну это уже какие-то фантазии пошли. «бы», да «кабы». Несерьёзный разговор.

@mtyurin 12 окт 2015 в 21:45

а какой может быть серьезный, если его и не может быть… так как полноценного открытого коммунити нет

@kaamos 12 окт 2015 в 21:54

Михаил, вы сначала вот сюда гляньте: http://db-engines.com/en/ranking

А потом возвращайтесь, поговорим про community.

@mtyurin 12 окт 2015 в 21:59

а вы потом удивляетесь, почему у вас на хабре минус в рейтинге? там плюс, тут минус)

@mtyurin 12 окт 2015 в 22:05

как раз вы начали монгу щас встраивать, всё в тренде)

@mtyurin 14 окт 2015 в 08:39

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

дак вот. и у кого мне покупать: перкона / оракл? и в чьи листы мне писать: мария / перкона / оракл / монга / токудб / etc etc? где ядро разработчиков сидит, в каком листе, у оракла или у марии?

далее, что мне там обсуждать: как ФЛОАТЫ на РАВЕНСТВО = сравнивать или что бинарная репликация sql тредом обрабатывается?

вот и думаю…

@kaamos 14 окт 2015 в 08:51

Да напишите хоть в какой-нибудь уже.

А так да, есть несколько компаний, которые поддерживают списки рассылки и форумы. Причём, в список рассылки Percona или MariaDB можно задавать вопросы не только по продуктам Перконы, но и по Oracle и MariaDB. И в этих списках отвечают на вопросы абсолютно бесплатно. Я лично отвечал.

А ещё есть локальные группы пользователей. Например, московская. Со своим списком рассылки, где не очень большой трафик, но на вопросы отвечают довольно оперативно. Я лично отвечал тоже, да. Безвомездно, то есть даром.

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

@svetasmirnova 14 окт 2015 в 20:03

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

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

Что мы советуем для текущих версий?

Во-первых, единственный параметр, который не может здесь играть роли — это innodb_thread_concurrency. Все остальные performance-related параметры вполне tunable.

Далее, если слейв read-only, можно отключить innodb_doublewrite_buffer, sync_binlog, xa транзакции, поставить innodb_flush_log_at_trx_commit в 0 и сделать ещё ряд подобных вещей, которые, строго говоря, не рекомендуются, так как могут привести к потере данных. Но так как данные у нас есть где-то ещё (а бинарные логи ~~нужно~~можно бэкапить при помощи mysqlbinlog --raw ..., а не рабочего слейва) — это можно пережить.

Плюс есть ещё параметры типа innodb_io_capacity, которые нужно ставить в то, что написано в мануале (реальную скорость диска), а не от балды и innodb_flush_method, который таки лучше потестировать в конкретном окружении после того, как его выставили в O_DIRECT

Это для слейва, с которого не читают.

Для слейва, с которого активно читают, innodb_thread_concurrency тоже будет работать. Для запросов на чтение.

@kaamos 14 окт 2015 в 20:45

Света, объясни мне, пожалуйста, как всё вышеперечисленное может повлиять на CPU-bound нагрузку, которую здесь неоднократно упомянули?

@svetasmirnova 14 окт 2015 в 22:02

Параллельности тут не выжмешь. Зато можно снизить нагрузку на CPU. Те же disk writes — они требуют CPU. То есть будет работать также одно ядро, но будет работать меньше.

@kaamos 14 окт 2015 в 08:58

> далее, что мне там обсуждать: как ФЛОАТЫ на РАВЕНСТВО = сравнивать или что бинарная репликация sql тредом обрабатывается?

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

Вы хоть книжки почитайте по теме. Вот эта хорошая: «What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg».

@mtyurin 14 окт 2015 в 10:12

> флоаты на равенство сравнивать можно. результаты вычислений нельзя. это вы слышали звон, но не очень в курсе где он.

а как мне понять, какой флоат более результат, чем другой)

и как там всё таки про рассылки, где правильная, можно всё таки пояснить? гугл мне как минимум дает майсиквеловские и мариишные листы

@kaamos 14 окт 2015 в 10:37

> а как мне понять, какой флоат более результат, чем другой)

вас вот что смутило в моём примере. PostgreSQL показывает два _разных_ бинарных флоата как одинаковые числа. Поэтому сравнение выдаёт 'false', что неинтуитивно. MySQL разные _бинарные_ числа всегда показывает разными. Поэтому и результат сравнения вопросов не вызывает.

> и как там всё таки про рассылки, где правильная, можно всё таки пояснить? гугл мне как минимум дает майсиквеловские и мариишные листы

Ну кому вы больше доверяете, туда и пишите. Возвращаясь к примеру с дистрибутивами Linux, вот есть у вас проблема с Linux kernel. Можно написать в LKML. А можно в список рассылки своего дистрибутива. А можно в местную группу пользователей. Или везде сразу.

@mtyurin 14 окт 2015 в 14:19

> PostgreSQL показывает два _разных_ бинарных флоата как одинаковые числа

чтобы понять, что значения «одинаковые» их надо сравнить. «одинаковость» — это не текстовое представление глазами сверять. ох, как с вами тяжело.

когда мы работаем с такими сложными объектами как флоаты, ни о какой интуитивности (они на то и сложные, что не интуитивные) речи быть не может. особенно, когда эти флоаты поедут дальше и потом приедут обратно.

> Ну кому вы больше доверяете

@kaamos 14 окт 2015 в 14:25

> чтобы понять, что значения «одинаковые» их надо сравнить. «одинаковость» — это не текстовое представление глазами сверять. ох, как с вами тяжело.

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

@mtyurin 14 окт 2015 в 14:31

он показывал, что они разные
select a = b from d2;
?column?
f

false

@kaamos 14 окт 2015 в 14:38

«select a = b» сравнивает бинарные представления. А текстовые представления в PostgreSQL одинаковые. А в MySQL бинарные числа разные, и текстовые представления разные. Так понятно?

@mtyurin 15 окт 2015 в 21:21

да, одинаковые. надеюсь вы не всерьёз обсуждаете сравнение флоатов по текстовым представлениям. DBL_DIG, мы на второй круг. про «интуитивно для приложения» это вы рассказывайте кому-то другому.

@kaamos 16 окт 2015 в 07:35

Вы лучше попробуйте ответить на вопросы зачем вообще нужен extra_float_digits и почему extra_float_digits=3 не включен по умолчанию, если это единственный режим, в котором PostgreSQL печатает корректные значения. Для себя ответить, не для меня. Я ответ знаю.

@mtyurin 16 окт 2015 в 08:54

ок! за вас можно только порадоваться.

а для тех, кто еще сомневается в своих знаниях, рекомендую раздел из одной, пожалуй лучшей в мире, документации:
http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-FLOAT

и выдержка про «inexact»

The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies.

ну и смысл: связались с флоатами — вы значит крутые уже — сами знаете, что делаете, НЕТ там ничего «интуитивного» и всё может зависеть

@kaamos 16 окт 2015 в 09:13

Да. Только это всё не о том, и никак не отвечает на заданные мною вопросы.

@mtyurin 14 окт 2015 в 14:25

> Ну кому вы больше доверяете

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

а по поводу линукса — я просто беру lts ядро и дебиан, и воспринимаю это как то, что надо резервировать. известных багов fsync за всё время использования не наблюдал.

@zabivator 14 окт 2015 в 09:58

Алексей, спасибо за краткий пересказ моего доклада, я буду это цитировать.

@kaamos 14 окт 2015 в 10:07

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

@zabivator 14 окт 2015 в 10:13

Ну, если так, то странно почему не упомянут разбор различных типов журналов (логический/физический), их компаративный анализ, а также бенчмарк MySQL 5.7

В причинах тормозов не разобрались — вы правы, однако у меня задачи в проекте стояли несколько отличные от «починить mysql».
И я до сих пор очень хочу увидеть исследование LOGICAL_CLOCK в 5.7, которое:
— показывает существенный прирост производительности репликации
— указано железо и настройки
— указаны числа в эксперименте

Сейчас 5.7 выглядит странно — вроде есть крутая фича, которая должна давать практически линейный прирост, но по факту я его даже измерить не смог

@svetasmirnova 13 окт 2015 в 18:11

Реплика не поспевает не из-за движков, а потому что по умолчанию у неё только один SQL thread. Соответственно InnoDB и прочие не могут полностью утилизировать CPU (при обработке одного лишь SQL thread-а).

@mtyurin 14 окт 2015 в 08:17

вот я всё понимаю (на самом деле, видимо больше, чем говорю тут), но КАК могут быть связаны бинарная репликация с sql и cpu. оно потому и бинарная, что не sql.

и как я понимаю — такая «особенность» — есть следствие многодвижковости в том числе

@svetasmirnova 14 окт 2015 в 08:37

Я так понимаю, что вы под «бинарной» репликацией имеете в виду, что реплицируется transaction log движка? Как если бы MySQL репликация была построена на InnoDB redo log file (чего нет). С CPU этот вариант, кстати, тоже будет связан. Потому что transaction log нужно будет как-то передать по сети, записать на диск (CPU!) и в каком-то порядке apply (ага, в ту же многопоточность и возможность её реализации упираемся).

В MySQL же отдельный независимый binary log. Я затрудняюсь сказать почему именно так в своё время реализовали. С моей точки зрения это, скорее, плюс, потому что делает репликацию независимой от реализации формата хранения данных. Можно, наверное, и на многодвижковость всё списать (которые тогда были ещй не-pluggable). Или на то, что много лет назад, когда встроенная репликация в MySQL была, а для PostgreSQL существовали только third-party решения, основным движком был MyISAM, у которого нет transaction log вообще.

Но вообще смотрите dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_parallel_type Лучше в 5.7, в 5.6 поддерживается только DATABASE.

@mtyurin 14 окт 2015 в 09:40

Светлана, в pg бинарный проигрыватель wal логов на реплике, по факту, не дороже бинарного писателя на мастере, так как и то и то делает суть одно и то же но в разных порядках:

мастер: пишет страницы в куче во много потоков, НО при этом со всякими задержками на локах и логике (я про локи и логику на уровне движка таблиц/индексов и уровня sql); потом в один поток (особенности синхронизации, см WALInsertLock) в журнал
слейв: пишет в журнал, потом в кучу — всё в один поток, НО! уже без уровня таблиц/индексов/sql

@svetasmirnova 14 окт 2015 в 18:14

> слейв: пишет в журнал, потом в кучу — всё в один поток, НО! уже без уровня таблиц/индексов/sql

А что происходит, если какой-то параллельный процесс на слейве будет читать или писать из/в тех же таблиц?

@mtyurin 15 окт 2015 в 14:04

слейв всегда ридонли. могут быть ожидания со стороны проигрывателя wal логов, он может ждать читающий трафик, но «размер» допустимой «задержи» жестко задается в конфиге, приоритет отдается проигрывателю, читающий запрос получит исключение о прерывании (сбросе).

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

@svetasmirnova 15 окт 2015 в 17:23

слейв всегда ридонли

Ну вот и ответ. В MySQL слейв всегда read-write. Даже когда включена опция read-only суперпользователю разрешается писать.

В принципе можно написать feature request на bugs.mysql.com, чтобы добавили подобную оптимизацию для real read-only слейвов. (Типа как read-only транзакции сделали в 5.6) Проблема только в том, что большинство пользователей MySQL пишут на слейв =(

А сейчас я ниже писала как повысить производительность единственного thread-а. Дополнительно можно понизить transaction isolation level и принудительно включить read only транзакции на слейве для всего, а не только для single-statement select (что по-любому read only в 5.6).

@zabivator 14 окт 2015 в 10:01

Света, самое печальное — я до сих пор не видел ни одного исследования slave_parallel_type=LOGICAL_CLOCK, где было бы существенное улучшение производительности, и где были бы приведены числа и настройки.

Т.е. девушка есть, но мы вам её не покажем.

@kaamos 14 окт 2015 в 10:13

Так может это потому, что MySQL 5.7 ещё как бы не вышел? GA версия появится скорее всего через пару недель на Oracle OpenWorld. Как обычно какого-то широкого использвания не стоит ждать в течение года. А вот по-настоящему серьёзных исследований нужно ждать, когда на 5.7 начнут переходить тяжеловесы типа Facebook/Twitter/LinkedIn/Dropbox.

@zabivator 14 окт 2015 в 10:19

Ну, пусть так. Только что делать с CPU-bound репликацией на нагруженных проектах — вопросов остаётся открытым
Да, я знаю workaround'ы — записать general query log, построить по каждому типу запроса сводную статистику average response time и total time, отранжировать и исправить/убрать запросы
Понятно, что можно базу распилить.
Но это выглядит немного эээ странно, — у мастера ещё большой запас по CPU и IO, а реплика уже сдохла.

Не было бы этой проблемы — не было бы моего доклада. И судя по фидбеку после доклада — многие с этим наелись, и как решать — никто не понимает

@kaamos 14 окт 2015 в 10:42

Я там ниже написал про read-free replication. Надо сказать, я удачно зашёл на Хабр. Интересно, есть вообще темы в MySQL, которые в этом посте про PostgreSQL ещё не обсудили? :)

@svetasmirnova 14 окт 2015 в 20:03

Я тут немножко про другие воркэраунды написала: habrahabr.ru/post/268631/?reply_to=8613105#comment_8614031

@svetasmirnova 14 окт 2015 в 18:20

В принципе можно будет потестировать. ~~Или попросить кого-нибудь потестировать.~~ Если не забуду: опубликую результаты.

@Bozaro 14 окт 2015 в 08:44

Я попробую объяснить.

В MySQL используется логическая репликация: изменения записываются в лог репликации (в случае statement — сами запросы, в случае row-based — изменения записи).
Далее на стороне реплики нужно:

для statement — выполнить этот запрос;
для row-based — найти эту запись по ключу и поменять.

Поиск по ключу и выполнение запроса — дорогое удовольствие — отсюда лишняя нагрузка на IO и CPU.

Плюс ко всему прочему, нужно все транзакции выстраивать в очередь — отсюда однопоточность.

В PostgreSQL используется физическая репликация: на реплику передаются изменения страниц из лога транзакций.

При этом несколько транзакций могут схлопываться в одну при групповом коммите (https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-group_commit.html, wiki.postgresql.org/wiki/Group_commit).

Далее на стороне реплики нужно просто записывать эти страницы. Реплика даже не знает, что за данные поменялись — для неё это просто набор байтов.

Недавно в PostgreSQL так же появилась логическая репликация для реализации мастер-мастер реплики.

Сделать же физическую репликацию в MySQL нельзя, так как на уровне движка лога транзакций не существует (он может есть в хранилище InnoDB и отсутствует в MySQL, но движок о нем не знает).

@mtyurin 14 окт 2015 в 08:52

ну вот я и не понимаю, как серьезные ребята выдают поиск по b-tree за бинарную репликацию. и потом быстро переходят на сравнение флоатов (флоатов!) на равенство =

@samokhvalov 12 окт 2015 в 22:51

Алексей, привет. Отвратительно здесь всё работает. Хотел поднять карму — но, видите ли, «нельзя голосовать за пользователей, у которых нет размещённых публикаций».

@galaxy 12 окт 2015 в 23:38

ТМ веселые правила жизни выдумывает. Ему, видимо, в песочницу надо писать.

@kaamos 13 окт 2015 в 06:39

Николай, говорят можно в песочнице создать пост. Спасибо, что пытались помочь! :)

@varanio 13 окт 2015 в 06:42

я тоже пытался плюсануть карму и не смог. Пишите в песочницу, я вам плюсану после этого.

@vlivyur 13 окт 2015 в 07:36

Recovery mode

@ksm 13 окт 2015 в 22:29

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

@mtyurin 14 окт 2015 в 08:23

даже не вдаваясь в подробности (куда вы и не вдавались), а только по внешнему признаку упираемости в цпу абсолютно очевидно, что она «тру» не более, чем никак

@ksm 14 окт 2015 в 19:30

Судить по cpu-bound о типе репликации это что-то новое. Ну да бог с ним, давайте вдадимся в подробности и Вы расскажите почему RBR не бинарная.

@Bozaro 15 окт 2015 в 07:26

Бинарная — очень плохой термин. Он не говорит ни о чем.

О типе репликации я писал выше: habrahabr.ru/post/268631/#comment_8612987

@ksm 15 окт 2015 в 09:15

О чем и речь. Комментарий о типах репликации я видел, в целом согласен. Хотя скажем в моем (ангажированном, конечно) понимании RBR более гибка, чем допустим вариант в постгресе (хотя я уже не имел возможности его глубоко потестить ). В RBR я могу на слейве иметь другую структуру таблиц: меньше полей (но с сохранением порядка следования), другие типы данных (по совместимые опять же). Отвязанность от storage engine дает возомжность менять (относительно, конечно) формат хранения страниц в движке не ломая совместимость в режимах old master — new slave.
Да еще никто не упомянул GTID, которые позволяют иметь фактически любую топологию, менять ее и гарантировать что а) данные на серверах будут up-to-date, б) никакой апдейт не применится более одного раза на сервере.

@mtyurin 14 окт 2015 в 08:59

посмотрите выше по треду — за вас (Bozaro) вдались в подрбности

@kaamos 14 окт 2015 в 10:29

Всем, кто обсуждает проблемы row-based репликации в MySQL (именно так её правильно называть, чтобы говорить на одном с PostgreSQL и избежать путаницы с терминами логическая/бинарная/физическая репликация). Основная проблема, как я вижу, это чтение при репликации (так правильнее называть проблему с CPU-bound, как её здесь часто называют). Решение есть, например в TokuDB, называется read-free replication. Читать можно начинать отсюда: https://github.com/percona/tokudb-engine/wiki/Read-Free-Replication-with-TokuDB

@ksm 14 окт 2015 в 19:33

Да я то как раз в курсе.

@gatoazul 12 окт 2015 в 10:07

Поиск по IP в mysql совсем несложно сделать, если хранить адрес длинным числом, а не строкой. Легко привешиваются и индексы.

@vitalif 12 окт 2015 в 10:24

ну просто по сути более правильное название этого преимущества — поддержка большого числа кастомных типов данных =)

@Sway 12 окт 2015 в 10:07

С полнотекстовым поиском далеко не всё в порядке. Кроме того, что его настраивать довольно муторно, особенно для кириллицы, у него так же много косяков с самим поиском когда он не находит то, что обычным лайком находится на ура. Часто это связано с именами/названиями. Приходится комбинировать его с like или ~ чтобы поиск был более-менее адекватным. + спец-словари для русского вообще сейчас невозможно найти (все ссылки древние и битые). Функционал сам по себе достаточный, но работает недостаточно хорошо чтобы использовать для языков отличных от английского.

@mtyurin 12 окт 2015 в 10:17

поиск по регулярке — это не поиск по морфологии, как бы вы всё перепутали. к полнотекстовому поиску есть вопросы по ранжированию, но ТОЧНО это не про ваши недорегулярки на лайках. а для поиска по регуляркам, например, есть САША КОРОТКОВ и его оптимизация регулярок на n-граммах и соответствующих индексах.

@Sway 12 окт 2015 в 10:23

Я про проблемы с морфологией и пишу. Для русского языка всё не очень хорошо в случае поиска по словам типа «Хабрахабр». Далеко не факт что он найдет этот вариант при поиске «Хаб» или «Хабр» (не проверял конкретно это название, но все фейлы поиска были для подобных названий). Я напоролся на кучу проблем с такими названиями и в итоге пришлось страховаться регулярками т.к. попытки найти решение используя FTS провалились полностью.

@BlessMaster 12 окт 2015 в 13:11

Ну так это и разные слова, если уже на то пошло (и русский язык в этом не виноват). Точно так же регулярки бессильны сопоставить «человек» и «люди», что элементарно для FTS с полноценной морфологией. Вхождение же «хабр» в «хабрахабр» — лишь частный случай, далеко не универсальный.
Для разных же слов существуют тезаурусы, LSI, WordNet и прочие высшие материи.
Чистый же FTS — не про это, хотя для некоторых задач достаточно даже простого стеммера вместо словарей — дёшево и сердито.
Но в любом случае FTS — это не серебряная пуля, и нужно понимать, какая цель и какой инструмент лучше подходит.

@Sway 12 окт 2015 в 13:53

Для разных же слов существуют тезаурусы, LSI, WordNet и прочие высшие материи.

Вот тут-то и начинаются проблемы — я искал словари для русского. Либо ссылки мертвые, либо объем маловат.
Вот и получается так, что постгресовский FTS годен разве что для поиска по тексту в худ. литературе, где нет терминов и т.п.
С задачей типа «поиск по названиям книг и авторов» он не справляется =(
На сколько помню древний sphinx с аналогичной задачей справлялся без проблем (последний раз использовал его 4 года назад, сейчас он еще лучше будет). Получается что эффективнее как раз внешнее решение. И настраивать его не так муторно.

@BlessMaster 12 окт 2015 в 14:38

Когда мне нужен был FTS для предыдущего проекта, я использовал словарь ispell, установленный в системе, вручную сконвертировав его в utf-8. Сейчас, да, ispell объявлен устаревшим и давно уже отсутствует в моей системе (если нужно — могу поделиться).
В этом году я вернулся к этой теме, но обстоятельства уже немного поменялись: python + pymorphy2 (со словарями opencorpora.ru) + jsonb — это небо и земля, по сравнению с тем, что было, когда FTS появился в Postgres. (Кстати, jsonb, как и hstore ранее, и индексы GiST и GIN, применяемые для всего этого, создавались одними и теми же людьми).
FTS — это вообще очень непростое и капризное блюдо, которое нужно готовить как рыбу Фугу и качественные словари — основа всего. К сожалению действительно качественных и при этом бесплатных в свободном доступе для русского языка — нет. И вряд ли в ближайшее время появится. Словари от opencorpora в сочетании с pymorphy, прямо скажем, на голову лучше всего остального, с чем я сталкивался, но… совершенству предела нет, а русский язык — не самый простой, плюс ещё много факторов, добавляющих к ложке дёгтя.
То есть, подытоживая, postgres — это не более чем коробка с инструментами. Инструменты качественные, с большими возможностями, но сами по себе они «ничто». FTS в постгрес — это фреймворк для создания собственных решений (который можно использовать как целиком, так и по частям), плюс пара утилит, чтобы набросать что-то на скорую руку и продемонстрировать концепцию (то же самое можно сказать про SQL в принципе — инструмент обработки данных не может заменить сами данные).
Sphinx — это уже готовый специализированный продукт, имеющий какое-то завершённое и оттестированное решение. Наверняка «изкоробки» он будет работать лучше, чем фреймворк, в котором ещё нужно разобраться. Но я не уверен, что он будет работать лучше чем Postgres при прочих равных (использование открытых свободно-распространяемых словарей). Я успешно искал по сводной базе объявлений, думаю, книги — не более сложная задача. Но, если нужно «работает уже вчера» и всё устраивает, то может действительно стоит предпочесть готовое решение.

@Sway 12 окт 2015 в 15:12

Так оно и есть. Из коробки только английский работал хорошо и без осечек.
Внешнее решение наверняка будет уступать разве что по скорости, зато его намного проще и быстрее поднять. Вот если бы sphinx еще поддерживал нотификации postgresql (NOTIFY) — было бы вообще прекрасно =)

@BlessMaster 15 окт 2015 в 17:41

Внешнее решение хорошо ровным счётом до того момента, пока оно без особых затрат на синхронизацию (к сожалению, я не в курсе, как сфинкс стыкуется с базой) полностью вписывается в решаемую задачу. Как только нужно больше — возвращаемся к тому, с чего начали, но при этом объём задачи уже сильно вырос, а опыт её решения — не накоплен, и что-то даже пошло в бизнес-модели и ломать/перестраивать становится тяжело и дорого. Хуже того, когда это тиражируемая система — приходится поддерживать сразу два варианта, поскольку не во всех экземплярах замена возможна. В общем, как всегда — в каждом случае своя цена, компромиссы и перспективы, которые нужно стратегически спланировать :-)

@vitalif 12 окт 2015 в 10:26

там ещё прикол в том что обязательно tsvector надо в таблице хранить, иначе очень медленно ранжирование работает.
был доклад «полнотекстовый поиск в postgresql за миллисекунды», где они грозились допилить его, чтобы он сам информацию из tsvector'а хранил, и тогда его скорость реально приближалась к Sphinx'у. но по-видимому пока что не сделали…

@Timosha 12 окт 2015 в 14:17

tsvector можно хранить в функциональном индексе

@vitalif 12 окт 2015 в 19:17

вот именно тогда ранжирование и начинает тормозить, т.к. если для поиска нужен только индекс, для ранжирования нужен ещё и сам tsvector

@sebres 12 окт 2015 в 10:17

4. В Mysql можно прямо в запросе оперировать переменными…
В посгресе такого нет, по крайней мере я не нашел (напишите, если ошибся)

В PostgreSQL есть такое понятие как анонимная функция или анонимный код-блок — используйте ключевое слово DO

do $$
  declare x int;
  begin
    select col1 into x from table1;
  end;
$$;

Не все всегда гладко с execution plan (по крайней мере было пару лет назад в смысле повторного оптимального использования такого кода с параметрами снаружи, хотя возможно уже поправили).

@mtyurin 12 окт 2015 в 10:18

есть еще set [local] myvar = 'myval'

@sebres 12 окт 2015 в 10:22

Нее… set только для установки локальных или сессионных конфиг-параметров. Это не переменная.

@mtyurin 12 окт 2015 в 13:44

нееее… это именно ПЕРЕМЕННАЯ, причем может быть именно только в скопе транзакции

@sebres 12 окт 2015 в 15:29

Если вы про этот set, то это как-раз то что я имел ввиду выше как параметр… (безотносительно области действия, что транзакция, что сессия).
Если вы все же настаиваете, то приведите хотя бы пример использования этого как переменной (надеюсь вы понимаете что такое переменная в SQL).
Если вы все же про set из psql (\set), то это вообще из другой оперы (только psql, никакого отношения к переменным SQL не имеющая в принципе).

@Tonkonozhenko 12 окт 2015 в 12:21

Но, к сожалению, вернуть что-то из этого блока нельзя.

@sebres 12 окт 2015 в 12:34

Ну почему же, если используемое API поддерживает cursor-ы, вы можете вернуть ответ определив это как refcursor или напрямую, навскидку:

do $$
  declare x int;
  begin
    select col1 into x from table1;
    select x as result_x;
  end;
$$;

Ну или используя raise, по моему как-то так (нет postgres под рукой):

do $$
  declare x int;
  begin
    select col1 into x from table1;
    raise notice '%', x;
  end;
$$;

@Tonkonozhenko 12 окт 2015 в 12:41

Оба варианта у меня не сработали. Можете скинуть пример как это сделать через курсоры?
И в доках вот что нашел:

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

@sebres 12 окт 2015 в 13:04

Не сработали — это из области «У меня компьютер сломался»… Можно подробнее (например в личку)?
Под рукой к сожалению нет ничего, да и postgresql я юзаю в основном через свой биндинг c/c++ или в tcl (Pgtcl или tclodbc если под виндой) но однозначно помню, что возвращал так значения (и курсором, и out-bind-параметрами, и через raise):

код-блок точно работает как prepared statement, т.е. можно привязать параметры, и насколько помню даже output parameter;
для курсора используется то же api, что и для multi cursor statements;
чтобы прочитать значение, возвращаемое через raise нужно читать message(s) а не result(s)

Если вспомню что-то из «общепринятого» api типа пыха или питона или доберусь до железа с ним, солью сюда пример…

@sebres 12 окт 2015 в 13:10

Кстати если у кого api совсем уж ущербное (т.е. ну совсем никак не читается), то вот пример через temporary table.
Конечно не совсем комильфо, но на безрыбье как говорится…

@varanio 14 окт 2015 в 06:32

Это не совсем то.
В приведенном мной примере «SELECT @x:= @x+1 FROM table» в результирующе выдаче в каждой новой строке будет новое число. В посгресе прямо в запросе (т.е. без циклов) этого вроде бы не сделать.

@galaxy 14 окт 2015 в 11:53

Можно, хотя не через переменные и не очень очевидным образом:

# with recursive counter as (
  select 1 as var
   union all
  select var + 1 as var from counter
   where var < 10
)
select var from counter;

 var
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

@sebres 14 окт 2015 в 15:45

Месье знает толк…
Организовать рекурсию, чтоб вернуть counter, это просто что-то — чем row_number() то не устраивает?

@galaxy 14 окт 2015 в 15:48

Человек хотел counter. Если бы нужны были числа Фибоначии — без проблем, я продемонстрировал принцип.

@sebres 14 окт 2015 в 16:50

ну так-то ясно, но… человек хотел переменные в statement, у вас же оно — виртуальный столбец виртуальной таблицы, да еще и рекурсия сверху погоняет…

@varanio 14 окт 2015 в 17:00

Я всё это умею, и оконные функции, и рекурсивные CTE, и циклы, и курсоры. Но в мускуле все-таки это немного проще и читабельней. Конечно, субъективно это. Так что именно такой же функциональности в пг все-таки, считаю, нет.

@BlessMaster 15 окт 2015 в 18:28

Наивный вопрос: зачем на самом деле нужны переменные? Насколько хорошо запросы с переменными поддаются оптимизации? Документация сразу честно предупреждает, что далеко не всё с переменными просто и интуитивно, есть ряд ограничений.
Я, конечно, не совсем в теме, но краем уха слышал о проблемах с использованием переменных при репликации, возможно это неактуальная информация (или звон совсем не там был, надеюсь кто-то просветит?)
Такие вот вопросы.

В общем, если посмотреть на проблему немного шире, Postgres силён своей расширяемостью — есть вот такие «переменные» http://www.garret.ru/imcs/user_guide.html :-)
Наверно можно что-то попроще и полаконичней нарисовать, если есть такая необходимость.

@VolCh 16 окт 2015 в 03:16

Основное назначение, вроде как, совпадает с назначением оконных функций типа lag, first_value и т. п., а так же агрегирующих с OVER — результаты текущей строки типа накопительных итогов или порядковых номеров (по сути тоже накопительный итог) зависят от других строк. В случае MySQL — только предшествующих, что частично обходится сортировкой. Но геммороя с ними куда больше.

@sebres 14 окт 2015 в 15:43

Да можно же… FOR… LOOP, курсоры и т.д.

В вашем же конкетном случае я и переменные использовать не буду:

SELECT row_number() over () as x, * FROM table

@BlessMaster 15 окт 2015 в 18:38

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

@vitalif 12 окт 2015 в 10:22

Мне лично в pg ещё не нравится отсутствие удобных команд типа SHOW TABLES, SHOW DATABASES и т.п.
Очень уж геморно запоминать вместо них \l \t \dl и что там ещё при работе в консоли (а я в ней обычно и работаю).
Ещё не нравится отсутствие возможности добавить колонку в заданное место (ALTER TABLE ADD x AFTER y).

@BlessMaster 12 окт 2015 в 13:43

В консоли постгрес работает автодополнение, которое может показать все базы, таблицы, поля и т.д. прямо в процессе написания запроса (или если включён bashcomp, то и при запуске psql — подсказывает имеющиеся локально базы).

Более того, в postgres в каждой базе есть две стандартных схемы: «pg_catalog» и «information_schema», из которых можно извлечь абсолютно всю информацию о базе. Набросайте себе несколько хранимок и пользуйтесь ими как расширением. В интернете много готовых рецептов на разные случаи жизни и возможности выходят далеко за рамки SHOW TABLES, DESCRIBE DATABASE.

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

@vitalif 12 окт 2015 в 19:22

Это всё ясно и pg_catalog безусловно лучше чем то что есть в мыскле, но всё равно в консоли show tables удобнее)

@varanio 12 окт 2015 в 19:27

Дык писать дольше, чем \dt. Или просто проще запомнить?

@vitalif 12 окт 2015 в 19:49

Ага… И ещё SHOW CREATE TABLE удобная тема. Причём в mysql это же всё не команды консольного клиента, а просто SQL запросы, то есть их и из скриптов можно дёргать — тоже удобно. Минус правда в том, что зачастую в mysql это единственный реальный способ вытащить какую-то информацию о таблице, т.е. приходится парсить show create table вместо того, чтобы просто посмотреть в нужное место каталога…

@varanio 12 окт 2015 в 19:53

мне попадалась где-то на stackoverflow хранимка, которая выдает аналог show create table. Костыльно конечно, чо уж там.
Если мне нужна схема таблицы я обычно делаю pg_dump… --table mytable --schema-only

@svetasmirnova 13 окт 2015 в 18:16

> Минус правда в том, что зачастую в mysql это единственный реальный способ вытащить какую-то информацию о таблице, т.е. приходится парсить show create table вместо того, чтобы просто посмотреть в нужное место каталога…

Эээ, а что есть в SHOW CREATE TABLE и нет в Information Schema?

@BlessMaster 15 окт 2015 в 18:51

Так а чем не подходит нажать Tab вместо того, чтобы печатать SHOW TABLES? Даже не знаю, куда уже проще запомнить :-)

@Borz 13 окт 2015 в 05:44

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

в MySQL тоже работает автодополнение

postgres в каждой базе есть две стандартных схемы

и в MySQL есть… три стандартные схемы: mysql, information_schema, performance_schema, правда они глобальные и требуется делать WHERE с именем нужной схемы.
например консольная команда «show tables from mysql;» трансформируется в такой запрос: «SELECT table_name FROM tables WHERE table_schema='mysql';»

@galaxy 13 окт 2015 в 20:12

в MySQL тоже работает автодополнение

Есть, только работает через задницу, как и многое в mysql:

mysql> select * from use
use                          user.Create_view_priv        user.Insert_priv             user.Show_db_priv            user.max_questions
user                         user.Delete_priv             user.Lock_tables_priv        user.Show_view_priv          user.max_updates
user.Alter_priv              user.Drop_priv               user.Password                user.Shutdown_priv           user.max_user_connections
user.Alter_routine_priv      user.Event_priv              user.Process_priv            user.Super_priv              user.plugin
user.Create_priv             user.Execute_priv            user.References_priv         user.Trigger_priv            user.ssl_cipher
user.Create_routine_priv     user.File_priv               user.Reload_priv             user.Update_priv             user.ssl_type
user.Create_tablespace_priv  user.Grant_priv              user.Repl_client_priv        user.User                    user.x509_issuer
user.Create_tmp_table_priv   user.Host                    user.Repl_slave_priv         user.authentication_string   user.x509_subject
user.Create_user_priv        user.Index_priv              user.Select_priv             user.max_connections         user_host

Сравните с контекстным дополнением в psql:

postgres=# S
SAVEPOINT       SECURITY LABEL  SELECT          SET             SHOW            START
postgres=# SE
SECURITY LABEL  SELECT          SET
postgres=# SELECT * from pg_t
pg_tables            pg_temp_1.           pg_timezone_names    pg_toast_temp_1.     pg_ts_config         pg_ts_dict           pg_ts_template
pg_tablespace        pg_timezone_abbrevs  pg_toast.            pg_trigger           pg_ts_config_map     pg_ts_parser         pg_type
postgres=# SELECT * from pg_tables where table
tablename   tableowner  tablespace

@Borz 13 окт 2015 в 20:28

вы чувствуете, как вы подменили критерий «нет в MySQL» на «есть, но работает не так, как мне хочется»?

@galaxy 13 окт 2015 в 22:51

Я не говорил «этого нет в MySQL». И работает оно не «не так, как мне хочется», а хреново. Объективно.

@BlessMaster 15 окт 2015 в 19:28

Тезис был про невосполнимость утраты SHOW TABLES при переходе с MySQL.
Как видим, общего, на самом деле, больше, чем различий и не так всё страшно.

Если же обсуждать «в MySQL тоже есть», то по поводу автодополнения выше уже прошлись и похоже ситуация за те 7 лет, что я не пользовался им, мало изменилась, что на самом деле факт неприятный, но делает наличие SHOW TABLES и прочих подобных команд востребованным. Вместе с тем это воспитывает соответствующий стиль работы, ломать который тяжело и в итоге поднимается вопрос, «в %СУБД не хватает SHOW TABLES».

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

Hans-Henrik Stærfeldt on February 20 2009

The implementation of INFORMATION_SCHEMA can have serious impact on performance of the server. If you have many tables, and query into INFORMATION_SCHEMA without limitations on the schema and if possible the table itself, performance is severely impacted while the query runs.

Здесь на хабре кажется тоже статья пробегала на эту тему. Речь, если не изменяет память шла про версию 5.5 и альфу 5.6, в которой не предвиделось положительных сдвигов. В документации к 5.7 этот вопрос частично затрагивается, упоминается просадка производительности при запросе информации по нескольким базам и рекомендуется такие запросы тюнить.

Наверно это не критичная особенность, утверждать «всё плохо» не буду, хотя ряд ограничений на использование information_schema накладывает.

@vitalif 12 окт 2015 в 10:28

А, и это, есть же phpPgAdmin, почему его никто не вспомнил?
Неказист конечно немного, но юзабелен в целом.

@youROCK 12 окт 2015 в 10:45

Поставил плюс, потому что подобных статей на хабре люди пытались писать много, но они всегда получались однобокими — либо человек хорошо знал MySQL и плохо — Postgre, либо наоборот. В этой статье, как мне кажется, человеку более-менее удалось соблюсти баланс, хотя некоторые утверждения всё равно весьма спорны, особенно про производительность :). Но всё равно хорошо, статья вполне четко отвечает на вопрос в заголовке и описывает всё, что нужно.

@kaamos 12 окт 2015 в 10:50

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

Аналогов Galera для PostgreSQL нет и видимо нескоро появятся;
утверждения про «встроенность» транзакций в PostgreSQL весьма поверхностны. В MySQL они «встроены» ничуть не хуже. Да, есть нетранзакционные движки, но в 2015 про них можно говорить только в поддержке совместимости со старыми приложениями, т.е. нет никакого смысла их использовать в новых приложениях.
да, в MySQL 5.7 строгий режим по умолчанию. Да и для предыдущих версий включить его не составляло проблем, что люди и делают при любом хоть сколько-нибуть серьёзном использовании MySQL.
Утверждение «В mysql есть только AUTO_INCREMENT на поле таблицы, который тикает по одному» неверно, см. auto_increment_increment.
но абсолютно согласен, sequence куда более гибкий механизм, чем AUTO_INCREMENT. Кстати, в MariaDB есть sequence storage engine, который частично устраняет этот пробел в функционале.
полноценная поддержка JSON будет (есть) в MySQL 5.7.
функциональные индексы реализованы в 5.7 через virtual columns, по которым можно строить индексы.
понятно, что утверждения про лучшую производительность PostgreSQL и «меньше багов» субъективны по определению (у всех разные нагрузки и сценарии использования), но хорошо бы подтверждать конкретикой. Я слышал прямо противоположные утверждения от пользователей, которые долго работали как с MySQL, так и с PostgreSQL.

@varanio 12 окт 2015 в 11:01

Спасибо!
А не могли бы вы написать подробный пост на эту тему? Очень интересно на самом деле.

@kaamos 12 окт 2015 в 11:06

Да, конечно. In my copious free time… :)

@Bozaro 12 окт 2015 в 13:43

Работал с обоими СУБД. Лично мигрировал с MySQL на PostgreSQL.

Транзакции в MySQL транзакции действительно прикручены сбоку. Это имеет кучу сайд-эффектов: несколько механизмов блокировок, несколько логов транзакций, хрупкая репликация, нетранзакционный DDL, репликация порождает распределенную транзакцию внутри БД (между логом репликации и логом движка) и т.п.
У нас была забавная беда с AUTO_INCREMENT-ом: в случае использования InnoDB возможно повторное использование идентификаторов, т.к. при старте он начинается с максимального имеющегося в таблице значения. Это очень сильно доставляло при отдельном хранении исторических данных.
В PostgreSQL много лучше оптимизатор запросов. В MySQL он, ИМХО, никогда не будет работать хорошо: MySQL-хранилища идут как плагины. Из-за этого получается, что выделение API хранилища требует, чтобы движок о них знал как можно меньше, а для реализации оптимизатора — как можно больше. Т.е. либо несколько хранилищ, либо качественный оптимизатор.

Еще хотел бы отметить:

В MySQL очень дорогой DDL: ALTER TABLE очень любит пересоздавать таблицы даже при удалении CONSTRAINT-ов.
За утилиту mysqldump авторам надо оторвать руки — по умолчанию она делает не консистентный слепок данных.

Из киллер-фичей MySQL от которой было очень боязно отказываться (она появилась только в PostgreSQL 9.3): читаемые bin-логи, позволяющие восстановить историю изменений в БД.

@youROCK 12 окт 2015 в 15:53

Кстати фича «engine condition pushdown» в MySQL (https://dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html) и её аналоги — это как раз способ все же сделать так, чтобы оптимизатор мог работать отдельно от общего движка. То есть, утверждение о том, что оптимизатор в MySQL сделать лучше нельзя, тоже не совсем верно. Другое дело, что в PostgreSQL намного проще сделать хороший оптимизатор, потому что там всего-лишь один движок.

@Bozaro 12 окт 2015 в 16:14

dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html

Эта фича по-сути является костылём: расширили API хранилища, чтобы хранилище возвращало меньше бесполезных данных, которые будут откинуты движком. И, что важно, она должна быть в том или ином виде поддержана во всех движках.

То есть, хотите более быстрое выполнение запросов:

Расширьте API для хранилища;
Научите оптимизатор этим API пользоваться (включая ситуации, когда в хранилище это API реализовано не до конца и когда идет запрос между хранилищами);
Реализуйте это API во всех хранилищах.

При этом многие хотелки будут разбиваться о то, что:

они полезны только для одного хранилища;
на разных хранилищах оптимизации могут давать противоположный результат.

Я уверен, что до отказа от концепции подключаемых хранилищ, в MySQL не будет ни нормального оптимизатора, ни транзакционного DDL, ни стабильной репликации. Пойдут ли разработчики на этот шаг — большой вопрос.

@maep 13 окт 2015 в 06:52

Транзакционный DDL!? Он даже в Oracle не транзакционный, кажись…

@zzashpaupat 13 окт 2015 в 10:09

Вот тут говорят, что transactional DDL есть в PostgreSQL, MS SQL Server, DB2, Firebird

@svetasmirnova 13 окт 2015 в 18:20

> В MySQL очень дорогой DDL: ALTER TABLE очень любит пересоздавать таблицы даже при удалении CONSTRAINT-ов.

Он с каждой версией становится всё дешевле и дешевле: dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

@Bozaro 13 окт 2015 в 19:21

О чем собственно и речь: InnoDB все больше и больше срастается с движком MySQL.

@mtyurin 14 окт 2015 в 08:30

ждем

НЛО прилетело и опубликовало эту надпись здесь

@ksm 13 окт 2015 в 22:49

По последнему пункту — Оракл расширил команду MySQL, но приоритет отдается стабильности, поэтому форки имеют возможность дать больше фич раньше оригинального продукта.

@maximw 12 окт 2015 в 19:33

Не слишком хорошо знаю про sequence в PostgreSQL. Но в МуSQL sequence вполне реализуется, о чем написано в конце описания функции LAST_INSERT_ID()

@Bozaro 13 окт 2015 в 08:12

Есть два важных момента:

sequence дает идентификатор до вставки записи, что бывает иногда полезно;
sequence не сбрасывается при рестарте базы, а auto_increment в InnoDB сбрасывается (https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html)

@maximw 13 окт 2015 в 11:45

1) Ну да, sequence дает идентификатор до вставки записи. Тут как бы тоже можно в любой момент (даже без вставки какой-либо записи) получить число, которое гарантированно не будет выдано никому другому и использовать его как id или еще как-то.
Инициализация:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

Использование:

UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();

2) Интересный нюанс, спасибо, почитаю.

@Bozaro 13 окт 2015 в 12:23

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

До миграции на PostgreSQL я вынуждено эмулировал SEQUENCE в MySQL через отдельную таблицу. Вспоминаю об этом с содроганием.

@varanio 14 окт 2015 в 06:26

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

@Bozaro 14 окт 2015 в 07:06

Ответ на вопрос зачем простой — legacy.

Мигрировать живой проект на другую СУБД не так-то просто: у нас процесс миграции на PostgreSQL занял более года.

@rakot 12 окт 2015 в 12:09

Не сочтите за рекламу, но открыл для себя www.adminer.org вполне функционально, по крайней мере точно быстрее чем через консоль.

@sudoroot 14 окт 2015 в 05:17

Большое спасибо вам за сий ресурс!

@Envek 12 окт 2015 в 12:41

Оконные функции — сказка. Сколько раз они выручали, позволяя делать всякие не очень тривиальные штуки в миграциях одним запросом. Дюже рекомендую к изучению.

@Cord 12 окт 2015 в 12:51

А есть ли полноценная многоверсионность в MySQL?
Когда много пользователей читает/пишет в Мускуле, локи на уровне строки/таблицы раньше были задницей. То есть Мускул был __непригоден__ для нормальной нагрузке.

А Постгрес вполне себе летал, и уже 8-9ка вполне себе сильно ближе по возможностям и уровню к Ораклу, а не игрушечному-плюшевому MySQL.

@Bozaro 12 окт 2015 в 13:51

Как минимум, в MySQL 5.6 о многоверсионности можно только мечтать :(

@youROCK 12 окт 2015 в 15:54

Не могли бы вы пожалуйста рассказать подробнее, что не так с MVCC в InnoDB?

@vitalif 12 окт 2015 в 20:07

подозреваю, что это о временах myisam :-)

@Bozaro 13 окт 2015 в 08:43

InnoDB вешает блокировку при попытке изменять читаемые кем-либо записи. В PostgreSQL этой проблемы нет.

@youROCK 13 окт 2015 в 09:44

Приведите пример, пожалуйста

@Bozaro 13 окт 2015 в 10:19

Я страдал от этой проблемы (https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html):

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

When InnoDB scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where id would be bigger than 100, the locks set by InnoDB include a lock on the gap following id value 102.

@youROCK 13 окт 2015 в 13:21

Можете привести рабочий пример?
Я утверждаю, что обычные SELECT'ы (т.е. без LOCK IN SHARE MODE и FOR UPDATE) в дефолтном уровне изоляции (то есть, REPEATABLE READ, а не SERIALIZABLE!) никогда не блокируют запись и наоборот.
То есть, вы можете иметь сколько угодно параллельных селектов из таблицы, и это никак не будет мешать выполнению UPDATE или INSERT в эту же таблицу. Аналогично, выполняющийся UPDATE или INSERT в таблицу не будут блокировать чтение из этой же таблицы, даже тех же строк.
Что может происходить – это что лок на запись будет браться больший, чем нужно (т.е. вряд ли у вас пройдут параллельные UPDATE следующего вида: UPDATE… WHERE id % 2 = 0 и UPDATE… WHERE id % 2 = 1)

@Bozaro 13 окт 2015 в 13:43

Я утверждаю, что обычные SELECT'ы (т.е. без LOCK IN SHARE MODE и FOR UPDATE) в дефолтном уровне изоляции (то есть, REPEATABLE READ, а не SERIALIZABLE!) никогда не блокируют запись и наоборот.

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

@galaxy 13 окт 2015 в 15:21

Не читайте до обеда документации mysql. Там даже пример с SELECT FOR UPDATE. И блокирует только в этом случае.

@MacIn 12 окт 2015 в 17:22

Что такое «нормальная нагрузка»? Нормально локи работают, еще от уровня изоляции зависит.

@Anarchist 12 окт 2015 в 14:08

> Mysql все знают, postgresql никто не знает.
«никто не знает» — слишком категоричная оценка. Уверяю Вас, на крупных проектах о существовании Pg осведомлены и частенько даже используют оный.

> Есть целый класс php-программистов, для которых слово “база данных” и mysql — это одно и то же, слова-синонимы.
Такие «программисты» нам не нужны. :)

> Особо бесит то, что для highload-проектов практически всегда нужен еще и pgbouncer (это сторонняя разработка), который копит коннекты в очереди и следит, чтобы к самому посгресу было ограниченное число коннектов.
Если в языке реализованы пулы соединений, зачем нужен pgbouncer? От него одна головная боль, особенно, в том, что касается подготовленных запросов.

@varanio 12 окт 2015 в 16:46

А если у вас много серверов с приложениями, которые коннектятся к одной базе?
Ну или когда юзаешь какое-нибудь php-fpm, то без pgbouncer не обойтись.

@stalkerg 14 окт 2015 в 13:54

Я это уже много где писал:
проблема с пулерами только у PHP.
Почему? Есть статья на хабре про это. :)

@josser 9 ноя 2016 в 15:54

а дайте ссылку, не знаю даже что в поиск вбить просто

@stalkerg 10 ноя 2016 в 11:25

https://habrahabr.ru/post/179399/

@NeX 12 окт 2015 в 15:59

Еще к плюсу MySQL можно отнести
1) очень быстрые воборки по primary key (из постгреса не получается выжать быстрее, чем 2-3 мс на запрос 1 записи по pk)
2) Если надо еще быстрее — www.percona.com/doc/percona-server/5.5/performance/handlersocket.html

К постгресу:
1) Рекурсивные запросы (исопльзуйте аккуратно) habrahabr.ru/post/73700

@mtyurin 12 окт 2015 в 16:49

> из постгреса не получается выжать быстрее, чем 2-3 мс на запрос 1 записи по pk

покажите explain (analyze, buffers) select * вашпример (добейтесь стабильного отклика и приведите его)

@varanio 12 окт 2015 в 17:17

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

@varanio 12 окт 2015 в 17:28

Хотя вот нашел и в посгресе такое же
www.postgresql.org/docs/9.4/static/sql-cluster.html
т.е. таблицу можно кластеризовать под конкретный индекс

@NeX 13 окт 2015 в 14:53

Был неправ, когда все данные в кеше все хорошо

@mtyurin 14 окт 2015 в 09:45

когда все данные в кеше и там и там. а когда данные не в кеше — я бы еще сильно посмотрел, где как. но это уже в лоб еще сложнее сравнивать

@BlessMaster 15 окт 2015 в 21:02

Ну вот этот самый HandlerSocket [http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html] наглядно продемонстрировал, что абстракция, повешенная над движком InnoDB, замедляет доступ по ключу на порядок и всё упирается в процессор сильно раньше. То есть, InnoDB конечно быстрый движок, но не его скорость является бутылочным горлышком. В то же время Postgres имеет меньше лишних абстракций. Поэтому теоретические построения «кто кого» пусты и холиварны. А вот практические результаты всегда интересно знать с привязкой к условиям эксперимента.

@Moskus 12 окт 2015 в 16:55

postgis.net
Для MySQL такого нет, с географическими типами у него тоже все фигово.

@milabs 12 окт 2015 в 20:45

В PostgreSQL есть табличный INHERITANCE, в MySQL его нет.

@Stan_1 12 окт 2015 в 20:58

В достоинства PostgreSQL еще можно добавить поддержку scheme. Крайне удобная штука для структурирования проекта и разбивки таблиц на группы.

@Envek 12 окт 2015 в 23:01

В этом году на PgDay был восхитительный доклад, как с помощью небольшой магии со схемами, триггерами и вьюхами сделать полноценную «машину времени» для базы данных, внося минимальные изменения в приложение: www.hagander.net/talks/tardis_orm.pdf

Я полюбил схемы после этого доклада.

И, кстати да, почему ещё никто не сказал про классные range-типы?

@samokhvalov 12 окт 2015 в 23:11

Да много про чего тут не сказали, чего уж там ;)

@svetasmirnova 13 окт 2015 в 18:25

В MySQL schema — это синоним database. Можно сказать, что в MySQL всего одна база, если сравнивать с PostgreSQL или Oracle. А того, что там схем нет — нельзя.

@samokhvalov 12 окт 2015 в 23:09

Для тех, кто любит сравнивать, есть отличная старая и постоянно развиваемая статья в Википедии: en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

@zzashpaupat 13 окт 2015 в 10:10

Еще есть вот тут неплохое сравнение: www.sql-workbench.net/dbms_comparison.html

@horlon 13 окт 2015 в 06:07

Мой выбор однозначно MySQL… Функцый, которые там есть мне с головой достаточно. Всегда удивлялся непонятным доводомам крутоты postgresql… Там есть то, там есть се… Почему-то никогда не было потребности в том функцыонале. База, прежде всего — это просто база, а не истрибитель чтобы картошку возить…

@Envek 13 окт 2015 в 06:36

Это всего лишь значит, что у вас ещё не было сложного проекта. Я до недавнего времени мог обходиться вообще SQLite'ом, но теперь мы в проекте используем jsonb, range-типы, массивы, full-text search, рекурсивные CTE, оконные функции и ещё кучу фич и в хвост и в гриву и очень довольны. Постгрес офигенен, слазить с него не собираемся.

@horlon 13 окт 2015 в 10:45

Сложных проэктов было много. Постоянная переписка проэктов c FireBird и Postgresql на MySQL показала, что никаких проблем в отсутствии функционала MySQL нет (вопреки уверенным заявлениям предыдущих разработчиков). Никогда не использую внутренний функционал баз СУБД (в основном только INSERT, UPDATE, DELETE, SELECT). Просто пишу классы на PHP.
SQLite недотягивает до моих потребностей совсем немного — базы слижком большие и по размеру и по количеству записей да и самих баз много и медленный он для таких масштабов, а самое главное, что я привык работать с MySQL и написал под него массу своих утилит. Не раз хотел пересесть (когда только начал работать с базами) на postgre или Firebird, но получив большой опыт в работе с базами и в самом программировании (до такого уровня, что могу сам написать СУБД) — смысла в этом так и не вижу. SQLite меня привлекает более полной лицензионной свободой (помню времена были, когда postgre и Firebird тоже были под BSD лицензиями), но не дотягивает он до идеала… Несмотря на то, что он менее громоздкий, нежели другие СУБД, но и в нем есть куча ненужного мне функцыонала, а нужные качества отсутствуют. В данный момент переписываю очередную систему идентификации с FireBird на MySQL, полет нормальный…

@VolCh 13 окт 2015 в 07:58

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

@chabapok 13 окт 2015 в 10:36

Правда ли, что постгресу нужен строго родной коннектор — c тем же номером билда, что и сам постгрес?
Есть история от человека, работавшего с постгресом — понадобилось обслуживать систему, которой несколько лет, а коннектор оказался утерян. Новые коннекторы все ругаются, мол, конфликт версий, и не хотят коннектиться к старому постгресу. Старых версий уже просто так не найти. В результате, даже базу не экспортировать, и вообще ничего не сделать. Искали, просили по форумам, на поиски ушел где-то месяц. Повезло, нашелся человек, который им помог. Было это лет 7-8 назад.

@galaxy 13 окт 2015 в 13:26

Нет. У них протокол менялся в районе 8-й что ли версии, так что все, что умеет новый протокол, будет работать и с новейшими версиями базы.
(Если под коннектором вы имеете в виду клиентскую библиотеку типа libpq, на уровне фреймворков и active record все, конечно, по-своему)

@phoenixweiss 13 окт 2015 в 11:49

Если мы работаем с определенным уровнем абстракции, например используя ActiveRecord, для большинства «обычных» проектов нет разнице, pg там или не pg, за взаимодействие в любом случае отвечают коннекторы. Но понимание технологичских отличий в структуре БД в любом случае нужно.
Спасибо за статью, нескольких моментов не знал. На слоне у нас всего пара проектов за все время была, где без него никак, в подавляющем большинстве других спокойно использовали и используем мускуль.

@jonywtf 13 окт 2015 в 12:07

Не так давно открыл для себя pgmodeler. На мой взгляд это ближайший кроссплатформенный аналог MySQL workbench для PostgreSQL.
Исходники открыты, но за бинарную сборку просят денег) Кому не лень можно собрать из исходников.
Всем доволен, но хотелось бы создавать отдельные EER диаграммы. Сейчас все таблицы в одной куче…

@romy4 13 окт 2015 в 14:48

Navicat умеет в EER диаграммы

@viat0r 13 окт 2015 в 12:27

Документацию забыли. Как по мне, то документация Postgres не только на голову выше, чем в MySQL — она одна из лучших среди всего opensource. Мне думается, что по ней можно изучать не только СУБД, но и SQL.

@maxidler 13 окт 2015 в 16:44

Хорошая статья, но все очень субъективно, когда касается скорости работы и т.д. Цифры где?
Автор говорит, что postgres не работает из коробки если как следует не настроить. Кстатим MySQL тоже работает из коробки, но как-то очень плохо, если его не настроить. А еще в обоих случаях нужно обязательно тюнить операционную систему.
Не очень понятно — тюнил ли автор MySQL и ОС, когда пытался использовать MySQL?

Функционалом Postgres очень богат — ФАКТ!!!
Но вот всем ли нужен этот функционал???

В целом получилась неплохая реклама для Postgres, а я ждал больше объективностей, из которых новичок мог бы следать вывод типа: Ого! В моем проекте нужно просто правильно потюнить MySQL. ИЛИ Упс! Да в моем проекте без Postgres никак!

@varanio 13 окт 2015 в 18:41

> Цифры где?
Ну просто сферические тесты в вакууме приводить не хотел. Поэтому честно написал про субъективность. Хотя может в следующей статье… Кстати, какие бы тесты вы хотели бы видеть, чтобы они были наиболее объективны?

> тюнил ли автор MySQL и ОС
yes. Мы нанимали DBA для тюнинга и выжимали всё что могли. Пришлось перейти на postgres в основном из-за того, что репликация на наших объемах тупо не успевала. И баги были довольно странные (опять же, на малых объемах их может быть и не было бы). После перехода на постгрес с репликацией не было проблем вообще, ну может какие-то несущественные мелочи.

> а я ждал больше объективностей
Собственно, я честно пытался придумать, чем mysql может быть круче, и ожидал, что в коментах накидают много всего дополнительно. Но накидали как-то вяло, если честно.

@FSA 13 окт 2015 в 18:25

Ну вот. Статья снова привела в уныние что изначально в своём небольшом проекте использовал MySQL, а не PostgreSQL. Столько всего вкусного что упростило бы жизнь.

@varanio 13 окт 2015 в 18:42

Если проект небольшой, то сможете перевести его на pg за день или два. Если будут вопросы — обращайтесь в личку, помогу чем смогу

@FSA 13 окт 2015 в 18:46

Я давно задумывался о переводе. Но, к сожалению, быдлокод мешает. Сейчас полным ходом перевожу PHP код на использование PDO. Когда всё доделаю, можно уже и переходе задуматься. Один раз пробовал переделать, но столкнулся с проблемой прямого преобразования из-за изобилия всяких «0000-00-00 00:00:00» и прочих перлов. Тоже приходится по ходу изменения кода править базу.

@FSA 13 окт 2015 в 21:15

Есть необходимость переноса нескольких десятков таблиц из MySQL в PostgreSQL. В своё время не нашёл как автоматизировать процесс. Какие есть варианты? Желательно чтобы это можно было сделать под Linux.

@zzashpaupat 13 окт 2015 в 21:21

Сам недавно занимался подобным делом. Для начала посмотрите тут:
wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

Лично мне больше всего приглянулся pgloader, им и воспользовался.

@varanio 14 окт 2015 в 04:05

Спасибо. Судя по описанию, pgloader охренеть какая штука, на будущее запишу себе на всякий случай.
Кстати, перед миграцией, я бы для начала поставил strict mode в mysql и погонял код на предмет ошибок

@lybin 16 окт 2015 в 18:01

Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить. Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать.

Аналогично и с mysql, на серьезных проектах требуется тюнинг иначе медленно работает(в сети множество статей и куча параметров). Отсюда становится под вопросом пункт про производительность.

Зарегистрируйтесь на Хабре, чтобы оставить комментарий