Доброе время суток. Имеется не очень большая (пока) база PG 9.2\Gentoo на VM (8vCPU, 24 GB, RAID 10).
shared_buffers = 16GB. Основная нагрузка приходится на 4 таблицы, которые в сумме занимают около 60 GB. Периодично (несколько раз в день) происходит удаление и перезапись данных (математические расчеты) в объеме примерно 10-15 %. Еще реже - примерно 1 раз в месяц - полное обновление всех активных таблиц (на текущем железе ресурсоемкий процесс занимает 10-12 часов).
Уже выполнено партицирование, активный объем данных для связанных таблиц примерно 3-4 GB. Оптимизация чтения организована с помощью предрасчитаных агрегатов (этакая мини пародия на OLAP). Сейчас на этапе разработки достаточно VACUUM FULL
Вопрос - как организовать работу на продуктиве? Как обычно, 24/7, доступ блокировать нельзя даже частично. Как в таких условиях настроить autovacuum? Пытались сделать это на настройках по умолчанию, но при запущенных операциях пересчета все тормозится до совсем медленной скорости. Или изощраться и придумывать варианты с кроном?

Maxim Boguk,

Боюсь, что я не совсем корректно поставил вопрос. Есть некоторая система, которая содержит много текстов. Эти тексты анализируются по определенным правилам, которые меняются со временем. После каждого изменения - нужно обработать часть текстов. Обработка ведется не СУБД, а отдельной программой по типу BOINC - прочитал аргумент, посчитал, удалил старые результаты, записал новые. Тексты и результаты разбиты в партиции, объем связанных данных в одной партиции - 3-4 GB, то есть в теории в памяти могут быть размещены 2-3 партиции совершенно свободно (сервер выделеный).
На текущий момент обработчиков 6, причем каждый из них работает в 4 потока - всего 24 подключения. Загрузка CPU - 75-80%. При дальнейшем наращивании мощности начинают проскакивать ошибки подключения и взаимоблокировки. Особенность обработки такова, что такая загрузка может прийти в 24/7. Естесвенно, при включенном autovacuum (AV) в жестком режиме скорость обработки падает до неприемлемых величин + тормозятся еще и функции выборок для других пользователей.
Пока есть следующие мысли:
1) Оптимизировать планировщик задач, что бы он выдавал задание на обработку из разных партиций одновременно (2-3, что бы использовать буфер по полной). Это позволит снизить вероятность конкуренции на уровне доступа
2) В сочетании с п.1 перейти на PG 9.4 и использовать разогреватель кэша ( http://habrahabr.ru/post/234909/#prewarm)
3) Выполнять VACUUM после каждой обработки (или перед что неважно) целиком или по мере обработки партиций для каждой партиции
4) Ломать голову над детальной настройкой AV - что бы он включался, но только при минимальной нагрузке в фоновом режиме. Но как я понял, явно указать это нельзя, только косвенно через его настройки. Только я не могу понять как.

kamakama,

Для того, чтоб бы понимать объем записей. На обработку одного текста уходит порядка секунды, вместе с чтением самого текста, обработкой и записью результата. Стало быть, где-то в секунду идут 25-30 транзакций, содержащих DELETE, INSERT и UPDATE

Maxim Boguk,

Насчет дисков - стоит RAID 10 и виртуальная машина. Сейчас тащим все это на физическую машину с двумя RAID 10, на одном разместим БД, на другом clog и xlog. Но принципиальные проблемы, описанные выше, все равно останутся

Maxim Boguk,

Я не спорю, что живут. Главное отличие от остальных проектов в том, что у меня нагрузка не размазана более-менее по времени и при этом нельзя выделить время в течение суток или недель с минимальной нагрузкой для оптимизации. Нагрузка может прийти 24\7
Производительность вычислителей достигла таких возможностей, что запись результатов стала самым узким местом, однако суммарная скорость обработки все равно не такая хорошая, как хотелось бы. При стандартной работе AV получается так, что он отследит изменения в таблицах autovacuum_vacuum_threshold (например) = 10000 (это произойдет через секунд 30 после начала работы с партицией), и потом начнет ее оптимизировать. На партицию уходит по 30 минут времени в среднем. А в это время ее все равно продолжают пользовать другие вычислители и довольно активно (расчет не кончен). Получается рост нагрузки, который заметно тормозит сам процесс расчетов. Вот я и пытаюсь подобрать наиболее оптимальный режим. Думаю над autovacuum_vacuum_scale_factor, поставить его в 0.5, что бы максимально снизить влияние на работу вычислителей. Или воспользоваться идеей п.3 в асинхронном режиме для каждой партиции.

qwwq,

Тексты - увы, они обновляются (в них вставляются определенные метки). Причем сами метки и их положение расчетные величины. По результатам расчета в отдельные таблицы кладутся описания этих меток (на текст их много, до 1000) + некоторая доп. информация, всего 4 таблицы. Truncate - увы, невозможен, будет нарушена целостность БД при сбоях. Задачи раздаются вычислителя небольшими порциями (по 1000 штук, время обработки около 3-4 минуты). Тем более, что задача может стоять не в том, чтобы обработать все тексты в партиции, а только часть в них

