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

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

> Об Оракле? Прирост в записи, возможность асинхронного io... ммм... а так да, слабовато.

Откуда берется прирост в записи на некэшируемые диски - можешь рассказать? :)

asynс io может работать на файловых системах, raw устройства для этого не обязательны. Не использовтаь кэш тоже большинство нормальных ФС умеет. Накладные расходы на ФС операции? доли процента?

> В контексте СУБД-ОС - возможность содания оптимизированной под СУБД ФС.

В чем именно заключается "оптимизация под СУБД"?

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

Нет, постгрес полагается на OS. Вовсю эксперементируют c неблокирующим и/или упреждающем чтениием.

wtf неблокирующие чтение применительно к контроллеру !?

упреждающее чтение имхо особой полезности большим базам не принесет, во всяком случае mysql и firebird не принес. а вот scatered read по заявам оракла/m$ бывает на порядок быстрее sequental reads и гораздо сильней съэкономит i/o
http://www.dba-oracle.com/art_builder_io_speed.htm

>Откуда берется прирост в записи на некэшируемые диски - можешь рассказать? :)

Стоимость обслуживания линейных устройств выше, чем для файловых систем UNIX (ufs) [4, Millsap (1995a), 15–17]. Но для VLDB с интенсивной записью эта цена крайне мала в сравнении со стоимостью ненужной загрузки ЦПУ и уже без того высокой ценой администрирования системы с сотнями или тысячами дисковых устройств. Производительность при случайном чтении — Крайне незначительно лучше по сравнению с ufs. Производительность при случайной записи — Значительно лучше, в сравнение с ufs, изза уменьшения объема выполняемого кода. Линейные устройства также позволяют реализовать асинхронный ввод-вывод, если он поддерживается операционной платформой. Производительность при последовательном чтении — Незначительно хуже в сравнение с ufs. Использование линейных устройств может катастрофически снизить производительность плохо оптимизированных SQL-приложений по сравнению с ufs-реализацией, поскольку UNIX-кэширование работает лучше кэширования сервера Oracle при полном сканировании таблиц. Производительность при последовательной записи — Значительно лучше, в сравнение с ufs, из-за уменьшения объема выполняемого кода и возможности асинхронного ввода/вывода. Частота отказов — Риск возникновения возрастает из-за необходимости в более опытном администраторе. Длительность простоя — Риск увеличения возрастает из-за необходимости в более опытном администраторе. Снижение производительности в течение отказа — Отличия от нормальной производительности обусловлены замечаниями приведенными выше. Стоимость приобретения — Та же, что для ufs. Стоимость обслуживания — Хуже, чем для ufs. Требуется большее обучение и оплата труда персонала для конфигурирования и обслуживания линейных устройств. Конфигурирование линейных устройств также требуют закупки или разработки программных средств для упрощения управления подсистемой ввода/вывода. Различие стоимости администрирования линейных устройств и ufs незаметны на фоне общей стоимости системы ввода/вывода для VLDB с тысячами дисков. Стоимость обслуживания оперативных журнальных файлов не изменяется, поскольку эти файлы архивируются также как на ufs процессом ARCH. http://www.citforum.ru/database/oracle/vldb/6.shtml#3.5.

>В чем именно заключается "оптимизация под СУБД"?

Хотя бы отмена журналирования ФС, унификация этой функции wal, обращение к диску всех програм через СУБД-ФС с выставление приоритетов, выполнение части кода в едином адресном пространстве.

>Хотя бы отмена журналирования ФС, унификация этой функции wal, обращение к диску всех програм через СУБД-ФС с выставление приоритетов, выполнение части кода в едином адресном пространстве.

дык, поставь oracle files на raw и будет у тебя это счастье ...

