FreeBSD + PostgreSQL: тюнинг сервера БД

Привет, Хабрсообщество!

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

Входные данные моей задачи следующие: двухпроцессорная (Intel Xeon) машинка, 8 хардов по 500Гб и 12Гб оперативки. И полный, в том числе физический, доступ к этому добру. Задача: организовать быстрый сервер БД на основе ОС FreeBSD и PostgreSQL.

1. RAID


Правильное разбиение имеющихся хардов на рейды нам понадобится для такой возможности PostgreSQL, как tablespacing (об это ниже). Свои 8 хардов я разбил на пары, организовав таким образом: две пары объеденил в RAID1 и две пары в RAID0 (вообще, для наших целей нужно минимум 6 хардов — две пары объеденить в RAID1, остальные 2 оставить как есть). При наличии же большего числа хардов можно придумать что-нибудь и понадежнее, типа RAID5, RAID10 и т.п., но есть вероятность, что работать это будет несколько медленнее. Не буду вдаваться в подробности как организовывать рейды, т.к. в железе я не силен, скажу лишь только, что никаких контроллеров не трогал, т.к. на серваке после биоса грузится утилита, которая позволяет это сделать программно.

2. Установка ОС, сервера БД и использование своего ядра


Сначала просто ставим фряху на первый RAID1. Я ставил дистрибутив FreeBSD 8.2 Release AMD64 со всеми файлами. 64-х битная версия нужна, чтобы система «видела» всю оперативную память.

Теперь самое интересное: для чего нам компилить ядро и какие параметры изменять? Это нужно, чтобы разрешить серверу PostgreSQL использовать столько ресурсов, сколько потребуется для высокой нагруженности. Итак, какие параметры БД нас интересуют. В книге Алексея Васильева «Работа с Postgresql. Настройка, масштабирование» рекомендованы следующие параметры для высоконагруженных БД (файл postgresql.conf):
  • shared_buffers = 1/8 RAM или больше (но не более 1/4);
  • swork_mem в 1/20 RAM;
  • smaintenance_work_mem в 1/4 RAM;
  • smax_fsm_relations в планируемое кол–во таблиц в базах * 1.5;
  • max_fsm_pages в max_fsm_relations * 2000;
  • fsync = true;
  • wal_sync_method = fdatasync;
  • commit_delay = от 10 до 100;
  • commit_siblings = от 5 до 10;
  • effective_cache_size = 0.9 от значения cached, которое показывает free;
  • random_page_cost = 2 для быстрых cpu, 4 для медленных;
  • cpu_tuple_cost = 0.001 для быстрых cpu, 0.01 для медленных;
  • cpu_index_tuple_cost = 0.0005 для быстрых cpu, 0.005 для медленных;
  • autovacuum = on;
  • autovacuum_vacuum_threshold = 1800;
  • autovacuum_analyze_threshold = 900;

Эти опции нас действительно устраивают, кроме двух:

1) Максимальное число соединений

Зависит от конкретной ситуации. У меня работает скрипт в кроне (коннектиться к БД и заносит данные), я посчитал, что должно хватить 256:
  • max_connection = 256;

Но умолчальная конфигурация FreeBSD не предусматривает такое значение для числа соединений. Если вы выставите такое значение и попытаетесь запустить демон постгреса, то ничего не выйдет. Нужно увеличивать соответствующие параметры системы. Для этого и соберем свое ядро. Возьмем умолчальный конфиг ядра GENERIC, сделаем копию с именем KERNEL_MAX_PERF, отредактируем KERNEL_MAX_PERF следующим образом: изменим число семафоров, добавив к дефолтным опциям строки:
options SEMMNI=512
options SEMMNS=1024
options SEMUME=64
options SEMMNU=512

(это значения для max_connection = 256).

2) Максимальный объем оперативной памяти, который может занять PostgreSQL (это важно при объемных запросах). За него отвечает параметр shared_buffers в postgresql.conf. Насчет значения для этой величины существуют разные рекомендации. Я пришел к выводу, что если это выделенный под БД сервер, то можно одному процессу отдать почти весь объем оперативки минус то, что нужно системе на свои нужды. Я выделил 8Гб из 12. Для того, чтобы система разрешила выставить нужное нам значение для shared_buffers, в ядре необходимо изменить опцию SHMMAXPGS, значение которой вычисляется по формуле:

