Целью данного сообщения не является holy war и вообще какое-либо общее сравнение различных СУБД. Я лишь хочу показать, что для моей задачи лучше подходит postgresql, и объяснить, почему я так считаю. Возможно, кому-нибудь это поможет сделать более взвешенный выбор, а комментарии к этому сообщению помогут мне по-другому взглянуть на мои проблемы. Кроме того, прошу о некоторой доли снисходительности, так как я не являюсь специалистом по СУБД, и мои знания по ним имеют достаточно узкий и несистематический характер.

Итак, моя задача - предбиллинг:
1. хранение большого количества телефонных вызовов (~300 млн. записей);
2. поиск в диапазоне дат, что соответствует индексному скану по большому количеству записей (от 400 тыс. до нескольких млн.);
3. объёмные выборки (обычно за сутки, что соответствует ~400 тыс. записей);
4. малое количество одновременно работающих клиентов (максимум 2);
5. отсутствие UPDATE - только однократный ежесуточный массовый INSERT;
6. увы, скромное железо: P4 2.4 GHz, 1Gb RAM, выделенный под БД SATA винт;
7. Windows 2000 Server SP4.

Около года успешно использую Firebird 1.5 SS . База в основном состоит из большой (18 Гбайт) таблицы и индекса по полю типа timestamp (размер индекса 2 Гбайта). Остальные таблицы сравнительно маленькие.
Что не нравится:
- медленный backup и restore. В моём случае намного быстрее остановить работу БД и сделать файловый бэкап базы, чем пользоваться gbak. Впрочем, в версии 2.0 появилась утилита nbackup, которая кардинально шустрее и гибче, чем gbak.
- невозможно штатными средствами прервать длительный запрос (?)
- некорректное, с моей точки зрения, использование кеша Windows, что на выборках большого количества записей приводит к распуханию кеша и активному свопу. Как мне удалось выяснить, одной из причин является открытие файла БД с флагом FILE_FLAG_RANDOM_ACCESS, из-за чего Windows даже при последовательном чтении файла пытается сохранить в кеше как можно больше страниц файла. Проблему удалось решить правкой исходников FB, чтобы файл БД открывался с флагом запрета кеширования. В Yaffil это можно настроить через конфиг. В конфигурации железа с 1,5 и более гигабайтами ОЗУ этот пункт практически неактуален, так как Windows в любом случае выделяет под кэш не более 900 мегабайт. В FB 2.0 эту, если можно так выразиться, проблему тоже не решили.
А в целом, жить вполне можно.

Как альтернатива, была испробована СУБД MySQL 5.0.16 (как на MyISAM так и на InnoDB). Плюсы движка MyISAM - компактность таблиц, недостаток (в моём случае не очень серьёзный) - отсутствие транзакций. InnoDB - намного больший объём, но с транзакциями. Особо тщательно, правда, я этот движок не тестировал. Недостатки MySQL (на обоих движках), из-за которых я не стал использовать эту СУБД:
- медленный индексный скан. По сравнению с FB разница 3 раза не в пользу MySQL даже на MyISAM.
- любое создание/удаление индекса приводит к полному пересозданию таблицы и всех существующих для неё индексов.

О PostgreSQL у меня на основе слухов сложилось мнение, что это какая-то навороченная, но медленная и сырая СУБД. (Не спрашивайте ссылок и точных источников - сложилось и всё). Но для коллекции, как только на работе появилась передышка, решил попробовать и её (сейчас стоит версия 8.1.4 ).
Прежде всего, столкнулся с нелогичностью установочной процедуры под Windows: сначала в инсталляторе предлагается установить СУБД как службу, что требует права администратора, а затем создать базу, для которой наоборот надо обязательно права простого пользователя. Так, извините, под каким пользователем запускать инсталлятор? :) Проблему решил просто: поставил под админом, от создания базы отказался, и создал её потом под обычным юзером вручную.
На sql.ru подсказали, что для моей задачи лучше всего применить partitioning. Попробовал - действительно удобно, и позволяет совсем отказаться от индекса по timestamp. Правда, были какие-то странные глюки с падением производительности при скане таблиц. После 'vacuum analyze' всех таблиц глюк пропал (просто analyze не помогало).
Теперь, что удалось добиться (по сравнению с FB) по пунктам:
- Значительно уменьшилось время внесения данных в базу (с 400 до 60 секунд). Такая разница была достигнута в большей степени в результате значительной оптимизации программы подготовки данных, которая, в свою очередь, стала возможной благодаря наличию команды 'copy'. В общем, быстродействие СУБД в этом случае не играет решающей роли.
- PG "дружит" с кешем Windows даже на больших выборках, так что ничего "подкручивать" в этом плане не пришлось.
- Теперь об очень приятном: выборка типа 'select ... from <моя большая таблица> where (datetime between ... and ...) and ...' на PG проходит в 4 раза быстрей, чем на FB. На PG узким местом является винчестер (50 Мб/сек, загрузка CPU ~60%), а на FB - процессор (12 Мб/сек, загрузка CPU ~90%). Я это связываю с накладными расходами на распаковку записей и индексов в FB, а может дело в чём и другом, не знаю...
- Применению partitioning очень способствовала версионность DDL. В результате, в одной транзакции я, если необходимо, удаляю старую таблицу, создаю новую, вношу в неё данные. А если что-то пошло не так, делаю rollback, и всё остаётся как было. В FB такой фокус с DDL не пройдёт, но там частый DDL, мягко говоря, и не приветствуется.