и когда уже можно будет использовать postgre с 1с без патчей ? (((

>и когда уже можно будет использовать postgre с 1с без патчей ? (((

это к 1С а не к постгресу. Напилили блокировочный адъ, теперь версионник патчат. ну окромя сортировки, это да.

> Напилили блокировочный адъ, теперь версионник патчат. ну окромя сортировки, это да.

Блокировочный адъ думается возник не на пустом месте, а из-за увлечения всякими нездоровыми базками типа MySQL и MsSQL. Есть у них небольшая проблемка, которая не позволяет им полноценно работать в качестве многопользовательских баз данныхъ. Вот в частности Том Кайт упоминает об этой проблеме в применении к Ораклу (MySQL он не перечислил, но если мне ничо не изменяет, то он тоже должен быть в чёрном списке):

What impact? Unlike other RDBMS products (SQL-Server, Sybase and Ingres come to mind) there are three very critical differences in how concurrency and locking are handled in Oracle:

a) there are no lock overheads in Oracle (locking 10 million rows is as "expensive" as locking a 1 row as there is no "expense")

b) as there are no expense, there are no lock escalation due to resources being scares (in other RDBMS products locks consumes resources)

c) lockers-of-rows (writers) do not block readers (there is no such concept as a dirty read in Oracle)

So where is the impact on locking when doing a commit after 100 rows versus doing a commit after 10 million rows?

There is no locking impact. The only impact is sufficient redo/undo space to cater for a 10 million row transaction.

После этого становится понятно, что сравнивать базы данных нужно более внимательно, чем по скорости исполнения ими отдельных запросов. То, что оптимизатор иногда врёт и не использует индексы - ну да, это непричтно и неудобно, но это можно обойти. А вот когда приложение, претендующее на гордое звание многопользовательской БД сериализует запросы - это жопа врожденная.

> wtf неблокирующие чтение применительно к контроллеру !?

оно же async io

> упреждающее чтение имхо особой полезности большим базам не принесет

см pgsql-hackers. Может принести в определенных ситуациях.

>>Откуда берется прирост в записи на некэшируемые диски - можешь рассказать? :)

> Стоимость обслуживания линейных устройств выше ... http://www.citforum.ru/database/oracle/vldb/6.shtml#3.5.

Это статья от 1996 года :) и уже в ней говорится - при чтении выигрыш незначителен. При записи - выигрыш связан с объемом кода ФС. по нынешним временам - смешно звучит?

Дальше, еще смешнее ;)

Там еще смешнее. У всех баз данных свои отношения с NULL, 1с создавался тогда, когда MSSQL совсем уж сильно плевал на стандарты ( NULL=NULL возвращает true) и 1с рассчитывает на такое поведение. Остатки такого поведения в MSSQL до сих пор есть, например NULL может быть только один в unique index, что расширяет возможности для оптимизирования запросов.

Ну и дальше в таком же духе.

Еще одна особенность: есть два эквивалентных запроса, один из них быстрее выполняется постгресом, другой - MSSQL. А архитектура/реализация 1c не позволяет выбирать запрос в зависимости от бд.

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

И в результате у них получается platform-and-version-dependend структура датафайлов, и переезд с платформы на платформу возможен только на уровне логическом (imp/exp).

> При записи - выигрыш связан с объемом кода ФС.

А как насчет того, что при базе 30GB и оперативке 8GB, в случае ФС ты отдаешь примерно 3GB на кэш ФС (причем те же данные кэшируются и в СУБД), и в процессе работы слетаешь на Disk I/O

> упреждающее чтение имхо особой полезности большим базам не принесет, во всяком случае mysql и firebird не принес. а вот scatered read по заявам оракла/m$ бывает на порядок быстрее sequental reads и гораздо сильней съэкономит i/o http://www.dba-oracle.com/art_builder_io_speed.htm

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

> Мне вот всегда было интересно, как постгря работает в вебе? Она ж стартует по процессу на каждое соединение - это ж безумно дорого, если разом привалит тысяча-другая человек?

Не знаю как с другими приложениями (php, perl), но веб приложению на жаве обычно хватает одного соединения. Таким образом жава программа представляет собой тонкий клиент.

>Таким образом жава программа представляет собой тонкий клиент.

Вы не претендуете на копирайт-ограничения для цитирования?

> Вы не претендуете на копирайт-ограничения для цитирования?

Даже на лорквотесах его бред не закоммитят :-)

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

А где из explain plan вообще видно что идекса нет ? Я может туплю но надписей типа full scan я не вижу...

>А где из explain plan вообще видно что идекса нет ? Я может туплю но надписей типа full scan я не вижу...

-> 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)

Seq Scan - и есть Full Scan. Видно, что на сортировка фулл-выборки дает львиную долю выполнения: (cost=1451414.25..1457683.87 rows=2507851 width=346) (actual time=169748.964..171696.721 rows=2508901 loops=1) из тотала (cost=1816189.56..1816189.59 rows=10 width=399) (actual time=203229.526..203229.568 rows=10 loops=1)

Уважаемые, расскажите, кто-нибудь как-нибудь победил проблему сборки мусора в PG? Была база, к которой непрерывно шли обращения (select, update, delete). При включенном автовакууме всё тормозило, а делать vacuum full на постоянно нагруженной системе не есть хорошо. Как победить? И как с этим в новых версиях?

> надписей типа full scan я не вижу

Может это: "Seq Scan on msgbase"?