SHMMAXPGS = shared_buffers / PAGE_SIZE

в моем случае shared_buffers = 8Гб, PAGE_SIZE = 4Кб для всех i386, значит
SHMMAXPGS = 8 * 1024 * 1024 / 4 = 2097152); теперь можем записать параметр SHMMAX (вычисляется в ядре динамически). Итак, пишем в конфиг ядра:
options SHMMAXPGS = 2097152
options SHMMAX = "(SHMMAXPGS*PAGE_SIZE + 1)"


Осталось скомпилировать ядро с конфигом KERNEL_MAX_PERF. Сама процедура компиляции ядра проста, здесь я вас отсылаю к официальному ману.

Загружаем ОС со своим ядром, устанавливаем PostgreSQL последней версии (у меня это была версия 9.0.4), сначала для проверки стартуем PostgreSQL с умолчальным конфигом. Если все ок, меняем параметры в postgresql.conf на оговоренные выше, делаем рестарт PostgreSQL. Запустилось — едем дальше.

Замечание: если по какой-либо причине не удалось скомпилить ядро с выставленными параметрами, то можно их прописать в sysctl.conf:
kern.ipc.shmall=2097152
kern.ipc.shmmax=8589938688
kern.ipc.semmap=256

и запускать фряху с умолчальным ядром GENERIC.


3. Tablespacing


Tablespacing — это возможность PostgreSQL определять в файловой системе локации, где будут храниться файлы, представляющие объекты баз данных. Проще говоря, если мы раскидаем таблицы, индексы и логи на разные диски, то чтение запись/данных будет происходить быстрее, чем если бы все это находилось на одном диске.

Вот здесь нам и понадобятся наши рейды. Напомню, что у нас имеется четыре раздела: два RAID1 и два RAID0. На первом RAID1 у нас установлена операционка и постгрес. На втором RAID1 будем хранить таблицы нашей БД. Предположим, что он монтирован как /disk1. На первом RAID0 будем хранить индексы. Пусть он будет монтирован в файловой системе как /disk2. Логам оставим второй RAID0, предполагаем, что он монтирован как /disk3.

Необходимо сделать следующие шаги:
  1. создать папки под таблицы, индексы и лог:
    #mkdir -p /disk1/postgresql/tables
    #mkdir -p /disk2/postgresql/ind
    #mkdir -p /disk3/postgresql/log
  2. сделать оунера postgres для этих папок, а у остальных отнять все права (напомню, что postgres – это пользователь, который заводится при установке PostgreSQL, если установку производить стандартным образом по официальному ману):
    #chown -R postgres /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log
    #chmod -R go-rwx /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log

  3. зайти в клиент psql под postgres и создать два tablespace:
    CREATE TABLESPACE space_table LOCATION '/disk1/postgresql/tables'
    CREATE TABLESPACE space_index LOCATION '/disk2/postgresql/ind';

  4. если оунер вашей БД не postgres, а, например, myuser, то необходимо дать пользователю myuser права на созданные tablespace (можно выполнить также в клиенте):
    GRANT CREATE ON TABLESPACE space_table TO myuser;
    GRANT CREATE ON TABLESPACE space_index TO myuser;

  5. теперь под myuser 'ом можно изменить tablespace для таблиц и индексов:
    ALTER TABLE mytable SET TABLESPACE space_table;
    ALTER INDEX mytable SET TABLESPACE space_index;

  6. остановить демон постгреса, переместить папку с логом и сделать символическую ссылку на нее:
    #/usr/local/bin/rc.d/postgres.sh stop
    #mv /usr/local/pgsql/data/pg_xlog /disk3/postgresql/log
    #cd /usr/local/pgsql/data
    #ln -s /disk3/postgresql/log/pg_xlog

    Запускаем постгрес:
    #/usr/local/bin/rc.d/postgres.sh start

    Если все сделали правильно, демон должен запуститься.


4. Partitioning


Партицирование – это логическое разбиение одной большой таблицы на маленькие физические куски. Это позволяет существенно ускорить время выполнения запросов, если таблица действительно большая.

