Всем доброго времени суток. Иcпользуется сервер PostgreSQL 8.3 в качестве БД для хранения полученной мониторинговой информации. То есть записи в нескольких таблицах обновляются постоянно, практически независимо от времени суток, минимальное число транзакций 10-15 в секунду, померяно запросом
SELECT SUM(xact_commit+xact_rollback) as trans from pg_stat_database where datname = '...'
и отслежена разница значений в различные промежутки времени. В течение суток нагрузка по обновлению так же меняется, пиково до 50-60 транзакций в секунду, в среднем 30-35.
Объем основной таблицы в чистом виде пока около 30 млн строк (естественно, растет довольно быстро). Для анализа мониторинговой информации используются различные запросы, основная цель которых - обработка данных мониторинга за некий временной период(вполне большой, например, месяц) и выдача статистистических данных типа суммы ,среднего и пр.
Как сделать так, чтоб при выполнении этих запросов снизить уже сейчас сильные тормоза?
Варианты с использованием индексов, VACCUM уже пробовали, помогает, но слабо. Нужно изменить структуры выполнения запросов.

Естественным образом родилась идея считать промежуточные отчеты в периоды низкой загрузки БД ночью (например, за час или сутки) и хранить отдельно, используя при генерации окончательных отчетов. Но тут встали вопросы:
1) С какой периодичностью делать промежуточные отчеты (минута, час, сутки, неделя)?
2) Делать ли это в несколько слоев? То есть собирать промежуточные отчеты за меньшие промежутки времени, потом за большие, а потом уже их использовать в окончательных запросах?

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

Хотелось бы узнать, правильной ли дорогой идем и делал ли кто так раньше. Если делали по другому, то поведайте, как именно

Nafсделать две базы:
1. OLTP, максимально упрощенная, без лишних связей и взаимодействий между таблицами, просто тупо пишем в нее
2. OLAP, реплицируем в нее данные в менее нагруженное время. Данные могут быть избыточны для ускорения скорости работы запросов. Запросы только на чтение.
С уважением, Naf

Спасибо за совет.
Была подобная мысль. Только не для разных БД, а для разных схем в пределах БД (особенность используемой СУБД, но по сути тоже самое). Пока отложили ее реализацию, идея не понравилась тем, что база будет разрастаться просто так вдвое, если выполнять полную репликацию. Можно совместить Вашу идею с нашей, то есть выполнять репликацию уже с промежуточными отчетами

Кифирчик,

К сожалению, решение бюджетное на одном диске ATA/SATA, поэтому приходится компенсировать отсутсвие RAID и/или SCSI программными изворотами. В конкретном контексте (идет постоянный INSERT в таблицу, откуда идут выборки) увеличение кэша практически не поможет :(
Отношение hit / read (http://postgresmen.ru/articles/view/48) для базы очень маленькое (около 55-60), поэтому самым узким местом у нас являестя диск

Konstantin~выбор варианта зависит от ситуации. Если вы стоите/поддерживаете систему "для себя", т.е. система для внутреннего использования, то вариант А предпочтительней т.к. покупка второго сервера и настройка репликации [намного ] дешевле времени разработчиков на переделку и тестирование новой системы по варианту Б.

если система делается на продажу или это система которая приносит основной доход организации, то тогда надо переделать систему по варианту Б.

Существующий вариант интересный. Некоторые системы уже стоят на внешних объектах и изменить их "железный состав" нельзя, некоторые еще на стадии разработки и согласования, соот-но, их состав поменять можно. То, что крайне желательно использовать многодисковые структуры - это понятно (теперь:( ), сейчас вопрос состоит в минимизации суммарных затрат денег и времени на внедрение внедрение более-менее эффективного решения, которое можно использовать как на развертываемых системах, так и на существующих.
Использование OLAP под PostgreSQL (http://www.sql.ru/forum/actualthread.aspx?tid=345406) под большим сомнением, предложенная по ссылке документация писана иероглифами Может, кто знает другие средства?
Может, не очень ясно была поставлена задача - в отчетах нужна информация как архивная, так и оперативная, то есть с точностью до последнего обновления таблицы телеметрии. Если использовать схему с разделением, то все равно потребуется выполнять выборку к таблице с оперативной информацией.

На Ваш взгляд, какова будет эффективность использования подобной схемы:
1) Вынести на один диск малоизменяющуюся часть базы и операционку (диск speed_l), на второй - таблицу телеметрии и индексов по ней (диск speed_h)
2) По мере заполнения таблицы телеметрии (раз час, например, период подобрать опытным можно путем), переносить данные на диск speed_l и там уже считать статистику напрямую. А перенесенные данные с диска speed_h удалять, выполняя vacuum для поддержания высокой эффективности вставки и поиска.
3) Уже для перенесенных данных на диске speed_l выполнять подсчет промежуточной статистики и хранить ее в отдельной таблице (делать это в моменты наименьшей загрузки). Хотя тут уже спорно, может и не выполнять промежуточных подсчетов.
4) При запросе отчетов брать данные из промежуточных подсчетов, а затем, если в отчете требуется оперативная информация, лезть на диск speed_h за ней

Zorrik,

Как я понял, основной упор сделан на распараллеливании записией по нескольким БД, находящихся на разных машинах и кэшировании запросов обновлений? Причем признаком объединения записей в обин блок кэша служит код устройства, приславшего данные?

Если так, то да, для очень масштабных решений это скорее всего подойдет. Хотя интересно - а собирали ли Вы статистическую информацию на такой архитектуре? И как будет выполнятся запрос, если данные потребуются не по устройству-источнику сообщений, а по объекту, на котором могут быть установлены разные устройства или будет идти миграция устройств со временем по объектам? Ведь прийдется шарить по разным базам на разных машинах. У Вас есть отдельный механизм, который это регулирует?

Zorrik,

Просто я так понял, что информацию Вы раскидываете по разным машинам, объединяя их по устрйоствам? скажем, данные с первых 100 устройств на 1 машине, со следующих 100 на 2й и тд?
И отдельно храните информацию по установкам устройств на объекты. Так вот, интересно, если устройства будут мигрировать по объектам (монтаж/демонтаж) как Вы будете получать информацию по данным с одного объекта, если там за запрашиваемый период стояло разное оборудование (сначало одно устрйоство, а потом другое)? Ведь получится, что запрос нужно будет разбивать к разным БД на разных машинах?
1) Это прийдется предусматривать чисто технически (или используется какая-то схема автоматизации?)
2) Как долго будет выполнятся такой запрос? С учетом сетевого взаимодействия между БД при выполнении запросов.

Или изначально в проект заложено, что вероятность миграции устройств мала и узким местом это точно не будет?

Zorrik,

А какой анализ скорости выполнения запросов на выборку? Например, не элементарного запроса по номеру устройства, а чего-нибудь посложнее, гарантированно требующего обращения к нескольким серверам на разных машинах? Ожидание тайм-аутов, передача сообщений и пр.
Как я понимаю, такая архитектура была выбрана из-за ограниченя на поддержку произвольных СУБД? Иначе в пределах того же Postgres или Oracle тоже самое можно орагнизовать встроенными средствами.

Всем спасибо за выраженные мнения и переданный опыт! Будем экспериментировать. Если еще у кого есть какие мнения - делитесь, пожалуйста

ZorrikЗапросы пока профайлили не особо. По сравнению со старой версией быстрее раза в полтора на выборках по 100 тыс. сообщений.
А как была организованна предыдущая версия? Чтоб было с чем сравнить