Переезд с PostgreSQL на MySQL

[serega011]

Вам повезло что в вашей базе ругань была только на «type», когда я проект переносил ругань была буквально на все. Я не понял вы переносили только структуру?, я переносил еще и данные, в результате сбились поля где хранилось время+дата, хотя эти поля были для нас маловажны, поэтому нас это удовлетворило.(кстати у меня был дамп Постгрес базы, а не сервер с самой БД, т.е. я еще себе предварительно устанавливал сам постгрес и пытался туда сунуть этот дамп, тоже кстати без ручной правки не обошлось).

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

Наверно все это можно было проделать проще и быстрее, но я не знал как.

[godin]

Переносил данные, а схемы таблиц Drupal создал сам. Время+дата не сбились — проверял.
И да — MySQL пришлось поставить и пытаться туда сунуть дамп :)
Надо будет взглянуть на MSQL2PSQLS131 — ссылку не подскажите?

[godin]

И да — ругань была не только на «type», а также на всякие «что-угодно». Прикол именно в кавычках на запросе INSERT INTO name («column») VALUES (...);

[nerezus]

> MySQL to PostgreSQL, понятно почему
С PgSQL оно производительнее?

[godin]

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

[khim]

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

P.S. Речь идёт, понятно, о продуктах более-менее заточенных на определённую СУБД. Если вы используете универсальное решение типа Drupal, то вы теряете на универсальности столько, что говорить о преимуществе MySQL или PostgreSQL смысла нет.

[OdobenusRosmarus]

Прямо таки все? и даже если есть индекс по user-rating, то использовать его не будет?

[pwlnw]

А у вас ведь в табличке articles хранятся тексты? вы используете тип TEXT или BLOB?
Есть такая проблема и она связана с тем что mysql не может определить сколько памяти ему понадобится для хранения результата, поэтому создает файл. Все здравомыслящие люди размещают временный каталог mysql в памяти. Особенно кстати оказывается tmpfs в линуксе.

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

explain select * from articles
inner join (select article_id FROM articles WHERE user_rating > 5 ORDER BY published_at DESC LIMIT 100 )
as lim using (article_id);
*************************** 1. row *********
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row *********
           id: 1
  select_type: PRIMARY
        table: articles
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: lim.article_id
         rows: 1
        Extra:
*************************** 3. row *********
           id: 2
  select_type: DERIVED
        table: articles
         type: index
possible_keys: NULL
          key: published_at
      key_len: 14
          ref: NULL
         rows: 5
        Extra: Using where; Using index

Как видите filesort исчез и появился covering index. Как чудесно читать документацию, правда?

Другое дело что mysql действительно мог бы быть поумнее. Эта оптимизация выглядит очевидной для человека.

[OdobenusRosmarus]

уу… если не идти против ОРМ, то тут сразу надо просто покупать новые сервера… :-) и это слабо зависит от СУБД

[khim]

Простейший запрос вида SELECT * FROM articles WHERE user_rating > 5 ORDER BY published_at DESC LIMIT 100; мусклем выполняется из рук вон плохо: он будет сортировать на жестком диске _все_ статьи.

Действительно подобный запрос, который сложнее, чем 99% (а то и 99.9%) запросов, выполняемых web-приложениями в таком виде будет выполняться медленно — особенно если не будет соотвествующих индексов.

[khim]

Я не буду даже пытаться этого сделать! Вместо этого я введу ещё одну таблицу где соберу только статьи пользователей с рейтингом больше 5 и отсортирую их по published_at.

Что-что? Я потеряю в гибкости? Ну да, разумеется. Но тут уж выбирайте — вам шашечки или ехать.