У меня довольно типичная ситуация: в кроне работает скрипт, собирая статистику по некоему измерению. По веб-интерфейсу пользователь может эту статистику посмотреть. За неделю в таблицу инсертится примерно 10млн строк. Если все писать в одну таблицу, то вас проклянут. Работать все это будет ужасно медленно.

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

К сожалению, в PostgreSQL партицирование не реализовано на уровне БД, поэтому придется его делать ручками, используя свойство наследования таблиц.

Итак, есть у нас таблица measure_data_master, куда мы пишем свои измерения. Допустим, в качестве временного промежутка устраивает одна неделя. Поехали:
  1. для мастер-таблицы measure_data_master НЕ делать никаких ограничений целостности check и НЕ создавайте индексов
  2. в конфиг postgresql.conf редактировать опцию:
    constraint_exclusion = on

  3. создать таблицы-потомки вида:
    CREATE TABLE measure_data_y2011m06d06 (CHECK(measure_time = DATE '2011-06-06' AND measure_time DATE '2011-06-13')
    ) INHERITS (measure_data_master);

  4. создать индексы для таблиц-потомков:
    CREATE INDEX measure_data_y2011m06d06_key ON measure_data_y2011m06d06(measure_time);

  5. необходимо, чтобы при вставке новой строки, она записывалась в нужную таблицу-потомка. Создадим для этого триггерную функцию:
    CREATE OR REPLACE FUNCTION measure_insert_trigger()
    RETURNS TRIGGER AS $$ 
    BEGIN 
        IF(NEW.measure_time >= DATE '2011-06-06' AND
           NEW.measure_time < DATE '2011-06-13')  THEN
             INSERT INTO measure_data_y2011m06d06 VALUES(NEW.*);
        ELSIF(NEW.measure_time >= DATE '2011-06-13' AND
              NEW.measure_time < DATE '2011-06-20') THEN 
                INSERT INTO measure_data_y2011m06d13 VALUES(NEW.*);
    .....................................
        ELSIF(NEW.measure_time >= DATE '2011-12-19' AND
              NEW.measure_time < DATE '2011-12-26') THEN
                INSERT INTO measure_data_y2011m12d19 VALUES NEW.*);
        ELSE
            RAISE EXCEPTION 'Date out of range.Fix the measure_insert_trigger() function!';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;

  6. ну и сам триггер, который вызовет функцию:
    CREATE TRIGGER insert_measure_trigger
        BEFORE INSERT ON measure_data_master
        FOR EACH ROW EXECUTE PROCEDURE measure_insert_trigger();


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