Для себя отметил следующие недостатки PG:
- как упомянуто выше - нелогичный инсталлятор.
- данные занимают в PG заметно больше места, чем в FB. В первую очередь из-за заголовка записи (около 28 байт в PG против 13 байт в FB), плюс все char и varchar поля в PG имеют 4-хбайтовый префикс длины поля. В FB - он 2 байта. В моём случае это вылилось в то, что одни и те же данные в FB занимают 18 гигабайт, а в PG - около 25.
- меньше актуальной русскоязычной документации, чем по FB или MySQL. Недавно видел в конференции по PG как за описанием версионности отправляли на ibase.ru. :-) Вообще, должен отметить, что ibase - просто замечательный кладезь информации по IB/FB, а в тамошней конференции много грамотных специалистов, которые помогут в сложной ситуации. Так что, если ваш выбор FB, и вы всё ещё не были на ibase.ru - бегом туда! :)
- DBD-Pg без "обработки напильником" не компилируется под Windows. Так как я с БД работаю в основном из перла, то для меня это весьма неприятное обстоятельство.
- PgAdmin сильно проигрывает по удобству и функциональности таким программа как IBExpert или Firebird Development Studio.

Теперь о "недостатках", которые я таковыми не считаю:
- Говорят, PG сложен в настройке, и без предварительного тюнинга в реальной работе практически неработоспособен. У меня вся настройка заняла 1 рабочий день при том, что я PG вообще первый раз в жизни увидел. По второму пункту: покажите мне того разработчика, который поставит неизвестную ему СУБД под рабочую нагрузку, не покрутив или хотя бы не проверив её настройки? По крайней мере, для всех пробуемых мной СУБД я считал своим долгом хотя бы ознакомиться со всеми доступными настройками.
- В FB база лежит в одном файле, а вот в PG - целый каталог. Ну, тут комментировать, собственно, нечего - бэкапить всё равно в архив, а там не важно, каталог это или один файл.

Комментарии и замечания всячески приветствуются.

dimitrпозволю себе несколько замечаний:

по FB:

1) про FILE_FLAG_RANDOM_ACCESS ты не прав :-)


Не прав, значит не прав - спорить с разработчиком FB не буду :)

dimitr
3) про медленный backup. Прошу прощения, но с чем сравнивали? С дампом в скрипт или с файловым копированием (имею ввиду другие СУБД)?


Сравнивал я, конечно, с файловым копированием. Дамп в скрипт на таких объёмах - просто нереален. (На PG я планирую настроить бэкап через архивирование WAL). Nbackup в FB 2.0, я считаю, фактически решает проблему бэкапа и оперативного (!) восстановления большого объёма данных. А инкрементный бэкап так вообще замечательная штука. Мои претензии относятся только к gbak.

dimitr
по PG:

1) про "очень приятное" - это просто уход от индексного скана в пользу фуллскана партиции, тут все ожидаемо.


В том-то и дело, что первым вариантом на PG был индексный скан по одной большой таблице. В прямую он не сработал - оптимизатор не включал bitmap scan. А вот когда я создал индекс не по timestamp, а по выражению cast(datetime as date), то включился bitmap scan и я получил при индексном скане более 30 мегабайт в секунду. Примерно столько даёт FB на фуллскане.

Alexander A. SakПро инсталлятор PG.
Мне он как раз показался логичным.
...
Мне, кстати, инсталлер сам все сделал: и юзера создал и службу от его имени запустил.

Беру свои слова назад. Видимо, что-то я не то вовремя установки делал. Сейчас попробовал на другом компьютере - действительно поставилось без проблем. Прошу прощения за дезинформацию.

Не думал, что мой пост вызовет такой резонанс. :)

Почему выбирал именно из этих трёх СУБД? Потому что бесплатно, и настраиваются намного проще коммерческих монстров, на предварительное изучение которых надо потратить не один месяц.