> И в результате у них получается platform-and-version-dependend структура датафайлов, и переезд с платформы на платформу возможен только на уровне логическом (imp/exp).

У постгресса структура файлов version dependend. На тему платформо-независимости я не знаю, но не стал бы надеяться.

> Уважаемые, расскажите, кто-нибудь как-нибудь победил проблему сборки мусора в PG?

В 8.3 с этим должно быть сильно лучше из-за HOT (Heap Only Tuples). Т.е. при выполнении ряда условий место на странице может реюзаться без вакуума и без апдейта индексов.

>У постгресса структура файлов version dependend. На тему платформо-независимости я не знаю, но не стал бы надеяться.

И платформно-зависима: 32/64 бита, byte order, memory alignment.

>> При записи - выигрыш связан с объемом кода ФС.

> А как насчет того, что при базе 30GB и оперативке 8GB, в случае ФС ты отдаешь примерно 3GB на кэш ФС (причем те же данные кэшируются и в СУБД), и в процессе работы слетаешь на Disk I/O

Не, я понимаю что предыдущие сообщения читать не модно. Поэтому повторюсь - большинство ФС, даже в линуксе, не говоря про всякие устаревшие солярки и аиксы, умеют писать на ФС минуя кэш.

scott/tiger

>Уважаемые, расскажите, кто-нибудь как-нибудь победил проблему сборки мусора в PG? Была база, к которой непрерывно шли обращения (select, update, delete). При включенном автовакууме всё тормозило, а делать vacuum full на постоянно нагруженной системе не есть хорошо. Как победить? И как с этим в новых версиях?

Не смотрел в эту сторону:

max_fsm_pages Для целей сборки мусора следует знать где этот мусор находится. Число страниц отведённых под эту задачу должно быть больше, чем число удалённых или изменённых записей между сборками мусора (VACUUM). Если страниц достаточно выполнение жёстких оптимизирующих операций, таких как VACUUM FULL или REINDEX никогда и не понадобится. Так как объём требуемой для этого памяти не очень большой (по 6 байт на страницу), то жадничать не стоит. Проще всего узнать необходимое число max_fsm_pages запустив, VACUUM VERBOSE ANALYZE.
(с) http://www.inp.nsk.su/~baldin/PostgreSQL/postgresql-tuning.pdf

> Видно, что на сортировка фулл-выборки дает львиную долю

А Index scan по primary key (msgbase.id) не используется. Можно попробовать с enable_seqscan=off, но много счастья это не принесет.

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

Писать не очень критично, читать более интересно. Но постгрес вполне рассчитывает на кеширование OS, параметр effective_cache_size как раз об этом.

2maxcom: а Вы настроили этот параметр? 8.2 оптимайзер запросов стал очень чувствителен к нему.

А подскажите, какие запросы к ФС выполняются при различных операциях БД? Т.е. при select random read или sequential read, так? А что при update/insert/delete и т.п.? Т.е. какие свойства ФС наиболее востребованы при использовании БД?

>> Уважаемые, расскажите, кто-нибудь как-нибудь победил проблему сборки мусора в PG? > В 8.3 с этим должно быть сильно лучше из-за HOT (Heap Only Tuples)

Поясните.

>Не, я понимаю что предыдущие сообщения читать не модно. Поэтому повторюсь - большинство ФС, даже в линуксе, не говоря про всякие устаревшие солярки и аиксы, умеют писать на ФС минуя кэш.

Это как же? :)

Direct-io работает только с блочными устройствами, так что запись в обычные файлы _всегда_ идет через кеш страниц.

>Поясните.

Поясняю: http://postgresmen.ru/articles/view/78

> А подскажите, какие запросы к ФС выполняются при различных операциях БД? Т.е. при select random read или sequential read, так? А что при update/insert/delete и т.п.? Т.е. какие свойства ФС наиболее востребованы при использовании БД?

"Перескажите мне кратенько всё, что на сегодня известно человечеству" :)

> какие запросы к ФС выполняются при различных операциях БД?

Про все DB говорить не буду, но для постгреса важно:

1) эффективность ФС на работе со сравнительно небольшим кол-вом файлов приличного размера

2) Оперирование большим числом файловых дескрипторов (по-умолчанию, постгрес одновременно открывает не более 2000 файлов)

3) малая фрагментация больших файлов

4) Размер блока ФС должен быть равен или быть кратно меньше странице БД (8кБ default)

5) всяие atime, журналирование - убийца производительности. Постгрес сам ведет логи операций (естественно, fsync должен функционировать безупречно)

> Т.е. при select random read или sequential read, так?