tadminkamakamaОбработка ведется не СУБД, а отдельной программой по типу BOINC - прочитал аргумент, посчитал, удалил старые результаты, записал новые.
Вы придумали себе проблему - автовакуум хотите с ней бороться. Но, возможно, ваша проблема выше описана.
Не делает ли внешняя программа лишней работы?
Не перезаписывает ли по 10 раз в одной транзакции большие объемы данных, порождая мегатонны dead tuples?
Не следует ли хранить промежуточные данные в unlogged таблицах или вообще в памяти?
Нельзя ли внешнюю программу (или самые дорогостоящие операции) перенести в хранимые процедуры?
1) Поэтому и думаю, как его настроить или отказаться и делать это в зависимости от нагрузки
2) Нет, одна транзакция - один обработанный текст.
3) Интересная мысль, думаю вполне возможно, осталось решить вопрос с целостностью на случай сбоев. При пропаже питания все результаты пропадут. Но это это излечимо повторной обработкой
4) Ууу, нет, с этого начинали. У этого решения в принципе нет масштабируемости (ну кроме покупки мэйнфреймов с 256 и более ядрами). Как только уперлись в производительность сервера (для моих задач главный критерий - это число ядер CPU) и обработка стала занимать несколько суток сразу пошли другим путем.

Кстати, так возможно использовать только unlogged. Temp table есть только в рамках одного подключения, а у меня по подключению на поток, всего потоков пока 24

qwwqkamakamaqwwq,

Тексты - увы, они обновляются (в них вставляются определенные метки). Причем сами метки и их положение расчетные величины. По результатам расчета в отдельные таблицы кладутся описания этих меток (на текст их много, до 1000) + некоторая доп. информация, всего 4 таблицы. Truncate - увы, невозможен, будет нарушена целостность БД при сбоях. Задачи раздаются вычислителя небольшими порциями (по 1000 штук, время обработки около 3-4 минуты). Тем более, что задача может стоять не в том, чтобы обработать все тексты в партиции, а только часть в нихкхм.

первое что приходит в голову -- рассказать анекдот про буратино и гайку на пузе.

но не будем об очевидном.

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

хотя единственно верное радикальное решение --расстрелять автора перед строем из духового ружья.
или же переехать на блокировочник
Радикально. Но все же отвечу
1) Про совмещение расчетных меток - еще большой вопрос, что будет оптимальней с точки зрения системы в целом. Функция (plperl скорее всего), которая генерирует тексты на сервере многократно при обращении к ней каждый раз или однократный ее расчет и запись результата
2) Уже упоминалось, что расчет может вестись не по всей партиции, а по ее части. Можно писать результаты в unlogged, но потом придется делать операции над множествами, выясняя, кто ж попал в пересчет, а кто нет. Тоже весьма спорный вопрос с точки зрения производительности, хотя мы выиграем truncate вместо delete. Его разрешит только проведение опыт. Спасибо за советы:)

tadminЕще непонятно, как используются данные.
Одно дело, если для потребителей они целиком заморожены (на день, часы). Тогда прямая дорога последовать совету qwwq.
Если же потребители должны получать непрерывно обновляемые данные, это будет много сложнее

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

qwwqkamakama,

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

как только вся партиция накроется новыми версиями (в новых партициях) [отслеживать по журналу, а не сравнивая множества. с вас станется] -- дропать старьё к беням.
Хм. А если вся партиция не накроется никогда? Ну будет один текст, который не покроется правилами и не будет включен ни в один пересчет, а остальные пересчитают по 10 раз? Так и будем хранить журнал, который будет в разы больше оригинала и каждый раз при обращении считать дельту?

vyegorovkamakamaПроизводительность вычислителей достигла таких возможностей, что запись результатов стала самым узким местом, однако суммарная скорость обработки все равно не такая хорошая, как хотелось бы.
Полагаю, что проблема больше в записи результатов. Я бы попробовал shared_buffers уменьшить. 16Гб приводят к избыточной нагрузке на ЦПУ и неравномерной дисковой нагрузке.

Поставьте 2Гб и сделайте bgwriter гораздо агрессивнее. Спасибо, попробуем

tadmin,

Не, никаких извратов типа JSON в строке или чего-то подобно. Просто анализируется текст, по результатам анализа пишутся в таблицы слова + найденные характеристики (позиция, окружение и т.п.). При изменении правил выделения слова могут в том же тексте как добавляться, так и удаляться. А в самом тексте ставятся маркеры, для отображения информации. Да, это дублирующая операция и в принципе можно каждый раз восстанавливать маркеры "налету" при запроса текста, но это накладно с точки зрения ресурсов (нужно рассчитывать в том числе случаи, когда маркеры накладываются друг на друга. Даже если это делать с конца строки, то все равно накладно. Да и это лишняя нагрузка на сервер, с которой неплохо справится клиент). Сразу предвосхищая вопросы про FTS и почему его не используем. Он не устраивает по ряду причин, в частности, из-за своей прямолинейности (хотя и эффективности, особенно GIN. Размер GIN на одну партицию с текстами примерно 200-250 МБ, работает шустро). Точнее, его используем но только на предварительном этапе обработки