P.S. Вообще для большинства Web-приложений (не требующих обработки 10'000 запросов в секунду) MySQL — не лучший выбор именно потому что сложно заранее предусмотреть все запросы подобного вида и предусмотреть для них отдельные таблицы.

[khim]

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

Популярность MySQL там, где его козыри не могут себя проявить, а недостатки очевидны — загадка и для меня самого.

[khim]

так в чем его козыри то?

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

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

Какого фига MySQL используют web-студии для простых и слабо загруженных сайтов (когда возможный выигрыш от MySQL не сможет дополнительных денег, вбуханных в разработку и за 1000 лет) — мне неведомо. В большинстве случаев в результате система работает хуже и медленнее, чем могла бы на основе PostgreSQL…

P.S. Есть у PostgreSQL одна вещь из-за которых я не рекомендую использовать его для больших проектов: невозможность постепенного изменения формата базы (нужен цикл dump/restore при смене версий). Но, опять-таки, это проблема для проектов где данных сотни гигабайт, а их не так много…

[khim]

ммм, зачем dump/restore? В 8.2+ уже есть достаточно неплохой автовакуум, но как говорят, все равно нужно вакуумизировать руками.

Какая разница? Все эти способы всё равно требуют останова системы на изрядный промежуток времени и не позволяют делать эффективный откат. Как происходит upgrade у нормальной базы данных? В четыре этапа:
1. Устанавливается новая версия бинарников (данные остаются в старом формате).
2. Данные конвертируются в новый формат «на лету» (измененённые данные заносятся в специальные временные таблицы — понятно что для этого нужна поддержка в приложении).
3. Приложение переписывается с использованием новых фич — разумеется они работают только с переконвертированными таблицами.
4. Постепенно все данные переводятся в новый формат и мы готовы к следующему циклу.
Каждый из этих этапов занимает несколько недель (либо сам процесс столько занимает, либо столько времени требуется на то, чтобы убедиться в том, что всё работает) и каждый можно откатить за секунды. Но для того, чтобы это дело работало нужно чтобы версия базы данных X поддерживала два формата: X и X-1.

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

Да. Меньше накладные расходы. Насколько я знаю — это до сих пор правда, хотя последний раз бенчмарки я видел год или полтора назад. Просто разработчики PostgreSQL сконцентрированы на другом.

[khim]

Насчет апдейта понял. Разве тут у мускля есть серьезные преимущества перед постгресом?

Да. При upgrade вам необходимо обязательно сконвертировать в новый формат одну базу: mysql. В которой хранятся системные настройки. Они практически никогда не бывает больше пары мег (и даже это — редкость). Всё остальное можно переводить постепенно — буквально по одной таблице.

На 8.3 я посмотрю, но, опять-таки, всё это для небольших проектов. PostgreSQL при всех своих наворотах никак не хочет отказаться от своего «исследовательско-университетского прошлого», что грустно. Может быть поэтому его «серъёзные люди» и не используют?

[Scala]

Почему же все тогда MySQL пользуют?

[khim]

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

Та же ситуация что и «Windows vs Linux» или «Windows vs MacOS»: почти всем очевидно что Windows — самый худший вариант из трёх, но ведь к нему уже все привыкли и для него написаны миллионы программ! Как же их вот прямо так взять и бросить?

P.S. Надо кстати сказать что во многих случаях переход на PostgreSQL всё равно не даст вам того, что можно получить от перепроектирования системы. Ну какой PostgreSQL может спасти «ультрагибкую CMS», которая чтобы породить одну страничку делает пару сотен запросов к базе данных?

[Scala]

Ну так и MySQL же тоже растёт, к 6 версии новый Storage Engine обещали, или вы считаете, что её уже ничего не спасёт?

Все нынешние вордпрессы, джумлы, рельсы и прочие джанги поддерживают как MySQL, так и PgSQL — аргумент «но ведь к нему уже все привыкли и для него написаны миллионы програм» немного не в тему.

[khim]

Falcon — это жутко крутая вещь. Будет. Когда-нибудь. Когда — одному богу ведомо. Там масса проблем и не так шоб уж сильно много рабочих рук.

Что касается «прочие джанги которые поддерживают как MySQL, так и PgSQL» — то это вообще не в кассу. Они поддерживают и MySQL и PostgreSQL одинаково отвратительно. Если вы хотите говорить о производительности — о переносимости сразу забудьте, а если производительность неважна (а это, как ни удивительно, не такой и редкий случай), то не всё ли равно какую СУБД использовать.

[zzeus]

почему же все пользуют php при наличии python/ruby?

[khim]

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

MySQL отлично держит нагрузку на огромном количестве простых запросов. Судя по вашему примеру у вас несколько смещено понятие «простого» запроса. Возможно на вас плохо повлияло обучение релицонным базам данных, возможно вы просто как-то по другому мыслите, но простой запрос — это простой запрос: выборка по одному индексу, сравнение на =, <, >. Вы не поверите, но для огромного числа задач ничего больше и не нужно. Если правильно базу организовать.

[khim]

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

Хотите посмотреть на пример грамотно спроектированной системы на основе MySQL с исходниками — посмотрите на mediawiki: Wikipedia выдерживает хорошо за 10'000 qps.

Но если экстремальные нагрузки вас не интересуют и вас интересует гибкость, то MySQL, несомненно, плохой выбор.

[symbix]

mysql> create table articles (article_id int auto_increment primary key, user_rating int, published_at datetime, key(published_at, user_rating)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into articles values(1,1,'2008-01-01'),(2,4,'2008-04-04'),(3,10,'2007-12-12'),(4,20,'2008-11-11'),(5,10,'2008-11-11');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> explain select * from articles where user_rating > 5 order by published_at desc;
+----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | articles | index | NULL          | published_at | 14      | NULL |    5 | Using where; Using index |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.02 sec)

что я делаю не так?

[pwlnw]

Вы не используете поля типа TEXT или BLOB А автор текстов и постов просто не достаточно глубоко изучил Mysql.

[pwlnw]

проиллюстрирую:

CREATE TABLE `articles` (
`article_id` int(11) NOT NULL AUTO_INCREMENT,
`pagetext` text,
`user_rating` int(11) DEFAULT NULL,
`published_at` datetime DEFAULT NULL,
PRIMARY KEY (`article_id`),
KEY `published_at` (`published_at`,`user_rating`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=cp1251

mysql> explain select * from articles where user_rating > 5 order by published_at desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where; Using filesort

[symbix]

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

[pwlnw]

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

[maxcom]

PostgreSQL до 8.3 тоже так делал при отсутствии индекса

[deerua]

разумные ходы!!! ;) жизнь наверное до этого уже покидала при переносе с одной бд на другую

[deerua]

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

[Quetzal]

Люблю людей который так говорят :)

[elfxf]

люблю людей, которые как и я это любят подчеркнуть :)

[serega011]

А я вообще людей люблю, особенно женского пола.

[godin]

Заранее спасибо.
Кстати, думаю что в случае с для Drupal'ом предложенный способ должен сработать и в обратную сторону ;)

[hobocta]

Кроме того в пункте 7 наряду с тем, что мы «включаем нужные модули» следует упомянуть, что нужно создать необходимые типы материалов с их дополнительными cck полями, иначе не будут созданы таблицы content_type_NODETYPE.

[MasMaX]

Спасибо за статью. Перенес друпал с одного хостинга на другой по пути поменяв базу с PostgreSQL на MySQL.
Было два типа ошибок при переносе:
1) Названия полей в кавычках (всего в двух таблицах было). Быстро решилось заменой в редакторе.
2) Значения полей типа BLOB. Из Постгреса они пришли в виде '\x123abc', а для mysql надо сделать 0x123abc (без кавычек и с нулем). Это самое геморное, но в итоге решил автозаменой регулярным выражением.

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