В среднем по больнице, базам требуется малое время seek. Потому что под нагрузкой (с большим кол-вом разных запросов) доступ к диску происходит достаточно хаотично

>> Не, я понимаю что предыдущие сообщения читать не модно. Поэтому повторюсь - большинство ФС, даже в линуксе, не говоря про всякие устаревшие солярки и аиксы, умеют писать на ФС минуя кэш.

> Это как же? :) Direct-io работает только с блочными устройствами, так что запись в обычные файлы _всегда_ идет через кеш страниц.

Братег-анонимчег, ты тоже читаешь мануалы 12 летней давности? :)

scott/tiger

>>Поясните. > Поясняю: http://postgresmen.ru/articles/view/78

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

>> Direct-io работает только с блочными устройствами, так что запись в обычные файлы _всегда_ идет через кеш страниц. > Братег-анонимчег, ты тоже читаешь мануалы 12 летней давности? :)

А чо, 12 лет назад файлы ещё не лежали на блочных устройствах??

system/manager

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

Tuple - версия row в таблице, каждый row может иметь несколько tuple. Но транзакции виден только одна версия (tuple) row. Tuple, который не виден никому есть мусор.

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

Все зависит от клиента, если это тонкий жаба клиент, то связи действительно не возникают )

Не путай человека, а то убъешся распутывать.

> 4) Размер блока ФС должен быть равен или быть кратно меньше странице БД (8кБ default)

А с размером страницы памяти в системе размер "страницы БД" должен как-либо соотноситься?

> А чо, 12 лет назад файлы ещё не лежали на блочных устройствах??

Да не, ничо, лежали. Но к тому времени, afair, еще даже vxfs direct io for db не родили. И тем более для ufs не было опции монтирования forcedirectio, для jfs - cio/dio и т.д. Про линукс даже не вспоминаю.

Почитай мануалы помоднее, братег :) Или как ут моно говорить - вылезай из анабиоза? ;)

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

нет, это разные вещи. к примеру у MySQL есть упреждающее, он просто насилует диск sequential read'ами больше, чем нужно запросу в надежде, что "лишние" блоки понадобятся еще кому-то.
scattered read это одна команда конроллеру, дай отсюда, по сюда, и проц в этом деле не участвует, контроллер сам через DMA складывает прочитаное, отсюда туча экономии в i/o

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

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

>> Это как же? :) Direct-io работает только с блочными устройствами, так что запись в обычные файлы _всегда_ идет через кеш страниц.

>Братег-анонимчег, ты тоже читаешь мануалы 12 летней давности? :) 

Нет, я открываю обычный файл на запись с флагом O_DIRECT, делаю запись и получаю ошибку :)

#define _GNU_SOURCE

#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <errno.h>
#include <stdio.h>

int main(int argc, char *argv[])
{
	int fd, err;

	fd = open("/tmp/test", O_RDWR | O_DIRECT);
	if (fd == -1) {
		printf("Failed to open file with O_DIRECT, errno: %d.\n", errno);
		return -1;
	}

	err = write(fd, "This is a test\n", 15);
	if (err < 0) {
		printf("Failed to write into file, errno: %d.\n", errno);
		return -1;
	}

	printf("All is ok.\n");
	return 0;
}

$ ls -l /tmp/test
-rw-rw-r-- 1 chu chu 7 2008-02-21 16:44 /tmp/test
$ gcc ./directio.c 
$ ./a.out 
Failed to write into file, errno: 22.

> (с) http://www.inp.nsk.su/~baldin/PostgreSQL/postgresql-tuning.pdf
Спасибо за ссылку.
Тормозило там все в двух местах (тогда, когда сборки давно не было):
1. на очень большой (относительно) часто обновляемой таблице.
2. на очень маленьких очень часто обновляемых таблицах.
Почему автовакуум не справлялся даже с очень маленькими таблицами для меня загадка...
Для маленьких было проще сделать vacuum full, а вот большую было гораздо быстрее перезалить, чем дождаться вакуума. Я, конечно, уверен, что это не проблема pg, а проблема реализации ПО. Но поведение сборщика мусора для меня было странным. Не слышал про подобные проблемы в других СУБД...

>Уважаемые, расскажите, кто-нибудь как-нибудь победил проблему сборки мусора в PG? Была база, к которой непрерывно шли обращения (select, update, delete). При включенном автовакууме всё тормозило, а делать vacuum full на постоянно нагруженной системе не есть хорошо. Как победить? И как с этим в новых версиях?

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

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

Каких тестов?? Я просто пытаюсь понять что такое "страница БД" в терминологии постгреса.