PostgreSQL 8.3: улучшение производительности в разы

>До ли дело ms access - все интуитивно понятно.

Глобально и Надежно забыл.

Может кто подскажет, я заметил у Postgresql такую фичу (багу?)

Есть две таблицы, допустим
CREATE TABLE tbl_a (
id INT PRIMARY KEY
);

CREATE TABLE tbl_b (
ref INT REFERENCES tbl_a(id) ON DELETE CASCADE ON UPDATE CASCADE
);

У tbl_b есть тригер на BEFORE DELETE, который иногда НЕ возвращает OLD при удалении (из-за неких условий).

Так вот, если сделать DELETE FROM tbl_a WHERE id = xxx,
при этом, когда в каскадном удалении будут удаляться записи из tbl_b, и тригер вернет неуспех, то из tbl_a запись удалиться, а в tbl_b останется.

Таким образом целостность базы вроде как нарушается. Я думал оно все в одной транзакции работает и удаление из tbl_a должно откатиться, если тригер в tbl_b вернет неуспех.

Где я ошибаюсь?

>Неоптимально, но качественно? :) сильно сказано.

Что-то не правильно? Лучше сначала правильно и ортогонально, с продуманой архитектурой, хорошей масштабируемостью и приличным функционалом. Оптимизация тогда - дело житейское, показатель качества, тогда как ранняя оптимизация зарыла не один проект.

>От дистрибутива к дистрибутиву - все разное, от конфигов до до мест их хранения. До ли дело ms access - все интуитивно понятно.

Бредишь?

>Где я ошибаюсь?

Нигде, все правильно, так и должно быть. Вы вмешались в ход транзакции, не передали дальше ОЛД - запись не удалилась.

>До ли дело ms access - все интуитивно понятно.

Ага, особенно когда куча VBA кода который использует тележку внешних библиотек ради ActiveX компонетов и импорта-экспорта.

>>>Где я ошибаюсь?

>>Нигде, все правильно, так и должно быть. Вы вмешались в ход транзакции, не передали дальше ОЛД - запись не удалилась.

И как это лечить?

О! Недавно сам перелез на postgres. Кстати может кто знает, в убунтушной сборке после отката транзакции в mysql данные остаются, подключаю аналогичный mysql на другой машине (виндовой) - всё ок, меняю DataSource на сабж - всё ок. Это и послужило толчком к переходу на postgres.

>KRoN73 - для embedded есть SQLite и хорошо так есть :)

H2 - Pure Java и может быть полностью включён в проект. Автономным .jar А SQLite потребует биндингов и таскания с собой sqlite-либ, разных для винды и для Linux. Придётся делать два разных дистра.

>Кстате - to любителю "померить скорость" - этот малыш порвет всех!

По тестам sqlite капитально сливает на параллельных запросах.

>H2 - Pure Java и может быть полностью включён в проект. Автономным .jar

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

>И как это лечить?

По телепатии у меня одни двойки были :) Пишите в аську 199295556.

> Может PostgreSQL и лучше, но баблос пилить на MSSQL намного эффективней. Факт.

это люди не знаю как пилить бабло. На MSSQL в любом случае большая часть перепадёт MS, а с PostgerSQL/MySQL можно 95% загрести себе (5% -- это обналичка)

Недавно тестил postgresql 8.2.6 с помощью встроенной утилиты pgbench. Сначала поставил fedora 8 (i386) собрал postgres, сделал несколько прогонов теста ... затем поставил fedora 8 (x86_64) и сделал тоже самое. Машина использовалась одна и таже. Для чистоты экперимента все сервиса заглушил в обоих случаях. В общем на i386 (x86_32) показатели производительности были существенно лучше чем на x86_64. Слегка удивился ... В общем в плане повышения производительности есть куда копать.

>>Кстате - to любителю "померить скорость" - этот малыш порвет всех!

> Ну ну. "Тормоз перестройки" (с) это, как только доходит дело до более менее серёзных запросов.

так Вам embedded или "серьёзные запросы"? для последних как раз и делают отдельные базы, чтоб им основная программа не мешала

>это люди не знаю как пилить бабло. На MSSQL в любом случае большая часть перепадёт MS, а с PostgerSQL/MySQL можно 95% загрести себе (5% -- это обналичка)