Вот, пожалуй, и все, что я хотел рассказать. Если поделитесь своим опытом из данной области, буду очень благодарен.

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 67

    +5
    То, что вы разбираетесь в PostgreSQL, заслуживает уважения.
    На презентации Яндекса была шутка о том, что в России есть специалисты по PostgreSQL, и оба они работают в Яндексе :)
      0
      К сожалению, не могу похвастаться, что разбираюсь. Только начинаю вникать во все это. Именно поэтому собираюсь посетить конференцию HighLoad++ по проблемам высконагруженных систем, которая пройдет в октябре в Москве. Надеюсь, постгресятников там будет поболее, чем двое:)
        –1
        Логи на raid0, как-то напрягают. Для логов лучше raid1. Их даже можно забросить на туже дисковую систему, на которой стоит ОС. У меня данные (и индексы) постгрес крутятся на железном рейде (raid6 512м аппаратного кеша, 8 простых 2х терабайтника, место нужно было, поэтому raid6 а не raid10), а логи вынесены на софтовый raid1. И могу сказать, что в моем случае скорость записи логов не является узким местом.
          0
          Руководствовался следующими соображениями: если индексы и логи пропадут в случае поломки, ну ладно:) — это не критично для данных. Индексы можно завести вновь. А вот поломка дисков с разделами, где система с постгре и таблицы с данными — это критично, поэтому их на разделы с зеркалированием.
          А вот логи и постгре на одном разделе хранить, мне кажется не айс, т.к. диску придется по очереди читать то одно, то другое.
            0
            > А вот логи и постгре на одном разделе хранить

            Логи и исполняемые файлы системы — на одной дисковой системе (!= раздел) это нормально. Но логи в любом случае надо писать в отдельный раздел.

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

              0
              Соглашусь.
              0
              если… логи пропадут в случае поломки… это не критично для данных
              Что-то мне подсказывает, что это неверно. Наверное, вот эта статья.
                0
                Хочется взять и проверить.
        0
        Спасибо большое! Интересно что из вышеперечисленного можно применить к mysql?
          0
          Рад был оказаться полезным.
          Несмотря на то, что с MySQL знаком намного дольше, чем с PostgreSQL, пока не приходилось сталкиваться с проблемой высокой нагрузки. Могу лишь сказать, что партицирование для нее будет организовать удобнее, т.к. механизм партицирования реализован на уровне БД.
          +1
          Спасибо за статью, автор! Правда, встречаются слишком очевидные вещи. Те, кто все таки юзает фряху, наверняка сами знают как запустить демон постгреса ;)
            0
            Писал как для себя, может поэтому где-то излишние подробности. Не судите строго:)
            +4
            Да, тюнинг фрюхи смешной. :) Про постгресс немного интереснее. ;)
              0
              Тюнинга фряхи как такового нет. Просто поправил пару параметров для постгре. Но раз уж вы об этом заговорили, поделитесь, пожалуйста, своими знаниями.

              Коллеги, которые наплюсовали к данному посту, не стесняемся, высказываем свое мнение;)
                +2
                Вам уже подсказали ниже, пересборка ядра VS sysctl — это как замена фундамента когда надо окна помыть. А если какой-нибудь параметр надо будет еще потюнить? sysctl — не просто так придуман.
                  0
                  Этот пост раньше был написан.
                  Спасибо за мнение. Мне так даже удобнее — дописать пару строчек в конфиг, чем ядро компилить.
              +1
              Зачим ядро пересобирать дарагой, а? сись-ка-тэ-эл шайтанама и проблема нема, э?
                +1
                Зачем sysctl когда можно пофиксить глобально и навсегда?
                Вы же, когда стекло в окно вставляете, если оно меньше окна, не скотчем кусочки стекла наклеиваете, а просто покупаете другое, большее стекло.

                //я не фряшник
                  0
                  Занес в /etc/sysctl.conf — чем не глобально и навсегда?
                  Можно вообще в /boot/loader.conf засунуть — глобальнее некуда)
                    –2
                    Видимо просто разные философии у нас с вами :_) Оба варианта годны же, думаю.
                    Я просто считаю, что максимум нужно делать «чем раньше, нем лучше» (в адекватной мере).
                      +2
                      Пересобрав ядро из-за такой мелочи, вы лишаетесь возможности обновления ядра через freebsd-update.
                      Т.е. вместо быстрого обновления вы получаете цирк с конями возню с обновлением сорцов и пересбором ядра.
                        0
                        А если для обновления загрузить дефолтное ядро?
                          +2
                          А смысл?

                          Есть три варианта:
                          1) Все стандартное — обновление проще простого.
                          2) Ядро свое, «мир» стандартный — ядро обновляется вручную, «мир» через freebsd-update
                          3) Все собрано вручную — полностью ручное обновление.

                          Может конечно быть еще вариант когда ядро стандартное а «мир» нет — но это уже извращение.

                          В упор не вижу смысла из-за нескольких значений, которые замечательно крутятся через sysctl, наживать себе геморрой в будущем.
                          При апгрейде на более свежую версию «ручное» обновление бывает безумно интересно :)
                            0
                            Спасибо, учту.

                            P.S. А если перспектива с обновлением не грозит, тогда какой вариант изменения параметров предпочтете вы?
                              0
                              sysctl-way
                                +2
                                Я, когда начинал только (с freebsd, кстати, тоже) — фраза "пересобрать ядро" завораживала. Не говоря уже о самом процессе его сборки!

                                По неопытности пересобирал по поводу и без :)
                                  +1
                                  Во-во:) процесс завораживающий:)
                                    +1
                                    Со временем начинает вызывать отвращение:
                                    «О боги опять это дерьмо не работает, теперь пересобирать все ядро :(»
                                    «О черт опять я 2 часа не буду спать из-за того что freebsd-update тупанул или клиент пересобрал себе ядро (на кой черт-то?)»
                                    «Вот блин надо перекомпилировать ядро с новыми 0-day патчами, 4 часа из жизни вон :(, т.к. мир тоже пересобирать»
                                    Короче, не знаю, когда я обновлял с 4.11 до 6.4 это было вызовом, но у меня видимо характер такой, как я добьюсь меня перестает это завораживать и начинает раздражать. Хотя может я просто задолбался тратить 4 часа на пересборку ядра, или 8 когда что-то пойдет не так?
                                +1
                                +1 к systcl
                                на мой вкус, если что-то можно сделать без компиляции, это нужно делать именно так. Наличие стандартных компонент (которые скомпилировал кто-то другой) сильно облегчает поддержку/обслуживание решения в целом.
                          +5
                          А потом при апгрейде ковырять манулов и вспоминать, что творили? :)
                          Сборка ядра хороша, когда надо пофиксить баг, который коммитер ядра уже 3 года подряд «забывает» добавить (например ata_to) или собрать микроядро по размерам для embedded девайса.
                          А если это можно поправить в sysctl.conf или loader.conf зачем мучаться и тратить время?
                          Мне вообще по душе мобильное ядро, когда все что можно и нельзя можно модифицировать в sysctl или loader и желательно на лету. И насколько я вижу разработчикам фряхи это тоже к счастью по душе. Большинство модулей уже можно подключать динамически, а большинство внутренних скрытых переменных модифицируемы через sysctl.conf и loader.conf.
                    +1
                    Хозяйке на заметку:
                    Вместо пересбора ядра, достаточно лишь выкрутить соответствующие sysctl'и:
                    kern.ipc.sem*
                    kern.ipc.shm*
                      +1
                      Единственный момент — в итоге, если выкручивать всякие значения с азартом или в следствии острой нужды, это может упереться в максимальное кол-во памяти, доступной ядру (vm.kmem_size и vm.kmem_size_max).

                      При увеличении kmem >= 1Gb на i386 все-таки надо будет пересобрать ядро с увеличенным значением KVA_PAGES, иначе будет паника.
                      +1
                      Кстати, советую кодировку для пользователей системы выбрать UTF-8. Меньше будет проблем с PostgreSQL.
                        +1
                        work_mem в 1/20 RAM;

                        Зачем так много? Какой-нибудь неправильный запрос и в своп уйдете.

                        The normal guidance for work_mem is to consider how much free RAM is around after shared_buffers is allocated (the same OS caching size figure needed to compute effective_cache_size), divide by max_connections, and then take a fraction of that figure; a half of that would be an aggressive work_mem value. In that case, only
                        if every client had two sorts active all at the same time would the server be likely
                        to run out of memory, which is an unlikely scenario.
                          +1
                          Значение этого параметра обсуждаемо. Думаю, нужно поэкспериментировать.
                            0
                            Используя этот параметр надо понимать для чего выставлять и какой объем, если система использует какой нить ORM то не обязательно делать его большим если же есть «OLAP» выборки… то малое значение запросто увеличит время выполнения в сотни, а то и в тысячи раз по той же причине.

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

                            fsync — держать включенным или выключенным зависит уже от архитектуры системы, и обширного числа параметров… Хотя с отключенным fsync жизнь есть!
                              0
                              Здесь вообще все спорно, кроме fsync — без него не работает партицирование.
                            +1
                            думаю вот это поможет в дальнейшем изучении постгреса
                              +1
                              А это и есть книга Алексея Васильева «Работа с Postgresql. Настройка, масштабирование», которая упоминается в моей статье. Также я написал, почему не согласен с некоторыми параметрами.
                                +1
                                а, ну упоминаемую книгу я не читал, поэтому не знал, что Вы не согласны с тезисами статьи.
                                И так с помощью нее я постгрес под 1С затачивал. Смог добиться неплохого ускорения производительности.
                                  +2
                                  Первоначально эти рекомендации и мне принесли неплохое ускорение. Но на больших запросах постгре захлебывался, оперативки под один процесс понадобилось больше, чем 1/8 от всего объема.
                                    +1
                                    спасибо, учту
                              0
                              не понятно, какой результат всей этой работы? на сколько выросла производительность — 1%? 10%? 100%? 1000%? может достаточно было поставить всё по умолчанию и разницы бы никто не заметил?
                                +1
                                Конечно, если подходить к вопросу более педантично, нужно было бы замерять. Но совершенно точно могу сказать, что ускорение производительности очень существенное. Статистика за сутки строится секунд за 5 максимум (и тормозится уже прострением графика, а не выборкой из БД), при том что размер мастер-таблицы перешагнул за 100млн записей. До произведенных действий по разгону скрипт работал минут 20 и выходил по таймауту.
                                +3
                                Судя по max_fsm_pages у вас PostgreSQL 8.3 или более ранняя версия. Я бы для новых установок рекомендовал 9.0, текущая версия 9.0.4 достаточно стабильная для использования в production.

                                Советы, приведенные вами (со ссылкой на книгу) очень неоднозначны, для того, чтобы менять tuple_cost/page_cost в оптимизаторе нужно четко представлять себе, что они меняют, выбор этих параметров сильно зависит от соотношения доступная RAM/размер базы данных и скорость произвольного доступа к устройству хранения данных.

                                Хороший обзор тюнинга сервера приведен в книжке Грэга Смита (Greg Smith):
                                www.packtpub.com/postgresql-90-high-performance/book

                                Статья Грэга в PostgreSQL Wiki:
                                wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
                                  +1
                                  У меня как раз версия 9.0.4 (в статье это указано). Это просто была цитата из книги.
                                  Спасибо за ссылки, обязательно изучу этот материал. На данный момент скрость работы БД меня вполне устраивает, но как только появится необходимость, буду тюнить дальше. Тогда, возможно, появится вторая часть статьи.
                                  +1
                                  Скажите, а почему бы не генерировать «на год вперед» таблицы, а проверять существование таблицы прямо в триггере и, при необходимости, создавать ее? Имена таблиц у вас в определенном формате, т.к. скрипт по крону, то производительность высокая, вероятно, не нужна и на тормоза инсерта (может, все ваше if'ы сработают быстрее, конечно, не тестил) будет пофиг и можно использовать что-то вроде:
                                  CREATE TABLE measure_data_master(
                                       measure_time TIMESTAMP NOT NULL DEFAULT NOW(),
                                       message TEXT
                                  ); 
                                  


                                  затем триггер:

                                  CREATE OR REPLACE FUNCTION measure_insert_trigger() RETURNS TRIGGER AS $$
                                  DECLARE
                                      tbl_name TEXT;
                                      d_start DATE;
                                      d_stop DATE;
                                  BEGIN
                                      SELECT INTO tbl_name TO_CHAR(DATE_TRUNC('week', NEW.measure_time), '"measure_data_y"YYYY"m"MM"d"DD');
                                      IF NOT EXISTS(SELECT * FROM pg_tables where tablename = tbl_name) THEN
                                          SELECT INTO d_start TO_CHAR(DATE_TRUNC('week', NEW.measure_time), 'YYYY-MM-DD');
                                          SELECT INTO d_stop TO_CHAR(DATE_TRUNC('week', NEW.measure_time + INTERVAL '6 DAYS'), 'YYYY-MM-DD');
                                          EXECUTE 'CREATE TABLE '||tbl_name||'(CHECK (measure_time >= '''||d_start||''' AND measure_time < '''||d_stop||''')) INHERITS(measure_data_master);';
                                          EXECUTE 'CREATE INDEX '||tbl_name||'_key ON '||tbl_name||'(measure_time);';
                                      END IF;
                                      EXECUTE 'INSERT INTO '||tbl_name||' VALUES ('''||NEW.measure_time||''','''||NEW.message||''');';
                                      RETURN NULL;
                                  END;
                                  $$ LANGUAGE plpgsql;
                                  
                                  CREATE TRIGGER insert_measure_trigger BEFORE INSERT ON measure_data_master FOR EACH ROW EXECUTE PROCEDURE measure_insert_trigger(); 
                                  


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

                                  Да, и еще. Чтобы "… серверу БД при запросе придется шерстить не всю большую таблицу, а несколько маленьких, попавших в выбранный временной промежуток..." надо выставить параметр constraint_exclusion = ON.
                                    0
                                    Отличная идея, спасибо!
                                    Но в моем случае это менее предпочтительно, т.к. скрипт инсертит за каждый свой запуск довольно много строк (и предполагается увеличение), так что придется делать так, как я описал в статье. Ну, для полной автоматизации, можно также скрипт, который таблицы создает, в крон засунуть:)

                                    По constraint_exclusion = ON вы правы. У меня так и есть, но вот забыл об этом упомянуть. Сейчас подредактирую.
                                      0
                                      Было бы интересно, если бы вы привели результаты сравнения (учитывая ваши немалые объемы данных) способа, подобного моему, и вашего. Просто ради интереса :) Я сам буду рад встать на путь истинный.
                                        0
                                        Ну, вроде б мой побыстрее должен быть. Вопрос только в том, существенно или нет. Релиз на носу, экспериментировать немного боязно. Я проверю ваш способ при первой возможности.
                                          0
                                          Ну, если выдастся вариант, вы апдейтните топик, а я уж в избранном послежу. Спасибо, удачного вам релиза! :)
                                  0
                                  postgresql.leopard.in.ua/ — добавьте ссылочку там где про книгу упоминаете, чтоб понятно было что за книга
                                    0
                                    > max_connection = 256;
                                    а вы уверены что это хотябы рядом стояло с оптимизацией???

                                    возьмите табличку на 100 000 000 записей или более, чтоб она весила больше Shared Memory раза в 2-3. и сделайте параллельно 256 выборок по индексу с фильтром и лимитом в сотню строк… т.е. обеспечте выборку со всех частей таблицы(в реальной жизни это почти всегда так)

                                    как вы думаете, как скоро вы получите результат?
                                      0
                                      Прежде чем писать столь эмоциональные посты, рекомендую вникнуть в контекст, а не вырывать цитаты из цельного текста.
                                        0
                                        я со всем согласен, везде все параметры рассчитаны в зависимости от объёма памяти и др. аттрибутов, кроме max_connection… у вас max_connection зависит от кол-ва кроновых заданий, а нет от числа процессоров, скоростных характеристик дисковой подсистемы и т.д.
                                          0
                                          Да, это так, пришлось исходить из конкретных условий конкретной задачи. Если вы мне поможете скорректировать параметры постгре под мою задачу, буду благодарен.
                                            +1
                                            V — объём базы
                                            S — объём разделяемой памяти
                                            K — средний объём данных на запрос

                                            m — % базы в памяти (S*100)/V
                                            d — % базы на диске V — m

                                            R — средний объём данных считанных с диска на запрос (K*d)/100

                                            — при R близком к 0 можно ставить max_connection = 256, но если в запросах нет длительных сортировок и агрегаций, либо число процессоров близко к max_connection
                                            — при R 2кб и более нужно искать золотую середину. как правило чем меньше параллельных запросов, тем быстрее. всё зависит от кол-ва дисков
                                              0
                                              У меня имеется три вида запросов:
                                              1) много мелких типа INSERT (то, что делает скрипт в кроне)
                                              2) средний типа SELECT (порядок 10 в 4 степени строк, строки по 8 полей), происходит редко, когда пользователь смотрит статистику
                                              3) большой типа SELECT (порядок 10 в 5 степени строк, строки по 8 полей), происходит еще реже, когда пользователь смотрит статистику
                                              Как при таком раскладе искать золотую середину?
                                        –1
                                        В случае insert и update это 100% дисковая операция, поэтому множество параллельных даже мало пишущих запросов начнут драться за головку диска
                                          0
                                          Да, еще забыл добавить, что меньше 256 соединений скрипту просто не хватает. Как следствие возникают «дыры» в статистике, и разрастается лог.
                                            0
                                            для этого есть pgbouncer или др. пулер
                                          0
                                          Классная статья.
                                          НО почему так не любим родной язык? Какой оунер?
                                          owner — владелец, хозяин.
                                            0
                                            Спасибо.
                                            Родной язык очень даже люблю:) Это скорее влияние профессии — когда технические термины не переводишь в разговорной речи, и со временем начинаешь воспринимать их уже как часть если не речи, то сленга.
                                              0
                                              /del
                                              0
                                              CREATE TABLE measure_data_y2011m06d06 (CHECK(measure_time = DATE '2011-06-06' AND measure_time DATE '2011-06-13')
                                              ) INHERITS (measure_data_master);

                                              CHECK(measure_time = DATE '2011-06-06' AND measure_time DATE '2011-06-13')

                                              Мне кажется или тут закралась ошибка?
                                                0
                                                Совершенно верно. Видимо, парсер съел знаки < и >.
                                                В первом случае >=, а во втором <.

                                              Only users with full accounts can post comments. Log in, please.