Вообще, чтобы понять мой выбор, и как я дошёл до жизни такой :), немного истории (надеюсь, модератор мне простит лирику, так как она имеет отношение к теме топика).
Работаю я программистом на небольшом узле связи, имею непосредственное отношение к биллинговым файлам ЦАТС EWSD, хранением и экспортом которых и занимаюсь. Ко всему прочему, я с двумя своими коллегами-инженерами обслуживаю эту станцию (модуль поменять, новое направление сконфигурировать, кабель протянуть и т.д.), слежу за небольшим и, слава Богу, не хлопотным парком компьютером (пара RM200 под Reliant Unix, несколько компов под Windows, собственноручно настроенный маршрутизатор под FreeBSD). Обеспечиваю связь станции с внешним миром, например автоматическое включение/выключение абонентов за неуплату. Обслуживаю 12 компов ADMOSS (это полуавтоматика, больше известная как «07»). Ну и прочее, прочее...
Так как на одну зарплату не прожить :), по совместительству я занимаюсь сбором, хранением и экспортом биллинговых файлов ЦАТС C&C08 в другой конторе (тоже всё сделал на FB). Но там объёмы намного скромнее и не достойны упоминания. В общем, совмещаю работу программиста, инженера АТС и эникейщика, и свободного времени у меня не густо. Вернее, непрерывного свободного времени, которое я мог бы продуктивно посвятить глубокому изучению чего-нибудь сложного, и чтобы не дёргали постоянно… :(
Так вот, на основе информации из биллинговых файлов готовится несколько видов статистики, делается экспорт на сервер учёта стоимости (у нас повремёнка с 1996 года), экспорт в головную контору для обсчёта присоединённых операторов и их статистики. Раньше все эти программы работали с биллинговым файлом напрямую. Но однажды мне сильно надоело писать однотипные программы, большую часть которых составляли процедуры чтения биллинговых файлов, и я перешёл сначала на промежуточные csv-файлы (ужас какой!), а потом уже на Firebird. А сейчас пытаюсь мигрировать на PostgreSQL. При этом моё высокое и не очень руководство не то что не поощряло мою инициативу, а наоборот: с большим скрипом удалось выпросить отдельный винчестер, а памяти, хотя и просил два гига, купили только один. На компьютере, на котором я сейчас пишу это письмо (его конфигурацию я указывал в первом письме топика), кроме меня с компиляторами, Opera, двумя СУБД (FB и PG), Apache, Squid, Argus (фидошная нода) и прочая-прочая :), ещё крутится контроллер домена, а в терминальной сессии копошится мой начальник.
А вы говорите, «попробуй DB2», а «пусть начальство купит»... :-(

dimitr Andrew SagulinСравнивал я, конечно, с файловым копированием
вот именно. Разные же вещи. GBAK надо именно со скриптом сравнивать. Это не придирка, а скорее уточнение.

Дело даже не в бэкапе, который длится около 2 часов, что совсем не напряжно при таких объёмах. Дело в ресторе, который на моём железе выполняется, точно не скажу, потому что до конца так ни разу и не дождался, но точно более 14 часов. Вот и перешёл на файловый бэкап, что, как тут сказали, "стрёмно" :), но, тем не менее, работает.

vadiminfo
Вы тоже верите что из ничего можно делать что-то? А как же закон сохранения материи?


Я просто выкручиваюсь как могу, при этом ещё "стараюсь получить удовольствие".
Согласитесь, что для поставленной задачи нет нужды ставить коммерческие СУБД вроде Oracle или DB2. Как я понимаю, их сила (и вытекающая из этого сложность изучения и настройки) в универсальности и возможности заточить под сложные условия эксплутации. Меня же вообще устраивал FB, но попробовав PG, я понял, что для моей задачи он всё же больше подходит.

Причём, FB с прикладной обвязкой мне удалось запустить в работу в течение пары месяцев (я до этого не то что с FB, вообще с базами данных никогда не работал!). На настройку и выбора способа работы с PG ушло меньше недели (с Ораклом так получится?). Сейчас не спеша переписываю под PG свои перловые скрипты, а дома по вечерам, уложив сына спать, урывками дочитываю недавно купленного Грабера (азы SQL надо знать! :) ).
Честно говоря, я искренне завидую людям, у которых есть возможность глубоко и последовательно изучать что-нибудь одно, достигая в этом больших высот (или глубин :) ). Я был бы счастлив с головой погрузиться в изучение того же Оракла (есть тут у меня на одном компе работающая 8-ка personal edition) - благо там есть, где развернуться, но ведь не получится - текучка задолбает. :(