Да вот не только. Например, самая распростаненная система с БД - 1С. Не далее месяца назад выслушивал от франчайзи, что они, конечно(а как же), смотрели постгрес, но это небольшое поделие для маленьких конторок компов на 5-ть, а мсскл - это сила, это ИМЯ, ПРОВЕРЕННЫЙ БРЭНД, вот его они нам и продадут, со скидкой. ) И слово ПРОВЕРЕННЫЙ БРЭНД - главное для моего начальства (

>Недавно тестил postgresql 8.2.6 с помощью встроенной утилиты pgbench. Сначала поставил fedora 8 (i386) собрал postgres, сделал несколько прогонов теста ... затем поставил fedora 8 (x86_64) и сделал тоже самое. Машина использовалась одна и таже. Для чистоты экперимента все сервиса заглушил в обоих случаях. В общем на i386 (x86_32) показатели производительности были существенно лучше чем на x86_64. Слегка удивился ... В общем в плане повышения производительности есть куда копать.

А пробовали тоже самое на бинарниках с репозитария или оффсайта? Собрать, оно, знаете, по разному получается, тут контрольный бенч относительно готовых бинарников нужен. Я использую и в работе и x86_64, и x86_32, бинарники, чтобы именно _существенные_ отличия были не замечал. Хотя особо и не сравнивал.

Интересно, а хоть одна астрономическая БД на этом проверенном бренде крутится? А это Вам не хухры-мухры - конторы любых размеров нервно крят в сторонке против баз с такими размерами. В случае PostgreSQL с этим всё тип топ - искать в сторону Олега Бартунова.

>Интересно, а хоть одна астрономическая БД на этом проверенном бренде крутится? А это Вам не хухры-мухры - конторы любых размеров нервно крят в сторонке против баз с такими размерами. В случае PostgreSQL с этим всё тип топ - искать в сторону Олега Бартунова.

Это я и без вас знаю, и кто такой Бартунов знаю, и что такое постгрес хорошо знаю - пять лет решения на нем строю, разные, астрономией не увлекался, но ГИС-подсистема была. Но слово "ПРОВЕРЕННАЯ СУБД" сказанное представительным молчелом в "ачках, кастюме и галстуке" весит куда больше моего, и галстук я не ношу. ПО _продавать_ надо, от начала и доконца, как докторскую, и тут играют первоочередную роль другие факторы нежели его качество.

> Интересно, а хоть одна астрономическая БД на этом проверенном бренде крутится?

Крутяться - в США. Часть базы Олега конвертирована из MSSQL - около 2Тб. Это был просто праздник жизни: загрузить 2Тб архива в MSSQL и сдампить в pgsql. Операция заняла около двух недель.

Суммарный объем астрономической базы около 8Тб.

>>Неоптимально, но качественно? :) сильно сказано.

> Что-то не правильно? Лучше сначала правильно и ортогонально,

А это как?

> с продуманой архитектурой, хорошей масштабируемостью и приличным функционалом.

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

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

>А это как?

Это просто. Главное чтобы на гугле не забанили.

>Пример был - полная выборка с сохранением результатов на диск, для того чтобы после отобрать диапазон - это продуманная архитектура и хорошая масштабируемость?

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

>Объём сам по себе не имеет значения - данные лежат себе на диске и лежат, если их не трогать. А что с характером запросов к базе?

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

>> Что-то не правильно? Лучше сначала правильно и ортогонально, >А это как?

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

> Пример был - полная выборка с сохранением результатов на диск, для того чтобы после отобрать диапазон - это продуманная архитектура и хорошая масштабируемость?

Так никогда не было. При использовании LIMIT и ORDER BY было два варианта поведения постгреса:

1) Полная сортировка рез-татов выборки и выдача необходимых по LIMIT

2) Если ORDER BY совпадает с индексом, то шаг сортировки мог отсутствовать (именно мог, естьь ситуации где постгрес не использует такой индекс)

Сейчас появился третий способ: Top-N sort. Идея его проста - что бы получить первые десять рез-татов по ORDER BY совершенно не требуется сортировать всю выборку, достаточно просмотреть всю выборку и отобрать первые 10 рез-татов. То есть, первый способ требует время ~ M*log(M), где М - число результатов в выборке, а новый при N<<M - ~M.

> Данные умеют лежать на диске по разному. Постгресу бы умение юзать raw-девайсы, оптимизировать движение головок, и организовывать самостоятельно полный дисковый кэш - было бы нереально здорово.

В современном мире используются RAID'ы - не науправляешься из базы.

> В общем на i386 (x86_32) показатели производительности были существенно лучше чем на x86_64. Слегка удивился ...

Сайбейз, к примеру, честно и осторожно писал (для 32 и 64 битных версий под солярис), что при объемах памяти меньше 4Г 64-битная версия не дает преимуществ и в некоторых случаях может работать медленнее.

Хотя Оракл, например, на сайте пишет что использование 64 битной архитектуры позволяет обрабатывать вдвое больше данных за такт и дает прирост производительности до 2-х раз :)

>В современном мире используются RAID'ы - не науправляешься из базы.

Хардвардными? А Оракл то и не знает.

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

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

Это с нуля или оспользуя наработки open source? Я не призываю курочить постгрес, но отдельный проект, который бы слил ОС с СУБД был бы интересен. Шо, помечтать нельзя? :)

>> В современном мире используются RAID'ы - не науправляешься из базы.

> Хардвардными? А Оракл то и не знает.

А чем оракл управляет? движением головок? :) и что такое полный дисовый кеш?

> Это с нуля или оспользуя наработки open source? Я не призываю курочить постгрес, но отдельный проект, который бы слил ОС с СУБД был бы интересен. Шо, помечтать нельзя? :)

"Слиять" постгрес и ОС бессмысленно - что бы получить выигрыш от управления дисками самим постгресом, нужно писать свой аналог FS - с заточками под базу и ее особенностями хранения. Да еще и учитывающую собственно характеристики железки. Например: постгрес складывает таблицы гигабайтными файлами (что бы не бороться с ограничениями FS на размер файла). В оптимизированной FS имеет смысл сразу резервировать этот гиг - что бы sequence scan читал блоки последовательно. Но для SSD можно так не напрягаться - random seek у них много дешевле. С другой стороны, зачем тогда вообще оставлять нарезку?

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

А так, конечно мечтай :)

>"Слиять" постгрес и ОС бессмысленно ...

Все же все сказанное вами как раз осмысливает такое проект, я, конечно, не имею ввиду простое тупое "сливание".

> Данные умеют лежать на диске по разному.

Данные ничего не имеют. И пока их никто не трогает на производительности их объём не сказывается.

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

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

> Например: постгрес складывает таблицы гигабайтными файлами (что бы не бороться с ограничениями FS на размер файла). В оптимизированной FS имеет смысл ...

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

Про raw-девайсы, похоже гёрла просто слабо себе представляет в чем их преимущество.

> Хардвардными? А Оракл то и не знает.

представь себе.

Ну да. У 1-го варианта с сортировкой есть некоторые проблемы. Если данные для сортировки не помещаются в work_mem то и происходит сброс результатов выборки на диск. При этом, limit/sort находится в верху плана исполнения запроса (если нет подзапросов), что может быть сильно не оптимально. Вот например два запроса, дающих одинаковый результат:

1) select comments.id, msgbase.message from comments, msgbase where comments.id=msgbase.id and comments.id in (select id from comments where postdate>(CURRENT_TIMESTAMP-'1 year'::interval) order by title limit 10) order by title;

2) select comments.id, msgbase.message from comments, msgbase where msgbase.id=comments.id and postdate>(CURRENT_TIMESTAMP-'1 year'::interval) order by title desc limit 10

Только первый исполняется 20 секунд, а второй - 204 секунды. В запросах специально выбрана сортировка по полю title по которому нет индекса.

Если сортировать по полю на который есть индекс, то все встает на свои места (2-й выполняется быстрее).

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

Угу. Согласен.

>Ну да. У 1-го варианта с сортировкой есть некоторые проблемы.

Есть и сейчас или были до 8.3 ???

Хм. А explain analyze обоих запросов глянуть можно?

>представь себе.

представил. понравилось :) да, фигню морознул.

>Про raw-девайсы, похоже гёрла просто слабо себе представляет в чем их преимущество.

Об Оракле? Прирост в записи, возможность асинхронного io... ммм... а так да, слабовато. В контексте СУБД-ОС - возможность содания оптимизированной под СУБД ФС.

Первый:
 Sort  (cost=127908.25..127908.27 rows=10 width=399) (actual time=20692.882..20692.883 rows=10 loops=1)
   Sort Key: public.comments.title
   ->  Nested Loop  (cost=127733.57..127908.08 rows=10 width=399) (actual time=20542.653..20692.783 rows=10 loops=1)
         ->  Nested Loop  (cost=127733.57..127818.97 rows=10 width=65) (actual time=20522.088..20614.089 rows=10 loops=1)
               ->  HashAggregate  (cost=127733.57..127733.67 rows=10 width=4) (actual time=20485.396..20485.407 rows=10 loops=1)
                     ->  Limit  (cost=127733.42..127733.44 rows=10 width=61) (actual time=20485.353..20485.381 rows=10 loops=1)
                           ->  Sort  (cost=127733.42..129441.71 rows=683316 width=61) (actual time=20485.350..20485.373 rows=10 loops=1)
                                 Sort Key: public.comments.title
                                 ->  Index Scan using comment_postdate on comments  (cost=0.00..33484.60 rows=683316 width=61) (actual time=0.105..883.664 rows=684857 loops=1)
                                       Index Cond: (postdate > (now() - '1 year'::interval))
               ->  Index Scan using comments_pkey on comments  (cost=0.00..8.52 rows=1 width=61) (actual time=12.864..12.864 rows=1 loops=10)
                     Index Cond: (public.comments.id = "IN_subquery".id)
         ->  Index Scan using msgbase_pkey on msgbase  (cost=0.00..8.90 rows=1 width=346) (actual time=7.865..7.867 rows=1 loops=10)
               Index Cond: (public.comments.id = msgbase.id)
 Total runtime: 20712.815 ms


Второй:

 Limit  (cost=1816189.56..1816189.59 rows=10 width=399) (actual time=203229.526..203229.568 rows=10 loops=1)
   ->  Sort  (cost=1816189.56..1817897.85 rows=683315 width=399) (actual time=203229.524..203229.561 rows=10 loops=1)
         Sort Key: comments.title
         ->  Merge Join  (cost=1601059.07..1623846.35 rows=683315 width=399) (actual time=173706.568..178257.319 rows=684860 loops=1)
               Merge Cond: (comments.id = msgbase.id)
               ->  Sort  (cost=149644.82..151353.11 rows=683315 width=61) (actual time=2170.860..5215.803 rows=684860 loops=1)
                     Sort Key: comments.id
                     ->  Index Scan using comment_postdate on comments  (cost=0.00..55396.11 rows=683315 width=61) (actual time=0.059..860.830 rows=684860 loops=1)
                           Index Cond: (postdate > (now() - '1 year'::interval))
               ->  Sort  (cost=1451414.25..1457683.87 rows=2507851 width=346) (actual time=169748.964..171696.721 rows=2508901 loops=1)
                     Sort Key: msgbase.id
                     ->  Seq Scan on msgbase  (cost=0.00..361962.51 rows=2507851 width=346) (actual time=43.313..64588.979 rows=2508901 loops=1)
 Total runtime: 204002.733 ms

> но отдельный проект, который бы слил ОС с СУБД был бы интересен.

Тебе смотреть на OS/400. Оно и есть в самом натуральном виде. Правда, не опенсорц и по IBMовской цене.

V.K.

>Хм. А explain analyze обоих запросов глянуть можно?

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

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

>Тебе смотреть на OS/400. Оно и есть в самом натуральном виде. Правда, не опенсорц и по IBMовской цене.

На IBMовском железе по IBMовской цене, с IBMовской поддержкой по IBMовской цене? :)

> Есть и сейчас или были до 8.3 ???

У меня 8.2, 8.3 я еще не пробовал. Насколько я понимаю, работа 1-го варианта осталась без изменений, но теперь кроме второго если еще и 3-й, который как раз и предназначен для ситуаций когда количество строк в LIMIT небольшое, а количество строк в выборке велико.

Нда. Для второго плана замена Sort на Top-N sort сильно не поможет.

Надо постгрес учить пропихивать лимит вниз по дереву запроса.

>На IBMовском железе по IBMовской цене, с IBMовской поддержкой по IBMовской цене? :)

Ну дык! Но зато именно то, что заказывали - ОС-СУБД. Уже 20 лет как... Да и в плюс - .NET-подобное ядро системы и Java на уровне ядра. В общем, вкусно, коли денег хватит :-)

V.K.

Вопрос: постгрес при фуллскане умеет заюзать scattered read сказийного контроллера ? под виндой mssql и оракл readfilescatter из win32 api для юзают.